GetSchemaTable problem

[ewhitchu2]

I am using VS2008 with .NET Data Provider V2 to examine schema of tables as part of a database conversion project.
A table with a Primary Key constraint does not seem to be recognised as such by the GetSchemaTable call with CommandBehavior.KeyInfo set. The Connectivity manual indicates that two relevant columns IsUnique and IsKeyColumn will return true in this case. In fact the second column is called IsKey. Both return false in all cases.

Below is a summary of my code. What am I missing?

I use the following (C#):
.......
string sqlString = "SELECT FIRST 1 * FROM " + tableName;
if (ingConn.State != ConnectionState.Open)
{
ingConn.Open();
}

dataT = null;
IngresCommand cmd = new IngresCommand(sqlString, ingConn);
IngresDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
// now build data table from reader
dataT = rdr.GetSchemaTable();
rdr.Close();
rdr.Dispose();
.............
bool isKey = dataT.Rows[0].Field<bool>("IsKey");
bool isUnique = dataT.Rows[0].Field<bool>("IsUnique");
.............

[ewhitchu2]

Sorry but I have realised that there was some code missing that solved it. I lost the iteration through the table, looking at each column, when I moved some code around.

Problem should be solved now!

[ewhitchu2]

checking columns which are part of a PRIMARY KEY constraint shows the IsUnique and IsKey columns for them as false. I display the DataTable using debug in VisualStudio, and the columns show false, even though using VDBA shows the primary key in the properties tab, and a copydb generates the code below in copy.in (a real case):

alter table object_auth add constraint pk__object_auth PRIMARY KEY(originator_id, object_id)
GetSchemaTable shows IsKey and IsUnique for both columns as false.

[thoda04]

The Ingres .NET Data Provider does try to get the primary key information from iikeys and iialt_columns tables in the Ingres catalog. Can you list your CREATE TABLE syntax for the object_auth table for the relevant columns so we can duplicate the problem?

Thanks,
Dave

[ewhitchu2]

Attached is the copy.in for the table, generated by copydb. The output .ingres file itself is a bit big so I have not included the data.

The table and the key are represented in the two views you mention.

For the time being I am using a combination of the iicolumns key_sequence field (>0) and the unique_rule from iitables to determine if a Primary Key constraint should be generated with its columns for a SQL Server create table command to duplicate the table in SQL Server. In other words I ignore any PRIMARY KEY constraint and work from what the modify command has generated.

[thoda04]

Thank you for the table definition. The bug in the data provider is initiated by the "FIRST 1" clause. Without the clause the IsKey correctly has a value of true for the originator_id and object_id to indicate that each column is participates in the primary key of the table. The IsUnique value correctly remains false since, while the combination of the two columns is unique, there is no definition that states each individual column is unique.

The only workarounds at the moment are to not use the "FIRST n" clause in the SELECT statement or to use your workaround of directly accessing the catalog tables.

Dave

[ewhitchu2]

Dave,

Many thanks. That explains my problem neatly and yes I can now work around it.