Bug #6892 Parameterized SQL_TYPE_TIMESTAMP columns set NULL value to 0000-00-00 00:00:00

• Submitted: 30 Nov 2004 11:02
• Modified: 9 Dec 2004 1:12
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: Tested with v3.51.10
• OS: Windows (WinXp SP2)
• CPU Architecture: Any

[30 Nov 2004 11:02] [ name withheld ]

Description:
When using a parameterized query with an SQL_TYPE_TIMESTAMP (=DATETIME) column which has the value NULL, the wrong value is inserted.

How to repeat:
Example:

MyDateTime is a DATETIME column with default value NULL.

INSERT INTO
  MyTable
  (`MyDateTime`)
VALUES
  (?)

Result:
  MyDateTime = '0000-00-00 00:00:00'
Expected:
  MyDateTime = NULL

[30 Nov 2004 11:08] [ name withheld ]

The parameter is bound with the SQLBindParameter function, and StrLen_or_IndPtr 
is set to SQL_NULL_DATA

SQLRETURN SQLBindParameter(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT     ParameterNumber,
     SQLSMALLINT     InputOutputType,
     SQLSMALLINT     ValueType,
     SQLSMALLINT     ParameterType,
     SQLUINTEGER     ColumnSize,
     SQLSMALLINT     DecimalDigits,
     SQLPOINTER     ParameterValuePtr,
     SQLINTEGER     BufferLength,
     SQLINTEGER *     StrLen_or_IndPtr);

[9 Dec 2004 1:12] Harun Eren

Hi,

Thanks for you report.
I have tested this with the myodbc driver version 3.51.10 and i got the result 
value = NULL. Thus the driver works correctly.

Get Data All:
"mydatetime"
<Null>

In the ODBC Documentation :
The StrLen_or_IndPtr argument points to a buffer that, when SQLExecute or SQLExecDirect is called .... SQL_NULL_DATA. The parameter value is NULL. 

For testing i am use the followwing odbc calls:

SQLExecDirect:
In:	hstmt = 0x003A1910, 
	szSqlStr = "create table mytest (mydatetime datetime default null)", 
	cbSqlStr = -3

SQLPrepare:
In:	StatementHandle = 0x003A1910, 
	StatementText = "INSERT INTO  mytest (`mydatetime`) VALUES (?)", 
	TextLength = 45

SQLBindParameter:
In:	StatementHandle = 0x003A1910, ParameterNumber = 1, 
	InputOutputtype = SQL_PARAM_INPUT=1, ValueType = SQL_C_CHAR=1, 
	ParameterType = SQL_TIMESTAMP=11, ColumnSize=0, DecimalDigits=0, 
	ParameterValuePtr = SQL_NULL_HANDLE, BufferLength = 0, 
	StrLen_or_IndPtr=SQL_NULL_DATA=-1,SQL_LEN_DATA_AT_EXEC= FALSE, 
	Buffer Size = 300

SQLExecute:
In:	StatementHandle = 0x003A1910

Please can you check this again!

Regards