Overview
Features
Download
Documentation
Community
Add-Ons & Services

Corrupt ODBC SQL Statement?

Please post support and help requests here.

Corrupt ODBC SQL Statement?

Postby steveh » 27 Feb 2013, 17:24

Hi all,

This is my first post. I've just started using the Poco ODBC Data library to connect to SQL Server, and have hit a problem with the following code:

Code: Select all
int id=-1;

Session session(m_sessionPool->get());

std::string query("SELECT CompanyId FROM Suppliers WHERE CompanyName=:name");
std::string customer("Example Company");

Statement statement = ( session << query, into(id), use(customer) );
statement.execute();

The execute() call fails with an exception giving an incorrect syntax near ":". I stepped into the code and found that the SQL code within the statement was:

Code: Select all
"SELECT CompanyId FROM Suppliers WHERE CompanyName=:nameÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍýýýý««««««««îþîþ"

This corruption appears to happen in Poco/Data/StatementCreator.h in the function

Code: Select all
   template <typename T>
   Statement operator << (const T& t)
      /// Creates a Statement.
   {
      Statement stmt(_ptrImpl->createStatementImpl());
      stmt << t;
      return stmt;
   }

Where the std::string that is passed in is "SELECT CompanyId FROM Suppliers WHERE CompanyName=:name" but the string stored in the stmt variable is corrupted as above.

If I use "SELECT CompanyId FROM Suppliers WHERE CompanyName=?" instead, I get two SQL errors: "The data types nvarchar and text are incompatible in the equal to operator"; and "Statement(s) could not be prepared".

Thanks in advance for any suggestions!
steveh
 
Posts: 4
Joined: 27 Feb 2013, 16:31

Re: Corrupt ODBC SQL Statement?

Postby alex » 27 Feb 2013, 19:43

Try LIKE instead:

Code: Select all
"SELECT CompanyId FROM Suppliers WHERE CompanyName LIKE ?"
alex
 
Posts: 1113
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: Corrupt ODBC SQL Statement?

Postby steveh » 28 Feb 2013, 12:09

Thanks Alex, that worked. 8-)

Is there a reason why one works but the other doesn't? I imagine that "like" is slower than "=", even without wildcards, so would prefer to use "=" if possible.
steveh
 
Posts: 4
Joined: 27 Feb 2013, 16:31

Re: Corrupt ODBC SQL Statement?

Postby rakesh » 28 Feb 2013, 17:16

Isn't that a feature/restriction of the database server itself? Does = work on clob type fields if you directly query your database?
rakesh
 
Posts: 78
Joined: 13 Apr 2011, 17:43
Location: Chicago

Re: Corrupt ODBC SQL Statement?

Postby steveh » 01 Mar 2013, 11:23

I never had a problem with this with my previous database interface, only when I moved to Poco.

Overall, interacting with SQL Server using Poco is much better than what I was using before. ;)
steveh
 
Posts: 4
Joined: 27 Feb 2013, 16:31

Re: Corrupt ODBC SQL Statement?

Postby alex » 04 Mar 2013, 18:52

steveh wrote:I never had a problem with this with my previous database interface, only when I moved to Poco.

This is not POCO-specific.
alex
 
Posts: 1113
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: Corrupt ODBC SQL Statement?

Postby steveh » 06 Mar 2013, 11:08

Thanks Alex. The column in my database is an nvarchar, so I'm not sure why the query returns the nvarchar/text incompatibility. Is POCO passing the std::string Parameter as a Text value? If so, is there a way to tell it what type to pass it as?
steveh
 
Posts: 4
Joined: 27 Feb 2013, 16:31

Re: Corrupt ODBC SQL Statement?

Postby alex » 07 Mar 2013, 15:17

steveh wrote:If so, is there a way to tell it what type to pass it as?

No provision was made for user to arbitrarily map data types to columns; framework will do its best to map data correctly. The problem you are encountering is SQLServer-specific and we have to be generic for all ODBC drivers. Data mapping is done in ODBCMetaColumn and if you have a proposal how to improve it, let us know.
alex
 
Posts: 1113
Joined: 11 Jul 2006, 16:27
Location: United_States


Return to Support

Who is online

Users browsing this forum: No registered users and 4 guests