Parameter binding with Poco::Data ODBC

[argoneus]

hello,

we've noticed with poco-1.3.2-data in Parameter.cpp the position of the first parameter is 0, not 1, as is expected in the ODBC Param() functions. none of our ODBC drivers work with the poco-1.3.2 ODBC code, as it doesn't make it past the parameter binding code.

we've tested with iODBC on Mac OS X and Linux.

this seems to be fixed in trunk, but hasn't been backported to 1.3.3.

my question is: will this be fixed in 1.3.3?

Also, as a follow-up: when will 1.3.3 be released?

thanks,

nick

[alex]

Nick,

Thanks for the bug report.

> my question is: will this be fixed in 1.3.3?

[https://sourceforge.net/tracker/index.php?func=detail&aid=2014556&group_id=132964&atid=725711|Done]

> Also, as a follow-up: when will 1.3.3 be released?

We are shooting for September for 1.3.3 and end of year for 1.4. Data::ODBC (I need time to properly test it across platforms and ODBC driver managers) and lack of time are the main showstoppers right now. Any help would be very much appreciated.

[argoneus]

> hello,
>
> we've noticed with poco-1.3.2-data in Parameter.cpp the position of the first parameter is 0, not 1, as is expected in the ODBC Param() functions. none of our ODBC drivers work with the poco-1.3.2 ODBC code, as it doesn't make it past the parameter binding code.
>
> we've tested with iODBC on Mac OS X and Linux.
>
> this seems to be fixed in trunk, but hasn't been backported to 1.3.3.
>
> my question is: will this be fixed in 1.3.3?
>
> Also, as a follow-up: when will 1.3.3 be released?
>
> thanks,
>
> nick

Alex,

Thanks for the prompt reply and fix. I downloaded the 1.3.3 branch from SVN, revision 667, and tried this with our database creation app (the same app works fine with our own ODBC implementation, but we recently began using Poco::Data ODBC).
I'm using the SQLiteODBC driver (www.ch-werner.de/sqliteodbc) for testing.

The issue still seems to be with parameter binding: I traced through the code with gdb, and the parameter binding code doesn't correctly set the number of parameters to bind in the StatementHandle in the constructor for Parameter.cpp. The call to SQLDescribeParam() doesn't return SQL_SUCCESS, since the position passed (1) is greater than the number of parameters to bind (which is 0, and should be 4).

[alex]

I'm using the SQLiteODBC driver (www.ch-werner.de/sqliteodbc) for testing.

A word of warning: that driver is, by author's own admission, not of production quality. I have myself experienced some erratic behavior with it. If you need SQLite, Poco native connector works well and interface is pretty much the same.

The issue still seems to be with parameter binding: I traced through the code with gdb, and the parameter binding code doesn't correctly set the number of parameters to bind in the StatementHandle in the constructor for Parameter.cpp. The call to SQLDescribeParam() doesn't return SQL_SUCCESS, since the position passed (1) is greater than the number of parameters to bind (which is 0, and should be 4).

It's hard to know what exactly is going on. I'd say that parameter should never be created if there are no parameters. After the last fix, I have ran the full set of tests on Windows against SQL Server, Oracle, and PostgreSQL without any errors. I'll try to run SQLite as well this weekend. If you can pinpoint the exact reason and sequence to reproduce the failure, I may look into this particular issue relatively quickly (week or so). Otherwise, it'll have to wait until I sit down to do the proper multi-platform /multi-driver(manager) testing. But even then, I do not plan to spend much time on SQLite ODBC driver - given its status and availability of our own connector, it's not worth it.

[argoneus]

It's hard to know what exactly is going on. I'd say that parameter should never be created if there are no parameters. After the last fix, I have ran the full set of tests on Windows against SQL Server, Oracle, and PostgreSQL without any errors. I'll try to run SQLite as well this weekend. If you can pinpoint the exact reason and sequence to reproduce the failure, I may look into this particular issue relatively quickly (week or so). Otherwise, it'll have to wait until I sit down to do the proper multi-platform /multi-driver(manager) testing. But even then, I do not plan to spend much time on SQLite ODBC driver - given its status and availability of our own connector, it's not worth it.

I've actually tried with the PostgreSQL ODBC driver with the same result (again, using branch 1.3.3 revision 667): parameter binding seems to fail in the same manner. I've traced the code to SQLDescribeParam(), and the PostgreSQL driver triggers STMT_BAD_PARAMETER_NUMBER_ERROR, "Invalid parameter number for PGAPI_DescribeParam", and StatementException is thrown in Parameter::init(). The statement SQL is

__"INSERT INTO demo VALUES (:test1, :test2, :test3, :test4);"__

The four params are all ints that are bound with use()

I'll be happy to do any additional testing or produce log files if there's more information needed.

[alex]

^ __"INSERT INTO demo VALUES (:test1, :test2, :test3, :test4);"__
The four params are all ints that are bound with use()
I'll be happy to do any additional testing or produce log files if there's more information needed.
^
Nick

Two things:

* please post your whole snippet of C++ code that fails.
* the only portable way to specify parameter holders for ODBC is using question marks

Alex

[argoneus]

^ Two things:

* please post your whole snippet of C++ code that fails.
* the only portable way to specify parameter holders for ODBC is using question marks

Alex^

Alex,

I tried using "?" instead of ":param_name", and that fixed the issue. I appreciate all of the assistance.

Nick