UCCX Database Script - Wrong String format after the step DB GET

[TBerwart]

Hi team,
I am working on a simple script to get data from a SQL Server database, the query is based on the zip code.
Here is my table :

In the DB Get Step, I use a String variable to store the Dn (and other fields but the problem is the same)

and in the result, the format is strange :

I have U" , \r\n in the variable. but there are not present in the database.
It looks like Unicode ?! I tried to convert it in Java in the script but "StandardCharsets.UTF_8" is not recognized, I think I have to import the class but how ?!

Or do you think the problem is located in the database ?
The collation is "Latin1_General_CI_AI"

it's a problem for me, because I have to forward the call to this Dn and the format is wrong

Any help or advices will be really appreciated

Thank you so much,
Thierry

PS I already spent too much time on it

[Elliot Dierksen]

I have never seen that before, but I have always connected to databases that are storing the data in English. How do you have the data source defined? There might be some parameters you need to set there so that UCCX doesn't consider the data to be unicode.

[TBerwart]

Hello Elliot,

it's also my first experience too ^_^

JDBC URL* : jdbc:jtds:sqlserver://157.164.183.86:1433/voip

I also tried with setting a charset : jdbc:jtds:sqlserver://157.164.183.86:1433/voip;charset=ISO-8859-1

but the result was the same.
I asked to the DBA to change the Collation ... I will check it after.

I have also another idea : the SQL Server is the version 2019. This is not officially supported by UCCX but my queries are working...

I keep you posted after the new collation,
Kind regards,
Thierry

[Elliot Dierksen]

The collation seems like the most likely suspect. Even if SQL Server 2019 isn't explicitly supported, JTDS is handling that so it seems unlikely to me that the SQL version is the problem.

[TBerwart]

Hi Elliot,
They changed the collation to "French_CI_AS" and the result is the same ...
I am still trying to understand what is the problem and how can I fix it (in the DB or in the CCX Script)

[Elliot Dierksen]

I am afraid you are surpassing my SQL server knowledge in this area. I'd be willing to bet that it is the collation. I did a google query for "sql server returning unicode string" and this article looks like it is similar to the problem you are experiencing.

https://stackoverflow.com/questions/28175050/sql-server-query-unicode-string 

[TBerwart]

I stopped trying to understand and I used parsing in Java ... that's not nice at all but it was working ...
String s = new String("U\"997010\r\n");     // to test it
int pos1 = s.indexOf("U\"");
if (pos1 != -1) s = s.substring (pos1 + 2);
s = s.replaceAll("\\r\\n", "");

BUT
the Script Editor told me that there is an error with "U\"":

aaarrrggghhhh
I think I will have to create a custom java class for that in ... java version 1.8.0_26, if I can find this old version somewhere

Thank you again for your advice's !

[TBerwart]

Finally, it's not working Arg

As I was not able to enter "U\"" in the script editor, I just removed the 2 first characters

-> Instead of having "Direction de Wavre" after the treatment of U"Direction de Wavre\r\n, I've got in my variable U"rection de Wavre\r\n"

so it removed the 2 first char but it's not U", it's Di
Meaning that U" is really about encodage
Return to the first step of the game ...

[TBerwart]

Hello guys,

I also checked in the database and it should not be in Unicode :

As the varchar is defined as a simple String ...
Maybe the problem is in the driver ?  I used  jTDS version 1.3.1

Any help should be appreciated

Thanks !

[TBerwart]

Hi Guys,

I found an interesting article on Microsoft SQL Server, the collation, encodage and unicode !
https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/

It seems that SQL Server 2019 convert internally the data in unicode even though if the type of column is varchar !

I asked the support of the DBA... I keep you posted