Page 1 of 1
SetSchemaInfo(stColumns,...) returns no records
Posted: Mon 06 Jul 2009 03:25
by ChrisB
I am using the latest dbExpress driver for SQL Server (DbxSda 4.45.16) but am constrained to using Delphi 6 for the current project.
I need to read metadata for the fields (columns) of the tables that I'm dealing with. I am doing this as below, this being a trivial port from another project of mine which [successfully] used C++, RAD Studio 2007 and CodeGear's dbExpress driver for SQL Server.
The code below does not generate any errors, it just causes EOF to be True and, hence, no metadata to be read.
I have stepped into Borland's debug code but it all looks normal and I hit dead ends when their code accesses the driver interface (apparently).
Code: Select all
SQLDataSet.SetSchemaInfo( stColumns, sTableName, '' );
SQLDataSet.Active := True;
try
SQLDataSet.First;
iFldCount := SQLDataSet.Fields.Count;
while not SQLDataSet.Eof do
begin
SQLDataSet.Next;
end;
finally
SQLDataSet.Active := False;
end;
SQLDataSet.SetSchemaInfo( stNoSchema, sTableName, '' );
Further problem with metadata retrieval
Posted: Tue 07 Jul 2009 01:41
by ChrisB
I have found a further problem with metadata not being returned:
I have been using TClientDataSet.Append; this call itself succeeds.
But, when doing TClientDataSet.ApplyUpdates(-1) I get "Incorrect syntax near ''" and "Invalid column name ''" EMSError exceptions.
Tracking the calls through the call stack, I see that the calls pass up through various layers to TCustomSQLDataSet.PSExecuteStatement which contains a SQL string parameter "insert into ...". Examining the SQL shows that the fields are undelimited by any QuoteChar. Further examination shows that when TSQLConnection.GetQuoteChar is called it returns a space character! (I can't debug into the FSQLMetadata.getOption function which returns the incorrect QuoteChar.)
Since some tables in this database have fields that contain spaces, the fields absolutely require delimiting. I am unable to change the field names; it is a legacy database which does not belong to me.
devart, please help with these issues. If they can't be resolved then I will be forced to drop the dbExpress technology (and your driver) and go for an ADO solution. I really don't want this because, as I have proven, performance will suffer.
Also:
Not sure if it was relevant in my previous "SetSchemaInfo" post but I am using SQL Server Express 2005.
Posted: Mon 13 Jul 2009 11:20
by Challenger
Please specify the value of the sTableName variable. Please also execute the following statement
Code: Select all
select * from sysobjects where type in ('U')
and find your table in the list.
Posted: Tue 14 Jul 2009 06:37
by ChrisB
The value of sTableName was 'dbo.'. After dropping the 'dbo.' schema name prefix, the metadata records are now able to be read.
Running the query showed that the values in the "name" field of sysobjects do not include the schema name prefix. (Well done with your post, it pointed me straight at the issue.)
Interestingly, I originally added the schema name prefix when working with RAD Studio 2007 which has a bug whose work-around requires the addition of the prefix.
Thanks for your help.
Comment:
Having read the metadata records I see that the name/value pairs are different to those that I read in RAD Studio 2007 using the Borland dbExpress driver. What I actually need from the metadata is to know whether a field is an autonumber/identity field. In 2007 each field has an associated "IsAutoIncrement" metadata record. With your driver this is indicated when the COLUMN_TYPE record has a value of 13. (This was not very obvious and took quite some to work out. I expected it to have been defined as either a COLUMN_DATATYPE or a combination of COLUMN_DATATYPE and COLUMN_SUBTYPE. COLUMN_SUBTYPE was always zero for every record read, so this is no use.) It is slightly frustrating that the metadata varies from driver to driver but, other than some extra work, this is readily fixable.
Incidentally, if anybody knows of another way of determining whether a field is an autonumber/identity field, I would be interested to know.
Second Issue:
As for the other issue, I still cannot get the QuoteChar to be anything other than a space. Any help to fix this is appreciated.
Posted: Mon 20 Jul 2009 10:46
by Dimon
To use QuoteChar you sould use the TCRSQLConnection component and set the UseQuoteChar parameter, like this:
Code: Select all
CRSQLConnection.Params.Values['UseQuoteChar'] := 'True';