Oracle 11.2.0 vs Oracle 12.1.0 TWideStringField.Size

[aehimself]

I just realized something funny.

When the user clicks in a field, I'm displaying some information about that field, like maximum length, etc.
For that I'm using the Field.Size property, which returns the size of the buffer in bytes. Since we are using Unicode, I'm dividing it by 4. Works like a charm.
Unless - you connect to a 11.2.0 database, where Field.Size returns 1, seems it doesn't consider the code page settings.

It's not a big deal for me, my tool only shows the maximum field length completely messed up, I can live with that. Don't use outdated crap.
But... afaik Zeos is also using this property to allocate buffers? Isn't it a source of trouble if we are using 1 byte instead of 4 to allocate space for 1 character...? Or if Zeos is allocating properly, is there a way to get the bytes-per-character divider Zeos is using?

My code is simple...

Code: Select all

 If field.Size > 0 Then
   Case field.DataType Of
    ftString, ftMemo, ftWideString, ftWideMemo: Result := Result + ', size: ' + Integer(field.Size Div 4).ToString + ' character(s)';
    Else Result := Result + ', size: ' + field.Size.ToString + ' byte(s)';
   End;

Driver is 19.8.0, only the server version differs.

[marsupilami]

Erm - TWideStringField.Size should always give you the Size in characters. See http://docwiki.embarcadero.com/Librarie ... Field.Size - even though this page only documents the size for fields of type ftString. But the implementation for GetDataSize supports this:

Code: Select all

function TWideStringField.GetDataSize: Integer;
begin
  Result := (Size + 1) * SizeOf(WideChar);
end;

GetDataSize in turn should return the number of bytes that are required for storing the fields data.

The number of 4 would make sense in a Lazarus world. There we used to set the size property of TStringField to 4 * (Number of Characters) because otherwise the Lazaus TStringField wouldn't have eough space for storing UTF8 encoded data. With Zeos 7.2 on FPC 3.2 we recently changed to use the character set awarenes of the TStringField type there. So in that setting Size now should report the correct nimber of characters. But then - I thought you were on Zeos 8 and Delphi?

[aehimself]

But then - I thought you were on Zeos 8 and Delphi?

Yes sir, I am.
This means that not 1, but the 4 is incorrect? I think majority of the drivers I use report 4*size for T(Wide)StringFields (MySQL, mssql)
I'll doublecheck these, though.

[aehimself]

So it all comes down to FFieldDefs.Precision. I made a small test application, which connects to the 11 server and then the 12 server.

FieldDefs are created and initialized here:

