[BUGS] BUG #1621: ODBC binding Unicode null-terminated string parameter doesn't work

[Jeremy Stanley]

The following bug has been logged online:

Bug reference:      1621
Logged by:          Jeremy Stanley
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 8.0.2
Operating system:   Windows XP SP2
Description:        ODBC binding Unicode null-terminated string parameter
doesn't work
Details: 

When I attempt to bind a null-terminated Unicode string parameter using
SQLBindParameter, the database engine receives a string representation of
today's date instead of the data I passed.  The same issue does not occur if
I explicitly specify the length of the string instead of using SQL_NTS.

Steps to reproduce:

(Step 1)  Install PostgreSQL 8.0.2 for Win32, full install


(Step 2)  Create database

CREATE DATABASE pgodbctest
  WITH ENCODING='UNICODE';


(Step 3)  Create table

CREATE TABLE testtable
(
   stringa1 text, 
   stringa2 text,
   stringw1 text,
   stringw2 text
);


(Step 4)  Create ODBC DSN (substitute your own username/password here):

 Data Source: pgodbctest
 Database: pgodbctest
 Server: localhost
 User Name: postgres
 Password: postgres


(Step 5)  Compile and run test program (error checking omitted for
clarity):
I've compiled with VC++ 6.0 and VC++ 7.1 with no difference in behavior.

#include <windows.h>
#include <sqlext.h>
#include <stdio.h>

int main(int argc, char **argv)
{
        SQLHENV henv;
        SQLHDBC hdbc;
        SQLHSTMT hstmt;
        SQLCHAR thestringa[80];
        SQLWCHAR thestringw[80];
        SQLLEN lena, lenw, nts;

        SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
        SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);

        SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
        /* put in your own username and password here if not postgres/postgres 
*/
        SQLConnect(hdbc, "pgodbctest", SQL_NTS, "postgres", SQL_NTS, "postgres",
SQL_NTS);

        SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
        SQLPrepare(hstmt, "insert into testtable (stringa1, stringa2, stringw1,
stringw2) values (?,?,?,?)", SQL_NTS);

        strcpy(thestringa, "ansi test string");
        lena = strlen(thestringa) * sizeof(SQLCHAR);

        wcscpy(thestringw, L"unicode test string");
        lenw = wcslen(thestringw) * sizeof(SQLWCHAR);

        nts = SQL_NTS;

        SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, 
SQL_LONGVARCHAR, 0,
0, thestringa, 0, &lena);
        SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, 
SQL_LONGVARCHAR, 0,
0, thestringa, 0, &nts);

        SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_WCHAR, 
SQL_WLONGVARCHAR,
0, 0, thestringw, 0, &lenw);
        SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_WCHAR, 
SQL_WLONGVARCHAR,
0, 0, thestringw, 0, &nts);


        SQLExecute(hstmt);

        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
}


(Step 6) Look at results:

pgodbctest=# select * from testtable;
     stringa1     |     stringa2     |      stringw1       |      stringw2

------------------+------------------+---------------------+----------------
----
-
 ansi test string | ansi test string | unicode test string | 2005-04-22
00:00:00
(1 row)



(Step 7) Expected results:

"unicode test string" should have been inserted into the stringw2
column--not today's date!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org