Possible issue with 64bit Oracle

[MJFShark]

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

[marsupilami]

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.

[MJFShark]

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

[marsupilami]

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:

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);

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.

[marsupilami]

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

[MJFShark]

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

[marsupilami]

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

Unfortunately that is only true for new array elements. Old elements will stay the same as they were.

[MJFShark]

Oops! Good point! Thanks again!

-Mark

[aehimself]

In the mean time I submitted my request to our legal team for a data extract of the table where the UTF16 issue can be reproduced. I'll keep you posted.

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?

[marsupilami]

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?

[aehimself]

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.

[marsupilami]

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

[marsupilami]

Note: Is it correct that the CSV file is encoded in WIN1252? Or is it another encoding?

[aehimself]

Note: Is it correct that the CSV file is encoded in WIN1252?

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);

to save the export file so it's normal that it is using my codepage. In this era it definitely should be UTF8 though so you just pointed out a bug :)