I'm trying to insert TimeStamp Value into AzureSQL Server's DB table using ODBC driver 18 and I have set ODBC version as 3.0(SQL_OV_ODBC3). However I am getting below error
[Microsoft][ODBC Driver 18 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. SqlState = 22008
I write a sample code just to figured out that it also failed with same error. Below is the sample code
std::string sQuery("insert into DB_TEST.backlog_period (periodid, starttime) values (?, ?)");
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
SQLRETURN retcode; // Return status
WCHAR* wsQuery = string_to_whar(sQuery);
// Allocate Statement Handle
retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hstmt);
if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
{
std::cerr << "Error";
return;
}
SQL_NUMERIC_STRUCT* pOid = new SQL_NUMERIC_STRUCT;
SQL_TIMESTAMP_STRUCT* m_pTimeStamp = new SQL_TIMESTAMP_STRUCT;
size_t nPos = 0;
SYSTEMTIME Time;
GetSystemTime(&Time);
ConvertDoubleToNumericStruct(9999, *pOid);
m_pTimeStamp.year = Time.wYear;
m_pTimeStamp.month = Time.wMonth;
m_pTimeStamp.day = Time.wDay;
m_pTimeStamp.hour = Time.wHour;
m_pTimeStamp.minute = Time.wMinute;
m_pTimeStamp.second = Time.wSecond;
m_pTimeStamp.fraction = Time.wMilliseconds;
SQLWCHAR ColumnName[MAX_COL_NAME_LEN];
SQLSMALLINT ColumnNameLen;
SQLSMALLINT ColumnDataType;
SQLULEN ColumnDataSize;
SQLSMALLINT ColumnDataDigits;
SQLSMALLINT ColumnDataNullable;
retcode = SQLPrepare(hstmt, (SQLWCHAR*)wsQuery, SQL_NTSL);
SQLSMALLINT NumParams;
SQLNumParams(hstmt, &NumParams);
retcode = SQLDescribeParam(hstmt, 1, &ColumnDataType, &ColumnDataSize, &ColumnDataDigits, &ColumnDataNullable);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, ColumnDataSize, ColumnDataDigits, pOid, sizeof(SQL_NUMERIC_STRUCT), 0);
//above call is equivalent to
//SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_NUMERIC, SQL_NUMERIC, 10, 0, pOid, sizeof(SQL_NUMERIC_STRUCT), 0);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLDescribeParam(hstmt, 2, &ColumnDataType, &ColumnDataSize, &ColumnDataDigits, &ColumnDataNullable);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, ColumnDataSize, ColumnDataDigits, m_pTimeStamp, sizeof(SQL_TIMESTAMP_STRUCT), 0);
//above call is equivalent to
//SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 22, 2, m_pTimeStamp, sizeof(SQL_TIMESTAMP_STRUCT), 0);
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
retcode = SQLExecute(hstmt); // This is getting failed
checkAndThrowError(retcode, hstmt, SQL_HANDLE_STMT);
This sample code is also geeting failed with same error.
I am also attaching the table description
I have seen similar problem and solution on stackoverflow but nothing works for me.
I'm using Microsoft SQL Azure (RTM) - 12.0.2000.8
I'll be greatfull If any one can help. Thanks in Advance.