FireDAC problem with MSSQL
FireDAC problem with MSSQL
Folks
We have an issue using FireDAC on Microsoft SQL Server 2016, in that we have a case sensitive ( not the insensitive default ) server and database and when we setup our .Params on our FDConnection and then call Connected := True we get an exception:
Invalid object name SYS.SYSDATABASES
Which, we assume is because our SYS.SYSDATABASES is actually sys.sysdatabases.
Surely, there is a way to override this somehow.
Has anyone ( Marco Cantù, Team B ) experienced this and know of a good solution or work around?
Thanks
Folks
We have an issue using FireDAC on Microsoft SQL Server 2016, in that we have a case sensitive ( not the insensitive default ) server and database and when we setup our .Params on our FDConnection and then call Connected := True we get an exception:
Invalid object name SYS.SYSDATABASES
Which, we assume is because our SYS.SYSDATABASES is actually sys.sysdatabases.
Surely, there is a way to override this somehow.
Has anyone ( Marco Cantù, Team B ) experienced this and know of a good solution or work around?
Thanks
Why on earth do you have a case sensitive server, would be my first response, but that is hardly helpful.
ReplyDeleteSecondly, I find no uppercase SYSDATABASES in the Delphi libs, but I do find:
Data.DBXMSSQLMetaDataReader.pas(288): Result := 'SELECT name FROM master..sysdatabases';
in
function TDBXMsSqlMetaDataReader.GetSqlForCatalogs: string; override;
which means you in theory can inherit and override the selection.
However - are you 100% sure that the user you log in with, has access rights to sysdatabases?
Lars Fosdal We have found uppercased SYS.SYSCOLUMNS for example, just do a search in the code it's there
ReplyDeleteTony Danby syscolumns, sysobjects, systypes, sysindexes, sysindexkeys are all lowercase in source\data\dbx\Data.DBXMSSQLMetaDataReader.pas in both Berlin and Tokyo. Its uppercase in the Informix and Sybase drivers.
ReplyDeleteTDBXDatabaseMetaData has a property SupportsUpperCaseIdentifiers:Boolean;
I searched for "uppercase", and there is a enumerated type IDENTIFIEROption , containing idMakeUpperCase / idMakeLowerCase in Data.DBCommon.pas. Perhaps it possible to override whatever is the default somewhere in the parameterization during create/init/config?
Lars Fosdal We don't see that at all; There is just one or two places where it is lowercase, the rest is upper cased.
ReplyDeleteIf it's not upper cased why are we seeing a failure for SYS.SYSDATABASES
Why are the names not lower cased as they would be correct in both case insensitive and sensitive then?
Lars Fosdal They are using FireDAC not dbExpress ;-)
ReplyDeletejeff weir That's correct. Not only that, but it worked on either 10.1 and we think even 10.2, but it's broken in 10.2.1 ..... Sorry Lars Fosdal should have stated that from the start
ReplyDeleteAlthough I agree with Lars that it will be difficult to find another one with the same server configuration, you might try to experiment with the Connection Definition Parameters MetaCaseIns and MetaCaseInsCat:
ReplyDeletehttp://docwiki.embarcadero.com/RADStudio/Tokyo/en/Connect_to_Microsoft_SQL_Server_(FireDAC)#Connection_Definition_Parameters
Uwe Raabe Myself and the other developer have both messed around with those and got nowhere
ReplyDeleteAre you using the same ODBC Client driver?
ReplyDeleteMarco Cantù, Uwe Raabe The sys. meta tables have always been lower cased, so this is a BUG. I shouldn't have to specify what case they are in, if they were in lowercase in the code they would work for both case sensitive and case insensitive databases / servers
ReplyDeletejeff weir We havn't specified the driver, we are just using ( trying to ) the native MSSQL
ReplyDeleteTony Danby
ReplyDeleteMy bad on the DBX file references. I was blinded by finding search results.
I only have 101.2 and 10.2 - not 10.2.1 installed. If you find uppercase references, that's definitively a 10.2.1 changes.
Do you use SQLNCLI ?
Can there be ODBC parameterization that can affect the queries?
Lars Fosdal We are not specifying anything, We are just using and FDConnection set to MSSQL without anything else specified
ReplyDeleteI have pasted my connection Info below if that helps:
================================
Connection definition parameters
================================
ConnectionDef=MSSQL_EXPRESS_SQL2014
DriverID=MSSQL
Server=EXPRESS\SQL2014
Database=master
User_Name=sa
Password=***
MetaDefSchema=dbo
MetaDefCatalog=master
ExtendedMetadata=True
Name=MSSQL_EXPRESS_SQL2014
================================
FireDAC info
================================
Tool = RAD Studio 10.2
FireDAC = 16.0.0 (Build 88974)
Platform = Windows 32 bit
Defines = FireDAC_NOLOCALE_META;FireDAC_MONITOR
================================
Client info
================================
Loading driver MSSQL ...
Loading odbc32.dll driver manager
Creating ODBC environment handle
Variable [ODBCDriver]: [SQL Server Native Client 11.0]
================================
I can only find one reference to SYS.DATABASES in TFDPhysMSSQLConnection.InternalSetMeta, but no SYS.SYSDATABASES - neither upper nor lower case.
ReplyDeleteTony Danby We use SQLNCLI11 (installs separately on client PC). It gives a great performance boost. Judging by your client info - so do you.
ReplyDeleteThere doesn't seem to be any overrides possible on ODBC level.
docs.microsoft.com - Using Connection String Keywords with SQL Server Native Client
If you can prove that a small self contained application works on 10.1.x or 10.2, but not on 10.2.1 - you should file a QP issue.
Tony Danby Another idea - have you tried using the FireDAC Monitor tool to see what is actually sent to the database?
ReplyDeleteUwe Raabe From what I see looking at the source this morning, the MSSQL FireDAC driver does the low-level stuff with ODBC. That's why I wondered about which client was being used.
ReplyDeleteYou can also trace on ODBC level. knowledgebase.progress.com - Progress KB - Everything you want to know about ODBC tracing
ReplyDeleteIf you have the 10.1 or 10.2 source somewhere compare the TFDPhysMSSQLConnection.InternalCreateMetadata function in FireDAC.Phys.MSSQL.pas to the one in 10.2.1. Has it changed?
ReplyDeleteThanks everyone. I am currently on the road. I will test all of these suggestions in the morning
ReplyDeleteHowever I still hold the opinion that it is a bug that needs fixing
ReplyDeleteWithout a QP report you might have to wait some time for a fix.
ReplyDeleteLars Fosdal because the DAC should not interfere with case. Oh wait: IBX fails on Turkish systems because exactly that. gist.github.com - IBDataSetTestsUnit.pas
ReplyDeleteLars Fosdal Been snowed under with a ton of issues on another project today; but I will try all your suggestions ( esp. FDAC Monitor ) in the next day. Thank you !
ReplyDeleteUwe Raabe Well as soon as I get around to proving it consistently fails on a small test harness style app, then I will raise a QP ( BTW; I have never raised a QP could you please give me some pointers ? )
ReplyDeleteJeroen Wiert Pluimers Interesting ! thank you
ReplyDeletejeff weir My work colleague runs his Delphi on a Mac book pro inside several virtuals, so he could do it. I'll ask him thanks for the suggestion
ReplyDeleteTony Danby no problem. Note it contains the XE8 diff for fixing it as well: https://gist.github.com/jpluimers/ecc7ded715369028a5142bdc4f601327#file-ibx-xe8-diff-txt
ReplyDelete