MSSQL lower case -> readonly

[duzenko]

I have a software that is supposed to function across a number of different sql servers
Right now I have it more or less working with Firebird, MySQL and SQLite
I am now tasked to add support of MS SQL
The problem is, my software (ported from dbaseIV) makes use of many TZQuery's Edit and Insert methods
When I try to run it with MSSQL, I immediately get a readonly error
I narrowed this error down to case sensitive sql queries
For example, when I have a TZQuery programmed with select * from AUTOTEXT it works (quick test)
But if it was coded with select * from autotext then it gives an error
I honestly hope ZeosLib is not designed to work this way. I suspect we need an UpperCase somewhere in ZDbcResultSetMetadata.pas/ZDbcDbLibMetadata.pas/ZDbcMetadata.pas/ZSelectSchema.pas. The sheer amount of code wrapped around Edit/Insert makes it very hard for anyone from outside to pinpoint the exact place
I can see in SQL Monitor queries flying to the server like

Code: Select all

2015-07-23 19:23:07 cat: Execute, proto: FreeTDS_MsSQL>=2005, msg: select c.colid, c.name, c.type, c.prec, c.scale, c.colstat, c.status, c.iscomputed from syscolumns c inner join sysobjects o on (o.id = c.id) where o.name COLLATE Latin1_General_CS_AS = 'AUTOTEXT' and c.number=0 order by colid

So, my question, can we somehow work around this case-sensitivity or is there maybe an option in the components to automatically uppercase or should I hope for a beta fix anytime soon? I would hate to need to hack through sources on my own risking losing upgrade compatibility

[marsupilami]

Hello duzenko,

which driver do you use for connecting? ODBC? FreeTDS? mssql?

The problem is that some servers that were based on the interface, MSSQL uses (dblib), did expect table names to be case sensitive. Also there are some places in Zeos that can expect table names to be in the correct case. I had a similar issue with PostgreSQL. If you want to give it a try I could point you to the place that I used to get the driver working as I expected.

With best regards,

Jan

[duzenko]

Hi Jan,

I'm using protocol FreeTDS_MsSQL>=2005 (same thing with mssql) and the appropriate dblib.dll (ntwdblib.DLL)
Server version is 2008 R2
Any help is greatly appreciated

[marsupilami]

Hello duzenko,

it is my experience that you will get editable fields if you provide information about the field source to the upper layers of Zeos. Take a look at the code of TZDBLibResultSet.Open in ZDbcDbLibResultSet.pas. It provides information about the fields to the component layer of Zeos. But it doesn't fill the members SchemaName, ColumnName and TableName of the TZColumnInfo structure.
For the PostgreSQL driver I implemented a way to fill in these members and got writable fields again. If you want to have a look at my implementation, you should start at TZPostgreSQLResultSet.Open in ZDbcPostgreSqlResultSet.pas.

Please don't use the ntwdblib.dll and the mssql driver - they are outdated and MS doesn't suport them anymore. Also you might want to try the ADO driver because it might implement these things already.

Let me know, if I can help you any further.

With best regards,

Jan

[duzenko]

Hello duzenko,

it is my experience that you will get editable fields if you provide information about the field source to the upper layers of Zeos. Take a look at the code of TZDBLibResultSet.Open in ZDbcDbLibResultSet.pas. It provides information about the fields to the component layer of Zeos. But it doesn't fill the members SchemaName, ColumnName and TableName of the TZColumnInfo structure.
For the PostgreSQL driver I implemented a way to fill in these members and got writable fields again. If you want to have a look at my implementation, you should start at TZPostgreSQLResultSet.Open in ZDbcPostgreSqlResultSet.pas.

Please don't use the ntwdblib.dll and the mssql driver - they are outdated and MS doesn't suport them anymore. Also you might want to try the ADO driver because it might implement these things already.

Let me know, if I can help you any further.

With best regards,

Jan

Thank you for getting back to me
I don't intent to use ntwdblib.dll - I simply tried it to make sure it does not work after I couldn't get the other one (FreeTDS) working
I tried ODBC/ADO and it almost works. It can write data regardless char case in sql query. The problem is that in RefreshParameters (ZDbcAdoUtils.pas) it gives an exception Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. after each write, which I have no idea how to fix. Apparently this exception is squashed but I still can't use this driver under debugging as it will just stop execution every second

I am pretty sure that SchemaName, ColumnName and TableName are properly populated by the dblib driver.
Attached picture shows exact moment when it happens

Untitled.png

The readonly flag is set a little below this code where it check if TableColumns.IsNull(24) (null in case of lower char queries )

[marsupilami]

Hello duzenko,

I am sorry for my late answer - work has kept me from responding. You are on the right path. Yesterday I did some debugging and found that the culprit in this case is TZMsSqlDatabaseMetadata.UncachedGetColumns in ZDbcDbLibMetadata.pas. It tries to read meta data from the database using the table name that was used in the select statement. I attached a small patch that changes this behaviour. Could you please check, wether it solves your problem?
With best regards,

Jan

[duzenko]

Could you please check, wether it solves your problem?

Thanks a lot. The datasets are not readonly anymore

[marsupilami]

That is good to know. Today I saw that you opened a bug regarding date fields being treated as string fields. I did some checking abou t that one too. Could you please check in on the bug and let me know what you can do?
https://sourceforge.net/p/zeoslib/tickets/127/

Thank you, Jan