Oracle & UTF16 - Fetched column value was truncated (solved...?)

Forum related to Oracle Server

Moderators: gto, EgonHugeist, mdaems

User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Inserting the same set of checks into TZCachedResultSet.FillColumnInfo actually solves the issue:

Code: Select all

      If ColumnType In [stString, stUnicodeString] Then
        If ColumnCodePage = zCP_UTF16 Then ColumnType := stUnicodeString
          Else ColumnType := stString;
Now the question is... which protocol supports UTF16? I cannot see it as an option in SQLite.
Before sending a pull request I'd like to test if it works correctly everywhere else.

Is it possible that TZCachedResultSet is simply not handling UTF16 at all?
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Okay, let's give it a spin on Jenkins. Pull request is available on GitHub.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
marsupilami
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by marsupilami »

I applied the patch to trunk and waiting for the first results :o)
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Something is still not good with UTF16 Oracle and a number of VarChar columns.
Setting INTERNAL_BUFFER_SIZE to a high enough number will get rid of the Oracle error, but can result in empty fields, whereas the database does contain data:
Untitled.png
Why it's really problematic is that false data is returned but no errors or warnings are shown whatsoever.
Increasing INTERNAL_BUFFER_SIZE from 2000000 to 10000000 seems to solve the issue, data shows up. Question is, though, what doesn't...?

I'll try to debug it later tonight.
You do not have the required permissions to view the files attached to this post.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Bugtracker ticket created.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
MJFShark
Expert Boarder
Expert Boarder
Posts: 216
Joined: 04.06.2020, 13:59

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by MJFShark »

I was able to tentatively fix this issue by replacing the single large FRowsBuffer with individual buffers for each column item array. I didn't' find any smoking gun in the current code, but my guess is the issue relates to memory alignment. The Oracle OCI docs state:

Note:Output buffers must be 2-byte aligned.

The existing large byte buffer can place the output column buffer start address in an unaligned way. My solution aligns the buffer automatically since there is one per select list column.

As an aside, I also optimized the buffer size for UTF16 which gives a pretty good speed boost (since more records can be fetched at once.)

-Mark
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Are you planning to send a pull request about the fixed, sped-up UTF16 version? :)
I'd love to have it.
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
MJFShark
Expert Boarder
Expert Boarder
Posts: 216
Joined: 04.06.2020, 13:59

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by MJFShark »

I am, but I've been waylaid by covid this week... Hopefully later in the week I'll be up to it.

-Mark
MJFShark
Expert Boarder
Expert Boarder
Posts: 216
Joined: 04.06.2020, 13:59

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by MJFShark »

Feeling a bit better and got a pull request in. Let me know how it goes.

-Mark
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Uff, it is still at large? In my country it's all about the war now, everyone forgot about Covid (thankfully the fully remote work remains).

I put a comment in the pull request, some minor adjustments have to be done for the code to stay compatible with older Delphi versions. Once your changes are merged I can fire up my dev machine and make it to compile with Delphi 7.
So you can just sit back, relax and get better :)
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Soooo, I took the diff and applied it to my local Zeos repo.

SELECT * FROM BigTable, AL32UTF8
Opening dataset in background thread #6108 started.
[...]
Fetch Statement 10, affected 207454 row(s) in 479833 ms
Query returned 207 454 record(s) in 07:59
Opening dataset in background thread #6108 ended in 07:59.

SELECT * FROM BigTable, UTF16
Opening dataset in background thread #7332 started.
[...]
Fetch Statement 5, affected 207454 row(s) in 196785 ms
Query returned 207 454 record(s) in 03:16
Opening dataset in background thread #7332 ended in 03:16.

What the hell... :D
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
MJFShark
Expert Boarder
Expert Boarder
Posts: 216
Joined: 04.06.2020, 13:59

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by MJFShark »

Unfortunately, the performance part of my fix for the ORA-01406 issue is not correct. I've been testing using strings filled with the "Hwair" character (which is 4 bytes in both UTF8 and UTF16) and my "fix" truncates the data silently in UTF16 mode. The truncation is reported in the indicator variable. This means that the buffer calculation in NormalizeOracleTypeToSQLType need to be put back to the old values.

Code: Select all

    if (ScaleOrCharSetForm = SQLCS_NCHAR) or (ConSettings.ClientCodePage.Encoding = ceUTF16)
    then DataSize := Precision shl 2
