Convert value of bind variable to valid value like ‘N’ prefix?

[marel]

I have some Arabian addresses in a file, which needs to be imported in a database.
It looks like this: شارع حسن أفلاطون، أرض الجولف

Using a SQL string in application like
“update address set name1 = N’” || strVar || “’ where nr = 1000”
working fine. A selection in SQLTalk and the Application shows the correct value.

Using the SQL string with bind var (“update address set name1 = :strVar where nr = 1000”) stores just some ‘?’ in the database.
It’s exactly the same result like removing the ‘N’ prefix from the working SQL string.
I need to use bind variables. What’s the conversion function which do the same like the ‘N’ prefix?

I’m using TD 6.3 and SQLBase 12.3

Martin

[Steve Leighton]

.
Using SQLBase - good to see.

Instead of applying the 'N' prefix in your Sql Statement, you need to apply it to your database column/s.

NCHAR (or NVARCHAR) behaves identically to CHAR or VARCHAR , except that it stores national characters (Unicode) rather than just ANSI characters.
This data type allows a maximum of 450 characters (900 bytes).
LONG NVARCHAR stores national characters (Unicode) rather than just ANSI characters. This data type allows storage of data longer than 900 bytes.
This comes at a cost, it takes twice as much space to store Nvarchar than varchar.

e.g.

Code: Select all

Create table ADDRESS (  name1 Nvarchar(254) )
or
Alter table ADDRESS modify name1 Nvarchar(254)

then the following works fine:

Code: Select all

Set strVar = 'شارع حسن أفلاطون، أرض الجولف'
!
Call SqlPrepareAndExecute( hSql, 'Update address set name1 = :strVar where nr = 1000')
Call SqlCommit(hSql)

[marel]

Thanks for the answer.

I'm using NVARCHAR and the string is correctly stored when using 'N' prefix in SQLTalk or using a composited SQL string like <SQL> || 'N' || strVar || <SQL>.
Composited SQL strings are not allowed in the application because this enables SQL injection and/or need to manipulate the content of the strVar to prevent invalid SQL strings with special characters inside. So we need to use bind vars.

As far as I know, the 'N' prefix changes the national charset to the SQLBase unicode charset.
Is there any converting function available which can be used in a TD application which doing the same?

Martin

[Steve Leighton]

.
I too am using TD6.3 and SB12.3 correction SB12.1.1 , and all I know is that when my dB columns are defined as varchar(254) ,
the result of the following (using TD with a bind var ) is:

Code: Select all

set sVar = 'شارع حسن أفلاطون، أرض الجولف'
update TEST set col1 = :sVar where colPK = 1
/
commit
/
select * from TEST
/

gives

Code: Select all

????? ??????  ???? ??????? ?????

which indicates that SQLBase cannot find the characters on the default ( none UNicode ) code page.

and when my dB columns are defined as Nvarchar(254) ,
the result of the following (using TD with a bind var ) is:

Code: Select all

set sVar = 'شارع حسن أفلاطون، أرض الجولف'
update TEST set col1 = :sVar where colPK = 1
/
commit
/
select * from TEST
/

gives

Code: Select all

 شارع حسن أفلاطون، أرض الجولف

which indicates that SQLBase can find the characters on the UNicode code page.

All updates using bind vars.
No 'N' prefix other than on the column definition .

[marel]

Thanks for the help.

The used column's type is nvarchar(64).
In SQLTalk it works like this:

Code: Select all

update TEST set col1 = 'شارع حسن أفلاطون، أرض الجولف' where colPK = 1
/
commit
/
select * from TEST
/
result: ????? ??????  ???? ??????? ?????

Code: Select all

update TEST set col1 = N'شارع حسن أفلاطون، أرض الجولف' where colPK = 1
/
commit
/
select * from TEST
/
result: شارع حسن أفلاطون، أرض الجولف

The difference is just the 'N' prefix.

It works the same way in application.
This means when using a composited SQL string without 'N' prefix, only '???...' are stored in database.
When using a composited SQL String with the 'N' prefix, the correct (unicode) value is stored in database.

It's not allowed for us to use composited SQL strings in application.
We need to use bind vars in SQL commands.
So I'm still looking for the correct Team Developer function which do the same (string) value conversion like the 'N' prefix.

Martin

BTW: My Windows is using german charset. Maybe this is the reason why the SQLTalk behavior is different?

[Steve Leighton]

Hello Martin
I am thinking this could be a fault in SQLBase 12.3 - unless someone knows otherwise ?? which you should report . https://support.opentext.com/csm/
Because I have tested all scenarios in SQLBase 12.1.1 and it all works fine.
i.e.
Assuming column defined as Nvarchar(nn):

• In SQLTalk AND TeamDeveloper , literals ( composited SQL string ) do need the 'N' prefix.

• In TeamDeveloper , bind variables do not need the 'N' prefix. It is implicit so not needed.

This works fine using TeamDeveloper 6.3 with SQLBase 12.1.1 .

[marel]

Update: It looks like it's a TD 6.3.9 bug.
I tested the behavior with TD 7.3.5.
No need for 'N' prefix or any conversion with TD 7.3.5 and existing SQLBase 12.3

It's time for a TD Upgrade

Thanks for your help!

Martin