Hello, I ran into another problem with the ODBC driver when used with MS SQL server (the problem doesn't arise when using ODBC with Oracle, PostgreSQL or Firebird): consider a very simple table defined using this MS SQL code:
CREATE TABLE country
(
currency CHAR(3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
id CHAR(2),
name VARCHAR(50) NOT NULL,
CONSTRAINT pk_country PRIMARY KEY(id)
)
GO
It has records like this: ("USD", "US", "USA"), ("GBP", "UK", "United
Kingdom"), ("EUR", "FR", "France") and so on.
When I do the following:
session s;
string name,
country("FR");
s << "select name from country where id=:country",
into(name), use(country);
I get the following error with MS SQL ODBC driver (message extracted from
odbc_soci_error thrown by this code):
Statement Execute (ODBC error 42000: [Microsoft][ODBC SQL Server
Driver][SQL Server]The data types char and text are incompatible in
the equal to operator.)
Looking at the code, we use SQL_VARCHAR for string parameters and MS SQL
doesn't seem to like comparing them with CHAR(N) columns. Applying the
following patch makes the problem disappear:
--- a/src/backends/odbc/standard-use-type.cpp
+++ b/src/backends/odbc/standard-use-type.cpp
@@ -63,13 +63,7 @@ void* odbc_standard_use_type_backend::prepare_for_bind(
case x_stdstring:
{
std::string* s = static_cast<std::string*>(data_);
-#ifdef SOCI_ODBC_VERSION_3_IS_TO_BE_CHECKED
- sqlType = SQL_VARCHAR;
-#else
- // Note: SQL_LONGVARCHAR is an extended type for strings up to 1GB
- // But it might not work with ODBC version below 3.0
- sqlType = SQL_LONGVARCHAR;
-#endif
+ sqlType = SQL_CHAR;
cType = SQL_C_CHAR;
size = s->size();
buf_ = new char[size+1];
But I'm not sure if it doesn't introduce some other problems. Does anybody
know what was the original motivation for using SQL_VARCHAR here? FWIW
s << "select id from country where name=:name",
into(country), use(name);
still works after the patch, i.e. comparing SQL_CHAR parameter with VARCHAR
field in the database seems to be OK, unlike the other way around. Can
anybody think of anything else that could be broken by this change?
Thanks,
VZ
pgpv5xfu7OlfC.pgp
Description: PGP signature
------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________ Soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
