Problems with zeos Lazarus + Oracle.

elidorio2 · Post by **elidorio2** » 28.12.2008, 20:03

Good afternoon!

I'm having problems with zeos Lazarus + Oracle.
When you write a word in the database, appears each
strange characters.
Example:

the word "FUMAÇA PRETA" appears in the database:
"FUMAÃ‡A PRETA"

I am using the version of Zeos (6.6.4-stable)
Lazarus (0.9.27-2008-12-09) + Oracle XE.

I appreciate any help.

--
Edson Lidorio
Gtalk: elidorio@gmail.com

Post by **mdaems** » 31.12.2008, 11:01

Seems like a charset/encoding problem. I don't know the default client codepage used by Oracle and how your server is configured.
Even more : how do you check the value in the database. It IS possible your consultation program has te display problem.

Maybe you must try to add to the 'codepage=xxx' to the properties field of your tzconnection.
This sends Format('SET CHARACTER SET %s', [FClientCodePage]) to the server at connection time.

Mark

elidorio2 · Post by **elidorio2** » 31.12.2008, 12:29

Good Morning Mark!

I'm using the Oracle charset = AMERICAN_AMERICA.WE8MSWIN1252
I have a question on what you said.

In Component TzConnection and was therefore in the property?

Properties: "CODEPAGE = AMERICAN_AMERICA.WE8MSWIN1252 'Format (' SET CHARACTER SET% s', [FClientCodePage])

--
Edson

Post by **mdaems** » 31.12.2008, 13:24

CODEPAGE = AMERICAN_AMERICA.WE8MSWIN1252

Don't know if spaces are allowed around the equal sign.

But at work we also use oracle and I remember we had trouble with displaying 'strange' characters as well. So maybe you should check what bytes actually reach the server. Isn't there a way you can oracle force to show the hexadecimal representation of every byte in a string? Like "select rawtohex('ç') from dual" So you would know how zeoslib passes the data to the server.

If you're using Toad (or something similar) : make sure this tools shows the data right.

Mark

elidorio2 · Post by **elidorio2** » 31.12.2008, 13:49

Mark,

set this value CODEPAGE = AMERICAN_AMERICA.WE8MSWIN1252 in properties and also did not work.
What is strange, when I see by the example in a Dbgrid appears correct.
The information that are stored in the bank that are wrong.
Performed the same test with Delphi and is normal, the problem is with Lazarus and Oracle that is not related.

Post by **mdaems** » 31.12.2008, 14:21

What is strange, when I see by the example in a Dbgrid appears correct.

Do I understand correctly that when you retrieve the data again using a Lazarus DBGrid it appears correctly?
This looks more like a lazarus error...

Did you try storing the data using Delhpi and reading them using Lazarus and vice-versa?

Did you add a TZSQLMonitor component to your app? Are there differences between the Delphi and Lazarus log? (And do the logs show the Set characterset statement?)

Mark

Mark

elidorio2 · Post by **elidorio2** » 31.12.2008, 14:59

Mark!
Think a crazy thing.

When I saved with Delphi Oracle appears normal at the bank.
And the information that saved with Lazarus, appears wrong with Delphi and with the information that saved the Delphi Lazarus not view.
the problem must be in Lazarus.
Attached is the log of lazarus and delphi.

oracle
2008-12-31 11:53:18 cat: Connect, proto: oracle-9i, msg: CONNECT TO "xe" AS USER "analista3"
2008-12-31 11:53:18 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT
2008-12-31 11:53:18 cat: Execute, proto: oracle-9i, msg: select * from etiqueta_avulsa

2008-12-31 11:53:18 cat: Execute, proto: oracle-9i, msg: select * from etiqueta_avulsa

2008-12-31 11:53:18 cat: Execute, proto: oracle-9i, msg: SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE, DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL, DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE FROM SYS.ALL_TAB_COLUMNS WHERE OWNER LIKE '%' AND TABLE_NAME LIKE 'ETIQUETA_AVULSA' AND COLUMN_NAME LIKE '%'
2008-12-31 11:54:11 cat: Execute, proto: oracle-9i, msg: INSERT INTO ANALISTA3.ETIQUETA_AVULSA (NO_BOLETIM,PARA,AC,DE,DT_ENVIO,OBJETO_ENVIO) VALUES (?,?,?,?,?,?)
2008-12-31 11:54:11 cat: Execute, proto: oracle-9i, msg: INSERT INTO ANALISTA3.ETIQUETA_AVULSA (NO_BOLETIM,PARA,AC,DE,DT_ENVIO,OBJETO_ENVIO) VALUES (:P1,:P2,:P3,:P4,:P5,:P6)
2008-12-31 11:54:11 cat: Execute, proto: oracle-9i, msg: COMMIT

Lazarus

2008-12-31 11:49:50 cat: Execute, proto: oracle-9i, msg: select * from etiqueta_avulsa

2008-12-31 11:49:50 cat: Execute, proto: oracle-9i, msg: SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE, DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL, DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE FROM SYS.ALL_TAB_COLUMNS WHERE OWNER LIKE '%' AND TABLE_NAME LIKE 'ETIQUETA_AVULSA' AND COLUMN_NAME LIKE '%'
2008-12-31 11:50:26 cat: Execute, proto: oracle-9i, msg: INSERT INTO ANALISTA3.ETIQUETA_AVULSA (NO_BOLETIM,PARA,AC,DE,DT_ENVIO,OBJETO_ENVIO) VALUES (?,?,?,?,?,?)
2008-12-31 11:50:26 cat: Execute, proto: oracle-9i, msg: INSERT INTO ANALISTA3.ETIQUETA_AVULSA (NO_BOLETIM,PARA,AC,DE,DT_ENVIO,OBJETO_ENVIO) VALUES (:P1,:P2,:P3,:P4,:P5,:P6)
2008-12-31 11:50:26 cat: Execute, proto: oracle-9i, msg: COMMIT

