• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How do we get Dynamic SQL to use UTF8 Foreign Language Characters?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (1)

Resolved How do we get Dynamic SQL to use UTF8 Foreign Language Characters?

  1. New
  2. Issue
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Tuesday, 10 December 2024 21:43 PM UTC

Hi - Help Needed

Using PB 17.  Connecting to a legacy SQL Anywhere Database V17 with Char Character Encoding Windows-1252.  NChar encoding UTF-8

We have no trouble with reading and writing foreign language characters to nvarchar columns using a connection with Bind Valiables - however we cannot generally use this as the use of Bind Variables causes problems with our app with very large queries. At the time converting the database is not practical.

So when we connect using a datawindow to update nvarchar UTF8 foreign characters, we use the Bind Variable in the ODBC connection - this works well for extended characters reads and writes.

The problem is with the SQLSA using format 3 or 4 Dynamic SQL - even if we tell it to use the SQLCA_BIND connection in the prepare,  queries and database functions still return garbage characters when foreign characters (Japanese, Cyrillic, etc) are used in metadata.

How can we do dynamic SQL and get it to support foreign characters??

This is sort of urgently needed fix.

Any help or suggestions would be appreciated.

Thanks

Glenn Barber

 

  1. PowerBuilder (Appeon)
PowerBuilder Graph Question
Anyone have experience with PB App changing SQLAny...
Responses (1)
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Wednesday, 11 December 2024 03:30 AM UTC
  2. PowerBuilder
  3. # 1

After further evaluation we discovered this is not an issue with dynamic SQL - but rather a challenge with SQL Anywhere's SQL processing engine.

The problem is submitting anything into a sql statement which has UTF8 Characters which are not in the Windows-1252 character set.
 
What happens is that SQL Anywhere automatically converts characters in the SQL to the defined CHAR Character set (which in this database is Windows-1252). Our NCHAR character set is UTF8 which means we can read and write from the database getting characters represented in UTF - but we cant supply extended characters in a SQL Statement.
 
Even the simplest query eats the data....
image.png
 
Next steps is to find if there is a way around this issue in our current database configuration.  Otherwise we have to change the character set used for CHAR and rebuild and retest everything....
 
If anyone knows a way to override SQL Anywhere's translation on the SQL parsing - would be great help.
 
But for now we can mark this as solved (with qualifications)
 
Thanks
 
Glenn
Comment
Mark Goldsmith
  1. Mark Goldsmith
  2. Wednesday, 11 December 2024 17:44 PM UTC
Hi Glenn,

Are you able to provide an example of the dynamic SQL that you are using (even the simplest query)?  If the image you provided did show an example I can't see it as the image isn't displaying (at least on my machine).

Regards...Mark.
  1. Helpful
Glenn Barber
  1. Glenn Barber
  2. Wednesday, 11 December 2024 18:07 PM UTC
SELECT 'ABC_Цареубийца' literalcol FROM DUMMY;

returns 'ABC_'
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.