ZDbcOracleResultSet.TZOracleResultSet_A.Open
ZDbcOracleResultSet.TZOracleAbstractResultSet_A.Create(TZOraclePreparedStatement_A($1FEAB38) as IZStatement,'SELECT MYSTRINGFIELD FROM MYTABLE WHERE ROWNUM < 1'#$D#$A,$65C2CEC,$65C32E8,131072)
ZDbcOracleStatement.TZAbstractOracleStatement.CreateResultSet
ZDbcOracleStatement.TZAbstractOracleStatement.ExecuteQueryPrepared
ZAbstractRODataset.TZAbstractRODataset.CreateResultSet('SELECT MYSTRINGFIELD FROM MYTABLE WHERE ROWNUM < 1'#$D#$A,-1)
ZAbstractRODataset.TZAbstractRODataset.InternalOpen
Data.DB.TDataSet.DoInternalOpen
Data.DB.TDataSet.OpenCursor(???)
Data.DB.TDataSet.SetActive(???)
Data.DB.TDataSet.Open

ZDbcOracleResultSet:2439

Code: Select all

          FPlainDriver.OCIAttrGet(paramdpp, OCI_DTYPE_PARAM,
            @ColumnInfo.Precision, nil, OCI_ATTR_DISP_SIZE, FOCIError);

This line returns 6 (correct maximum length) for the 11 server, but returns 24 for the 12 one. As this is changed by an oci.dll call, I suppose we are not initializing something correctly...?

[EgonHugeist]

Nasty, i added a version which does not use OCI_ATTR_DISP_SIZE:
https://sourceforge.net/p/zeoslib/code-0/7073/
All tests are passing. Hope i didn't break anything else?

[aehimself]

Michael,

I think nothing broke, but I also can not see a difference yet.
In ZDbcOracleResultSet:2373

Code: Select all

      FPlainDriver.OCIAttrGet(paramdpp, OCI_DTYPE_PARAM,
        @CurrentVar^.value_sz, nil, OCI_ATTR_DATA_SIZE, FOCIError);

Oracle 12.1 still receives 24, 11.2 receives 6 as "CurrentVar^.value_sz".

this is being passed over to the method NormalizeOracleTypeToSQLType in 2420, as parameter DataSize:

Code: Select all

    CurrentVar^.ColType := NormalizeOracleTypeToSQLType(CurrentVar.dty,
      CurrentVar.value_sz, CurrentVar^.DescriptorType,
      ColumnInfo.Precision, Status, ConSettings, OCI_TYPEPARAM_IN);

Which leads to our final destination, ZDbcOracleUtils:1311

Code: Select all

VCS:            MaxDataSizeToBytes(DataSize, Precision);

This method simply assigns Precision to DataSize:

Code: Select all

  procedure MaxDataSizeToBytes(var DataSize: ub4; var Precision: Integer);
  begin
    if ScaleOrCharSetForm = SQLCS_NCHAR
    then Precision := DataSize shr 1
    else begin
      Precision := DataSize;
      if (ConSettings.ClientCodePage.Encoding = ceUTF16) then
        DataSize := DataSize shl 1
      else if (ConSettings.ClientCodePage.CharWidth > 1) then
        DataSize := DataSize * Byte(ConSettings.ClientCodePage.CharWidth);
    end;
  end;

Now the issue is with OCI_ATTR_DATA_SIZE, which returns 24 for Oracle 12.1 and 6 for Oracle 11.2.

[aehimself]

Michael,

I saw your latest checkin with the character length issue, but the verification for 12.0 seems a bit... unprofessional.
Since I have ZERO knowledge about the oci driver (anything much deeper than a TDataSet, actually :D) I tried to do my homework and found this:

https://docs.oracle.com/cd/B19306_01/ap ... i06des.htm

OCI_ATTR_CHAR_SIZE - Returns the column character length which is the number of characters allowed in the column. It is the counterpart of OCI_ATTR_DATA_SIZE which gets the byte length. See "Character Length Semantics Support in Describing".

OCI_ATTR_DATA_SIZE - The maximum size of the column. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.

I feel like that we should do a check - if the field type is String, we should use OCI_ATTR_CHAR_SIZE instead of OCI_ATTR_DATA_SIZE. What do you think? This the server version is just an assumption which might change between even driver versions... I don't know. I'll continue to research.

[EgonHugeist]

I saw your latest checkin with the character length issue, but the verification for 12.0 seems a bit... unprofessional.

True, but you're faster on testing than i've time for the forum. I just wanted to know if it resolve the problem.

OCI_ATTR_CHAR_SIZE - Returns the column character length which is the number of characters allowed in the column. It is the counterpart of OCI_ATTR_DATA_SIZE which gets the byte length. See "Character Length Semantics Support in Describing".

Nice catch. Honestly i've no clue if it would break older oracle-server support. Findings?

OCI_ATTR_CHAR_SIZE whould require zeos always knows when to call it, such as for numeric/[time/]date/timestamp/interval types. Not so compilcated to implement except ... versioning?

Btw. would this patch resolve the UTF16 issue on your side?

[marsupilami]

I saw your latest checkin with the character length issue, but the verification for 12.0 seems a bit... unprofessional.
Since I have ZERO knowledge about the oci driver (anything much deeper than a TDataSet, actually ) I tried to do my homework and found this:

https://docs.oracle.com/cd/B19306_01/ap ... i06des.htm

OCI_ATTR_CHAR_SIZE - Returns the column character length which is the number of characters allowed in the column. It is the counterpart of OCI_ATTR_DATA_SIZE which gets the byte length. See "Character Length Semantics Support in Describing".

OCI_ATTR_DATA_SIZE - The maximum size of the column. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.

I feel like that we should do a check - if the field type is String, we should use OCI_ATTR_CHAR_SIZE instead of OCI_ATTR_DATA_SIZE. What do you think? This the server version is just an assumption which might change between even driver versions... I don't know. I'll continue to research.

Even though I feel like hijacking the discussion: I seem to remember that Oracle allows to declare character field length either in bytes or in characters.
Based on this, I think that Oracle assumes that users first use OCI_ATTR_CHAR_USED and depending on the result use OCI_ATTR_CHAR_SIZE or OCI_ATTR_DATA_SIZE.
The Zeos world (more or less - depending on the compiler and runtime library) works with characters only or a combination of characters and bytes (In the case of TWideStringField and TStringField with MBCS). So I assume with current Zeos and RTL support, we cannot fully support field lengths declared in bytes?

Honestly i've no clue if it would break older oracle-server support. Findings?

I could supply a machine with Oracle 10 XE for testing - just found an old DVD from 2006... Should I set something up? How much backwards compatibility do we want?

Best regards,

Jan

[aehimself]

True, but you're faster on testing than i've time for the forum. I just wanted to know if it resolve the problem.

Ummm... Now said field turned into WideMemo instead of WideString on 12.1...? On 11.2 it's still WideString, providing the correct .Size.

Btw. would this patch resolve the UTF16 issue on your side?

Which UTF-16 issue are we talking about? The maximum field size, or you know about something I already forgot? :roll:

[EgonHugeist]

@aehimself

I followed your advice and use OCI_ATTR_CHAR_SIZE for the character fields instead. Means we determine the Precision field and datasize will be calculated by NormalizeOracleTypeToSQLType. I found this constant in old oracle 8 headers, so it should work for old oracle servers too.

See: https://sourceforge.net/p/zeoslib/code-0/7095/

Which UTF-16 issue are we talking about? The maximum field size, or you know about something I already forgot?

I'm talking about connection characterset UTF16. IIRC did you report truncation errors, i could not reproduce with my old 11g dev edition. Still same result? UTF16 is loads faster than AL32UTF8.

@Jan, it's not worth it. But a Jenkins oracle battery from v11...v20 would be nice. I still had not the time to add 12up
according the link: Yes i know that. I've been testing the "NLS_LENGTH_SEMANTICS" parameter https://knowledge.informatica.com/s/article/388647 but my 11g simply ignores it. Means i can set it to byte or char semantic and the result is always the same. That's of course another Oracle bug like the Precision=38 for all ordinal declared types(Zeos8 maps it to stBigDecimal).

[aehimself]

I followed your advice and use OCI_ATTR_CHAR_SIZE for the character fields instead. Means we determine the Precision field and datasize will be calculated by NormalizeOracleTypeToSQLType. I found this constant in old oracle 8 headers, so it should work for old oracle servers too.

Let's see, I'll test as soon as the commits show up in Git.

I'm talking about connection characterset UTF16. IIRC did you report truncation errors, i could not reproduce with my old 11g dev edition. Still same result? UTF16 is loads faster than AL32UTF8.

Yeah, this is why I should stop handling multiple issues in the same thread.
Oracle 12.2.0.1 keeps throwing ORA-01406
To be honest I have absolutely no idea where the error happened so I'll just change my character encoding to UTF16 and continue my life. We'll see if I'll meet the same issue again.

Edit: Can not believe, but I found my test case :)

EZSQLException was raised with the message SQL Error: ORA-01406: fetched column value was truncated
Code: 1406 Message: FETCH ROW

Connection information: Oracle 12.1.0, client version: 19.8.0, database access component version: 8.0.0-a58f1cff5
Connected to database ***, schema *** at host *** as user ***
Loaded library: C:\Windows\oci.dll
Transaction isolation level: None
controls_cp: CP_UTF16
codepage: UTF16
DateWriteFormat: YYYY.MM.DD
OCIMultiThreaded: True
CachedLob: True
RawStringEncoding: DB_CP

This should be r7092 if I'm not mistaken and the issue is still present.

[2020.11.18 08:59:08.439] Prepare Statement 4 : SELECT * FROM MYTABLE
[2020.11.18 08:59:08.452] Prepare Statement 5 : SELECT ALL_TAB_COLUMNS.OWNER, ALL_TAB_COLUMNS.TABLE_NAME, ALL_TAB_COLUMNS.COLUMN_NAME, ALL_TAB_COLUMNS.DATA_TYPE, ALL_TAB_COLUMNS.DATA_LENGTH, ALL_TAB_COLUMNS.DATA_PRECISION, ALL_TAB_COLUMNS.DATA_SCALE, ALL_TAB_COLUMNS.NULLABLE, ALL_TAB_COLUMNS.DATA_DEFAULT, ALL_TAB_COLUMNS.COLUMN_ID, ALL_COL_COMMENTS.COMMENTS FROM ALL_TAB_COLUMNS JOIN ALL_COL_COMMENTS ON ALL_COL_COMMENTS.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME AND ALL_COL_COMMENTS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COLUMNS.OWNER WHERE ALL_TAB_COLUMNS.TABLE_NAME like 'MYTABLE' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2020.11.18 08:59:09.571] Execute prepared Statement 5 in 1119 ms
[2020.11.18 08:59:10.304] Fetch Statement 5, affected 71 row(s) in 1852 ms
[2020.11.18 08:59:10.324] Fetch FETCH ROW ORA-01406: fetched column value was truncated (#1406)

This will definitely will be an allocation issue in Zeos. The issue disappears in the moment when I remove a number of string fields from the query.

[aehimself]

I think majority of the drivers I use report 4*size for T(Wide)StringFields (MySQL, mssql)
I'll doublecheck these, though.

Just confirmed, both MySQL and FreeTDS (MSSQL) are reporting 4 times the maximum size as Field.Size on VARCHAR fields.

MySQL 8.0.18, client version: 8.0.18

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)
Jun 15 2019 00:26:19
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
with a kind of fresh FreeTDS binary from their own website.

