12-21-2022 01:00 AM
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
12-21-2022 03:22 AM
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.
12-21-2022 03:58 AM
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
12-21-2022 05:46 AM
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.
12-29-2022 01:13 AM
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)
12-29-2022 03:39 AM - edited 12-29-2022 03:40 AM
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
12-29-2022 05:58 AM
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 !
12-29-2022 07:06 AM
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 ...
01-01-2023 11:56 PM
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 !
01-02-2023 08:05 AM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide