Why does the log show garbled characters?

[ZLX]

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.

[SASKiwi · Accepted solution]

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.

[ZLX]

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"; 

[SASKiwi]

@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.

[ZLX]

Nice!