Use TZMetaData to identify what type of field

[aehimself]

Hello,

In my application the user can enter a TABLE.FIELD string and my code will "convert" that to a different format (XML needed for FastReport). To be able to do that I need to identify what kind of field is that in the database. Until now I executed a dummy query... SELECT field FROM table WHERE 1=0. After opening the query I simply checked the field's DataType.

I now changed this so that the user can select the table from a combo box and instead of entering everything I'm showing a TZMetaData result of the columns of said table. As I already have everything from TZMetaData, I'm wondering if I can ditch the dummy query and get the field type somehow. Unfortunately datatype as number and as string are coming from the RDBMS and are different in each.

If TZMetaData can not give me this information, are there better ways to determine the field type instead of the dummy query?

[marsupilami]

Hello aehimself,

it seems that the DATA_TYPE column returns ord(TZSQLType). So using this you can see what kind of field you have. But you cannot rely on the numbers to be portable between Zeos versions. It seems that one or two values were inserted anbd not appended between Zeos 7.2 and Zeos 8.0.

Best regards,

Jan

[aehimself]

Nice! I saw DATA_TYPE but the same field resulted different values in MSSQL and Oracle so I thought the RDBMS is sending that back.

Changing won't matter much if I'm doing

Code: Select all

Case TZSQLType(SQLMetaData.FieldByName('DATA_TYPE').AsInteger) Of

as even if the position changes, it should give back the correct enum.

Now, I only need to "translate" these to understandable values... too bad I can not use GetColumnType as I don't have a resultset to have metadata for it :)

[marsupilami]

Now, I only need to "translate" these to understandable values... too bad I can not use GetColumnType as I don't have a resultset to have metadata for it

Not sure, what you mean but TZSQLTypes is defined in ZDbcIntfs:

Code: Select all

  TZSQLType = (stUnknown,
    //fixed size DataTypes first
    stBoolean,
    stByte, stShort, stWord, stSmall, stLongWord, stInteger, stULong, stLong, //ordinals
    stFloat, stDouble, {$IFDEF ZEOS90UP}stDecimal128,{$ENDIF} //floating types
    stCurrency, stBigDecimal, //ExactTypes
    stDate, stTime, stTimestamp,
    stGUID,
    //now varying size types in equal order
    stString, {should be used for raw strings only}
    stUnicodeString{should be used for national strings only}, stBytes,
    //EH: these 3 enums below should be used for real streamed data only (FB/PG(OIDLobs)/MySQL/ODBC/OleDB/Oracle)
    //otherwise stString...stBytes are sufficent (SQLite, Postgres Varchar/Text/byteea f.e.)
    //our RowAccassor eats all long data since i added the ptr ref/deref technic years ago
    //it's not worth it handling all obselete TDataSet incompatibilities on Dbc layer
    //that's a problem of component layer only
    stAsciiStream{should be used for raw streams only}, stUnicodeStream{should be used for unicode streams only}, stBinaryStream,
    {$IFDEF ZEOS90UP}
    stJSON, stXML, stVariant,
    {$ENDIF ZEOS90UP}
    //finally the object types
    stArray, stResultSet{$IFDEF ZEOS90UP}, stStatement{$ENDIF});

In the webservice server I use the following code to translate the numeric values back into strings:

Code: Select all

TypeName := GetEnumName(TypeInfo(Value), Ord(Value));

Value is of type TZSQLType. GetEnumName is from the typinfo unit. TypeName will be something like stBoolean or stGUID after the call.

Does that help?

[aehimself]

No, what I need is what enums in TZSQLType represent strings, numbers, blobs, booleans, etc.
It will be a trial-and-error I suppose :)

[marsupilami]

Ok - so maybe it is time for me to do some educated guesses

stUnknown -> We don't know that data type, so we generate stUnknown
stBoolean -> boolean True / False
stByte -> Unsigned Byte (0..255)
stShort -> Signed Byte? (-128..127)
stWord -> 2 Bytes sized unsigned fields -> (0..65xxx)
stSmall -> 2 Bytes sized signed fields -> (-32768..32767)
stLongWord -> 4 Bytes (32 Bits) sized unsigned fields (Cardinal) (0 to 4 millions and a bit)
stInteger -> 4 Bytes (32 Bits) sized signed fields (Integer) (-2 Millions .. + 2 Millions)
stULong -> 8 Bytes (64 Bits) sized unsigned fields
stLong -> 8 Bytes (64 Bits) sized signed fields
stFloat -> sigle precision floating point fields
stDouble -> doubple precision floating point fields,
stDecimal128 -> not clear yet and not supported yet and post Zeos 8.0-> Probably something to support the decfloat data type in Firebird?
stCurrency -> not 100% sure bute maybe something to show that this really is a money type field
stBigDecimal -> numeric and decimal type fields
stDate -> date without timezone and without time
stTime -> time without time zone and without a date
stTimestamp -> date and time combined without time zone
stGUID -> GUID
stString -> ANSI String, including UTF8 (varchar)
stUnicodeString -> Unicode strings in UCE2 / UTF-16 format (nvarchar)
stBytes -> varbytes
stAsciiStream -> CLOB/Memo in ANSI encoding
stUnicodeStream -> CLOB/Memo in Unicode UCE2 / UTF-16 format
stBinaryStream -> BLOB - binary data
stJSON -> not supported yet and post Zeos 8.0-> JSON data types
stXML -> not supported yet and post Zeos 8.0-> XML data types
stVariant -> not supported yet and post Zeos 8.0-> Variant data types. Maybe to fully support SQLite and have tables with all TVariantFields?
stArray -> Arrays, I don't think, we support them
stResultSet -> sub resultsets? I don't think, we support that in any way
stStatement -> not supported yet and post Zeos 8.0-> Maybe a substatement that is part of a result set? I don't know.

