ODBC timestamp handling in Team Developer 6

[mohkawa]

I am encountering a problem with Team Developer 6 when interacting with timestamp data type in DB2 through ODBC.
I get the following error.

DB2/NT:-180[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

To look at Team Developer's ODBC log and trace, the values written below as "1> [3] 2011-03-01-00.00.00.000000" are set with SQLBindParameter. But according to DB2's ODBC trace file, the value is "2011-03-01 00:00" and the size of the value is 16, which are wrong. The value must be "2011-03-01-00.00.00.000000", and the length must be 26, if timestamp data type is created with precision 6.
I created a sample program and verified that SQLDescribeParam() and SQLBindParam() worked correctly.


DB2's ODBC trace file
-------------------------
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="2011-03-01 00:00" - x'323031312D30332D30312030303A3030', pcbValue=16, piIndicatorPtr=16 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="2011-03-01 00:00" - x'323031312D30332D30312030303A3030', pcbValue=16, piIndicatorPtr=16 )


Team Developer's ODBC log
-------------------------

Code: Select all

1> [compile]    SELECT   NAME                    FROM     ( SELECT * FROM
1> XMTEIINNAI    UNION   SELECT   '01','00',TO_DATE('200001','YYYYMM'),TO_
1> DATE('22221231','YYYYMMDD'),   '9','xxxxxxxx',0,NULL,NULL,N
1> ULL,NULL   FROM DUAL   )   WHERE   CCOMPKB = :1          AND CQTAIKEIKB
1>  = :2        AND DSTART <= :3         AND DEND >= :4         AND CODE =
1>  :5
1> [SQLFreeStmt] 82565176, 0
1> [SQLPrepare] 82565176, 94559992, -3
1> [SQLNumResultCols] 82565176
1> [SQLDescribeCol] 82565176, 1, 90611074, 89 [SQLBindCol]  82565176, 1, 
1> -8, 90611224, 202, 0. FRW=1.
1> [get database parameter] param = 1004 [get database parameter] param 
1> = 1004 [get database parameter] param = 1004 [execute] [1] 0 [2] 0 
1> [3] 2011-03-01-00.00.00.000000 [4] 2011-03-01-00.00.00.000000 [5] 1 
1> [SQLExecute] 82565176 [error] = DB2/NT:-180[IBM][CLI Driver][DB2/NT] 
1> SQL0180N  The syntax of the string representation of a datetime value 
1> is incorrect.  SQLSTATE=2
1> 2007

ODBC trace file
-------------------------

Code: Select all

UPRT0210        2d80-cac EXIT  SQLBindParameter  with return code 0
(SQL_SUCCESS)
       HSTMT               0x04EBD838
       UWORD                        3
       SWORD                        1 <SQL_PARAM_INPUT>
       SWORD                        1 <SQL_C_CHAR>
       SWORD                       11 <SQL_TIMESTAMP>
       SQLULEN                   26
       SWORD                        0
       PTR                0x05669FB0
       SQLLEN                    33
       SQLLEN *            0x05A2DE0C (16)
...
UPRT0210        2d80-cac EXIT  SQLBindParameter  with return code 0
(SQL_SUCCESS)
       HSTMT               0x04EBD838
       UWORD                        4
       SWORD                        1 <SQL_PARAM_INPUT>
       SWORD                        1 <SQL_C_CHAR>
       SWORD                       11 <SQL_TIMESTAMP>
       SQLULEN                   26
       SWORD                        0
       PTR                0x05669FD8
       SQLLEN                    33
       SQLLEN *            0x05A2DE6C (16)
...

sql.ini

Code: Select all

-------------------------
[win32client.dll]
comdll=sqlodb32

[win32client.odb32]
log=C:\odbc.log /Tx /Ld

[odbcrtr]
longbuffer=2048000
odbctrace=on
odbctracefile=C:\odbc_trace.log
remotedbname=SAMPLE,DSN=SAMPLE
servername=server1,sqlapipe
dbname=SJK,sqlapipe

sample program
-------------------

Code: Select all

#include <stdio.h>
#include <string.h>
/* #include <stdlib.h> */
/* #include <sqlutil.h> */
/* #include <sql.h> */
/* #include <sqlenv.h> */
#include <db2ApiDf.h>
/* #include <sqlcli.h> */
#include <sqlcli1.h>

