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