I've done some experimenting with detecting the truncation and redefining the buffers "on the fly", but I can't figure out how to re-fetch the current block of records without restarting the entire fetch from the beginning. It seems not to be possible. Maybe someone else knows more about that.

-Mark
marsupilami
Platinum Boarder
Platinum Boarder
Posts: 1956
Joined: 17.01.2011, 14:17

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by marsupilami »

Hello Mark,

if we increase the buffer size again - maybe the rest of your fix still fixes the original problem?

With best regards,

Jan
MJFShark
Expert Boarder
Expert Boarder
Posts: 216
Joined: 04.06.2020, 13:59

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by MJFShark »

Hi Jan,

I believe so. I've tested pretty extensively on that and haven't seen any issues, and I was able to cause it regularly with the old code. The new individual buffers and dynamic arrays for the indicator and ALen variables also allow a few fixes (which I'm currently working on and will submit when they've been tested as far as I can.)

One fix, for example, is that currently we convert CHAR/SQLT_AFC (which are space padded by Oracle) by using the entire data buffer which adds null chars to the result string. It looks like the GetAbsorbedTrailingSpacesLen function was done to try to fix the issue, but it doesn't appear to work. Now that we have the Alen results coming back we can trim the strings correctly. It's not a huge deal in general since embedded nulls in a string are often not displayed depending on how you deal with it, but I think it's a good fix.

Another benefit is that we can get rid of all of the range check disabling and enabling in ZDbcOracleResultSet that was done to use the old indp pointer as an array (since we have an actual array of them now.)

-Mark
User avatar
aehimself
Zeos Dev Team
Zeos Dev Team
Posts: 792
Joined: 18.11.2018, 17:37
Location: Hungary

Re: Oracle & UTF16 - Fetched column value was truncated (solved...?)

Post by aehimself »

Unfortunately it seems that while Mark's fix indeed makes things better, does not solve the actual issue. While I was implementing sequence metadata for Oracle I was greeted with:

Code: Select all

[2024. 11. 12. 17:31:16] db: Prepare Statement 33 : SELECT * FROM ALL_TRIGGERS
[2024. 11. 12. 17:31:16] db: Prepare Statement 34 : 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, ALL_TAB_COLUMNS.CHAR_LENGTH 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 'ALL_TRIGGERS' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2024. 11. 12. 17:31:17] db: Execute prepared Statement 34 in 657 ms
[2024. 11. 12. 17:31:17] db: Fetch Statement 34, affected 22 row(s) in 825 ms
[2024. 11. 12. 17:31:17] db: Unprepare Statement 34
[2024. 11. 12. 17:31:17] db: Execute prepared Statement 33 in 903 ms
[2024. 11. 12. 17:31:18] db: Fetch FETCH ROW ORA-01406: fetched column value was truncated Help: https://docs.oracle.com/error-help/db/ora-01406/  (#1406)
[2024. 11. 12. 17:31:18] EZSQLException was raised while Opening dataset with the message SQL Error: ORA-01406: fetched column value was truncated
Help: https://docs.oracle.com/error-help/db/ora-01406/
 
Code: 1406 Message: FETCH ROW
Same query executes perfectly if I connect with AL32UTF8:

Code: Select all

[2024. 11. 12. 17:36:21] db: Prepare Statement 38 : SELECT * FROM ALL_TRIGGERS
[2024. 11. 12. 17:36:21] db: Prepare Statement 39 : 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, ALL_TAB_COLUMNS.CHAR_LENGTH 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 'ALL_TRIGGERS' ORDER BY ALL_TAB_COLUMNS.COLUMN_ID
[2024. 11. 12. 17:36:21] db: Execute prepared Statement 39 in 484 ms
[2024. 11. 12. 17:36:21] db: Fetch Statement 39, affected 22 row(s) in 624 ms
[2024. 11. 12. 17:36:21] db: Unprepare Statement 39
[2024. 11. 12. 17:36:21] db: Execute prepared Statement 38 in 734 ms
[2024. 11. 12. 17:36:22] db: Fetch Statement 38, affected 180 row(s) in 1844 ms
[2024. 11. 12. 17:36:22] db: Query returned 180 record(s) in 1 second(s)
Just fyi... I have no idea if you guys can reproduce it on your end :)
Delphi 12.2, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmysql.dll 8.0.40 x64 5.7.19 x68, libmariadb.dll 3.3.11
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.15
- MSSQL 2012, 2019; sybdb.dll FreeTDS_3102
- SQLite 3.47
Post Reply