QODBC3

[XTerm]

Hi
I have been using Hybernate before, however this was while programming in Java.
Now im working on a project in C++. I must say i really enjoyed the features hybernate provided as debugging huge SQL sentences is pain.

The issue im having is that i would like to use the QODBC3 driver rather than the QSQLITE driver direct. I have made a test application so i can see that the config of odbc is propper, and is using a SQLite3.

I tried to compile and use the qxBlog application. It works straight out the box if i do NOT modify any code.(Just changed some path in the .pro). When i change the DB driver to QODBC3 thats when the errors start.

I get the following output

Code: Select all

[QxOrm] qx::QxSqlDatabase : create new database connection in thread '-1216039152' with key '{ca9870f6-c40d-496f-8e87-efbeba62aa30}'
[QxOrm] sql query (22 ms) : CREATE TABLE author (author_id TEXT NOT NULL PRIMARY KEY, name TEXT, birthdate DATE, sex INTEGER)
[QxOrm] sql query (1 ms) : CREATE TABLE comment (comment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, comment_text TEXT, date_creation TIMESTAMP, blog_id INTEGER)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (category_id INTEGER NOT NULL, blog_id INTEGER NOT NULL)
[QxOrm] sql query (2 ms) : CREATE TABLE category (category_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (blog_id INTEGER NOT NULL, category_id INTEGER NOT NULL)
[QxOrm] sql query (1 ms) : CREATE TABLE blog (blog_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, blog_text TEXT, date_creation TIMESTAMP, author_id TEXT)
"author_id_1"
QODBCResult::exec: Unable to execute statement: "[SQLite]invalid parameter"
[QxOrm] execute sql query failed : INSERT INTO author (author_id, name, birthdate, sex) VALUES (:author_id, :name, :birthdate, :sex)
QODBC3: Unable to execute statement
[SQLite]invalid parameter
[QxOrm] sql query (0 ms) : SELECT COUNT(*) FROM author
ASSERT: "qx::dao::count<author>() == 3" in file src/main.cpp, line 53


Cheking with sqlman i can see that the tables are created, but none of the insert commands worked.
Does anyone know how this can be solved so i can keep using QODBC3 driver? (Ive tried to set it to behave as a 2.0 and 3.0 driver with no change by using DB.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3")).

NOTE:
I have also made my own test app to check that the QODBC3 driver behaves correctly. Writing normal sql sentences everything happens as expected. No errors.
Ex: works

Code: Select all

"INSERT INTO author (name, birthdate, sex) VALUES (yey, 21111931, 1)


Hopefully there is more people with the same issue as me.

Cheers

[QxOrm admin]

Hi,

I think QODBC driver doesn't support default QxOrm syntax for sql parameters.

You can try to change the default behaviour with this method :

Code: Select all

qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle(qx::QxSqlDatabase::ph_style_question_mark);

This will modify your sql queries like this :

Code: Select all

INSERT INTO author (author_id, name, birthdate, sex) VALUES (?, ?, ?, ?)

instead of :

Code: Select all

INSERT INTO author (author_id, name, birthdate, sex) VALUES (:author_id, :name, :birthdate, :sex)

[XTerm]

Thanks for the quick reply.

I tested your code, however still no luck. Found out there is basicly 3 different methods you may use, unfortunantly none of them gave me the correct result.

Code: Select all

1: qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle(qx::QxSqlDatabase::ph_style_at_name);
2: qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle(qx::QxSqlDatabase::ph_style_2_point_name); //(default)
3: qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle(qx::QxSqlDatabase::ph_style_question_mark);


outputs where the same as listed above, where the change only beeing in the insert lines.(Still error on L53 as the insert did not work).
However when i chose the QSQLite driver instead of the QODBC with qestion_mark output, the program did not work anymore. There was issues on:

Code: Select all

[QxOrm] qx::QxSqlDatabase : create new database connection in thread '-1216858352' with key '{89e160e6-dfc3-44a6-b71d-cf7e8470f4dc}'
[QxOrm] sql query (9 ms) : CREATE TABLE author (author_id TEXT NOT NULL PRIMARY KEY, name TEXT, birthdate DATE, sex INTEGER)
[QxOrm] sql query (4 ms) : CREATE TABLE comment (comment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, comment_text TEXT, date_creation TIMESTAMP, blog_id INTEGER)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (category_id INTEGER NOT NULL, blog_id INTEGER NOT NULL)
[QxOrm] sql query (4 ms) : CREATE TABLE category (category_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (blog_id INTEGER NOT NULL, category_id INTEGER NOT NULL)
[QxOrm] sql query (1 ms) : CREATE TABLE blog (blog_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, blog_text TEXT, date_creation TIMESTAMP, author_id TEXT)
"author_id_1"
[QxOrm] sql query (2 ms) : INSERT INTO author (author_id, name, birthdate, sex) VALUES (?, ?, ?, ?)
[QxOrm] sql query (0 ms) : SELECT COUNT(*) FROM author
ASSERT: "! m_sQuery.isEmpty() && (qx::QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle() != qx::QxSqlDatabase::ph_style_question_mark)" in file src/QxDao/QxSqlQuery.cpp, line 56


That just suggests that QSQLite does not like that type of plase holder. so should be fine.
Cheers

[QxOrm admin]

About your assert :

Code: Select all

ASSERT: "! m_sQuery.isEmpty() && (qx::QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle() != qx::QxSqlDatabase::ph_style_question_mark)" in file src/QxDao/QxSqlQuery.cpp, line 56

This is not a problem with SQLite database :
* when you use "qx::QxSqlDatabase::ph_style_question_mark" style => you have to use "QxSqlQuery & QxSqlQuery::bind(const QVariant & vValue)" method to bind your values

So, in the "main.cpp" file of qxBlog project, you can write something like this (cf. line 57 to 59) :

Code: Select all

   // Create a query to fetch only female author : 'author_id_2' and 'author_id_3'
   if (qx::QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle() == qx::QxSqlDatabase::ph_style_question_mark)
   {
      qx::QxSqlQuery query("WHERE author.sex = ?");
      query.bind(author::female);
   }
   else if (qx::QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle() == qx::QxSqlDatabase::ph_style_2_point_name)
   {
      qx::QxSqlQuery query("WHERE author.sex = :sex");
      query.bind(":sex", author::female);
   }

[XTerm]

I see so a different syntax based on the place holder.
Still missing the issue with the QODBC3 tho, which is the main issue.

Output is:

Code: Select all

[QxOrm] qx::QxSqlDatabase : create new database connection in thread '-1216489712' with key '{0ce78985-1c79-4900-bd59-7239996d411c}'
[QxOrm] sql query (24 ms) : CREATE TABLE author (author_id TEXT NOT NULL PRIMARY KEY, name TEXT, birthdate DATE, sex INTEGER)
[QxOrm] sql query (8 ms) : CREATE TABLE comment (comment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, comment_text TEXT, date_creation TIMESTAMP, blog_id INTEGER)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (category_id INTEGER NOT NULL, blog_id INTEGER NOT NULL)
[QxOrm] sql query (2 ms) : CREATE TABLE category (category_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, description TEXT)
[QxOrm] create extra-table (relation many-to-many) : CREATE TABLE IF NOT EXISTS category_blog (blog_id INTEGER NOT NULL, category_id INTEGER NOT NULL)
[QxOrm] sql query (1 ms) : CREATE TABLE blog (blog_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, blog_text TEXT, date_creation TIMESTAMP, author_id TEXT)
"author_id_1"
QODBCResult::exec: Unable to execute statement: "[SQLite]invalid parameter"
[QxOrm] execute sql query failed : INSERT INTO author (author_id, name, birthdate, sex) VALUES (?, ?, ?, ?)
QODBC3: Unable to execute statement
[SQLite]invalid parameter
[QxOrm] sql query (1 ms) : SELECT COUNT(*) FROM author
ASSERT: "qx::dao::count<author>() == 3" in file src/main.cpp, line 53


Cheers

[QxOrm admin]

I have tested qxBlog project with SQLite database and "QODBC3" driver :
===> it works for me (both "question mark" and "2 point name" style), so I think you don't have the last version of ODBC driver.
I download it from this URL : http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.
I don't know if it is the last version but it works.

But, it seems that this driver doesn't support "LastInsertId()" method, so qxBlog project will fail later, at line "qAssert(category_1->m_id != 0);".
Perhaps you can workaround "LastInsertId()" problem with QxOrm trigger, but it needs some research on the web.
Anyway, all queries work with this ODBC driver.

[XTerm]

It seems i had an older version of those drivers. When i built the new once i got the same error which you are discribing there. I'm sure that using LastInsertId() can be avoided. Hopefully this will be solved in the future.
Thanks for all the help on this matter.
Cheers