ODBC SQLBindParameter and UNICODE strings

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