BUG #1776: Unable to insert datetime with fractional seconds in postgreSQL database using ODBC api - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1776
Logged by:          sankar p
Email address:      sankar_it@yahoo.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.