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

cybexr
Member
Registered: 2016-09-14
Posts: 78

SynDBOracle@FPC_Linux, Varchar2 column content is larger then expected

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 .

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

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

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

Offline

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

cybexr
Member
Registered: 2016-09-14
Posts: 78

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

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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

result := Dest+length(tmp);

Offline

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

cybexr
Member
Registered: 2016-09-14
Posts: 78

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

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)

Offline

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

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

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

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?

Offline

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

cybexr
Member
Registered: 2016-09-14
Posts: 78

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

@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"/>

Offline

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

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

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

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

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

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

Offline

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

cybexr
Member
Registered: 2016-09-14
Posts: 78

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

@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.

Offline

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

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

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

@cybexr

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

Offline

Board footer

Powered by FluxBB