Results 1 to 9 of 9

Thread: H2 database and Qt Database support

  1. #1
    Join Date
    Aug 2007
    Posts
    275
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default 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

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: H2 database and Qt Database support

    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.

  3. The following user says thank you to ChrisW67 for this useful post:

    baray98 (19th February 2020)

  4. #3
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: H2 database and Qt Database support

    Quote Originally Posted by ChrisW67 View Post
    /.../ 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 ?

  5. #4
    Join Date
    Aug 2007
    Posts
    275
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: H2 database and Qt Database support

    I will experiment and update this thread for feedback

  6. #5
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: H2 database and Qt Database support

    Quote Originally Posted by smyk View Post
    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.

  7. #6
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Cool Re: H2 database and Qt Database support

    Quote Originally Posted by ChrisW67 View Post
    /.../ 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

  8. #7
    Join Date
    Aug 2007
    Posts
    275
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: H2 database and Qt Database support

    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:
    1. C:\Program Files (x86)\H2\bin>java -cp h2-1.4.200.jar org.h2.tools.Server
    2. TCP server running at tcp://192.168.200.122:9092 (only local connections)
    3. PG server running at pg://192.168.200.122:5435 (only local connections)
    4. 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:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    2. QString connectString = QStringLiteral(
    3. "Driver={PostgreSQL Unicode(x64)};"
    4. "Database=C:\\database\\eventDB;"
    5. "UID=sa;"
    6. "PWD=\"\""
    7. "port=9092;"
    8. "Server=localhost;");
    9. db.setDatabaseName(connectString);
    10. bool ok = db.open("sa","");
    11.  
    12. if(!ok)
    13. 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:
    1. 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

  9. #8
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: H2 database and Qt Database support

    Quote Originally Posted by baray98 View Post
    started the server
    Qt Code:
    1. C:\Program Files (x86)\H2\bin>java -cp h2-1.4.200.jar org.h2.tools.Server
    2. ...
    3. PG server running at pg://192.168.200.122:5435 (only local connections)
    4. ...
    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 ?

  10. #9
    Join Date
    Aug 2007
    Posts
    275
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: H2 database and Qt Database support

    Quote Originally Posted by smyk View Post
    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:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    2. QString connectString = QStringLiteral(
    3. "Driver={PostgreSQL ANSI};"
    4. "Database=C:/database/eventDB;"
    5. // "Uid=sa;"
    6. // "Pwd=sa;"
    7. // "assumeMinServerVersion=10.0;"
    8. // "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
    9. "Port=5435;"
    10. "Server=localhost;");
    11. db.setDatabaseName(connectString);
    12. bool ok = db.open("sa","sa");
    13.  
    14. if(!ok)
    15. qDebug() << db.lastError();
    16.  
    17. 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:
    1. 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:


    Quote Originally Posted by smyk View Post
    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:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    2. QString connectString = QStringLiteral(
    3. "Driver={PostgreSQL ANSI};"
    4. "Database=C:/database/eventDB;"
    5. // "Uid=sa;"
    6. // "Pwd=sa;"
    7. // "assumeMinServerVersion=10.0;"
    8. // "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
    9. "Port=5435;"
    10. "Server=localhost;");
    11. db.setDatabaseName(connectString);
    12. bool ok = db.open("sa","sa");
    13.  
    14. if(!ok)
    15. qDebug() << db.lastError();
    16.  
    17. 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:
    1. 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

    Quote Originally Posted by smyk View Post
    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:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    2. QString connectString = QStringLiteral(
    3. "Driver={PostgreSQL ANSI};"
    4. "Database=C:/database/eventDB;"
    5. // "Uid=sa;"
    6. // "Pwd=sa;"
    7. // "assumeMinServerVersion=10.0;"
    8. // "MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;"
    9. "Port=5435;"
    10. "Server=localhost;");
    11. db.setDatabaseName(connectString);
    12. bool ok = db.open("sa","sa");
    13.  
    14. if(!ok)
    15. qDebug() << db.lastError();
    16.  
    17. 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:
    1. 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:
    1. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    2. QString connectString = QStringLiteral(
    3. "Driver={PostgreSQL Unicode(x64)};"
    4. "Database=C:/database/eventDB;" //fullpath without extension
    5. "Port=5435;"
    6. "Server=localhost;");
    7. db.setDatabaseName(connectString);
    8. bool ok = db.open("sa","sa");
    9.  
    10. if(!ok)
    11. qDebug() << db.lastError();
    12.  
    13. 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
    Attached Images Attached Images
    Last edited by baray98; 7th March 2020 at 04:20.

Similar Threads

  1. Replies: 0
    Last Post: 6th September 2018, 10:06
  2. Model/View programming support for database locks!
    By graciano in forum Qt Programming
    Replies: 1
    Last Post: 12th December 2013, 04:11
  3. Replies: 2
    Last Post: 27th August 2012, 04:27
  4. Qt to support XML database?
    By zhongzhu in forum Qt Programming
    Replies: 2
    Last Post: 9th July 2009, 12:51
  5. Qt support for MS Access database
    By Gayathri in forum Qt Programming
    Replies: 1
    Last Post: 23rd November 2006, 14:14

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.