inserting timestamp value in table using parameter binding from SQLDBC
Hi,
I am trying to insert a timestamp value into the table HOTEL.EMPLOYEE (I have added a new column of type TIMESTAMP called JOINDATE) by doing binding of parameter.
This is how the code looks:
//First i create a prepared statement like this....assume connection is already established.
SQLDBC_PreparedStatement *stmt = SQLDBC_Connection_createPreparedStatement(conn);
char *tempstr = "INSERT INTO HOTEL.EMPLOYEE VALUES (10, 1234, 'Mr', 'Raja', 'Santosh Panda',2000,?)";
rc = SQLDBC_PreparedStatement_prepare(stmt, tempstr, strlen(tempstr),encodAsciiType );
// Then i do binding like this:
int LengthIndicator = 20;
void *paramAddr = new char[26];
SQLDBC_PreparedStatement_bindParameter (stmt, 1, SQLDBC_HOSTTYPE_ASCII, paramAddr, &LengthIndicator, 20, SQLDBC_TRUE);
// Set the value in the ponter like this
memcpy(paramAddr,"2008-07-18 10:10:10",sizeof("2008-07-18 10:10:10"));
// Try to execute
rc = SQLDBC_PreparedStatement_executeASCII(stmt);
if(SQLDBC_OK != rc) {
SQLDBC_ErrorHndl *errHndl = SQLDBC_PreparedStatement_getError(stmt);
fprintf(stderr, "Got Execute Error %s \n", SQLDBC_ErrorHndl_getErrorText(errHndl));
return (1);
}
//This is the error i get
Got Execute Error POS(1) Invalid timestamp format:ISO
Can anyone help ??
Regards
Raja
Former Member replied
Following code snippet works at my site:
DDL: create table ttk1 (id int, ts timestamp)
int LengthIndicator = 26; void *paramAddr = new char[26]; tc_errorcheck(ps1)->prepare("INSERT INTO ttk1 VALUES (2, ?)", SQLDBC_StringEncodingAscii); tc_errorcheck(ps1)->bindParameter( 1, SQLDBC_HOSTTYPE_ASCII, paramAddr, &LengthIndicator, 26); // Set the value in the ponter like this memcpy(paramAddr,"2008-07-18 10:10:10.000000",sizeof("2008-07-18 10:10:10.000000")); // Try to execute tc_errorcheck(ps1)->executeBatch(); connection()->releaseStatement(ps1); connection()->commit ();
SQL_TIMESTAMP_STRUCT is a common ODBC type and is defined in sqltypes.h (a header file from ODBC):
typedef struct tagTIMESTAMP_STRUCT { SQLSMALLINT year; SQLUSMALLINT month; SQLUSMALLINT day; SQLUSMALLINT hour; SQLUSMALLINT minute; SQLUSMALLINT second; SQLUINTEGER fraction; } TIMESTAMP_STRUCT;
sqldbc_cons:
sqldbc_cons s a
s a = show all
reports, how the trace is configured and where it is written to.
HTH & regards Thomas