[marsupilami]

@Jan, it's not worth it. But a Jenkins oracle battery from v11...v20 would be nice. I still had not the time to add 12up

Well - problem is, I don't have the Windows licenses to do that And we still cannot run ztestall on Linux without having a GUI session - so automated testing on Linux still is not possible. I am pretty sure, I will restart that project once, I have the release notes for Zeos 8.0 finished and the proxy server driver working and ...

Anyway - as long as I only have express editions, I am sure, we will be limited to testing on Oracle 11 and on Oracle 18 because that are the last two Express Editions - according to this thread. It seems all other releases were not released as XE. Another option might be to check if the "Oracle Technology Network License Agreement" can help because it seems that one can get software for development purposes there...

[aehimself]

UTF16 on Oracle is not working and - most probably - corrupting memory!!!

I can not upload pictures because the board limit is reached, but Query -> DataSource -> DBGrid and the fields show chinese characters instead of "é". When I click the field, it shows "TInLineEdit" instead of "é", which is the name of my grid in-line editor component!!!!

So yeah, I'll keep using AL32UTF8 for the time being :D

Btw - I cherry-picked your commit from Master to my 8.0-patches branch (idk, lately new commits only go to your master branch in Git) and I can confirm: both Oracle 11.2 and 12.1 are now reporting string field .Size correctly! Now let's do the same for MySQL and FreeTDS too ;)