SQL strings with Unicode and Direct=True

upscene · Post by **upscene** » Mon 18 Jan 2010 14:34

Hi,

I've read several topics on Unicode already, but cannot figure out how to get things done.

I have a database with N(VAR)CHAR columns, so these should support Unicode as far as I can see.

Yet, trying to insert text into these fails when using an INSERT statement with character literals ( eg: N'')

Also, this example statement fails:

Code: Select all

select N'Rollprof仪器', UNISTR('Rollprof仪器') from dual

I need to get this working in both Direct and non-Direct mode.

Got any idea?

With regards,

Martijn Tonies
Upscene Productions[/code]

Plash · Post by **Plash** » Tue 19 Jan 2010 10:16

You should enable OCI Unicode environment to use Unicode characters in SQL statements. Set the OCIUnicode global variable from the OraCall unit to True.

upscene · Post by **upscene** » Tue 19 Jan 2010 10:20

Plash wrote:You should enable OCI Unicode environment to use Unicode characters in SQL statements. Set the OCIUnicode global variable from the OraCall unit to True.

I tried that, but does that also influence "Direct" mode?

All I get is either a hang or access violation or "invalid pointer operation". For example, on a simple "select 1 from dual" I get "Invalid pointer operation".

upscene · Post by **upscene** » Tue 19 Jan 2010 10:42

OK, the errors seem to have a different cause... but:

I created a small new application, single form, TOraSession, TOraQuery, Memo1, TDataSource and TDBGrid.

OraCall.OCIUnicode = True, OraSession.Options.UseUnicode = True.

A button to execute the query in the Memo, linked to the DBGrid.

This query:

Code: Select all

select N'Rollprof仪器', UNISTR('Rollprof仪器') from dual

Still returns:
Rollprof¿¿

for both columns.

What am I doing wrong?

upscene · Post by **upscene** » Tue 19 Jan 2010 11:00

upscene wrote:
Plash wrote:You should enable OCI Unicode environment to use Unicode characters in SQL statements. Set the OCIUnicode global variable from the OraCall unit to True.
I tried that, but does that also influence "Direct" mode?

All I get is either a hang or access violation or "invalid pointer operation". For example, on a simple "select 1 from dual" I get "Invalid pointer operation".

Well, something IS wrong with OCIUnicode = True, as soon as I add that before creating the TOraSession and run a query from a thread, I get an access violation in rtl120.bpl ...

a-s-z · Post by **a-s-z** » Tue 19 Jan 2010 16:16

Hello upscene,

upscene wrote:
upscene wrote:
Plash wrote:You should enable OCI Unicode environment to use Unicode characters in SQL statements. Set the OCIUnicode global variable from the OraCall unit to True.
I tried that, but does that also influence "Direct" mode?

All I get is either a hang or access violation or "invalid pointer operation". For example, on a simple "select 1 from dual" I get "Invalid pointer operation".
Well, something IS wrong with OCIUnicode = True, as soon as I add that before creating the TOraSession and run a query from a thread, I get an access violation in rtl120.bpl ...

I can confirm this problem. This is the same error I have reported yesterday. The error occurs here in direct mode when OciUnicode is set and Options.QueryRecCount is true or Prepare is called before Open. A call from another thread is not needed here to reproduce the access violation.

I hope a new version will get released soon! This error leads to a program hang and so I cannot use Direct mode with OciUnicode in 6.90.0.54!

Regards

upscene · Post by **upscene** » Tue 26 Jan 2010 08:28

Plash, anything on this?

Plash · Post by **Plash** » Tue 26 Jan 2010 08:43

We have fixed the problem with "Invalid pointer operation".

Oracle converts SQL statements to the database character set when it executes them. So you cannot use Unicode characters in SQL if the database character set is not UTF-8.

upscene · Post by **upscene** » Tue 26 Jan 2010 08:52

Plash wrote:We have fixed the problem with "Invalid pointer operation".

Oracle converts SQL statements to the database character set when it executes them. So you cannot use Unicode characters in SQL if the database character set is not UTF-8.

So if I understand correctly, no unicode literals to test/insert/update against N(VAR)CHAR?

Parameters should work?

a-s-z · Post by **a-s-z** » Tue 26 Jan 2010 11:06

Hi Plash,

Plash wrote:We have fixed the problem with "Invalid pointer operation".

Oracle converts SQL statements to the database character set when it executes them. So you cannot use Unicode characters in SQL if the database character set is not UTF-8.

Great news! When will you release the fixed version?

Plash · Post by **Plash** » Wed 27 Jan 2010 08:45

Yes, you cannot use Unicode characters in SQL if the database character set is not UTF-8.

You can use ftWideString parameters. Set the National property of the parameter to True.

We are planning to release the new build today.