void displayMessage(SQLSMALLINT handleType, SQLHANDLE handle);
void displayMessage(SQLSMALLINT handleType, SQLHANDLE handle)
{
	int i;
	char sqlstate[6];
	int sqlcode;
	char message[256];
	SQLSMALLINT msg_len;
	SQLRETURN rc;

	i = 1;
    while (((rc = SQLGetDiagRec(handleType, handle, i++, sqlstate, &sqlcode, 
        message, sizeof(message), &msg_len)) == SQL_SUCCESS) || (rc == SQL_SUCCESS_WITH_INFO)) {
            printf("\tSQLSTATE = \"%s\"\n", sqlstate);
            printf("\tSQLCODE = %d\n", sqlcode);
            printf("\tmessage = \"%s\"\n", message);
    }
}

int main()
{
	SQLHANDLE henv; /* environment handle */
	SQLHANDLE hdbc; /* connection handle */
	SQLHANDLE hstmt;
	SQLHANDLE hstmt2;
	SQLRETURN rc;
	SQLINTEGER c1;
	SQLCHAR c2[21];
	SQLCHAR c3[33];
	SQLSMALLINT dataType;
	SQLULEN parameterSize;
	SQLSMALLINT decimalDigits;
	SQLSMALLINT nullable;

	rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
	rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_NTS);
	rc = SQLConnect(hdbc, "SAMPLE", SQL_NTS, "user1", SQL_NTS, "password1", SQL_NTS);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_DBC, hdbc);
	}

	rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt2);
	rc = SQLExecDirect(hstmt, "drop table t1", SQL_NTS);
	rc = SQLExecDirect(hstmt, "create table t1 (c1 int not null primary key, c2 char(20), c3 timestamp(6))", SQL_NTS);
	rc = SQLPrepare(hstmt, "insert into t1 values (?, ?, ?)", SQL_NTS);

	rc = SQLDescribeParam(hstmt, 2, &dataType, &parameterSize, &decimalDigits, &nullable);
	printf("type=%d, parameterSize=%d, dicimalDegits=%d, nullable=%d\n",
		dataType, parameterSize, decimalDigits, nullable);

	c1 = 1;
	strcpy(c2, "hello");
	strcpy(c3, "2011-01-02-00.00.00.000000");
/*	strcpy(c3, "2011-01-02"); */
	printf("SQLBindParameter 1\n");
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &c1, 0, NULL);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt);
	}
	printf("SQLBindParameter 2\n");
	rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 20, 0, c2, 21, NULL);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt);
	}
	printf("SQLBindParameter 3\n");
	rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TIMESTAMP, 26, 0, c3, 33, NULL);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt);
	}
	printf("SQLExecute\n");
	rc = SQLExecute(hstmt);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt);
	}

/*	rc = SQLExecDirect(hstmt2, "select * from t1 where c3=?", SQL_NTS); */
	rc = SQLPrepare(hstmt2, "select * from t1 where c3>?", SQL_NTS);
	strcpy(c3, "2011-01-01-00.00.00.000000");
	rc = SQLBindParameter(hstmt2, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TIMESTAMP, 26, 0, c3, 33, NULL);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt2);
	}
	rc = SQLExecute(hstmt2);
	if (rc != SQL_SUCCESS) {
		displayMessage(SQL_HANDLE_STMT, hstmt2);
	}
	rc = SQLBindCol(hstmt2, 1, SQL_C_LONG, &c1, 0, NULL);
	rc = SQLBindCol(hstmt2, 2, SQL_C_CHAR, c2, sizeof(c2), NULL);
	rc = SQLBindCol(hstmt2, 3, SQL_C_CHAR, c3, sizeof(c3), NULL);
	while ((rc = SQLFetch(hstmt2)) != SQL_NO_DATA_FOUND) {
		printf("%d, \"%s\", \"%s\"\n", c1, c2, c3);
	}

	rc = SQLFreeStmt(hstmt, SQL_UNBIND);
	rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLFreeStmt(hstmt2, SQL_UNBIND);
	rc = SQLFreeStmt(hstmt2, SQL_RESET_PARAMS);
	rc = SQLFreeStmt(hstmt2, SQL_CLOSE);

	rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

	rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
	rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt2);

	rc = SQLDisconnect(hdbc);
	rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
	rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);

	return rc;

}

[Jeff Luther]

As you might imagine, it's difficult to debug a long left-justified listing. The forum msg. manager cannot manage indenting without selecting all the code, clicking the "Code" button above the ml field.

