Oracle 11.2.0 vs Oracle 12.1.0 TWideStringField.Size

[marsupilami]

I can not upload pictures because the board limit is reached

I raised the quota. It should work now.

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

Changes appear on 8.0-patches as soon as they get merged in SVN. But unfortunately they don't appear as the same patches there. They get merged into one commit, like https://github.com/marsupilami79/zeosli ... 64e9f064bf - which were two commits on master.

[aehimself]

I raised the quota. It should work now.

ERROR
Sorry, the board attachment quota has been reached.

Still :)

Changes appear on 8.0-patches as soon as they get merged in SVN. ;)

Aha, so commits go to "master" and then later commits get merged into 8.0-patches? Effectively, "master" is the real developer branch? If yes then I'll have to change my main branch too.

[marsupilami]

ERROR
Sorry, the board attachment quota has been reached.

Still

Hrmpf - somehow my change wasn't stored yesterday. I raised the quota to 512MB.

Aha, so commits go to "master" and then later commits get merged into 8.0-patches? Effectively, "master" is the real developer branch? If yes then I'll have to change my main branch too.

Yep - trunk / master is the development branch now. 8.0-patches is in preparation to become the new stable Zeos branch. A 9.0 branch would be forked from trunk / master. Sorry - I forgot to communicate this change...

[aehimself]

Hrmpf - somehow my change wasn't stored yesterday. I raised the quota to 512MB.

Capture.PNG

Yep - trunk / master is the development branch now. 8.0-patches is in preparation to become the new stable Zeos branch. A 9.0 branch would be forked from trunk / master. Sorry - I forgot to communicate this change...

Okay, I updated my git repository layout. My master is now your master, and I created a branch called "Zeos" which is where I push my changes. This way I can issue a pull request on master if I cherry-pick a commit.

[EgonHugeist]

ae i'm a bit puzzled about the reports now because we had not been on same code bandwith iirc.

So could you make a small summary after all the changes, plz?

[aehimself]

Michael,

We were on the same code level, I just manually had to Cherry pick some commits as I was in the wrong branch. But of course, I’ll re-run some tests to see if the issues are still present. I’ll travel home tomorrow so I’m expecting some free time at the end of this week.

As a summary, what we discussed until now:
- Oracle 11.2 and 12.1 both reports correct .Size for string fields
- MySQL and FreeTDS (MSSQL) still reports x4 .Size for string fields
- On Oracle, the UTF-16 ClientCodePage setting is probably corrupting memory, producing really strange results in a standard DBGrid, and still throws the “field value was truncated” error with many rows and string fields.

Now that I’m on the master branch, I’ll check these again to make sure that these statements are still correct.

[EgonHugeist]

Thank you, that's eaxactly what i want to know. I can't believe the truncation errors of Oracle using UTF16 will continue. But i'm waiting for confirmation.
I'm curious about the MySQL and FreeTDS reports.. investigating

[aehimself]

As a summary, what we discussed until now:
- Oracle 11.2 and 12.1 both reports correct .Size for string fields
- MySQL and FreeTDS (MSSQL) still reports x4 .Size for string fields
- On Oracle, the UTF-16 ClientCodePage setting is probably corrupting memory, producing really strange results in a standard DBGrid, and still throws the “field value was truncated” error with many rows and string fields.

So I refreshed my Git repository, currently on

74e7e12236f582d29795b384ac9db971f133b862
egonhugeist on 11/23/2020, 6:13:20 PM
Delphi package ProductVersion upgrade to 8.0 (string replace with vim)

I can confirm that with this update level:
- Oracle 11.2 and 12.1 both reports correct .Size for String fields.
- MySQL and FreeTDS (MSSQL) still reports x4 .Size for String fields.
- Oracle 11.2 and 12.1 both messes up String field values when ClientCodePage is set to UTF-16 AND CACHED LOBS ARE ENABLED. Without cached LOBs, all values are displayed correctly. Truncation error is also present, with or without cached LOBs:

Code: Select all

An error happened at 0x00000000010CA74E while opening dataset:

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-74e7e1223
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

Code: Select all

An error happened at 0x00000000010CA74E while opening dataset:

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

Connection information: Oracle 11.2.0, client version: 19.8.0, database access component version: 8.0.0-74e7e1223
Connected to database ***, schema *** at host *** as user ***
Loaded library: C:\WINDOWS\oci.dll
Transaction isolation level: None
controls_cp: CP_UTF16
codepage: UTF16
OCIMultiThreaded: True
RawStringEncoding: DB_CP

[EgonHugeist]

Curious...