Post by **mdaems** » 31.12.2008, 15:13

2008-12-31 11:53:18 cat: Connect, proto: oracle-9i, msg: CONNECT TO "xe" AS USER "analista3"
2008-12-31 11:53:18 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT

This part of the Delphi log looks good

If that's also in the Lazarus log, I can't understand where the difference is. The rest of the log seems equal to me.

Next thing you can check is the place where the oracle API buffers are filled:
The function LoadOracleVars in ZDbcOracleUtils.pas . Maybe there's a problem when copying the parameter value to the bind record.

Mark

mse · Post by **mse** » 01.01.2009, 16:17

mdaems wrote: Do I understand correctly that when you retrieve the data again using a Lazarus DBGrid it appears correctly?
This looks more like a lazarus error...

A possible explanation: Lazarus uses utf-8 encoding for all GUI text elements. If the database is not encoded in utf-8 the DB string values must be converted to/from utf-8 in code. IIRC there is a special TField event property for that purpose.
MSEgui on the other hand uses widestrings for GUI text elements. The ZEOS ansistrings are converted from the current system encoding to widestrings and back by the widestring manager.
The MSEgui database components use widstring as buffer for text fields, the conversion takes place on fetching the data from DB and before writing data to DB. The database encoding is selectable either utf-8 or the current system encoding.

Happy new year!

Martin

elidorio2 · Post by **elidorio2** » 01.01.2009, 23:44

Hello Martin!
Thanks for the explanation, the problem is with the unicode of Lazarus and the bank that Oracle is not entedem. I was talking with Mark found some problems, we will have news soon.
Happy New Year to you.

elidorio2 · Post by **elidorio2** » 04.01.2009, 01:36

Maik Hello!

Is attached the problem corrected, and Lazarus in Oracle. It was testing with Delphi and Lazarus, worked perfectly.

UPDATE by mdaems : removed attachment

Post by **mdaems** » 04.01.2009, 14:00

Edson,
Martin,

It was nice working together to find the resulution for the problem. What was going on here?

The oracle OCI expects the local characterset/encoding (set by NLS-parameters) to be used for sending strings to the server. In case of Delphi this was alright, the Delphi encoding did correspond to the encoding expected by the OCI. But as Martin indicated Lazarus components encode the strings in a UTF8 format.

How did we solve it?
Reading the OCI docs we found out the OCIEnvNlsCreate takes two parameters to indicate an alternative charset/encoding to be set for the connection to be used. In case of UTF8 this parameter is 871 (write it down, the value is difficult to find out)
Looking into the ZDbcOracle.pas unit we found out the environment was set up using an other OCI call : OCIEnvInit. This one didn't have that extra parameter.
So we changed the call and added the charactersetid parameters. In case of Delphi : 0, in case of FPC : 871.
These values are hard-coded for every compiler because:
- Value 0 doesn't change current behaviour and it worked for Delphi before
- FPC version now works (at least for Edson

)
- I'm not sure it's usefull to provide a choice
- Providing a choice also has a technical disadvantage. The programmer would have to provide the character set id (instead of a name) and these values are difficult to find when there's no connection yet. There is an OCI function to look up a value but it needs an active environment already.

Is this change ready for the stable branch? Not now. Because we only have tested this new way of initializing an OCI environment with one small utility program on one machine. But if you need this for using oracle with FPC : just copy the 3 units from the zip file above. It's still compatible with the 6.6 branch.

Are there some other users out there using Oracle and zeoslib on Delphi? Please, can you test your application with the new patch as wel?? If you can confirm me your application is a little more complex as the one we tested with and inserting non-standard charachters works like expected, I can move the change to the stable branch

SVN Rev. 560 (Testing branch)

Mark

mse · Post by **mse** » 04.01.2009, 17:41

mdaems wrote: So we changed the call and added the charactersetid parameters. In case of Delphi : 0, in case of FPC : 871.
These values are hard-coded for every compiler because:
- Value 0 doesn't change current behaviour and it worked for Delphi before
- FPC version now works (at least for Edson )

This probably means that it does not work with MSEgui and other frameworks which don't use utf-8 encoding internally.
MSEgui translates from TField.asstring to the widestring type used by MSEgui by behalf of the widestring manager which converts from/to the current system encoding.
Lazarus users AFAIK solve the problem by using an utf-8 encoded database, to set the DB encoding by connection parameters or to do utf-8 conversions in code.
I suggest to talk with the Lazarus developers about the best solution.

Edit:
tdscontroller.options dso_utf8 works for the MSEgui ZEOS datasets, so no problem with the hardcoded utf-8 encoding for Oracle on FPC with MSEgui.

Martin

Post by **mdaems** » 07.01.2009, 11:39

I did check with some people of the Lazarus development team on their IRC channel.
They agree with Martin. The Lazarus developer must set the Codepage himself!
So I changed the patch a little. Now users should be able to

Code: Select all

ZConnection1.Properties.Append('codepage=UTF8');

or

Code: Select all

ZConnection1.Properties.Append('codepage=871');

or whatever oracle codepage id they want to use. (BTW : UTF8 is the only string value supported)

SVN Rev. 562 (Testing branch)

Edson,
Can you please test this? (On your 6.6.4 version

)

elidorio2 · Post by **elidorio2** » 07.01.2009, 13:29

ok, I'll try out tonight