The syntax of the string representation of a datetime value is incorrect.

Based on that one sentence describing the error, a better way for you to debug your problem is write a small test case with a simple select, based on your:

Code: Select all

SELECT NAME FROM (
    SELECT * FROM XMTEIINNAI UNION
        SELECT '01', '00',  TO_DATE ( '200001', 'YYYYMM' ), TO_DATE( '22221231', ' YYYYMMDD' ), '9', xxxxxxxx', 0,
                    NULL, NULL, NULL, NULL FROM DUAL )
        WHERE...

I'm not familiar with DB2, though the issue would seem to revolve around the TO_DATE macros in the SELECT. Here's a link to info. for that macro:
http://publib.boulder.ibm.com/infocente ... 007109.htm

so, putting something simple together and testing that way might help figure out why TO_DATE doesn't seem to be working. That DB2 info. page says:

The TO_DATE function returns a timestamp from a character string that has been interpreted using a character template.

So it would seem to something else, since your TO_DATE parms. look correct. Does the SELECT work correctly if you replace TO_DATE with CURRENT_DATE or similar, if there is such a value in DB2? That's the way I debug: try to find an 'edge' between what works and doesn't work.

Hmmm, maybe this is not even a TD issue? Code at the end of your msg. is C, not TD. If not, then then this looks like a C/API issue. Closest forum area for that would be SQLBase, not TD, if this is the case.

[mohkawa]

TO_DATE isn't problem. The problem is TD's Date/Time variable handling, which is 'DSTART <= :3 AND DEND >= :4' in the trace.
If I remove 'DSTART <= :3 AND DEND >= :4' from the following SQL statement, this works fine.

Code: Select all

1> [compile] SELECT NAME FROM ( SELECT * FROM
1> XMTEIINNAI UNION SELECT '01','00',TO_DATE('200001','YYYYMM'),TO_
1> DATE('22221231','YYYYMMDD'), '9','xxxxxxxx',0,NULL,NULL,N
1> ULL,NULL FROM DUAL ) WHERE CCOMPKB = :1 AND CQTAIKEIKB
1> = :2 AND DSTART <= :3 AND DEND >= :4 AND CODE =
1> :5

For example, if column C1's data type is timestamp, the following select statement also doesn't work.
(MyDateTime below is a variable of Date/Time defined in TD.)

SELECT 'HELLO' FROM T1 WHERE C1 = :MyDateTime

DB2 expects the timestamp format to be like '2011-03-01-00.00.00.000000' and its length to be 26. But TD seems to set '2011-03-01 00:00' for the value and 16 for the length.

[Jeff Luther]

If I remove 'DSTART <= :3 AND DEND >= :4' from the following SQL statement, this works fine.

Then it sounds like something doesn't like TD's default format for the bind var's D/T value.

Try experimenting with these suggestions:
** convert your DT's to strings and concat. instead:
'DSTART <= ' || sVariableStartDT || ' AND DEND >= ' || sVariableEndDT

** If that doesn't work, then if it were me, I would try to find a format for DT that DB2 did like. I'd try:
SELECT <some_dateTime> from <table>...
to find out what format DB2's returning a DT value as. Then change your 'DSTART...' clause to have that same value right in the string literal.

DT's are so varied among the different DB brand -- what they like, format(s) they accept, what they throw as an error -- that trial&error are often the way to figure out what will work.

[mohkawa]

Try experimenting with these suggestions:
** convert your DT's to strings and concat. instead:
'DSTART <= ' || sVariableStartDT || ' AND DEND >= ' || sVariableEndDT

I tried, but the following error occurred at compile time.
(I translated Japanese message into English. My translation may be not good.)
character operator (||) requires character operand.

I guess this error occurred because the parameter is declared as Date/Time data type.

Original problem is that DB2 expects timestamp format as something like '2011-03-01-00.00.00', but TD sets timestamp as something like '2011-03-01 00:00'.
DB2 also accepts the format 'YYYY-MM-DD hh:mm:ss' like '2011-03-01 00:00:00'. But omitting the seconds is not allowed.

So changing from

DSTART <= :sVariableStartDT

to

DSTART <= timestamp_format(cast(:sVariableStartDT as varchar(16)), 'YYYY-MM-DD HH24:MI')

works.

If possible, I don't want to do this because this application already exists and works with other database. Changing all related codes would require a lot of effort.
Is there way to change the format or add the seconds in TD?