Thanks for the suggestions, Atanu.
Storing the data as base64 is an interesting idea. The overhead of encoding on the way to the DB and decoding on the way out of the DB would probably be acceptable, but would have to test. Unfortunately, I think storing the data as a base64-encoded string would prevent us from being able to easily query on this column and therefore I don't think it will work for our use case.
Creating a custom Java connector - or augmenting the existing integration.jar file - to handle NVARCHAR would certainly be another option, but much more involved and not something we have the time to tackle for this project.
For now, we will forgo parameters for these NVARCHAR types and just use the N'Value' syntax. However, I went ahead and submitted an IBM idea for NVARCHAR support here: https://ideas.ibm.com/ideas/IWF-I-1246
Original Message:
Sent: Sat November 30, 2024 01:48 AM
From: Atanu Roy
Subject: Unicode support with OOTB System Data SQL services
Hello Don,
Hope you are doing well!
I can think of two other alternatives -
- Store data as dase64 or hexadecimal strings - SQL Server decodes Base64 to Unicode.
- Create custom Java integration with JDBC - Full control over SQL operations and datatype handling
Thanks!
------------------------------
Atanu Roy
Lead Engineer
London Stock Exchange Group
Original Message:
Sent: Mon November 25, 2024 08:26 AM
From: Don Williams
Subject: Unicode support with OOTB System Data SQL services
We need to support saving unicode characters entered in a coach to a MS SQL Server database and we use the OOTB System Data toolkit SQL services, specifically SQL Execute Multiple Statements (SQLResult).
The column we have defined is an NVARCHAR type, but the BAW SQL services do not appear to support the NVARCHAR datatype as a SQLParameter. When inserting the data using a parameterized query and specifying VARCHAR as the parameter type (since that is all the BAW SQL services support), it comes in looking like this:
SQL:
CREATE TABLE TestUnicode (Test1 NVARCHAR(100), Test2 NVARCHAR(100))
BAW SQL:
tw.local.sqlStatement[0].sql = "INSERT INTO TestUnicode (Test1, Test2) VALUES (?, ?)";
tw.local.sqlStatement[0].parameters[0] = {value: "test1", type: "VARCHAR", mode: "IN"};
tw.local.sqlStatement[0].parameters[1] = {value: "المهم الاعتناء بالمريض، وأن", type: "VARCHAR", mode: "IN"};
If I run the following SQL in the SQL Server Management Studio, it works fine proving the column type is correct and can handle the unicode characters:
SQL:
INSERT INTO TestUnicode (Test1, Test2) VALUES ('test2', N'المهم الاعتناء بالمريض، وأن')
Obviously, the goal would be to used parameterized queries, but I don't think it's possible to support the N'value' syntax using a parameterized query and BAW doesn't support the NVARCHAR datatype. As a result of these limitations, the solution I've gone with for now is to not parameterize columns that need to support unicode, so it looks like this:
BAW SQL:
tw.local.sqlStatement[0].sql = "INSERT INTO TestUnicode (Test1, Test2) VALUES (?, N'المهم الاعتناء بالمريض، وأن'))";
tw.local.sqlStatement[0].parameters[0] = {value: "test1", type: "VARCHAR", mode: "IN"};
Before settling on this approach, I wanted to check with the community: has anyone successfully inserted unicode data into a SQL Server database using parameterized queries (SQLParameter) with BAW OOTB SQL services? Thanks in advance -Don
------------------------------
Don Williams
------------------------------