Wondering about your client-lib versions.. Non of them are the versions for the servers you/we are talking about! My client and server version are on same level, your client version is somewhat newer than the servers you're attaching. And i think the truncaction errors you report are reason of that... Plz, use the exact client version for the server, otherwise plz helpt. I don't think it's Zeos related.

[aehimself]

Based on the compatibility matrix (which is locked behind a support account now), they should be compatible.

[aehimself]

For MySQL string field size I think I found the culprit, will issue a pull request soon.

For FreeTDS, it seems that codepage is completely forgotten, when setting the columninfo precision; see ZDbcLibResultSet:384 (TZDBLibResultSet.Open.AssignGenericColumnInfoFromZDBCOL)

Code: Select all

        Precision := ColInfo.MaxLength;

ColInfo.MaxLength is returned by the FreeTDS driver itself at ZDbcLibResultSet:429 (TZDBLibResultSet.Open)

Code: Select all

      if FPlainDriver.dbcolinfo(FHandle, CI_REGULAR, I, 0, @tdsColInfo) <> DBSUCCEED then //might be possible for computed or cursor columns

The issue is - I don't know if the FreeTDS driver returns the wrong value, or we manually should divide it based on codepage (just like at MySQL) inside AssignGenericColumnInfoFromZDBCOL? I guess it will be the later, but - with zero knowledge about FreeTDS and/or the insides of MSSQL - I don't feel comfortable changing this part of the code.

Edit: @Jan, since the pull request for Fixed Delphi Linux64 target compilation was not yet closed, GitHub automatically added the MySQL field size fix to that one. Please import this commit too, before closing it now :)
Btw, is there an issue with the git synchronization / Jenkins? GitHub seems to be way before SubVersion and Jenkins is not available for me to check the task :(

[aehimself]

DBLib actually returns 0 for scale and 4 times the size for precision. Zeos simply sets the Scale equal to Precision in case of String-type fields.
I remember seeing somewhere that scale defines the character-per-byte ratio; and this is what we would need to be able to pass the correct Size parameter (Precision Div Scale).

Browsing the API of DBLib, I found dbcoltypeinfo. This method is loaded, but unused by Zeos. Maybe this is what we need to call in case of String fields...?

Edit: Tried, dbcoltypeinfo returns both scale and precision as 0. In the mean time I found this error report, where it's clearly stated that it is a FreeTDS issue so we can not rely on data returned by it. As the bug report is almost 2 years old and this behavior is still present in FreeTDS, I suppose the only way to fix this is by manually checking the codepage.

[aehimself]

Done, patch is sent via the very same pull request.

I added a condition, which will only divide the precision if FreeTDS is used in MSSQL mode. I don't have Sybase so please check if on Sybase TStringField.Size is correct...? If no, this condition (FDBLibConnection.GetProvider = dpMsSQL) has to be removed!

Once the patches will be imported to SubVersion, the only thing left is the Oracle UTF-16 truncation and corruption. I'll see if I can get a 12 version of the client library to test, but I'm almost certain this is not because of the version difference.

[aehimself]

So, I managed to grab the older version; truncation issue is still present:

An error happened at 0x000000000103FA5E while opening dataset:

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: 12.1.0, database access component version: 8.0.0-52e6a25f
Connected to database ***, schema *** at host *** as user ***
Loaded library: C:\Users\AEHimself\Downloads\instantclient-basiclite-windows.x64-12.1.0.2.0\instantclient_12_1\oci.dll
Transaction isolation level: None
controls_cp: CP_UTF16
codepage: UTF16
OCIMultiThreaded: True
RawStringEncoding: DB_CP

Also, text with special characters are still messed up in DBGrids using 12.1 as Client, UTF-16 ClientCodePage and cached lobs enabled.

[aehimself]

So the truncation error is raised at ZDbcOracleResultSet.pas:2620 (TZOracleResultSet_A.Next)

Status := FPlainDriver.OCIStmtExecute(FOCISvcCtx, FStmtHandle,
FOCIError, FIteration, 0, nil, nil, OCI_DEFAULT);

This is where OCIStmtExecute is returning -1, causing HandleErrorOrWarning at line 2657 to throw the exception.

I'm a little but baffled here. OCIStmtExecute doesn't really has a parameter which has to be allocated before...?
The only variable in this call is FIteration, which is 3 when the error occurs. There are a total of 5 cases where the code reached this point, with FIteration values of 504, 1, (after a longer wait) 127, 3 and finally 3 again, where the error is raised.
I also realized that FStmtHandle and FOCIError are changing values between each time; is it possible that we aren't allocating them correctly somehow, somewhere?