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

[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?

[aehimself]

Okay, let's give it a spin on Jenkins. Pull request is available on GitHub.

[marsupilami]

I applied the patch to trunk and waiting for the first results )

[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.

[aehimself]

Bugtracker ticket created.

[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

[aehimself]

Are you planning to send a pull request about the fixed, sped-up UTF16 version? :)
I'd love to have it.

[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]

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

-Mark

[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 :)

[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

[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]

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]

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

[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 :)