Maybe this can shed some light on the issue. But note that the metadata generation of some drivers might be faulty. I do think we are pretty good on dblib because there we simply call some procedures that were meant to be used for ODBC and JDBC. But I am very unsure about all other databases including Firebird. On these databases metadtaa generation works the way that the writer of the driver understood it...

[aehimself]

All is looking fine, except the string field sizes... while TField.Size returned the correct length in characters, I simply cannot get the same result from COLUMN_SIZE, BUFFER_LENGTH and CHAR_OCTET_LENGTH.

We have a varchar field with the maximum length of 6 characters. Oracle reports 24 - 97 - 96, MSSQL (with DBLib) 12 - 12 - 12.

Any hint on this? :)

[marsupilami]

All is looking fine, except the string field sizes... while TField.Size returned the correct length in characters, I simply cannot get the same result from COLUMN_SIZE, BUFFER_LENGTH and CHAR_OCTET_LENGTH.

We have a varchar field with the maximum length of 6 characters. Oracle reports 24 - 97 - 96, MSSQL (with DBLib) 12 - 12 - 12.

Any hint on this?

Yep - that is what I meant with faulty metadata generation. A column_size of 12 is wrong for dblib if you have nvarchar. I changed that.

What Oracle is doing - I don't know. I can only do an educated guess: 24 = 6 * 4. Maybe these are 6 characters multiplied with 4 bytes per character - as UTF8 would use. Zeos assumes that 24 characters are meant and multiplies these 24 assumed characters with 4 again which leads us to 96. And then 1 byte gets added for a trailing #0 charachter to calculate the buffer size.So it seems that somebody has to take a look into the oracle driver....

But then - Oracle is the most complex driver because varchar(x) can have different meanings there. In one setup x can be the number of bytes for the varchar field which is similar to what MS does. And in another setup x can mean the number of characters, as Firebird does. So all this has to be taken into account here.

[aehimself]

Now, DBLib reports 24 - 97 - 96 as well on the Varchar(6) field!
I suppose I can use this, only to divide COLUMN_SIZE by 4.
I just don't like magic numbers, that's all :)

Edit: probably connected to the wrong DB. DBLib actually says 6 - 12 - 12, making this an unreliable way to determine the length of a field. Hard-coding magic numbers is bad, hard-coding one per protocol is even worse :)
I'll try to dig into the code to see how Zeos determines field sizes for datasets as it reports everything correctly.

[marsupilami]

Now, DBLib reports 24 - 97 - 96 as well on the Varchar(6) field!
I suppose I can use this, only to divide COLUMN_SIZE by 4.
I just don't like magic numbers, that's all

I just retested this on my test server with SQL Server 2019 on Linux, default database collation = Latin1_General_CI_AS, Client on Windows with Delphi 11:

For varchar(6) I get:
COLUMN_SITZE = 6
BUFFER_LENGTH = 6
CHAR_OCTET_LENGTH = 6
These values are expected for any varchar(6) column, I think.

For nvarchar(6) I get:
COLUMN_SITZE = 6
BUFFER_LENGTH = 12
CHAR_OCTET_LENGTH = 12
These values are expected for any nvarchar(6) column, I think.

So I assume that for MS SQL Server we are good, I think.

Edit: probably connected to the wrong DB. DBLib actually says 6 - 12 - 12, making this an unreliable way to determine the length of a field. Hard-coding magic numbers is bad, hard-coding one per protocol is even worse

I can only assume that the problem is in the specification. This is what Oracles Java documentation has to say (see here):

COLUMN_SIZE int => column size.
CHAR_OCTET_LENGTH int => for char types the maximum number of bytes in the column

So while we have a very clear specification on CHAR_OCTET_LENGTH, we don't exactly know how to interpret COLUMN_SIZE. Honestly I assume that this is intentional because some databases calculate varchar sizes in bytes (MS SQL Server) and other databases calculate varchar sizes in characters (Firebird, PostgreSQL). And I assume that COLUMN_SIZE should be whatever is the x in varchar(x).

So the question would be: What do we want for COLUMN_SIZE to be? And what do oracle ODBC or JDBC drivers report? => Hmm - I might just check the ODBC part...

