Overview
Features
Download
Documentation
Community
Add-Ons & Services

SQL ERROR: "Text" is not compatible with "datetime"

Please post support and help requests here.

SQL ERROR: "Text" is not compatible with "datetime"

Postby scanf123 » 23 Apr 2014, 04:50

Hi,all
The SQL version is MS SQL 2005.

The codes is as follows:
/////////////////////////////////////////////
string sql_insert = "INSERT INTO INFO(cDevNo,cParkCode,cBuildCode,cUnitCode,cClientCode,dTime)VALUES(?,?,?,?,?,?)";
Poco::Data::Statement statment(session);
statment<<sql_insert, use(cDevNoStr),use(cParkStr),use(cBuildStr),use(cUnitCodeStr),use(cDevNoStr),use(dDateTimeStr);
//dDateTimeStr = "2014-10-23 12:12:12" for example.
statment.execute();
/////////////////////////////////////////////

When executing this sql cmd, the error info is shown as ""Text" is not compatible with "datetime"".
Then I try to use the DateTime type to fix my codes:
////////////////////////////////////////////
DateTime dt(2014,10,23,12,12,12);//for example
...
statment<<sql_insert, use(cDevNoStr),use(cParkStr),use(cBuildStr),use(cUnitCodeStr),use(cDevNoStr),use(dt);
statment.execute();
///////////////////////////////////////////
Howerver, the above code cannot build successful, vs2010 output :
"c:\poco\data\include\poco\data\typehandler.h(124): error C2664: 'void Poco::Data::AbstractBinder::bind(size_t,const Poco::Int8 &)' : cannot convert parameter 2 from 'const Poco::DateTime' to 'const Poco::Int8 &'
1> Reason: cannot convert from 'const Poco::DateTime' to 'const Poco::Int8'
1> No user-defined-conversion operator available that can perform this conversion, or the operator cannot be called
1> c:\poco\data\include\poco\data\typehandler.h(122) : while compiling class template member function 'void Poco::Data::TypeHandler<T>::bind(size_t,const T &,Poco::Data::AbstractBinder *)'
1> with
1> [
1> T=Poco::DateTime
1> ]"

How to deal with the problem? Thanks all!
scanf123
 
Posts: 1
Joined: 23 Apr 2014, 04:22

Re: SQL ERROR: "Text" is not compatible with "datetime"

Postby alex » 24 Apr 2014, 04:21

If you are using POCO 1.4.x, there is no DateTime binding, you need 1.5.x version. Alternatively, you can convert the string to datetime in T-SQL:

Code: Select all
CAST('2006-04-25T12:12:12.997' AS datetime)
CONVERT(datetime, '2006-04-25T12:12:12.997', 126)
alex
 
Posts: 1158
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: SQL ERROR: "Text" is not compatible with "datetime"

Postby DonChunior » 24 Jun 2014, 11:51

I'm using POCO 1.4.x, but CAST doesn't work for me:

Code: Select all
stmt << "INSERT INTO table_user (username, isAdministrator, creationDate, createdBy, lastModificationDate, lastModifiedBy) VALUES (?, ?, CAST(? AS date), 'useradmin', '2014-6-24', 'usermodifier')", use(dbUsername), use(isAdministrator), use(dbCreationDate);
stmt.execute();


Above code throws a Poco::Data::ODBC::HandleException - displayText shows in console:

Code: Select all
ODBC handle exception: SQLExecute()
Requested SQL statement: INSERT INTO table_user (username, isAdministrator, creationDate, createdBy, lastModificationDate, lastModifiedBy) VALUES (?, ?, CAST(? AS date), 'useradmin', '2014-6-24', 'usermodifier')
Native SQL statement: INSERT INTO table_user (username, isAdministrator, creationDate, createdBy, lastModificationDate, lastModifiedBy) VALUES (?, ?, CAST(? AS date), 'useradmin', '2014-6-24', 'usermodifier')
: Connection:NetConn: 056097C4
Server:Not applicable
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = 22018
Native Error Code = 529
[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit conversion from data type text to date is not allowed.

===========================
ODBC Diagnostic record #2:
===========================
SQLSTATE = 42000
Native Error Code = 8180
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
BOINC - Donate computing power instead of money
DonChunior
 
Posts: 10
Joined: 30 Oct 2012, 08:43

Re: SQL ERROR: "Text" is not compatible with "datetime"

Postby alex » 25 Jun 2014, 05:29

Could be problem with binding into stored procedure, which is not supported in 1.4; try
Code: Select all
stmt << "INSERT INTO table_user (username, isAdministrator, creationDate, createdBy, lastModificationDate, lastModifiedBy) VALUES (?, ?, CAST('2014-6-24' AS date), 'useradmin', '2014-6-24', 'usermodifier')", use(dbUsername), use(isAdministrator);
alex
 
Posts: 1158
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: SQL ERROR: "Text" is not compatible with "datetime"

Postby DonChunior » 25 Jun 2014, 09:02

Hello Alex,

if I use your SQL statement, everything works fine and the record gets inserted in the database?!
Any idea how to solve my problem?
BOINC - Donate computing power instead of money
DonChunior
 
Posts: 10
Joined: 30 Oct 2012, 08:43


Return to Support

Who is online

Users browsing this forum: No registered users and 3 guests