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. 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

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 ?

**baray98** · 19th February 2020, 04:15

I will experiment and update this thread for feedback

**ChrisW67** · 19th February 2020, 12:08

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.

**smyk** · 19th February 2020, 14:42

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

**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

Qt Code:

Switch view

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)

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)

To copy to clipboard, switch view to plain text mode

It seems to be running

Qt Code:

Switch view

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();

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();

To copy to clipboard, switch view to plain text mode

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

Error Output

Qt Code:

Switch view

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")

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")

To copy to clipboard, switch view to plain text mode

any thoughts are most welcome

Some more testing tomorrow..
baray98

**smyk** · 6th March 2020, 11:10

Originally Posted by baray98

started the server

Qt Code:

Switch view

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

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

To copy to clipboard, switch view to plain text mode

...
any thoughts are most welcome

Should you not use port 5435 for PG compatibility mode ?

**baray98** · 6th March 2020, 22:22

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

Qt Code:

Switch view

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;

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;

To copy to clipboard, switch view to plain text mode

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

Qt Code:

Switch view

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]")

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]")

To copy to clipboard, switch view to plain text mode

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:

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

Qt Code:

Switch view

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;

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;

To copy to clipboard, switch view to plain text mode

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

Qt Code:

Switch view

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]")

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]")

To copy to clipboard, switch view to plain text mode

attached is my version of my postgres driver.

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

baray98

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

Qt Code:

Switch view

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;

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;

To copy to clipboard, switch view to plain text mode

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

Qt Code:

Switch view

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]")

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]")

To copy to clipboard, switch view to plain text mode

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

Qt Code:

Switch view

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;

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;

To copy to clipboard, switch view to plain text mode

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