cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with Dynamic SQL and Unicode characters

Former Member
0 Kudos

Hello,

I am looking for help regarding the use of dynamic SQL and Unicode strings.

I am working on a large application, which has been around for 15 years or more.  This application has lots - many thousands - of dynamic SQL statements that are built and run with the Execute Immediate command.

An example of one of these statements is:

"UPDATE tbl_xyz SET is_created = 'Y' WHERE xyz_code = 'ALPHA'"

We are now investigating the support of Unicode data and have come to realize that SQL Server, our DB of choice, requires an N before the string.

Our database needs to support English, Portuguese, French, Spanish and Russian characters, so the datatypes being used are "nchar" and "nvarchar".

This would be an enormous undertaking if the only solution is to modify each and every one of these statements, along with any similar statements used by CreateFromSQL statements.   As I said there are many thousands of these statements.

Has anyone else encountered this situation, and found a way to resolve it?

Using PowerBuilder 12.5 build 2511

and SQL Server 2008, 2012

Thanks,

Heather Homeniuk

View Entire Topic
Former Member
0 Kudos

Hi Heather;

   Normally, the answer would be that you have to change all your embedded SQL syntax where literals are used to include the N'.

  The good news is that you could code & fix this issue in one place if all your Char & VarChar column types are being remapped to their Nxxxxxxx equivalent. Assuming that is the case, the SQL syntax fix can be trapped in the TRANSACTION object. In PB 12 and higher a new SQLPreview event! This event on the TO is the same as the DW/DS container where the application can modify the SQL leaving the application.

  Now the trick will be to parse the DML and replace each instance of 'xxxxxx'  with N'xxxxxx' and then reissue the modified DML statement over to SS.

Food for thought.

regards ... Chris

Former Member
0 Kudos

Chris,

Thanks for the help.

I will be investigating this as a possible solution for my situation.

Heather

Former Member
0 Kudos

Hi Heather;

  I just had another thought this morning. You could also use the ORCA interface API from PB to build a PB application that would sniff through your current PB application;s code and modify all the inline DML to reconfigure it to use the N'xxx' format.

Good luck!

Regards ... Chris