ODBC SQLBindParameter and UNICODE strings
От | Andy Hallam |
---|---|
Тема | ODBC SQLBindParameter and UNICODE strings |
Дата | |
Msg-id | 9qmeng$rbf$1@news.tht.net обсуждение исходный текст |
Список | pgsql-hackers |
Apologies if you think this mail is a little long-winded but I want to be as clear as possible on this. PostgreSQL - 7.1.3 (installed on Linux 2.4.2-2) PSQLODBC.DLL - 07.01.0007 Visual C++ - 6.0 I have a C++ app running on WINDOWS2000 and I am trying to use SQLBindParamater with a unicode (wchar_t) variable. I installed postgreSQL using the following arguments: ./configure --enable-multibyte=UNICODE --enable-unicode-conversion --enable- odbc I have tested my app against SQL SERVER and DB2 and it works fine. (You can run my program against SQL SERVER, DB2 and PostgreSQL by simply setting one of the global variables DBP_SQLSERVER, DBP_DB2 or DBP_POSTGRES to 1) The SQL to generate the database table and test data that my test program uses is as follows: --SQL Server drop table testtable go create table testtable ( col1 NVARCHAR(20) NOT NULL, col2 NVARCHAR(20) NOT NULL, col3 CHAR(20) NOT NULL, col4 INTEGER NOT NULL ) go insert into testtable values (N'record one', N'record one data a', 'record one data b', 1) go insert into testtable values (N'record two', N'record two data a', 'record two data b', 2) go insert into testtable values (N'record three', N'record three data a', 'record three data b', 3) go select * from testtable ---------------------------------------------------------------------------- ---- --DB2 drop table testtable go create table testtable ( col1 VARGRAPHIC(20) NOT NULL, col2 VARGRAPHIC(20) NOT NULL, col3 CHAR(20) NOT NULL, col4 INTEGER NOT NULL ) go insert into testtable values ('record one', 'record one data a', 'record one data b', 1) go insert into testtable values ('record two', 'record two data a', 'record two data b', 2) go insert into testtable values ('record three', 'record three data a', 'record three data b', 3) go select * from testtable ---------------------------------------------------------------------------- ---- --Postgres drop table testtable go create table testtable ( col1 NCHAR VARYING(20) NOT NULL, col2 NCHAR VARYING(20) NOT NULL, col3 CHAR(20) NOT NULL, col4 INTEGER NOT NULL ) go insert into testtable values ('record one', 'record one data a', 'record one data b', 1) go insert into testtable values ('record two', 'record two data a', 'record two data b', 2) go insert into testtable values ('record three', 'record three data a', 'record three data b', 3) go select * from testtable Here is my test program in full: //--- BEGIN PROGRAM SOURCE #include <stdio.h> #include <stdlib.h> #include <windows.h> #include <sqlext.h> int DBP_SQLSERVER = 1; int DBP_DB2 = 0; int DBP_POSTGRES = 0; #define ENV 1 #define DBC 2 #define STMT 3 #define SETCODE 1 #define SQLNOTFOUND 100 void OpenConnecton(void); void CloseConnection(void); void SelectSQL(void); void odbc_checkerr(wchar_t *, int, int); long set_native_sql(wchar_t *, int); void myexit(int); void ChangeSession(wchar_t *); SQLWCHAR out_connect_str[1024] = {0}; SQLWCHAR in_connect_str[1026] = {0}; SQLSMALLINT in_connect_str_len = 1024; SQLSMALLINT out_connect_str_len = 1024; SQLSMALLINT stringlen = 0; HENV henv; HDBC hdbc; HSTMT hstmt; long odbc_rc; long Native_sql_code; #define ATEND (Native_sql_code == 100) wchar_t Msg[SQL_MAX_MESSAGE_LENGTH]; wchar_t strSQL[513] = {0}; long lngCBInd = 0; void wmain(int argc, wchar_t **argv) {OpenConnecton();SelectSQL();CloseConnection(); myexit(0); } //************************************************************************** ****************** void OpenConnecton() {// CREATE THE ENVIRONMENT HANDLEodbc_rc = SQLAllocEnv(&henv);odbc_checkerr(L"OpenConnecton: SQLAllocEnv", ENV, SETCODE); // CREATE THE CONNECTION HANDLEodbc_rc = SQLAllocConnect(henv, &hdbc);odbc_checkerr(L"OpenConnecton: SQLAllocConnect", DBC,SETCODE); // BUILD CONNECTION STRINGif (DBP_SQLSERVER) { swprintf((wchar_t *)in_connect_str, L"Driver={SQL Server};" L"SERVER=MYSEQUELSERVER;" L"DATABASE=mydatabase;" L"UID=me;" L"PWD=me;" L"UseProcForPrepare=0");}else if (DBP_DB2) { swprintf((wchar_t*)in_connect_str, L"DRIVER={IBM DB2 ODBC Driver};" L"UID=me;" L"PWD=me;" L"GRAPHIC=3;" L"DBALIAS=MYALIAS;");}else{ // PostgreSQL swprintf((wchar_t *)in_connect_str, L"DRIVER={PostgreSQL};" L"UID=me;" L"PWD=me;" L"SERVER=MYPOSTSERVER;" L"DATABASE=mydatabase;");} // CONNECT TO SERVER wprintf(L"CONNECTION STRING <%s>\n", in_connect_str);odbc_rc = SQLDriverConnect(hdbc, (SQLHWND)0, (SQLWCHAR *)in_connect_str,(SQLSMALLINT)in_connect_str_len, (SQLWCHAR *)out_connect_str, (SQLSMALLINT)out_connect_str_len, &stringlen,SQL_DRIVER_NOPROMPT );odbc_checkerr(L"OpenConnecton: SQLDriverConnect", DBC, SETCODE); if (DBP_DB2) ChangeSession(L"efacdb"); } //************************************************************************** ****************** void CloseConnection(void) { wprintf(L"CLOSING CONNECTION\n"); odbc_rc = SQLDisconnect(hdbc);odbc_checkerr(L"CloseConnection: SQLDisconnect", DBC, SETCODE); odbc_rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);odbc_checkerr(L"CloseConnection: SQLFreeHandle", DBC, SETCODE); odbc_rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);odbc_checkerr(L"CloseConnection: SQLFreeHandle", ENV, SETCODE); } //************************************************************************** ****************** void SelectSQL(void) {long lngValue = 0;long rows = 0;wchar_t strBindInUni[21] = {0};wchar_t strBindOut[21] = {0};char strBindInAsc[21] = {0}; //****************************** // SELECT 1 (bind using INTEGER) //******************************odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);odbc_checkerr(L"SELECT 1: SQLAllocHandle",STMT, SETCODE); lngValue = 1; odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &lngValue, 0, NULL);odbc_checkerr(L"SELECT 1: SQLBindParameter", STMT, SETCODE); odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col4 = ?", SQL_NTS);odbc_checkerr(L"SELECT 1: SQLExecDirect", STMT, SETCODE); odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut), &lngCBInd);odbc_checkerr(L"SELECT 1: SQLBindCol", STMT, SETCODE); odbc_rc = SQLFetch(hstmt);set_native_sql(L"SELECT 1: SQLFetch", STMT); if (ATEND) { wprintf(L"SELECT 1: SQLFetch = ATEND\n"); SQLFreeStmt(hstmt, SQL_DROP); CloseConnection(); myexit(0);}odbc_checkerr(L"SELECT 1: SQLFetch", STMT, 0); wprintf(L"SELECT 1: DATA FETCHED: strBindOut = <%s>\n", strBindOut); SQLFreeStmt(hstmt, SQL_DROP); //*********************************** // SELECT 2 (bind using ASCII STRING) //***********************************odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);odbc_checkerr(L"SELECT 2: SQLAllocHandle",STMT, SETCODE); strcpy(strBindInAsc, "record two data b"); odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(strBindInAsc), 0, strBindInAsc, 0, NULL);odbc_checkerr(L"SELECT 2: SQLBindParameter", STMT, SETCODE); odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col3 = ?", SQL_NTS);odbc_checkerr(L"SELECT 2: SQLExecDirect", STMT, SETCODE); odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut), &lngCBInd);odbc_checkerr(L"SELECT 2: SQLBindCol", STMT, SETCODE); odbc_rc = SQLFetch(hstmt);set_native_sql(L"SELECT 2: SQLFetch", STMT); if (ATEND) { wprintf(L"SELECT 2: SQLFetch = ATEND\n"); SQLFreeStmt(hstmt, SQL_DROP); CloseConnection(); myexit(0);}odbc_checkerr(L"SELECT 2: SQLFetch", STMT, 0); wprintf(L"SELECT 2: DATA FETCHED: strBindOut = <%s>\n", strBindOut); SQLFreeStmt(hstmt, SQL_DROP); //************************************* // SELECT 3 (bind using UNICODE STRING) //*************************************odbc_rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);odbc_checkerr(L"SELECT 3:SQLAllocHandle", STMT, SETCODE); wcscpy(strBindInUni, L"record three"); odbc_rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, sizeof(strBindInUni), 0, strBindInUni, 0, NULL);odbc_checkerr(L"SELECT 3: SQLBindParameter", STMT, SETCODE); odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)L"select col2 from testtable where col1 = ?", SQL_NTS);odbc_checkerr(L"SELECT 3: SQLExecDirect", STMT, SETCODE); odbc_rc = SQLBindCol(hstmt, 1, SQL_C_WCHAR, strBindOut, sizeof(strBindOut), &lngCBInd);odbc_checkerr(L"SELECT 3: SQLBindCol", STMT, SETCODE); odbc_rc = SQLFetch(hstmt);set_native_sql(L"SELECT 3: SQLFetch", STMT); if (ATEND) { wprintf(L"SELECT 3: SQLFetch = ATEND\n"); SQLFreeStmt(hstmt, SQL_DROP); CloseConnection(); myexit(0);}odbc_checkerr(L"SELECT 3: SQLFetch", STMT, 0); wprintf(L"SELECT 3: DATA FETCHED: strBindOut = <%s>\n", strBindOut); SQLFreeStmt(hstmt, SQL_DROP); } //************************************************************************** ****************** void ChangeSession(wchar_t *session) {wchar_t strSQL[256] = {0}; swprintf(strSQL, L"SET SCHEMA = %s", session); wprintf(L"ChangeSession: session <%s>\n", session); odbc_rc = SQLAllocStmt(hdbc, &hstmt);odbc_checkerr(L"ChangeSession: SQLAllocStmt", STMT, SETCODE); odbc_rc = SQLExecDirect(hstmt, (SQLWCHAR *)strSQL, SQL_NTS);odbc_checkerr(L"ChangeSession: SQLExecDirect", STMT, SETCODE); SQLFreeStmt(hstmt, SQL_DROP); } //************************************************************************** ****************** void myexit(int num) {wchar_t s[2] = {0};_getws(s); exit(num); } //************************************************************************** ****************** long set_native_sql(wchar_t *str, int handle_type) { wchar_t SqlState[6]; SWORD MsgLen; //wprintf(L"set_native_sql: IN: odbc_rc = %ld, Native_sql_code = %ld, Msg <%s>\n", odbc_rc, Native_sql_code, Msg);if (odbc_rc == SQL_SUCCESS || (DBP_SQLSERVER && odbc_rc == SQL_SUCCESS_WITH_INFO)) return Native_sql_code = SQL_SUCCESS; if (handle_type == STMT) { if (odbc_rc == SQLNOTFOUND) return Native_sql_code = SQLNOTFOUND; else { if (SQLGetDiagRec( SQL_HANDLE_STMT, hstmt, 1, SqlState, &Native_sql_code, Msg, SQL_MAX_MESSAGE_LENGTH - 1, &MsgLen) != SQL_SUCCESS){ // Should never occur...? wprintf(L"STMT: (%s): ODBC produced an error but no error code could be found (%s)\n", str, Msg); myexit(0); } }}else if (handle_type == DBC) { if (SQLGetDiagRec( SQL_HANDLE_DBC, hdbc, 1, SqlState, &Native_sql_code, Msg, SQL_MAX_MESSAGE_LENGTH - 1, &MsgLen) != SQL_SUCCESS) { // Should never occur...? wprintf(L"DBC: (%s): ODBC produced an error but no error code could be found.", str); myexit(0); }}else { if (SQLGetDiagRec( SQL_HANDLE_ENV, henv, 1, SqlState, &Native_sql_code, Msg, SQL_MAX_MESSAGE_LENGTH - 1, &MsgLen) != SQL_SUCCESS) { // Should never occur...? wprintf(L"ENV:(%s): ODBC produced an error but no error code could be found.", str); myexit(0); }} if (Native_sql_code == 0) { // We have an error but their is no // native sql code, so set to 1000. Native_sql_code = 1000;} Native_sql_code = -Native_sql_code; return Native_sql_code; } //************************************************************************** ****************** void odbc_checkerr(wchar_t *str, int stattype, int checktype) { //wprintf(L"odbc_checkerr: odbc_rc = %ld\n", odbc_rc); if (odbc_rc == SQL_SUCCESS || ((DBP_SQLSERVER || DBP_DB2) && odbc_rc == SQL_SUCCESS_WITH_INFO)) { Native_sql_code = SQL_SUCCESS; return;} if (checktype == SETCODE) set_native_sql(str, stattype); if (Native_sql_code == SQL_SUCCESS) return; wprintf(L"ODBC ERROR:(%s) %ld (%s).", str, Native_sql_code, Msg); //CloseConnection(); myexit(0); } //--- ENDPROGRAM SOURCE And here is the output generated from my program running against the 3 databases: SQL SERVER: CONNECTION STRING <Driver={SQL Server};SERVER=MYSEQUELSERVER;DATABASE=mydatabase;UID=me;PWD=me;UseProcForPr epare=0> SELECT 1: DATA FETCHED: strBindOut = <record one data a> SELECT 2: DATA FETCHED: strBindOut = <record two data a> SELECT 3: DATA FETCHED: strBindOut = <record three data a> CLOSING CONNECTION DB2: CONNECTION STRING <DRIVER={IBM DB2 ODBC Driver};UID=me;PWD=me;GRAPHIC=3;DBALIAS=MYALIAS;> ChangeSession: session <efacdb> SELECT 1: DATA FETCHED: strBindOut = <record one data a> SELECT 2: DATA FETCHED: strBindOut = <record two data a> SELECT 3: DATA FETCHED: strBindOut = <record three data a> CLOSING CONNECTION PostgreSQL: CONNECTION STRING <DRIVER={PostgreSQL};UID=me;PWD=me;SERVER=MYPOSTSERVER;DATABASE=mydatabase;> SELECT 1: DATA FETCHED: strBindOut = <record one data a> SELECT 2: DATA FETCHED: strBindOut = <record two data a> set_native_sql: 01: Native_sql_code = 0, Msg <[Microsoft][ODBC Driver Manager] SQL data type out of range> ODBC ERROR:(SELECT 3: SQLBindParameter) -1000 ([Microsoft][ODBC Driver Manager] SQL data type out of range). As you can see I can succesfully use an ASCII character string for an INPUT parameter when binding but not a UNICODE character string. Surely PostgreSQL supports binding of UNICODE character strings ? Thanks for any help on this. Andy ahm@exel.co.uk
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Denis A UstimenkoДата:
Сообщение: Re: compiling libpq++ on Solaris with Sun SPRO6U2 (fixed
Следующее
От: Lee KindnessДата:
Сообщение: Re: compiling libpq++ on Solaris with Sun SPRO6U2 (fixed