inserting timestamp value in table using parameter binding from SQLDBC

[Former Member]

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

[TTK · Accepted solution]

Hello Raja

Apparently there seems to be a bug. The problem could be that timestamps in MaxDB have six fractional digits for seconds.

So please try as workaround

memcpy(paramAddr,"2008-07-18 10:10:10.000000",sizeof("2008-07-18 10:10:10.000000"));

and replace the length of 20 by 26 where necessary.

BTW, do you consider the trace tool "sqldbc_cons". Usually the trace level "sql on" shows, how application and server communicate.

HTH & regards Thomas

[Former Member · Accepted solution]

Hi Thomas,

I tried both the options,

memcpy(paramAddr,"2008-07-18 10:10:10.000000",sizeof("2008-07-18 10:10:10.000000"));

and

memcpy(paramAddr,"2008-07-18 10:10:10",sizeof("2008-07-18 10:10:10")); //simple insert sql with this value works from database studio.

But i get the same error.

I tried one more variation to this, ie., using a different host type while binding, used SQLDBC_HOSTTYPE_ODBCTIMESTAMP instead of SQLDBC_HOSTTYPE_ASCII used earlier:

SQLDBC_PreparedStatement_bindParameter (stmt, 2, SQLDBC_HOSTTYPE_ODBCTIMESTAMP, paramAddr, &len, 26, SQLDBC_TRUE);

This change gave me a different error

"Got Execute Error Illegal TIMESTAMP value for parameter/column (1)"

This brings us to one question, SQLDBC_Types.h has info 'in comment' that host type SQLDBC_HOSTTYPE_ODBCTIMESTAMP corresponds to SQL_TIMESTAMP_STRUCT

SQLDBC_HOSTTYPE_ODBCTIMESTAMP = 17, /*!< An ODBC <code>SQL_TIMESTAMP_STRUCT</code> struct. */

But is there anyway i can delare a struct of SQL_TIMESTAMP_STRUCT type in my code and use it for mentioning the date value to be used for this bind parameter. Hope it's clear what i intend to do here...

One more question is:

Regarding sqldbc_cons. I tried enabling it, by giving this command sqldbc_cons TRACE SQL ON

C:\>sqldbc_cons TRACE SQL ON

C:\>

But i don't see any .prt file being generate on my system. My installation directory "C:\Program Files\sdb" has only two sub directories MAXDB1 and programs and i have searched for any *.prt in them but don't find any.

Also just for information, i am compiling my code on a Red Hat Linux system and connecting to the maxb instance MAXDB1 which is installed on my windows system.

Regards

Raja

Edited by: Raja Panda on Sep 26, 2008 7:19 AM

Edited by: Raja Panda on Sep 26, 2008 7:23 AM

[TTK · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Thomas,

That solved my problem.

but am facing a new problem when i am trying to inculde sqltypes.h in my code, i'm getting this error:

sqltypes.h:137: error: redeclaration of C++ built-in type `long'

The error is a bit strange, since the line 137 is

typedef unsigned long ULONG; // and i don't see any problem with this.

Would you know what can cause this ?

Regards

Raja

[TTK · Accepted solution]

Don't know, what's going on there.

However, you could easily add the timestruct definition to SQLDBC_Types.h.

I assume, we will do this, too.

struct SQL_DATE_STRUCT

{

SQLDBC_Int2 year;

SQLDBC_UInt2 month;

SQLDBC_UInt2 day;

};

struct SQL_TIME_STRUCT

{

SQLDBC_UInt2 hour;

SQLDBC_UInt2 minute;

SQLDBC_UInt2 second;

};

struct SQL_TIMESTAMP_STRUCT

{

SQLDBC_Int2 year;

SQLDBC_UInt2 month;

SQLDBC_UInt2 day;

SQLDBC_UInt2 hour;

SQLDBC_UInt2 minute;

SQLDBC_UInt2 second;

SQLDBC_UInt4 fraction;

};

Regards Thomas