PDA

View Full Version : H2 database and Qt Database support


baray98
15th February 2020, 04:22
Hello,
I have an H2 database that my qt app (c++) would like to connect to. I understand Qt has postges support in sql database module and according to H2 website
"This database does not come with its own ODBC driver at this time, but it supports the PostgreSQL network protocol.".

I was searching for some sample code but I am out of luck.

Can somebody help me with link and clues on how to do this?

Any help is appreciated much,
baray98
ChrisW67
15th February 2020, 06:48
It all seems to be here (https://h2database.com/html/advanced.html#odbc_driver). Once you have the Postgres ODBC driver installed you use it from the Qt ODBC driver (not Postgres driver) like any other ODBC data source.

That page implies that the Pg ODBC driver is 32-bit but there are also 64-bit version on the site the page links to. Your application will have to be compiled 32 or 64-bit to match.
smyk
17th February 2020, 08:53
/.../ Once you have the Postgres ODBC driver installed you use it from the Qt ODBC driver (not Postgres driver) like any other ODBC data source.
Does it mean, the QPSQL (https://doc.qt.io/qt-5/sql-driver.html#qpsql) doesn't work ?
baray98
19th February 2020, 04:15
I will experiment and update this thread for feedback
ChrisW67
19th February 2020, 12:08
Does it mean, the QPSQL (https://doc.qt.io/qt-5/sql-driver.html#qpsql) doesn't work ?

That works if you are trying to talk to an actual Postgres database. It might work if this H2 database server looks enough like a Postgres database server. However, the H2 page explicitly says that you can reach it through the Postgres ODBC driver, which strongly suggests that a native Postgres connection will not.
smyk
19th February 2020, 14:42
/.../ It might work if this H2 database server looks enough like a Postgres database server. However, the H2 page explicitly says that you can reach it through the Postgres ODBC driver, which strongly suggests that a native Postgres connection will not.
Many ODBC drivers are just a wraper around the native API for the database server. I don't know the implementation details of the Postgres ODBC driver, but if both - the QPSQL and Postgres ODBC - internally use the Postgres API / Network protocol, it might be it works with QPSQL as well. I was hoping that maybe baray98 had tried it already :)
baray98
6th March 2020, 00:04
finally I found a slot to work on this. Stilll struggling

I have installed H2 database on my windows machine

started the server


C:\Program Files (x86)\H2\bin>java -cp h2-1.4.200.jar org.h2.tools.Server
TCP server running at tcp://192.168.200.122:9092 (only local connections)
PG server running at pg://192.168.200.122:5435 (only local connections)
Web Console server running at http://192.168.200.122:8082 (only local connections)

It seems to be running



QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectString = QStringLiteral(
"Driver={PostgreSQL Unicode(x64)};"
"Database=C:\\database\\eventDB;"
"UID=sa;"
"PWD=\"\""
"port=9092;"
"Server=localhost;");
db.setDatabaseName(connectString);
bool ok = db.open("sa","");

if(!ok)
qDebug() << db.lastError();



NO LUCK AT THIS POINT. This could be my connection string as I was trying DNSless connection.

Error Output


QDEBUG : H2Connection::test_case1() QSqlError("101", "QODBC3: Unable to connect", "could not connect to server: Connection refused (0x0000274D/10061)\n\tIs the server running on host \"localhost\" (::1) and accepting\n\tTCP/IP connections on port 5432?\ncould not connect to server: Connection refused (0x0000274D/10061)\n\tIs the server running on host \"localhost\" (127.0.0.1) and accepting\n\tTCP/IP connections on port 5432?\n")


any thoughts are most welcome

Some more testing tomorrow..
baray98
smyk
6th March 2020, 11:10
started the server


C:\Program Files (x86)\H2\bin>java -cp h2-1.4.200.jar org.h2.tools.Server
...
PG server running at pg://192.168.200.122:5435 (only local connections)
...
...
any thoughts are most welcome

Should you not use port 5435 for PG compatibility mode ?
baray98
6th March 2020, 22:22
Should you not use port 5435 for PG compatibility mode ?
good point

So I manage to talk to the PG server with the connection string below



QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectString = QStringLiteral(
"Driver={PostgreSQL ANSI};"
"Database=C:/database/eventDB;"
// "Uid=sa;"
// "Pwd=sa;"
// "assumeMinServerVersion=10.0;"
// "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
"Port=5435;"
"Server=localhost;");
db.setDatabaseName(connectString);
bool ok = db.open("sa","sa");

if(!ok)
qDebug() << db.lastError();

return ok;



But the server kicked me out after some sql syntax error at connect




QSqlError("110", "QODBC3: Unable to connect", "ERROR: Syntax error in SQL statement \"SET EXTRA_FLOAT_DIGITS = 2\"; expected \"@, AUTOCOMMIT, EXCLUSIVE, IGNORECASE, PASSWORD, SALT, MODE, COMPRESS_LOB, DATABASE, COLLATION, BINARY_COLLATION, UUID_COLLATION, CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, DEFAULT_TABLE_TYPE, CREATE, HSQLDB.DEFAULT_TABLE_TYPE, PAGE_STORE, CACHE_TYPE, FILE_LOCK, DB_CLOSE_ON_EXIT, AUTO_SERVER, AUTO_SERVER_PORT, AUTO_RECONNECT, ASSERT, ACCESS_MODE_DATA, OPEN_NEW, JMX, PAGE_SIZE, RECOVER, NAMES, SCOPE_GENERATED_KEYS, SCHEMA, CATALOG, DATESTYLE, SEARCH_PATH, SCHEMA_SEARCH_PATH, JAVA_OBJECT_SERIALIZER, IGNORE_CATALOGS, SESSION, TRANSACTION, LOGSIZE, FOREIGN_KEY_CHECKS\"; SQL statement:\nSET extra_float_digits = 2 [42001-200]")



attached is my version of my postgres driver.

I am still searching for I believe the last piece of the puzzle.

baray98

Added after 5 minutes:


Should you not use port 5435 for PG compatibility mode ?
good point

So I manage to talk to the PG server with the connection string below



QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectString = QStringLiteral(
"Driver={PostgreSQL ANSI};"
"Database=C:/database/eventDB;"
// "Uid=sa;"
// "Pwd=sa;"
// "assumeMinServerVersion=10.0;"
// "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
"Port=5435;"
"Server=localhost;");
db.setDatabaseName(connectString);
bool ok = db.open("sa","sa");

if(!ok)
qDebug() << db.lastError();

return ok;



But the server kicked me out after some sql syntax error at connect




QSqlError("110", "QODBC3: Unable to connect", "ERROR: Syntax error in SQL statement \"SET EXTRA_FLOAT_DIGITS = 2\"; expected \"@, AUTOCOMMIT, EXCLUSIVE, IGNORECASE, PASSWORD, SALT, MODE, COMPRESS_LOB, DATABASE, COLLATION, BINARY_COLLATION, UUID_COLLATION, CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, DEFAULT_TABLE_TYPE, CREATE, HSQLDB.DEFAULT_TABLE_TYPE, PAGE_STORE, CACHE_TYPE, FILE_LOCK, DB_CLOSE_ON_EXIT, AUTO_SERVER, AUTO_SERVER_PORT, AUTO_RECONNECT, ASSERT, ACCESS_MODE_DATA, OPEN_NEW, JMX, PAGE_SIZE, RECOVER, NAMES, SCOPE_GENERATED_KEYS, SCHEMA, CATALOG, DATESTYLE, SEARCH_PATH, SCHEMA_SEARCH_PATH, JAVA_OBJECT_SERIALIZER, IGNORE_CATALOGS, SESSION, TRANSACTION, LOGSIZE, FOREIGN_KEY_CHECKS\"; SQL statement:\nSET extra_float_digits = 2 [42001-200]")



attached is my version of my postgres driver.

I am still searching for I believe the last piece of the puzzle.

baray98


Should you not use port 5435 for PG compatibility mode ?
good point

So I manage to talk to the PG server with the connection string below



QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectString = QStringLiteral(
"Driver={PostgreSQL ANSI};"
"Database=C:/database/eventDB;"
// "Uid=sa;"
// "Pwd=sa;"
// "assumeMinServerVersion=10.0;"
// "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
"Port=5435;"
"Server=localhost;");
db.setDatabaseName(connectString);
bool ok = db.open("sa","sa");

if(!ok)
qDebug() << db.lastError();

return ok;



But the server kicked me out after some sql syntax error at connect




QSqlError("110", "QODBC3: Unable to connect", "ERROR: Syntax error in SQL statement \"SET EXTRA_FLOAT_DIGITS = 2\"; expected \"@, AUTOCOMMIT, EXCLUSIVE, IGNORECASE, PASSWORD, SALT, MODE, COMPRESS_LOB, DATABASE, COLLATION, BINARY_COLLATION, UUID_COLLATION, CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, DEFAULT_TABLE_TYPE, CREATE, HSQLDB.DEFAULT_TABLE_TYPE, PAGE_STORE, CACHE_TYPE, FILE_LOCK, DB_CLOSE_ON_EXIT, AUTO_SERVER, AUTO_SERVER_PORT, AUTO_RECONNECT, ASSERT, ACCESS_MODE_DATA, OPEN_NEW, JMX, PAGE_SIZE, RECOVER, NAMES, SCOPE_GENERATED_KEYS, SCHEMA, CATALOG, DATESTYLE, SEARCH_PATH, SCHEMA_SEARCH_PATH, JAVA_OBJECT_SERIALIZER, IGNORE_CATALOGS, SESSION, TRANSACTION, LOGSIZE, FOREIGN_KEY_CHECKS\"; SQL statement:\nSET extra_float_digits = 2 [42001-200]")



attached is my version of my postgres driver.

I am still searching for I believe the last piece of the puzzle.

baray98

Added after 53 minutes:

The final answer
WE can connect to H2 with QODBC using code below. QPSQL does not work but you will be using PostgresSQL ODBC driver. see code blow



QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
QString connectString = QStringLiteral(
"Driver={PostgreSQL Unicode(x64)};"
"Database=C:/database/eventDB;" //fullpath without extension
"Port=5435;"
"Server=localhost;");
db.setDatabaseName(connectString);
bool ok = db.open("sa","sa");

if(!ok)
qDebug() << db.lastError();

return ok;


Install H2 database in your machine
Run <yourh2dir>/bin run h2.bat but I prefer typing at the command line so you can see the output like qouted below
Connect to database with the code qouted above.




TCP server running at tcp://192.168.200.122:9092 (only local connections)
PG server running at pg://192.168.200.122:5435 (only local connections)
Web Console server running at http://192.168.200.122:8082 (only local connections)




Note:
PG server at H2 was not happy when i used the latest postgres driver I can only make it work with 9.05.02.00
Make sure QPSQL will connect to a postgres first to test if your QPSQL finds the dll it depended on.
H2 database need to have password ODBC will not connect to empty passworded database (i am not sure is empty passworded is a word:) )


case closed. I hope this will help somebody..

baray98