Possible issue with 64bit Oracle
Re: Possible issue with 64bit Oracle
HI Jan,
No worries. I'm not sure what you mean about it not being used by oracle functions, but those variables are used all the time for result sets as I showed in my sample console mode program on the first post of this. In 64bit mode with $A8 alignment (the default) that calculation will show 52 bytes, but the record size is 56 bytes due to alignment. That's the issue here. You can't (well, shouldn't) calculate a record size using the individual components unless it's a packed record.
-Mark
No worries. I'm not sure what you mean about it not being used by oracle functions, but those variables are used all the time for result sets as I showed in my sample console mode program on the first post of this. In 64bit mode with $A8 alignment (the default) that calculation will show 52 bytes, but the record size is 56 bytes due to alignment. That's the issue here. You can't (well, shouldn't) calculate a record size using the individual components unless it's a packed record.
-Mark
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
You are right. Even though it makes memory access slower, why don't we make TZSQLVar and TZSQLVars packed records? With that change the record size will be calculated correctly because there will be no alignment holes. And honestly I don't know how to chenge the declaration of TZSQLVars without breaking existing code when range checks are enabled.
Re: Possible issue with 64bit Oracle
For the purposes of the size calculation only TZSQLVars would need to be packed as far as I can tell. Sizeof is used on TZSQLVar which will work correctly regardless of alignment. Now I haven't looked closely at how that memory is used, so there could be other reasons that I don't know about for TSQLVar to be packed, but not for that getmem calc. I'm not sure why memory is being allocated for TZSQLVars in this way (via getmem with a calculated size), it seems very un-delphi like, but again, I haven't looked into it closely.
-Mark
-Mark
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
You are right - it is un-delphi like I did some digging in the svn and it seems that the code more or less has always been like that and nobody ever changed it:
This code was checked in to the SVN in revision 1 in 2006. I can only assume that it has its origins in days when Zeos supported Delphi versions before Delphi 4. According to this page dynamic arrays were introduced in Delphi 4.
These days I would change TZSQLVars to be a dynamic array.
Code: Select all
type
{** Declares SQL Object }
TZSQLVar = packed record
Handle: POCIHandle;
Define: POCIHandle;
BindHandle: POCIBind;
Data: Pointer;
DupData: Pointer;
DataType: ub2;
DataSize: ub2;
Length: Integer;
Precision: Integer;
Scale: Integer;
ColType: TZSQLType;
TypeCode: ub2;
Indicator: sb2;
Blob: IZBlob;
end;
PZSQLVar = ^TZSQLVar;
TZSQLVars = packed record
AllocNum: ub4;
ActualNum: ub4;
Variables: array[1..MAX_SQLVAR_LIMIT] of TZSQLVar;
end;
PZSQLVars = ^TZSQLVars;
{**
Allocates memory for Oracle SQL Variables.
@param Variables a pointer to array of variables.
@param Count a number of SQL variables.
}
procedure AllocateOracleSQLVars(var Variables: PZSQLVars; Count: Integer);
These days I would change TZSQLVars to be a dynamic array.
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
Hello Mark,
I added some changes to Zeos 8.0 and Zeos trunk that should fix your problem and should make it more Delphi style . Could you please check it?
With best regards,
Jan
I added some changes to Zeos 8.0 and Zeos trunk that should fix your problem and should make it more Delphi style . Could you please check it?
With best regards,
Jan
Re: Possible issue with 64bit Oracle
Nicely done! Passed all my tests. A very minor point is that I think you may be able to also remove AllocateOracleSQLVars as SetLength *should* be all that's needed and zeroes the memory automatically (should is in asterisks because I don't know if that's always been the case or how Laz/free pascal works.) Thanks for the fix!
-Mark
-Mark
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
Unfortunately that is only true for new array elements. Old elements will stay the same as they were.
Re: Possible issue with 64bit Oracle
Oops! Good point! Thanks again!
-Mark
-Mark
Re: Possible issue with 64bit Oracle
Guys, good news. My request was not just approved, but a secondary table was created with some obfuscated data. And the UTF16 error can be still reproduced!
There's one issue though - since the table has CLOB fields, PL/SQL cannot export it as a normal SQL script. I can extract it in a PL/SQL format (whatever that is) - can you import that format into your test databases?
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
Duh - as far as I remember, PL/SQL is installed with the express versions of Oracle. So it should be possible. Maybe you could give some example on how to do that?
Re: Possible issue with 64bit Oracle
So as it turned out Oracle is a piece of garbage. Our DB team said I need to specify the target host-, tablespace- and schema name, and only if the two machines can reach each other a dump file can be created...?
That is either incorrect or ridiculous. Anyway.
I copied the table create schema and dumped all data with my database manager application.
In CSV or TSV the dump file is about 94 MB but you can easily write your own tool to re-insert the records.
in my own binary data format the dump is 29 MB but you must use my database manager or recreate the same importing mechanism what I use.
I'll upload everything on my server overnight and will send the links in DMs - however the data is obfuscated twice, it's still proprietary.
The "Column value is truncated" error can be reproduced by simply executing a SELECT * FROM on this table while using UTF-16 to connect to the server.
That is either incorrect or ridiculous. Anyway.
I copied the table create schema and dumped all data with my database manager application.
In CSV or TSV the dump file is about 94 MB but you can easily write your own tool to re-insert the records.
in my own binary data format the dump is 29 MB but you must use my database manager or recreate the same importing mechanism what I use.
I'll upload everything on my server overnight and will send the links in DMs - however the data is obfuscated twice, it's still proprietary.
The "Column value is truncated" error can be reproduced by simply executing a SELECT * FROM on this table while using UTF-16 to connect to the server.
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
Hello aehimself,
I downloaded the data but will only be able to look at these things next week since I am on holidays now.
With best regards,
Jan
I downloaded the data but will only be able to look at these things next week since I am on holidays now.
With best regards,
Jan
-
- Platinum Boarder
- Posts: 1918
- Joined: 17.01.2011, 14:17
Re: Possible issue with 64bit Oracle
Note: Is it correct that the CSV file is encoded in WIN1252? Or is it another encoding?
Re: Possible issue with 64bit Oracle
Yes, it is and no, it is not :D
I'm simply calling
Code: Select all
If sl.Count > 0 Then
sl.SaveToFile(Self.FileName);
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2