Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a simple c++ code to insert a record in SQL Server 2014 via ODBC. It works fine with varchar fields, but it does not work with datetime field.

Can anybody help to understand that is wrong? I've got always:

"22018:1:0:[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification\n"

If I'm wrong here with my question, please give a hint which forum fits better. I've spent some days, but can't find the solution. For any help would be very appreciate.

What I have tried:

SQLHSTMT hstmt;
SQLRETURN rc;

rc = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt);
if(!SQL_SUCCEEDED(rc))
    return NULL;

SQLCHAR buf[64];
SQLLEN len;
SQLCHAR buf2[255];
SQLLEN len2;
SQLLEN len3;
double f;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 19, 0, (SQLCHAR*)buf, 0, &len);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 255, 0, (SQLCHAR*)buf2, 0, &len2);
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 15, 0, &f, 0, &len3);
rc = SQLPrepare(hstmt, (SQLCHAR*)"insert into test(timepoint,strvalue,floatvalue) values (?,?,?)", SQL_NTS);

strcpy((char*)buf,"2017-10-10T00:08:14");
len=strlen("2017-10-17T00:08:14");
strcpy((char*)buf2,"simple string");
len2=strlen("simple string");
f=1.34e+8;

SQLSMALLINT NumParams;
SQLNumParams(hstmt, &NumParams);
rc = SQLExecute(hstmt);
Posted
Updated 18-Jan-18 22:37pm

Invalid character value for cast specification comes from the driver when it detects a string to date time conversion.I urge you to change the data type to a string type or you can tune the logic to ensure that you don't get strings here.
 
Share this answer
 
Comments
Jochen Arndt 19-Jan-18 5:16am    
Using strings to store date and/or times in a database is a bad advice. Such types should be always stored in binary formats and passed as such.
Always pass time fields using binary data. That avoids any problems of converting strings to such fields.

Just lookup the corresponding C type for the field type. For SQL_TYPE_TIMESTAMP it is SQL_C_TYPE_TIMESTAMP:
C type identifier SQL_C_TYPE_TIMESTAMP[c]

ODBC C typedef SQL_TIMESTAMP_STRUCT

C type
C++
struct tagTIMESTAMP_STRUCT {  
   SQLSMALLINT year;  
   SQLUSMALLINT month;  
   SQLUSMALLINT day;  
   SQLUSMALLINT hour;  
   SQLUSMALLINT minute;  
   SQLUSMALLINT second;  
   SQLUINTEGER fraction;[b]   
} TIMESTAMP_STRUCT;[a]

Untested example:
C++
TIMESTAMP_STRUCT ts;
ts.year = 2017;
ts.month = 10;
ts.day = 10;
ts.hour = 8;
ts.minute = 14;
ts.second = 0;
ts.fraction = 0;
len = sizeof(ts);
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 19, 0, &ts, sizeof(ts), &len);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900