Re: SQL Error- SQLBindColumn and TimeStamp Field

I found the answer, and I'm posting the response in case somebody else someday has this problem. The problem is the field type change between ODBC version 2.0 and 3.0, so I need to use SQL_C_TIMESTAMP and SQL_TIMESTAMP instead of SQL_C_TYPE_TIMESTAMP and SQL_TYPE_TIMESTAMP, which are for version 3.0. ODBC will not map between them. So the call is

retcode := SQLBindParameter(hSTMT, i + 1, SQL_PARAM_INPUT,
SQL_C_TIMESTAMP,
SQL_TIMESTAMP,
SizeOf(SQL_TIMESTAMP_STRUCT), 0 ,
lParams[i],
SizeOf(SQL_TIMESTAMP_STRUCT), piTemp) ;

----- Original Message -----
From: Ed Brown
To: pgsql-odbc(at)postgresql(dot)org
Sent: Wednesday, November 17, 2004 11:03 AM
Subject: [ODBC] SQL Error- SQLBindColumn and TimeStamp Field

I have been trying to do an insert into a TimeStamp field in Postgresql 7.34 (running on Suse 9.0, if it matters). The application is in Delphi 7.0, but the code is similar enough that you C gurus should be able to see what's happening. I get the text as a string, parse it into the various elements, and populate the SQL_TimeStamp_Structure. When I execute the SQLBindColumn function I get the following message:

Unable to Bind Parameter- State: S1003, Error # 0, [Microsoft][ODBC Driver Manager] Program type out of range

The values in the structure are reasonable, and other elements work properly. Can anyone either tell me what I'm doing wrong, or show me an example of setting a Timestamp value that works?

Thanks very much. Code Follows

Ed Brown

------------------------------------------------------------------------------

New(pTimeStamp);
DecodeDate(dtThis, i1,i2,i3);
pTimeStamp^.year := i1;
pTimeStamp^.Month := i2;
pTimeStamp^.Day := i3;
DecodeTime(dtThis, i1,i2,i3,i4);
pTimeStamp^.Hour := i1;
pTimeStamp^.minute := i2;
pTimeStamp^.second := i3;
pTimeStamp^.fraction := i4;

New(piTemp);
lIntegers.Add(piTemp);

// Fails on next call
retcode := SQLBindParameter(hSTMT, i + 1, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP,
SQL_TYPE_TIMESTAMP,
SizeOf(SQL_TIMESTAMP_STRUCT), 0 ,
pTimeStamp,
SizeOf(SQL_TIMESTAMP_STRUCT), piTemp) ;

------------------------------------------------------------------------------

The values are:

pTimeStamp^.year = 200
pTimeStamp^.Month = 7
pTimeStamp^.Day = 15
pTimeStamp^.Hour := 11
pTimeStamp^.minute := 15
pTimeStamp^.second := 0
pTimeStamp^.fraction := 0