locale issue in msodbcsql17 #18
Comments
|
Hi kitsuyui, The issue you reported may be caused by the incorrect locale setting. The ODBC Driver 17 introduces the encoding support and you can find more details about this from section Character Set Support: It mentions
The application has its own locale which may be different from the operating system locale. For example, the application can be encoded in Shift-JIS while your system environment is UTF-8. If both of your application and system locales are UTF-8 e.g. en_US.UTF-8, it should be fine without setlocale() function call in the application. If it doesn't work, you may explicitly call
If your application is encoded in other code page rather than UTF-8, to make the driver works properly, it's necessary to call setlocale() with proper character set, e.g.
Hope it help a bit. Thanks, |
|
@karinazhou Thank you for your information. The URL says But the sudden aborting happens on normally macOS environment with UTF-8. I don't set charset such as ja_JP.CP932 or zh_cn.CP936. This aborting is not happened when in Linux with both of default POSIX system locale and explicitly setting UTF-8 by Debian (locale-gen), Ubuntu(locale-update). These occurring sudden abortion is the intended too? Why it happens on macOS only? |
|
@kitsuyui Thanks for the reply. I tested with a clean Mac High Sierra with OS: I have the following system locale: I installed the msodbcsql17 from After installing the driver, I ran a simple cpp application to connect to the server and retrieve data. The server is SQL Server 2016. I didn't call setlocale() in my application. I could not get the aborting message you met with. You may try the following code on your side as well to see whether you still get the aborting message or not. Create test table charTest on your server and insert some data into it: Create a locale_test.cpp with the following code: Build the cpp file: And run it on your machine. Moreover, could you please also run locale on your side to check whether they are all in en_US.UTF-8 ? Thanks, |
|
@karinazhou Thank you so much for your help! And I tried with debugging pyodbc, then I found the error happens on Would you be able to check a version of |
|
Hi @kitsuyui , The wide version of SQLDriverConnect will be automatically called if the application is unicode. SQLDriverConnect is just the entry point and the actual functionality in the back stage is the same. NOTE: you can ignore To make the change work, you need to add I can connect to the server with this change without error. Could you please try it again? In order to further investigate this issue, we need more information of your system environment. Could you please run the following commands on your side and let me know the result?
Thanks, |
|
@karinazhou
|
|
@kitsuyui Thank you for the detailed information! The environment you provided looks good to me. Could you try to turn on the ODBC tracing on your mac and run the SQLDriverConnectW version test again? Here's the instruction about how to do the ODBC tracing on Mac and Linux: Please attach the trace log so we can investigate more. One more thing you can try is to add SQLGetDiagRec after you get the return code. Here's the sample code to use SQLGetDiagRec : Also use SQL_DRIVER_NOPROMPT in SQLDriverConnectW if you meet with Dialog Failed error: It should be able to print some error messages for the connection failure. Thanks, |
|
@kitsuyui I have updated the previous post with some modification. The wprintf may not work with driver on Mac so please just use printf instead. Thanks, |
|
@karinazhou Thank you so much for your patience and kindness! Your sample code works fine. But I found more specific conditions that it happen.
HandleDiagnosticRecord doesn't show error when suddenly abort happens. However I got crash report. This dumps more detailed stack trace. These become hint? Reproducible codes#include <stdio.h>
#include <wchar.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <thread>
void showData(SQLHDBC dbc)
{
SQLHSTMT stmt = NULL;
SQLCHAR szData[10];
SQLLEN cbData = 0;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
ret= SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
while (true) {
ret = SQLFetch(stmt);
if (ret != SQL_SUCCESS)
break;
// Get data
SQLGetData(stmt, 1, SQL_C_CHAR, szData, 10, &cbData);
printf("data : %s\n", szData);
}
}
else {
printf("Failed to select the data\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode)
{
SQLSMALLINT iRec = 0;
SQLINTEGER iError;
SQLCHAR wszMessage[1000];
SQLCHAR wszState[10];
if (RetCode == SQL_INVALID_HANDLE)
{
printf("Invalid handle!\n");
return;
}
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
printf("[%5.5s] %s (%d)\n", wszState, wszMessage, iError);
}
}
extern "C" void test() {
HDBC dbc = SQL_NULL_HANDLE;
HENV env = SQL_NULL_HANDLE;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, sizeof(int));
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
wchar_t connectionStr[] = L"driver={ODBC Driver 17 for SQL Server}";
ret = SQLDriverConnectW(dbc, NULL, (SQLWCHAR*)&connectionStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
HandleDiagnosticRecord(dbc, SQL_HANDLE_DBC, ret);
if (SQL_SUCCEEDED(ret)) {
printf("Connected\n");
}
else {
printf("Failed to connect\n");
HandleDiagnosticRecord(dbc, SQL_HANDLE_DBC, ret);
}
showData(dbc);
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}$ g++ -fshort-wchar -shared -std=c++11 -stdlib=libc++ -I /usr/local/opt/msodbcsql17/include/msodbcsql17/ -o locale_test.dylib locale_test.cpp -g -lodbc$ brew install python3Python2It works. It shows an usual error message and it shows either Hello by $ python -c '
import ctypes
try:
ctypes.cdll.LoadLibrary("locale_test.dylib").test()
except Exception:
pass
print("Hello")
'
[ ] [Microsoft][ODBC Driver 17 for SQL Server]Neither DSN nor SERVER keyword supplied (0)
Failed to connect
[ ] [Microsoft][ODBC Driver 17 for SQL Server]Neither DSN nor SERVER keyword supplied (0)
Failed to select the data
HelloPython3It doesn't work. Suddenly abort happen. $ python3 -c '
import ctypes
try:
ctypes.cdll.LoadLibrary("locale_test.dylib").test()
except Exception:
pass
print("Hello")
'
libc++abi.dylib: terminating with uncaught exception of type std::runtime_error: collate_byname<char>::collate_byname failed to construct for C/en_US.UTF-8/C/C/C/C
Abort trap: 6ODBC Trace logCrash Report |
|
@kitsuyui Thank you so much for attaching more clues and reports. That's really helpful :) By following your repro steps, I can get the aborting error on my Mac when using python3 with shared library. That's right. There's no errors when using python2 and executable. From the crash report, line 10: When the SystemLocale singleton is initialized, there's a setlocale(LC_ALL, NULL) call. This is to query the name of the current locale. The return value of setlocale() will be passed to the constructor of std::locale . I tried to put the following line at the beginning of the test() in the cpp file to see the result:
When I set the locale to en_US.UTF-8, I get the following output.This is the result with python2 : This is the result with python3 : This is the result with executable only : When I set the locale to C, the result with python3 changed:
I tried it on Linux with python3 and get this: It seems that python3 somehow breaks the behavior of the old setlocale(LC_ALL, NULL) function on Mac. In order to make your code work properly in python3 with non-C environment, it's better to explicitly add something silimar to Thanks, |
|
@karinazhou Thank you so much. I finally found these reproducible code.
Yeah, Python3 seems to be changed about locale. But it is not a python's bug. Look this Apple Libc source code. (lines from 156 to 191.) Note: OpenBSD also accepts slash separated locales. OpenBSD setlocale(3) Examples reproducible code#include <stdio.h>
#include <wchar.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <locale>
void showData(SQLHDBC dbc)
{
SQLHSTMT stmt = NULL;
SQLCHAR szData[10];
SQLLEN cbData = 0;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
ret= SQLExecDirect(stmt, (SQLCHAR*)"SELECT 1", SQL_NTS);
if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
while (true) {
ret = SQLFetch(stmt);
if (ret != SQL_SUCCESS)
break;
// Get data
SQLGetData(stmt, 1, SQL_C_CHAR, szData, 10, &cbData);
printf("data : %s\n", szData);
}
}
else {
printf("Failed to select the data\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
void HandleDiagnosticRecord (SQLHANDLE hHandle,
SQLSMALLINT hType,
RETCODE RetCode)
{
SQLSMALLINT iRec = 0;
SQLINTEGER iError;
SQLCHAR wszMessage[1000];
SQLCHAR wszState[10];
if (RetCode == SQL_INVALID_HANDLE)
{
printf("Invalid handle!\n");
return;
}
while (SQLGetDiagRec(hType,
hHandle,
++iRec,
wszState,
&iError,
wszMessage,
(SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
(SQLSMALLINT *)NULL) == SQL_SUCCESS)
{
printf("[%5.5s] %s (%d)\n", wszState, wszMessage, iError);
}
}
int main() {
setlocale(LC_ALL, "C/UTF-8/C/C/C/C");
HDBC dbc = SQL_NULL_HANDLE;
HENV env = SQL_NULL_HANDLE;
SQLRETURN ret;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, sizeof(int));
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
wchar_t connectionStr[] = L"driver={ODBC Driver 17 for SQL Server}";
ret = SQLDriverConnectW(dbc, NULL, (SQLWCHAR*)&connectionStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
HandleDiagnosticRecord(dbc, SQL_HANDLE_DBC, ret);
if (SQL_SUCCEEDED(ret)) {
printf("Connected\n");
}
else {
printf("Failed to connect\n");
HandleDiagnosticRecord(dbc, SQL_HANDLE_DBC, ret);
}
showData(dbc);
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return 0;
}$ ./locale_test
libc++abi.dylib: terminating with uncaught exception of type std::runtime_error: collate_byname<char>::collate_byname failed to construct for C/UTF-8/C/C/C/C
Abort trap: 6 |
|
@kitsuyui Thank you for pointing this out:
The reason for this issue is that we call something like std::locale(setlocale(LC_ALL, NULL)) to check the locale of current environment in the driver. When using Python3 on Linux and Mac, the returned locale string from setlocale(LC_ALL, NULL) are with different format if locale is set to UTF-8, for example:
If you add this to your test code without calling the ODBC driver on Mac: You will see the same abort with slash string. Obviously, the std::locale doesn't like slash string on Mac. Instead of checking the LC_ALL, the LC_CTYPE will be sufficient for the driver. I have filed a bug in our tasks and the fix will come with the next release of the ODBC driver. Thank you again for the report. We really appreciate it. |
Wow, that's scary! That's great the new version will be released. I'm looking forward to it. |
|
Hi @kitsuyui , We are pleased to let you know that a new version of ODBC Driver 17 for SQL Server has been released. The new version 17.2 contains the fix for the locale issue. Please feel free to try the new driver :) Thanks, |
|
It works fine. I will close this issue. Thank you very much!!! |
|
interestingly, for those who still have issues check if gives an error, and if yes quick fix that works for me was: |
I found locale issue on msodbcsql17.
Following python code causes critical error.
It is happen on shared library. So it cannot catch on python error handling.
I wrote the detail here: mkleehammer/pyodbc#399
This error is not happen on msodbcsql13.
It seems to be happen on following haskell on macOS too. fpco/odbc#17
So I think this is not pyodbc issue. I think this is msodbcsql17 issue.
The text was updated successfully, but these errors were encountered: