topic Why does the log show garbled characters? in SAS Enterprise Guide

Why does the log show garbled characters?

ZLX — Mon, 07 Mar 2022 02:18:07 GMT

I am try to connect to MySQL database on remote server by using ODBC. I don't know whether the statement is wrong or something else because the log shows a lot of garbled characters. The SAS EG server system encode has been set to utf-8 already and the system language is Chinese.

Re: Why does the log show garbled characters?

SASKiwi — Mon, 07 Mar 2022 03:41:39 GMT

Database connection strings are notoriously tricky. They are sensitive to spaces, line feeds, lower and uppercase and so on. I doubt it will work the way you have it spread over several lines. This is what a working ODBC connection to SQL Server looks like:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

Has the correct ODBC driver been installed on the SAS App server? Check with your SAS administrator for the right set up.

Regarding the garbled characters. This is most likely caused by differing encoding / language settings in SAS versus MySQL. Getting help from SAS Tech Support is a good idea.

Re: Why does the log show garbled characters?

ZLX — Mon, 07 Mar 2022 09:48:41 GMT

Thanks for your remarkable prompt, which "Database connection strings are notoriously tricky. They are sensitive to spaces" inspires me quite a lot!

The driver name after DRIVER= option must not be with a leading space! It is my personal habit to type spaces between the euqal sign, which confused me pretty much when facing this scenario. I must watch out for this careully thereafter.

I also found that the value of DATABASE= option is the schema name, to which waited to connect. The MySQL database structure should be distinguished from MS SQL Server.

Thanks for your help again!!!!!!!!!

#Failed - with leading space
libname mysqldb odbc noprompt = "driver = MySQL ODBC 8.0 Unicode Driver;server=xxx;port=xxx;uid=xxx;pwd=xxx;database=xxx"; 

#Successful - without leading space
libname mysqldb odbc noprompt = "driver=MySQL ODBC 8.0 Unicode Driver;server=xxx;port=xxx;uid=xxx;pwd=xxx;database=xxx";

Re: Why does the log show garbled characters?

SASKiwi — Mon, 07 Mar 2022 19:50:21 GMT

@ZLX - I should have mentioned this very useful website: ConnectionStrings.com - Forgot that connection string? Get it here!

Yes, leaving out spaces is a very good idea when testing connection strings.

Re: Why does the log show garbled characters?

ZLX — Tue, 08 Mar 2022 00:47:33 GMT

Nice!