通过ODBC C ++将带有datetime的记录插入到SQL Server 2014的问题 [英] Issue to insert record with datetime to SQL server 2014 via ODBC C++

问题描述

我有一个简单的c ++代码，可以通过ODBC在SQL Server 2014中插入记录。它适用于varchar字段，但它不适用于datetime字段。



任何人都可以帮助理解这是错误的吗？我总是：



22018：1：0：[Microsoft] [ODBC SQL Server驱动程序]转换规范的字符值无效\ n



如果我的问题出错，请提示哪个论坛更合适。我花了几天时间，却找不到解决方案。对于任何帮助将非常感谢。



我尝试过：

 SQLHSTMT hstmt; 
 SQLRETURN rc; 
 
 rc = SQLAllocHandle（SQL_HANDLE_STMT，dbc，& hstmt）; 
 if（！SQL_SUCCEEDED（rc））
返回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 *）插入测试（timepoint，strvalue，floatvalue）值（？，？，？），SQL_NTS）; 
 
 strcpy（（char *）buf，2017-10-10T00：08：14）; 
 len = strlen（2017-10-17T00：08：14）; 
 strcpy（（char *）buf2，simple string）; 
 len2 = strlen（简单字符串）; 
 f = 1.34e + 8; 
 
 SQLSMALLINT NumParams; 
 SQLNumParams（hstmt，& NumParams）; 
 rc = SQLExecute（hstmt）; 
 

解决方案

转换规范的无效字符值来自驱动程序，当它检测到字符串为日期时间转换。我建议您将数据类型更改为字符串类型，或者您可以调整逻辑以确保您不在此处获取字符串。

始终使用二进制数据传递时间字段。这避免了将字符串转换为此类字段的任何问题。



只需查找字段类型的相应C类型即可。对于 SQL_TYPE_TIMESTAMP ，它是 SQL_C_TYPE_TIMESTAMP ：

C数据类型| Microsoft Docs [ ^ ]：

C类型标识符 SQL_C_TYPE_TIMESTAMP [c]



ODBC C typedef SQL_TIMESTAMP_STRUCT



C类型

  struct  tagTIMESTAMP_STRUCT {
 SQLSMALLINT year; 
 SQLUSMALLINT月; 
 SQLUSMALLINT日; 
 SQLUSMALLINT小时; 
 SQLUSMALLINT分钟; 
 SQLUSMALLINT秒; 
 SQLUINTEGER分数; [b] 
} TIMESTAMP_STRUCT; [a] 

未经测试的例子：

 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）; 

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);

解决方案

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.

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 Data Types | Microsoft Docs[^]:

C type identifier SQL_C_TYPE_TIMESTAMP[c]

ODBC C typedef SQL_TIMESTAMP_STRUCT

C type

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

Untested example:

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);

这篇关于通过ODBC C ++将带有datetime的记录插入到SQL Server 2014的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！