Failed to insert Poco::Nullable< Poco::Data::Date> data into MSSQL using ODBC

[zhe-ma]

Code:

  Poco::Nullable< Poco::Data::Date> date;
  session << "insert into student( birth) values(?)", use(date), now;

Error:

SQLExecute()
Requested SQL statement: insert into student( birth) values(?)
Native SQL statement: insert into student( birth) values(?)
: Connection:NetConn: 041B6E68
Server:Not applicable
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = 22018
Native Error Code = 206
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Operand type clash: tinyint is incompatible with date

===========================
ODBC Diagnostic record #2:
===========================
SQLSTATE = 42000
Native Error Code = 8180
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

[github-actions]

This issue is stale because it has been open for 365 days with no activity.

[github-actions]

This issue was closed because it has been inactive for 60 days since being marked as stale.

[rastocny]

Problem is caused by an invalid SQL data type in SQLBindParameter function. This cannot be solve without redesign of Poco::Data::AbstractBinder probably, which does not preserve c++ data type for NULL data. I hotfixed this issue by following changes rastocny@105e658

[matejk]

Poco::Data::Binder has bind functions for specific types, including NullData, which is used for null values.

SQLBindParameter requires column type for null values also. Data type is of course missing when null is passed as NullData type.

As I can see from the code, the best solution would be to replace arguments of bind member functions with Poco::Nullable<T>. Column data type and null would then be available and proper arguments could be passed to SQLBindParameter. bind overloads for NullData can be then removed.

Example:

	virtual void bind(std::size_t pos, const Poco::Nullable<UUID>& val, Direction dir = PD_IN) = 0;
		/// Binds a UUID.

	virtual void bind(std::size_t pos, const std::vector<Poco::Nullable<UUID>>& val, Direction dir = PD_IN);
		/// Binds a UUID vector.

	virtual void bind(std::size_t pos, const std::deque<Poco::Nullable<UUID>>& val, Direction dir = PD_IN);
		/// Binds a UUID deque.

	virtual void bind(std::size_t pos, const std::list<Poco::Nullable<UUID>>& val, Direction dir = PD_IN);
		/// Binds a UUID list.

However this is a major potentially breaking interface change.

@aleks-f , @obiltschnig, what is your opinion?

[aleks-f]

@matejk This looks like Poco::Date-specific, possibly also SQL server specific, problem. Other datatypes (including Poco::DateTime) seem to work fine for both insert and select. I will look into it

[aleks-f]

The problem was fixed (and too small in some cases) size of the binding storage. This fixed it.