ODBC – API – SQLBindParameter – Date, Time – Timestamp – Error Messages

Background

Wanted to highlight a few less than intuitive error messages one can run into when trying to work on dates using the SQLBindParameter.

BTW,  our programming language is C and ODBC.

Code

1) DATE_STRUCT // SQL_C_TYPE_DATE // SQL_TYPE_DATE

Code


DATE_STRUCT  dsOpenDate;

iSQLReturn = SQLBindParameter
	(
		  hstmt
		, 3
		, SQL_PARAM_INPUT
		, SQL_C_TYPE_DATE
		, SQL_TYPE_DATE
		, 0
		, 0
		, &dsOpenDate
		, 0
		, &cbOpenDate
	);

Output


SQLBindParameter - Binding Column 3 (OPENDATE).
c type is DATE_STRUCT.
Length is 6.
precision is 6

State is HYC00
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented

2) SQL_TIMESTAMP_STRUCT // SQL_C_TYPE_TIMESTAMP // SQL_TYPE_TIMESTAMP

Code


SQL_TIMESTAMP_STRUCT structSQLTS;

iColumnDTLen = sizeof(structSQLTS);

iColumnDTPrecision=iColumnDTLen;

iSQLReturn = SQLBindParameter
	(
		  hstmt
		, 3
		, SQL_PARAM_INPUT

		, SQL_C_TYPE_TIMESTAMP

		, SQL_TYPE_TIMESTAMP

		, iColumnDTPrecision

		//scale
		, 0

		, &structSQLTS

		, sizeof(structSQLTS)

		, &cbOpenDate
	);

Output


SQLBindParameter - Binding Column 3 (OPENDATE).
c type is SQL_TIMESTAMP_STRUCT.
Length is 16.
precision is 16

State is 22008
text is [Microsoft][ODBC SQL Server Driver]Datetime field overflow

3) SQL_TIMESTAMP_STRUCT // SQL_C_TYPE_TIMESTAMP // SQL_TYPE_TIMESTAMP

Outline

  1. Argument
    • ColumnSize
      • Hardcode 22
        • Though actual column size of SQL_TIMESTAMP_STRUCT is 16
        • We have to hard code 22
      • Please see the credit below

Code


SQL_TIMESTAMP_STRUCT structSQLTS;

SYSTEMTIME tv;

/*
	https://sites.google.com/site/polyhedradbms/how-to-guides/setting-and-retrieving-datetime-attributes
*/
GetSystemTime(&tv);
memset(&structSQLTS, '\0', sizeof(structSQLTS));
structSQLTS.year     = tv.wYear;
structSQLTS.month    = tv.wMonth;
structSQLTS.day      = tv.wDay;
structSQLTS.hour     = tv.wHour;
structSQLTS.minute   = tv.wMinute;
structSQLTS.second   = tv.wSecond;
/* convert milliseconds to nanoseconds */
structSQLTS.fraction = tv.wMilliseconds * 1000000; 

iColumnDTLen = sizeof(structSQLTS);

//iColumnDTPrecision=iColumnDTLen;
iColumnDTPrecision=iColumnDTLen;

#define SQL_TIMESTAMP_PRECISION_HARDCODED 22

iColumnDTPrecision=SQL_TIMESTAMP_PRECISION_HARDCODED;

iSQLReturn = SQLBindParameter
	(
		  hstmt
		, 3
		, SQL_PARAM_INPUT

		, SQL_C_TYPE_TIMESTAMP

		, SQL_TYPE_TIMESTAMP

		, iColumnDTPrecision

		//scale
		, 0

		, &structSQLTS

		, sizeof(structSQLTS)

		, &cbOpenDate
	);

Output


SQLBindParameter - Binding Column 3 (OPENDATE).
c type is SQL_TIMESTAMP_STRUCT.
Length is 16.
precision is 22

4) TIMESTAMP_STRUCT // SQL_C_TYPE_TIMESTAMP // SQL_TYPE_TIMESTAMP

Outline

  1. Argument
    • ColumnSize
      • sizeof(TIMESTAMP_STRUCT)
      • Please see the credit below
    • DecimalDigits
      • 0
    • Please see the credit below ( Jochen Arndt )

Code


TIMESTAMP_STRUCT tsStructure;
tsStructure.year = 2017;
tsStructure.month = 10;
tsStructure.day = 10;
tsStructure.hour = 8;
tsStructure.minute = 14;
tsStructure.second = 0;
tsStructure.fraction = 0;

iColumnDTLen = sizeof(tsStructure);
iColumnDTPrecision =0;

iSQLReturn = SQLBindParameter
	(
		  hstmt
		, 3
		, SQL_PARAM_INPUT

		, SQL_C_TYPE_TIMESTAMP

		, SQL_TYPE_TIMESTAMP

                , iColumnDTLen

		, iColumnDTPrecision

		, &tsStructure

		, sizeof(tsStructure)

		, &cbOpenDate
	);   

Output


SQLBindParameter - Binding Column 3 (OPENDATE).
c type is TIMESTAMP_STRUCT.
Length is 16.
precision is 0

Credits

  1. Wez Furlong
    • Calling SQLBindParameter() to bind SQL_TIMESTAMP_STRUCT as SQL_C_TYPE_TIMESTAMP, avoiding a datetime overflow.
      Link
  2. Jochen Arndt
    • CodeProject
      • Issue to insert record with datetime to SQL server 2014 via ODBC C++
        Posted 18-Jan-18 23:37pm
        Link
  3. Resources for the Polyhedra developer
    • Setting and retrieving datetime attribute
      Link

Summary

In gratitude to the Internet.

We do not all have to go crazy all at the same time!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s