National characters in insert statements

National characters in insert statements

Postby Relaur » Thu 28 May 2015 13:29

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
Relaur
 
Posts: 5
Joined: Thu 28 May 2015 12:47

Re: National characters in insert statements

Postby Relaur » Fri 29 May 2015 10:57

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
Relaur
 
Posts: 5
Joined: Thu 28 May 2015 12:47

Re: National characters in insert statements

Postby Relaur » Fri 29 May 2015 12:09

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. ???
Relaur
 
Posts: 5
Joined: Thu 28 May 2015 12:47

Re: National characters in insert statements

Postby Relaur » Fri 29 May 2015 14:04

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 !
Relaur
 
Posts: 5
Joined: Thu 28 May 2015 12:47

Re: National characters in insert statements

Postby AlexP » Tue 02 Jun 2015 08:03

Hello,

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';
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: National characters in insert statements

Postby Relaur » Tue 02 Jun 2015 09:05

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 ?
Relaur
 
Posts: 5
Joined: Thu 28 May 2015 12:47

Re: National characters in insert statements

Postby AlexP » Wed 03 Jun 2015 09:52

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.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for Oracle