SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

cybexr · 2018-12-04 07:02:00

SynDBOracle.pas when compiled with FPC target linux64, some varchar2 column value are larger ( 2 widechar or 5-6 bytes ) then expected, and the content encoding is correct (oracle-db is codepage 936, the client-got utf8 correctly) , but something more weired is some column-value are just as exptected.

The server-db connection info:

20181204 05594455 # SQL SynDBOracle.TSQLDBOracleStatement(00007FD647A5C830) SELECT NLS_CHARSET_ID(PROPERTY_VALUE) FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='NLS_CHARACTERSET'
20181204 05594456 # DB SynDBOracle.TSQLDBOracleStatement(00007FD647A5C830) 1 row(s) in 290us
20181204 05594456 # info SynDBOracle.TSQLDBOracleConnection(00007FD647AEB220) Connected to 172.16.16.100/orcl as haha with libclntsh.so rev. 11.2.0.4, codepage 936 (852/ZHS16GBK)

some other tests:
1. Same code & same sql compiled with Delphi7 running on windows is OK
2. Same code( just repacle syndboracle with zdbc) & same sql compiled with FPC running on linux 64 column-length is OK
3. btw , the zdbc resultset seems lost field length info, so it's not an alternative to me .

mpv · 2018-12-04 10:37:38

Did you try with nvarchar2 instead of varchar2 In our database we create nvarchar2 column for national (Cyrillic in my case) character column and all work as expected

cybexr · 2018-12-04 13:34:15

No it's a very old database, all string column is varchar2. And my server code fetch data uses: ISQLDBRows.FetchAllToBinary, return binary to client.
the situation looks like :

oracle-column: varchar2(10)

Delphi7@win32 got value(hex):
E7 94 B7, these three bytes are one character in Chinese means "man", and it's correct.

FPC@linux got value(hex):
E7 94 B7 E7 BA A7, the last three bytes are not expected.

thank you mpv, seems some FPC ansiconvert works failed, but not got a clue. Any hint is helpful.

ab · 2018-12-04 14:07:55

Try to find out if TSQLDBOracleConnection.STRToUTF8 is wrong, and why...

Perhaps there is a problem in FPC's code of TSynAnsiConvert.AnsiBufferToUnicode in SynCommons.pas.
I will check Ansi2UnicodeMoveProc() arguments.

ab · 2018-12-04 14:20:53

Could you please test in line 17834 of SynCommons.pas:

result := Dest+length(tmp);

cybexr · 2018-12-04 15:37:36

Thank you very much , ab, for so quick response.

as you stated, modify function TSynAnsiConvert.AnsiBufferToUnicode,
original code result := Dest+SourceChars;
fixed code result := Dest+length(tmp);

Works fine now , got exactly same data with D7@win32.
And I'm curios is it depends on OS? My ubuntu server installed Chinese locales, if it's not, will the Ansi->unicode conversion OK?

Last edited by cybexr (2018-12-04 15:54:29)

EgonHugeist · 2018-12-04 15:42:57

cybexr wrote:

3. btw , the zdbc resultset seems lost field length info, so it's not an alternative to me .

Could you please explain what that means? Which info is lost?

cybexr · 2018-12-04 16:06:00

@EgonHugeist

for Oracle- mycolumn: varchar2(10)

SyndbOracle will produce dataset contains
<FIELD fieldtype="string.uni" attrname="mycolumn" WIDTH="22"/>

while SynDBZeos will produce dataset like:
<FIELD attrname="mycolumn" fieldtype="bin.hex" SUBTYPE="Text"/>

EgonHugeist · 2018-12-04 16:21:56

cybexr wrote:

@EgonHugeist
for Oracle- mycolumn: varchar2(10)
SyndbOracle will produce dataset contains
<FIELD fieldtype="string.uni" attrname="mycolumn" WIDTH="22"/>
while SynDBZeos will produce dataset like:
<FIELD attrname="mycolumn" fieldtype="bin.hex" SUBTYPE="Text"/>

Which dataset? Looking to that i do understand your point of view.
@AB
Where are these informations comming from? Do i miss a point in mORMot wonderland?

ab · 2018-12-04 17:50:17

Encoding problem is fixed by https://synopse.info/fossil/info/bb91db8b0c
Only for FPC on POSIX (non Windows) system.

cybexr · 2018-12-05 01:16:03

@EgonHugeist

my server fetch data by ISQLDBRows.FetchAllToBinary(a Response:TRawByteStringStream as a parameter) and return Response ,
then client will create Tclientdataset uses SynDBMidasVCL.ToClientDataSet() funtion.

the difference comes from FetchAllToBinary, below is the partition about column definition in the byteString:

SynDBOracle
.. 41 4D 45 06 0B 0A
ZEOS
.. 41 4D 45 06 00 0A

the same three byte (41 4D 45) represents Fields name, then type & field size. we can see ZEOS lost 0B, which means length 11.

EgonHugeist · 2019-03-12 15:55:25

@cybexr

FYI: fixed in trunk and all testing branches.
May be you find time to confirm it?

mORMot Open Source

#1 2018-12-04 07:02:00

SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#2 2018-12-04 10:37:38

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#3 2018-12-04 13:34:15

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#4 2018-12-04 14:07:55

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#5 2018-12-04 14:20:53

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#6 2018-12-04 15:37:36

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#7 2018-12-04 15:42:57

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#8 2018-12-04 16:06:00

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#9 2018-12-04 16:21:56

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#10 2018-12-04 17:50:17

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#11 2018-12-05 01:16:03

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

#12 2019-03-12 15:55:25

Re: SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

Board footer