BUG #1776: Unable to insert datetime with fractional seconds in postgreSQL database using ODBC api

The following bug has been logged online:

Bug reference: 1776
Logged by: sankar p
Email address: sankar_it(at)yahoo(dot)com
PostgreSQL version: 7.4.6
Operating system: RHEL WS 4.0
Description: Unable to insert datetime with fractional seconds in
postgreSQL database using ODBC api
Details:

Hi,

Cause :
Unable to insert datetime with fractional seconds in postgreSQL database
(using timestamp escape sequence).

Environment : RHEL WS 4.0
ODBC DM : libiodbc-3.52.1.tar

1) Table is created using the following statement.
create table product(solddt timestamp);

2) inserted one record using the following statment.
insert into product values('1986-01-12 05:22:10:82375'). The timestamp
values is inserted successfully without any issue. I confirmed that the
timestamp datatype is working without any problem.

3) I have tried to insert using the following C-sample program. In this
sample, I am using ODBC escape sequences to insert TIMESTAMP value.

for example:- "{ts '1986-01-12 05:22:10'}")

4) When executing the C-sample program, I am getting the following error
message in the ODBC log.

Error Message in the ODBC log:-
Error while executing the query (non-fatal);
ERROR: date/time field value out of range: "0000-00-00 00:00:00"

C- Sample Program:-

#include <stdio.h>
#include <assert.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#define MAXNAME 250
#define MAXBUFLEN 250

int main(int argc, char* argv[])
{

SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
char szConnectString[]="DSN=postgres;UID=postgres;PWD=postgres";
SQLSMALLINT nODBCIsolationLevel=0;
short int lConnectStringLen;
char szConnectStringOut[250];
char szSQLStatement[250];

RETCODE retcode;
// SQLBindCol variables
SQLCHAR szName[MAXNAME+1];
SQLINTEGER cbName;
SQLTIMESTAMP tmStamp;
SQLINTEGER Len;
SQLCHAR inData[20];
SQL_TIMESTAMP_STRUCT stTimeStamp;
int length;
char strDate[26];

// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
if (retcode != 0)
printf("Allocate Env failed \n");
else
printf("Allocate Env Pass \n");

// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)
SQL_OV_ODBC3, SQL_IS_INTEGER);
// Allocate an ODBC connection and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
if (retcode != 0)
{
printf("Allocate ODBC Handle failed \n");
}
printf("Allocate ODBC Handle passed \n");
//retcode = SQLConnect(hdbc1,"mysql", SQL_NTS,"mysql", SQL_NTS,"mysql",
SQL_NTS);

retcode=SQLDriverConnect(hdbc1,NULL,(SQLCHAR*)szConnectString,SQL_NTS,(SQLCH
AR*)szConnectStringOut,sizeof(szConnectStringOut),
&lConnectStringLen,SQL_DRIVER_NOPROMPT);

if (retcode != 0)
{
printf("Connection failed \n");
}
else
printf("Connection Successful \n");

retcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc1,&hstmt1);
if (retcode != 0)
{
printf("Allocate Statement failed \n");
}

strcpy(szSQLStatement, "INSERT INTO PRODUCT(SOLDDT) VALUES(?)");

printf("Executing : %s \n", szSQLStatement);
retcode = SQLPrepare(hstmt1,(unsigned char *)szSQLStatement, SQL_NTS);

/*
stTimeStamp.day =12;
stTimeStamp.month =01;
stTimeStamp.year =2000;
stTimeStamp.hour =5;
stTimeStamp.minute =22;
stTimeStamp.second =10;
stTimeStamp.fraction =511212;
*/
memset(strDate, 0, strlen(strDate));

//not working
strcpy(strDate, "{ts '1986-01-12 05:22:10'}");

//working
// strcpy(strDate, "1986-01-12 05:22:10.82375");

/* retcode = SQLBindParameter( hstmt1,
1,
SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP,
SQL_TYPE_TIMESTAMP,
SQL_DESC_DATETIME_INTERVAL_PRECISION,
0,
&stTimeStamp,sizeof(stTimeStamp),
NULL);
*/
retcode = SQLBindParameter( hstmt1,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_TIMESTAMP,
19,
0,
strDate,sizeof(strDate),
NULL);

retcode = SQLExecute(hstmt1);

if ( retcode == 0 )
printf("SQLExecute - Success \r\n");
else
printf("SQLExecute - Not success \r\n");

/* Clean up. */
SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
SQLDisconnect(hdbc1);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

odbc trace log:

odbctime B7FEAA00 ENTER SQLPrepare
SQLHSTMT 0x8b9a7d8
SQLCHAR * 0xbfe30500
| INSERT INTO PRODUCT(SOLDDT) VALUES(?) |
SQLINTEGER -3 (SQL_NTS)

odbctime B7FEAA00 EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x8b9a7d8
SQLCHAR * 0xbfe30500
SQLINTEGER -3 (SQL_NTS)

odbctime B7FEAA00 ENTER SQLBindParameter
SQLHSTMT 0x8b9a7d8
SQLSMALLINT 1
SQLSMALLINT 1 (SQL_PARAM_INPUT)
SQLSMALLINT 1 (SQL_C_CHAR)
SQLSMALLINT 11 (SQL_TIMESTAMP)
SQLUINTEGER 19
SQLSMALLINT 0
SQLPOINTER 0xbfe30380
SQLLEN 26
SQLLEN * 0x0

odbctime B7FEAA00 EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
SQLHSTMT 0x8b9a7d8
SQLSMALLINT 1
SQLSMALLINT 1 (SQL_PARAM_INPUT)
SQLSMALLINT 1 (SQL_C_CHAR)
SQLSMALLINT 11 (SQL_TIMESTAMP)
SQLUINTEGER 19
SQLSMALLINT 0
SQLPOINTER 0xbfe30380
SQLLEN 26
SQLLEN * 0x0

odbctime B7FEAA00 ENTER SQLExecute
SQLHSTMT 0x8b9a7d8

odbctime B7FEAA00 EXIT SQLExecute with return code -1 (SQL_ERROR)
SQLHSTMT 0x8b9a7d8

Please reply to this message at the earliest.

Other observations:-
This error cann't be reproduced in windows.

Thanks & Regards,
Sankar.P.