[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Command Builder fails as missing owner/schema parameter

Clive Morgan

8/15/2002 5:22:00 PM

Ran same OdbcCommandBuilder test against a SQLServer data
source and against a Caché data source. The Caché test
fails because of a call to SQLPrimaryKeysW with NO value
specified for the 'Schema/Owner' input parameter. The
SQLServer test succeeds because SQLPrimaryKeysW is NOT
called!!!

Example table was created in both databases using :-
create table user1.table1
(field1 varchar(10) primary key,
field2 varchar(10))

Test performed using following C# code :-

OdbcCN = new OdbcConnection(connectonString)

OdbcDA = new OdbcDataAdapter();
OdbcDA.SelectCommand = new OdbcCommand(
"select * from user1.table1", OdbcCN);

OdbcCB = new OdbcCommandBuilder(OdbcDA);

OdbcCN.Open();
TheCommand = OdbcCB.GetUpdateCommand
().CommandText.ToString();

The failure is caused by a call to SQLPrimaryKeysW
which values the TableName input parameter as "table1"
and values the SchemaName/OwnerName input parameter as
"" rather than as "user1".

As there is no table called table1 (it's user1.table1),
no Primary keys are returned to the OdbcCommandBuilder
objects. Hence the error.

I have run this test against SQLServer using the
SQLServer ODBC driver and against Caché using the Caché
ODBC driver. The call to SQLPrimaryKeysW is only made
when using the Caché ODBC data source.

Why is SQLPrimaryKeysW being called and why is there no
value specified in the SchemaName input parameter?

The ODBC trace log shows that SQLColAttributeW is called
when connected to the SQLServer data source and that
SQLColAttributesW is called when connected to the
Caché ODBC data source. However detailed analysis has
shown the the same information was provided including
the vital information on which field in NULLABLE.

The test against the SQLServer ODBC data source does
not fail showing that there is no need to call
SQLPrimaryKeysW.

Is this a feature or a bug of the ODBC.NET provider.
It currently means that we are unable to use multiple
schema in the same catalog!!!! The only alternative is
NOT to use the OdbcCommandBuilder object.
5 Answers

Bob Beauchemin

8/15/2002 8:13:00 PM

0

I've noticed this too (and reported it). Using the Odbc data provider with
unsupported drivers (and maybe any drivers other than SQL Server) appears to
truncate the table name at a certain number of characters.

Bob Beauchemin
http://staff.develo...


"Clive Morgan" <cmorgan@intersys.com> wrote in message
news:249001c2446f$a071b3e0$36ef2ecf@tkmsftngxa12...
Ran same OdbcCommandBuilder test against a SQLServer data
source and against a Caché data source. The Caché test
fails because of a call to SQLPrimaryKeysW with NO value
specified for the 'Schema/Owner' input parameter. The
SQLServer test succeeds because SQLPrimaryKeysW is NOT
called!!!

Example table was created in both databases using :-
create table user1.table1
(field1 varchar(10) primary key,
field2 varchar(10))

Test performed using following C# code :-

OdbcCN = new OdbcConnection(connectonString)

OdbcDA = new OdbcDataAdapter();
OdbcDA.SelectCommand = new OdbcCommand(
"select * from user1.table1", OdbcCN);

OdbcCB = new OdbcCommandBuilder(OdbcDA);

OdbcCN.Open();
TheCommand = OdbcCB.GetUpdateCommand
().CommandText.ToString();

The failure is caused by a call to SQLPrimaryKeysW
which values the TableName input parameter as "table1"
and values the SchemaName/OwnerName input parameter as
"" rather than as "user1".

As there is no table called table1 (it's user1.table1),
no Primary keys are returned to the OdbcCommandBuilder
objects. Hence the error.

I have run this test against SQLServer using the
SQLServer ODBC driver and against Caché using the Caché
ODBC driver. The call to SQLPrimaryKeysW is only made
when using the Caché ODBC data source.

Why is SQLPrimaryKeysW being called and why is there no
value specified in the SchemaName input parameter?

The ODBC trace log shows that SQLColAttributeW is called
when connected to the SQLServer data source and that
SQLColAttributesW is called when connected to the
Caché ODBC data source. However detailed analysis has
shown the the same information was provided including
the vital information on which field in NULLABLE.

The test against the SQLServer ODBC data source does
not fail showing that there is no need to call
SQLPrimaryKeysW.

Is this a feature or a bug of the ODBC.NET provider.
It currently means that we are unable to use multiple
schema in the same catalog!!!! The only alternative is
NOT to use the OdbcCommandBuilder object.

Mike LaRocca

8/15/2002 10:05:00 PM

0

I thought any ODBC 2.5+ compliant driver would
be "supported".

Why would the call to SQLPrimaryKeysW strip off the
tableowner/schema from the table name? Does this
function even need to be called? Seems like a bug in
ODBC.NET's OdbcCommandBuilder object.

Any thoughts from the MS staff?

Thanks a lot,

-Mike



>-----Original Message-----
>I've noticed this too (and reported it). Using the Odbc
data provider with
>unsupported drivers (and maybe any drivers other than
SQL Server) appears to
>truncate the table name at a certain number of
characters.
>
>Bob Beauchemin
>http://staff.develo...
>
>
>"Clive Morgan" <cmorgan@intersys.com> wrote in message
>news:249001c2446f$a071b3e0$36ef2ecf@tkmsftngxa12...
>Ran same OdbcCommandBuilder test against a SQLServer data
>source and against a Caché data source. The Caché test
>fails because of a call to SQLPrimaryKeysW with NO value
>specified for the 'Schema/Owner' input parameter. The
>SQLServer test succeeds because SQLPrimaryKeysW is NOT
>called!!!
>
>Example table was created in both databases using :-
> create table user1.table1
> (field1 varchar(10) primary key,
> field2 varchar(10))
>
>Test performed using following C# code :-
>
> OdbcCN = new OdbcConnection(connectonString)
>
> OdbcDA = new OdbcDataAdapter();
> OdbcDA.SelectCommand = new OdbcCommand(
> "select * from user1.table1", OdbcCN);
>
> OdbcCB = new OdbcCommandBuilder(OdbcDA);
>
> OdbcCN.Open();
> TheCommand = OdbcCB.GetUpdateCommand
>().CommandText.ToString();
>
>The failure is caused by a call to SQLPrimaryKeysW
>which values the TableName input parameter as "table1"
>and values the SchemaName/OwnerName input parameter as
>"" rather than as "user1".
>
>As there is no table called table1 (it's user1.table1),
>no Primary keys are returned to the OdbcCommandBuilder
>objects. Hence the error.
>
>I have run this test against SQLServer using the
>SQLServer ODBC driver and against Caché using the Caché
>ODBC driver. The call to SQLPrimaryKeysW is only made
>when using the Caché ODBC data source.
>
>Why is SQLPrimaryKeysW being called and why is there no
>value specified in the SchemaName input parameter?
>
>The ODBC trace log shows that SQLColAttributeW is called
>when connected to the SQLServer data source and that
>SQLColAttributesW is called when connected to the
>Caché ODBC data source. However detailed analysis has
>shown the the same information was provided including
>the vital information on which field in NULLABLE.
>
>The test against the SQLServer ODBC data source does
>not fail showing that there is no need to call
>SQLPrimaryKeysW.
>
>Is this a feature or a bug of the ODBC.NET provider.
>It currently means that we are unable to use multiple
>schema in the same catalog!!!! The only alternative is
>NOT to use the OdbcCommandBuilder object.
>
>
>
>
>.
>

laled

8/20/2002 2:15:00 AM

0

odbc.net first calls SQLColAttribute for every column
after a Execute/Prepare statement to find out about the
primary keys. (This call is actually SQLColAttributes for
older drivers, < version 3) If the driver returns this
information from SQLColAttribute (SQL Server does), then
no call to SQLPrimaryKeys is needed.
If it doesn't, then we try to parse the SQL statement to
find the table names, and find about the primary keys
using SQLPrimaryKeys.
This call in your case is failing probably because we
don't know the schema name. If you want your odbc driver
to work similar to SQL Server's, provide correct
responses to SQLColAttribute(s) calls that are made prior
to SQLPrimaryKeys calls.

Thanks
Lane
>-----Original Message-----
>I thought any ODBC 2.5+ compliant driver would
>be "supported".
>
>Why would the call to SQLPrimaryKeysW strip off the
>tableowner/schema from the table name? Does this
>function even need to be called? Seems like a bug in
>ODBC.NET's OdbcCommandBuilder object.
>
>Any thoughts from the MS staff?
>
>Thanks a lot,
>
>-Mike
>
>
>
>>-----Original Message-----
>>I've noticed this too (and reported it). Using the Odbc
>data provider with
>>unsupported drivers (and maybe any drivers other than
>SQL Server) appears to
>>truncate the table name at a certain number of
>characters.
>>
>>Bob Beauchemin
>>http://staff.develo...
>>
>>
>>"Clive Morgan" <cmorgan@intersys.com> wrote in message
>>news:249001c2446f$a071b3e0$36ef2ecf@tkmsftngxa12...
>>Ran same OdbcCommandBuilder test against a SQLServer
data
>>source and against a Caché data source. The Caché test
>>fails because of a call to SQLPrimaryKeysW with NO value
>>specified for the 'Schema/Owner' input parameter. The
>>SQLServer test succeeds because SQLPrimaryKeysW is NOT
>>called!!!
>>
>>Example table was created in both databases using :-
>> create table user1.table1
>> (field1 varchar(10) primary key,
>> field2 varchar(10))
>>
>>Test performed using following C# code :-
>>
>> OdbcCN = new OdbcConnection(connectonString)
>>
>> OdbcDA = new OdbcDataAdapter();
>> OdbcDA.SelectCommand = new OdbcCommand(
>> "select * from user1.table1", OdbcCN);
>>
>> OdbcCB = new OdbcCommandBuilder(OdbcDA);
>>
>> OdbcCN.Open();
>> TheCommand = OdbcCB.GetUpdateCommand
>>().CommandText.ToString();
>>
>>The failure is caused by a call to SQLPrimaryKeysW
>>which values the TableName input parameter as "table1"
>>and values the SchemaName/OwnerName input parameter as
>>"" rather than as "user1".
>>
>>As there is no table called table1 (it's user1.table1),
>>no Primary keys are returned to the OdbcCommandBuilder
>>objects. Hence the error.
>>
>>I have run this test against SQLServer using the
>>SQLServer ODBC driver and against Caché using the Caché
>>ODBC driver. The call to SQLPrimaryKeysW is only made
>>when using the Caché ODBC data source.
>>
>>Why is SQLPrimaryKeysW being called and why is there no
>>value specified in the SchemaName input parameter?
>>
>>The ODBC trace log shows that SQLColAttributeW is called
>>when connected to the SQLServer data source and that
>>SQLColAttributesW is called when connected to the
>>Caché ODBC data source. However detailed analysis has
>>shown the the same information was provided including
>>the vital information on which field in NULLABLE.
>>
>>The test against the SQLServer ODBC data source does
>>not fail showing that there is no need to call
>>SQLPrimaryKeysW.
>>
>>Is this a feature or a bug of the ODBC.NET provider.
>>It currently means that we are unable to use multiple
>>schema in the same catalog!!!! The only alternative is
>>NOT to use the OdbcCommandBuilder object.
>>
>>
>>
>>
>>.
>>
>.
>

Clive Morgan

8/21/2002 2:25:00 PM

0

Hi Lane,

Thanks for the reply. I've got some more information to
share.

I've compared the ODBC traces produced when running
OdbcCB = new OdbcCommandBuilder(OdbcDA);
OdbcCN.Open();
TheCommand = OdbcCB.GetUpdateCommand
against the two ODBC data sources and can't find any
difference in the data returned by the SQLColAttribute(s)
calls

And without a knowledge of the ODBC.Net provider, I can't
tell
why SQLPrimaryKeysW might be called.

However, as the SQL statement prepared using SQLPrepare
contains the schema name, the ODBC.Net provider does know
the schema name so shouldn't it be using it when calling
SQLPrimaryKeysW?

Regards, Clive



>-----Original Message-----
>odbc.net first calls SQLColAttribute for every column
>after a Execute/Prepare statement to find out about the
>primary keys. (This call is actually SQLColAttributes for
>older drivers, < version 3) If the driver returns this
>information from SQLColAttribute (SQL Server does), then
>no call to SQLPrimaryKeys is needed.
>If it doesn't, then we try to parse the SQL statement to
>find the table names, and find about the primary keys
>using SQLPrimaryKeys.
>This call in your case is failing probably because we
>don't know the schema name. If you want your odbc driver
>to work similar to SQL Server's, provide correct
>responses to SQLColAttribute(s) calls that are made prior
>to SQLPrimaryKeys calls.
>
>Thanks
>Lane
>>-----Original Message-----
>>I thought any ODBC 2.5+ compliant driver would
>>be "supported".
>>
>>Why would the call to SQLPrimaryKeysW strip off the
>>tableowner/schema from the table name? Does this
>>function even need to be called? Seems like a bug in
>>ODBC.NET's OdbcCommandBuilder object.
>>
>>Any thoughts from the MS staff?
>>
>>Thanks a lot,
>>
>>-Mike
>>
>>
>>
>>>-----Original Message-----
>>>I've noticed this too (and reported it). Using the Odbc
>>data provider with
>>>unsupported drivers (and maybe any drivers other than
>>SQL Server) appears to
>>>truncate the table name at a certain number of
>>characters.
>>>
>>>Bob Beauchemin
>>>http://staff.develo...
>>>
>>>
>>>"Clive Morgan" <cmorgan@intersys.com> wrote in message
>>>news:249001c2446f$a071b3e0$36ef2ecf@tkmsftngxa12...
>>>Ran same OdbcCommandBuilder test against a SQLServer
>data
>>>source and against a Caché data source. The Caché test
>>>fails because of a call to SQLPrimaryKeysW with NO value
>>>specified for the 'Schema/Owner' input parameter. The
>>>SQLServer test succeeds because SQLPrimaryKeysW is NOT
>>>called!!!
>>>
>>>Example table was created in both databases using :-
>>> create table user1.table1
>>> (field1 varchar(10) primary key,
>>> field2 varchar(10))
>>>
>>>Test performed using following C# code :-
>>>
>>> OdbcCN = new OdbcConnection(connectonString)
>>>
>>> OdbcDA = new OdbcDataAdapter();
>>> OdbcDA.SelectCommand = new OdbcCommand(
>>> "select * from user1.table1", OdbcCN);
>>>
>>> OdbcCB = new OdbcCommandBuilder(OdbcDA);
>>>
>>> OdbcCN.Open();
>>> TheCommand = OdbcCB.GetUpdateCommand
>>>().CommandText.ToString();
>>>
>>>The failure is caused by a call to SQLPrimaryKeysW
>>>which values the TableName input parameter as "table1"
>>>and values the SchemaName/OwnerName input parameter as
>>>"" rather than as "user1".
>>>
>>>As there is no table called table1 (it's user1.table1),
>>>no Primary keys are returned to the OdbcCommandBuilder
>>>objects. Hence the error.
>>>
>>>I have run this test against SQLServer using the
>>>SQLServer ODBC driver and against Caché using the Caché
>>>ODBC driver. The call to SQLPrimaryKeysW is only made
>>>when using the Caché ODBC data source.
>>>
>>>Why is SQLPrimaryKeysW being called and why is there no
>>>value specified in the SchemaName input parameter?
>>>
>>>The ODBC trace log shows that SQLColAttributeW is called
>>>when connected to the SQLServer data source and that
>>>SQLColAttributesW is called when connected to the
>>>Caché ODBC data source. However detailed analysis has
>>>shown the the same information was provided including
>>>the vital information on which field in NULLABLE.
>>>
>>>The test against the SQLServer ODBC data source does
>>>not fail showing that there is no need to call
>>>SQLPrimaryKeysW.
>>>
>>>Is this a feature or a bug of the ODBC.NET provider.
>>>It currently means that we are unable to use multiple
>>>schema in the same catalog!!!! The only alternative is
>>>NOT to use the OdbcCommandBuilder object.
>>>
>>>
>>>
>>>
>>>.
>>>
>>.
>>
>.
>

laled

8/21/2002 11:40:00 PM

0

ODBC.Net calls SQLPrimaryKeysW when it doesn't have the
key information on a particular table. To see if you are
sending the key information or not, you can create an
OdbcCommand with the same select statement, call
ExecuteReader(CommandBehavior.KeyInfo), and then call
OdbcDataReader.GetSchemaTable on it and check to see if
the IsKey column is set properly. While there, also check
to see if the baseSchemaName and baseTableName are also
being reported properly. CommandBuilder uses this schema
table to build its DML statements.


This posting is provided "AS IS" with no warranties, and
confers no rights.

laled
>-----Original Message-----
>Hi Lane,
>
>Thanks for the reply. I've got some more information to
>share.
>
>I've compared the ODBC traces produced when running
> OdbcCB = new OdbcCommandBuilder(OdbcDA);
> OdbcCN.Open();
> TheCommand = OdbcCB.GetUpdateCommand
>against the two ODBC data sources and can't find any
>difference in the data returned by the SQLColAttribute(s)
>calls
>
>And without a knowledge of the ODBC.Net provider, I
can't
>tell
>why SQLPrimaryKeysW might be called.
>
>However, as the SQL statement prepared using SQLPrepare
>contains the schema name, the ODBC.Net provider does know
>the schema name so shouldn't it be using it when calling
>SQLPrimaryKeysW?
>
>Regards, Clive
>
>
>
>>-----Original Message-----
>>odbc.net first calls SQLColAttribute for every column
>>after a Execute/Prepare statement to find out about the
>>primary keys. (This call is actually SQLColAttributes
for
>>older drivers, < version 3) If the driver returns this
>>information from SQLColAttribute (SQL Server does),
then
>>no call to SQLPrimaryKeys is needed.
>>If it doesn't, then we try to parse the SQL statement
to
>>find the table names, and find about the primary keys
>>using SQLPrimaryKeys.
>>This call in your case is failing probably because we
>>don't know the schema name. If you want your odbc
driver
>>to work similar to SQL Server's, provide correct
>>responses to SQLColAttribute(s) calls that are made
prior
>>to SQLPrimaryKeys calls.
>>
>>Thanks
>>Lane
>>>-----Original Message-----
>>>I thought any ODBC 2.5+ compliant driver would
>>>be "supported".
>>>
>>>Why would the call to SQLPrimaryKeysW strip off the
>>>tableowner/schema from the table name? Does this
>>>function even need to be called? Seems like a bug in
>>>ODBC.NET's OdbcCommandBuilder object.
>>>
>>>Any thoughts from the MS staff?
>>>
>>>Thanks a lot,
>>>
>>>-Mike
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>I've noticed this too (and reported it). Using the
Odbc
>>>data provider with
>>>>unsupported drivers (and maybe any drivers other than
>>>SQL Server) appears to
>>>>truncate the table name at a certain number of
>>>characters.
>>>>
>>>>Bob Beauchemin
>>>>http://staff.develo...
>>>>
>>>>
>>>>"Clive Morgan" <cmorgan@intersys.com> wrote in message
>>>>news:249001c2446f$a071b3e0$36ef2ecf@tkmsftngxa12...
>>>>Ran same OdbcCommandBuilder test against a SQLServer
>>data
>>>>source and against a Caché data source. The Caché
test
>>>>fails because of a call to SQLPrimaryKeysW with NO
value
>>>>specified for the 'Schema/Owner' input parameter. The
>>>>SQLServer test succeeds because SQLPrimaryKeysW is NOT
>>>>called!!!
>>>>
>>>>Example table was created in both databases using :-
>>>> create table user1.table1
>>>> (field1 varchar(10) primary key,
>>>> field2 varchar(10))
>>>>
>>>>Test performed using following C# code :-
>>>>
>>>> OdbcCN = new OdbcConnection(connectonString)
>>>>
>>>> OdbcDA = new OdbcDataAdapter();
>>>> OdbcDA.SelectCommand = new OdbcCommand(
>>>> "select * from user1.table1", OdbcCN);
>>>>
>>>> OdbcCB = new OdbcCommandBuilder(OdbcDA);
>>>>
>>>> OdbcCN.Open();
>>>> TheCommand = OdbcCB.GetUpdateCommand
>>>>().CommandText.ToString();
>>>>
>>>>The failure is caused by a call to SQLPrimaryKeysW
>>>>which values the TableName input parameter as "table1"
>>>>and values the SchemaName/OwnerName input parameter as
>>>>"" rather than as "user1".
>>>>
>>>>As there is no table called table1 (it's
user1.table1),
>>>>no Primary keys are returned to the OdbcCommandBuilder
>>>>objects. Hence the error.
>>>>
>>>>I have run this test against SQLServer using the
>>>>SQLServer ODBC driver and against Caché using the
Caché
>>>>ODBC driver. The call to SQLPrimaryKeysW is only made
>>>>when using the Caché ODBC data source.
>>>>
>>>>Why is SQLPrimaryKeysW being called and why is there
no
>>>>value specified in the SchemaName input parameter?
>>>>
>>>>The ODBC trace log shows that SQLColAttributeW is
called
>>>>when connected to the SQLServer data source and that
>>>>SQLColAttributesW is called when connected to the
>>>>Caché ODBC data source. However detailed analysis has
>>>>shown the the same information was provided including
>>>>the vital information on which field in NULLABLE.
>>>>
>>>>The test against the SQLServer ODBC data source does
>>>>not fail showing that there is no need to call
>>>>SQLPrimaryKeysW.
>>>>
>>>>Is this a feature or a bug of the ODBC.NET provider.
>>>>It currently means that we are unable to use multiple
>>>>schema in the same catalog!!!! The only alternative
is
>>>>NOT to use the OdbcCommandBuilder object.
>>>>
>>>>
>>>>
>>>>
>>>>.
>>>>
>>>.
>>>
>>.
>>
>.
>