ODBC Driver 18 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. SqlState22008

439 views Asked by At

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 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.

0

There are 0 answers