H2 database and Qt Database support

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

It all seems to be here. 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.

Quote:

---

Originally Posted by ChrisW67

/.../ 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 doesn't work ?

I will experiment and update this thread for feedback

Quote:

---

Originally Posted by smyk

Does it mean, the 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.

Quote:

---

Originally Posted by ChrisW67

/.../ 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 :)

finally I found a slot to work on this. Stilll struggling

I have installed H2 database on my windows machine

started the server

Code:

---

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

Code:

---

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

Code:

---

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

Quote:

---

Originally Posted by baray98

started the server

Code:

---

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 ?

Quote:

---

Originally Posted by smyk

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

Code:

---

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

Code:

---

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:

Quote:

---

Originally Posted by smyk

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

Code:

---

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

Code:

---

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

Quote:

---

Originally Posted by smyk

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

Code:

---

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

Code:

---

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

Code:

---

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.

Quote:

---

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)

---

Quote:

---

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