I'll try to dig into the code to see how Zeos determines field sizes for datasets as it reports everything correctly.

Zeos usually uses the sizes reported by the databases for their result sets and doesn't care about sizes in GetColumns. But this requires you to actually have a result set.

[aehimself]

So the question would be: What do we want for COLUMN_SIZE to be? And what do oracle ODBC or JDBC drivers report? => Hmm - I might just check the ODBC part...

To be honest it does not matter. It just has to be consistent imo. Having the maximum character length would be beneficial, as the buffer size (maximum length in bytes) is already reported by the very same metadata in BUFFER_LENGTH. Just reduce by one and it's set.

Zeos usually uses the sizes reported by the databases for their result sets and doesn't care about sizes in GetColumns. But this requires you to actually have a result set.

I had a disgusting idea. The metadata actually returns a resultset, so... what if I check the SQLMetaData.FieldByName('COLUMN_NAME').Size (which is always 255 characters) and try to compare that with the size what the resultset metadata returns... SQLMetaData.DbcResultSet.GetMetadata.GetPrecision or .GetScale. Divinding these would give me the driver- and connection specific "magic number".
Unfortunately though, they both return 0 so no luck. I take credit for the idea though, was a nice try :)

[marsupilami]

To be honest it does not matter. It just has to be consistent imo. Having the maximum character length would be beneficial, as the buffer size (maximum length in bytes) is already reported by the very same metadata in BUFFER_LENGTH. Just reduce by one and it's set.

Well it is consistent in that it reports what has to be put inside the varchar(x) declaration. It just messes with you because for one database it reports bytes and for another it reports characters
Soo - I did some tests on my Oracle 11g XE. I assume that as documented here that "varchar(6)" means 6 bytes. This assumption seems to be confirmed in Zeos returning COLUMN_SIZE=6.
When I change the declaration to "varchar(6 char)", I also get COLUMN_SIZE=24, which makes sense because a 6 character UTF8 colulmn uses up to 24 bytes.
Sooo - in my opinion the Oracle driver should distinguish between byte and character semantics and then return the relevant value (bytes / characters) in the COLUMN_SIZE column. This leaves only one question open: How does the user get to know if the colulmn is declared with byte semantics or with character semantics?

I had a disgusting idea. The metadata actually returns a resultset, so... what if I check the SQLMetaData.FieldByName('COLUMN_NAME').Size (which is always 255 characters) and try to compare that with the size what the resultset metadata returns... SQLMetaData.DbcResultSet.GetMetadata.GetPrecision or .GetScale. Divinding these would give me the driver- and connection specific "magic number".
Unfortunately though, they both return 0 so no luck. I take credit for the idea though, was a nice try

Unfortunately that doesn't work. Metadata result sets are synthetic result sets that are not database depenedent in most cases.

[marsupilami]

Soo - I couldn't leave this alone. Zeos reads column metadata from that nice orcle view called ALL_TAB_COLUMNS. It has the info we need in the CHAR_LENGTH and CHAR_USED columns. I changed the driver to return the CHAR_LENGTH value for string types in the COLUMN_SIZE column and to only use DATA_LENGTH for the CHAR_OCTETS_LENGTH column. I am not sure what value to return for the BUFFER_LENGTH. The current calculation only makes sense for IDEs that use ANSISTRING and not UNICODESTRING as their default string type...

Opinions?

[miab3]

Hi Jan,

I see you are using Oracle 11g XE and maybe that's why you didn't respond to my remarks about ZEOS crashing when trying to open table lists in ZTable. The problem appeared with me when switching to 18g XE
Maybe it is worth coming for something more modern, e.g. Oracle Database 18c or 21c Express Edition(also free).

https://www.oracle.com/database/technol ... ev/xe.html
Resources:
Up to 12 GB of user data
Up to 2 GB of database RAM
Up to 2 CPU threads

https://www.oracle.com/database/technol ... loads.html

Regards,
Michał

[marsupilami]

Hello Michal,

the problem with Oracle and TZTable has several problems: The strongest being that I have no environment for testing. Setting up a good test environment will take me literally hours before I can even start debugging into this. I have to look for a Windows license, install Windows, install Oracle and install some version of Delphi before I even can start debugging. The current Oracle environment is Windows 7 with Oracle 11g - and I don't intend to upgrade this because I think it is also important to be able to test old versions. So - all that work for a feature that I don't use and will probably never use (I never use TZTable and I always use TZQuery) seems rather ... ugly ... or something like that. So my motivation for trying to debug into this is quite low...
Sooo - if you have a machine ready with Delphi installed etc that I can use for testing and debugging, I can give it a try. Otherwise I assume, this will stay low on my priority list. Currenty my main concern is to get automated tests with FPC on Linux running. This should allow me to get automated tests against Oracle 18 XE which I have tried to install on a Version of Cent OS. My hopes would be that this reveals something for your problem too. Unfortunately the test suite depends on the Forms unit which leads to problems when running in a console only environment.

Sorry for the bad news,

Jan