we are in process of migrating our VCL code from Delphi XE and dbXpress driver 4 to Delphi Berlin. I downloaded the trial version of latest dbXpress driver 7.3.5 to convert one of our projects to the new Delphi. So far I found out that I should use "dbo" in SchemaName where Delphi leaved it blank or used username instead "dbo". Or use parameter schema override. Okay, this seems to be known problem.
The more interesting thing is that I have a problem when opening a ClientDataset with custom SQL and get an error:
I used dbForge to trace the query and I found out that SQL Server was querried with the following SQL while opening the query:"Project ... raised exception class EMSError with message 'Invalid object name 'INFORMATION_SCHEMA.INDEXES'.'. "
Code: Select all
exec sp_executesql N'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CASE [UNIQUE] WHEN 1 THEN INDEX_NAME ELSE NULL END, CONVERT(BIT, [PRIMARY_KEY]), CONVERT(BIT, [UNIQUE]), CONVERT(BIT, 1) FROM INFORMATION_SCHEMA.INDEXES WHERE (TABLE_NAME = @P1 OR (1=2)) ORDER BY INDEX_NAME, ORDINAL_POSITION',N'@P1 nvarchar(16)',N'NastavitveKlient'
In Delphi, I have on datamodule TSQLConnection, some TSQLDatasets, TDatasetProviders, TClientDatasets... The problematic CDS is connected to dedicated TDatasetProvider with Options [poAllowCommandText, poUseQuoteChar] and UpdateMode upWhereKeyOnly. DatasetProvider is connected to dedicated TSQLDataset which has CommandType ctQuery, SchemaName dbo.
In ClientDataset connected to this chain the following code should be executed:
Code: Select all
with cdsNastKlient do begin DisableControls; try if Active then Close; CommandText := 'SELECT * FROM dbo.NastavitveKlient' + ^m + 'WHERE Upper(Klient) = ' + QuotedStr(UpperCase(FClientName)) + ^m + 'AND Upper(Koda) = ' + QuotedStr(UpperCase(FKoda)); try Open; First; ....
The exactly same code in Delphi XE and old (your) dbXpress driver (version 18.104.22.168) works normally, no problems there on same database, same server. Any ideas on how to resolve the problem?
Thank You in advance!