I have problems with national characters in insert/update statements.
Inserting or updating fields with "handwritten" statements fails or changes national characters to a upside-down question marks. When entering national characters through dataaware controls it works.
My Delphi 2010 app is using Clientdatasets connecting to a datasnap middletier service. The middletier is using Devart Oracle Dbexpress driver.
What is causing this behavoiur ?
My database, instance and session parameters are
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_CHARACTERSET WE8MSWIN1252
NLS_COMP BINARY BINARY BINARY
NLS_CURRENCY $ kr
NLS_DATE_FORMAT DD-MON-RR RR-MM-DD
NLS_DATE_LANGUAGE AMERICAN DANISH
NLS_DUAL_CURRENCY $ €
NLS_ISO_CURRENCY AMERICA DENMARK
NLS_LANGUAGE AMERICAN DANISH DANISH
NLS_LENGTH_SEMANTICS BYTE BYTE BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_NUMERIC_CHARACTERS ., ,.
NLS_RDBMS_VERSION 11.2.0.1.0
NLS_SORT BINARY DANISH
NLS_TERRITORY AMERICA DENMARK DENMARK
NLS_TIME_FORMAT HH.MI.SSXFF AM HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM RR-MM-DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR RR-MM-DD HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR HH24:MI:SSXFF TZR
National characters in insert statements
Re: National characters in insert statements
I can reproduce this in a very simple form with a TSqlConnection and a TSqlQuery.
TsqlConnection.Driver : DevartOracleDirect
TsqlQuery.sql.text : insert into log (User,Txt) values ('me','<æ>')
I get: ORA-01756: quoted string not properly terminated
or TsqlQuery.sql.text : insert into log (User,Txt) values ('me','<ø>')
Resulting in Field Txt = <¿>
Devart driver version 6.69
TsqlConnection.Driver : DevartOracleDirect
TsqlQuery.sql.text : insert into log (User,Txt) values ('me','<æ>')
I get: ORA-01756: quoted string not properly terminated
or TsqlQuery.sql.text : insert into log (User,Txt) values ('me','<ø>')
Resulting in Field Txt = <¿>
Devart driver version 6.69
Re: National characters in insert statements
I have tried another thing.
Changing the Driver to DevartOracle (which was the driver we used to use in our old com-based middletier app)
but I can't get connected - I am getting ORA-12545: Connect failed because target host or object does not exist.
I have installed Oracle Instant client 11g2.
What is wrong here. ???
Changing the Driver to DevartOracle (which was the driver we used to use in our old com-based middletier app)
but I can't get connected - I am getting ORA-12545: Connect failed because target host or object does not exist.
I have installed Oracle Instant client 11g2.
What is wrong here. ???
Re: National characters in insert statements
Problem solved.
When using Driver: DevartOracle it works. My problem connecting with this driver was due to a change in syntax for databse: Server:Port:DBname - does no longer connect, but Server:Port/DBname does
Anyway there persists a problem with national characters inTSqlQuery.sql.text when using DevartOracleDIRECT driver !
When using Driver: DevartOracle it works. My problem connecting with this driver was due to a change in syntax for databse: Server:Port:DBname - does no longer connect, but Server:Port/DBname does
Anyway there persists a problem with national characters inTSqlQuery.sql.text when using DevartOracleDIRECT driver !
Re: National characters in insert statements
Hello,
To make such query work, you should set the UseUnicode option and UnicodeEnvironment to True:
To make such query work, you should set the UseUnicode option and UnicodeEnvironment to True:
Code: Select all
SQLConnection.Params.Values['UseUnicode'] := 'True';
SQLConnection.Params.Values['UnicodeEnvironment'] := 'True';
Re: National characters in insert statements
Ah OK, thanks ! And the difference between DevartOracle and DevartOracleDirect (in this respect) is that
UnicodeEnvironment is true in the first and false in the latter. I haven't tried to change those values.
That means that we actually stille have the possibilty to choose between the two.
Which are the pros and cons for these two drivers ?
UnicodeEnvironment is true in the first and false in the latter. I haven't tried to change those values.
That means that we actually stille have the possibilty to choose between the two.
Which are the pros and cons for these two drivers ?
Re: National characters in insert statements
DevartOracle uses the standard client for work with Oracle, so the language settings of the client are used.
DevartOracleDirect allows work with the server directly (using only TCP/IP), that simplifies deployment of applications to end users. However, some specific features of Oracle may be unavailable.
DevartOracleDirect allows work with the server directly (using only TCP/IP), that simplifies deployment of applications to end users. However, some specific features of Oracle may be unavailable.