Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. SQLite: proper method for calling sqlite3 directly?
Forum Updated to NodeBB v4.3 + New Features

SQLite: proper method for calling sqlite3 directly?

Scheduled Pinned Locked Moved Unsolved General and Desktop
18 Posts 6 Posters 2.1k Views 3 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • D Offline
    D Offline
    davecotter
    wrote on 11 Jan 2021, 20:30 last edited by
    #1

    My app runs fine using QSqlDatabase but i have a need to call into sqlite3 directly. specifically i need to call sqlite3_busy_handler() to set up a busy handler that always just does a quick idle and returns "continue" so my database NEVER gets a "busy" signal. (this is all done on background threads so waiting around isn't a problem)

    i can write the code that does all this but it won't LINK because ... well cuz where do i link to? i don't see what to link to?
    I have tried instead just compiling sqlite3 into my app, but that seems AWEFULLY wasteful of space to include the lib again for JUST ONE CALL. can't i link directly to the existing sqlite3 that QSqlDatabase already provides?

    -dave

    1 Reply Last reply
    0
    • C Offline
      C Offline
      Christian Ehrlicher
      Lifetime Qt Champion
      wrote on 11 Jan 2021, 20:31 last edited by
      #2

      You're looking for QSqlDriver::handle()

      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
      Visit the Qt Academy at https://academy.qt.io/catalog

      1 Reply Last reply
      0
      • D Offline
        D Offline
        davecotter
        wrote on 11 Jan 2021, 20:39 last edited by davecotter 1 Nov 2021, 20:40
        #3

        i already have the handle.

        trouble is i can't DO anything with it cuz any function i attempt to call with it won't link.

        specifically sqlite3_busy_handler()

        1 Reply Last reply
        0
        • C Offline
          C Offline
          Christian Ehrlicher
          Lifetime Qt Champion
          wrote on 11 Jan 2021, 20:40 last edited by
          #4

          You have to link against the sqlite library by yourself. This also means you have to recompile the sqlite plugin to use the same sqlite library since you can't mix them.

          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
          Visit the Qt Academy at https://academy.qt.io/catalog

          1 Reply Last reply
          0
          • D Offline
            D Offline
            davecotter
            wrote on 11 Jan 2021, 20:41 last edited by davecotter 1 Nov 2021, 20:42
            #5

            i KNOW i have to link against the sqlite3 library..

            but HOW?

            and if i'm linking against the existing library then why would i need to recompile... the existing library?

            E J 2 Replies Last reply 11 Jan 2021, 20:57
            0
            • D davecotter
              11 Jan 2021, 20:41

              i KNOW i have to link against the sqlite3 library..

              but HOW?

              and if i'm linking against the existing library then why would i need to recompile... the existing library?

              E Offline
              E Offline
              eyllanesc
              wrote on 11 Jan 2021, 20:57 last edited by eyllanesc 1 Nov 2021, 20:59
              #6

              @davecotter You have to link sqlite3 in your application, you don't have to recompile Qt. If you require a more specific procedure then point out the characteristics of your environment: OS, Qt version and how you have installed it, compiler, Are you using qmake, cmake or qbs? etc.

              If you want me to help you develop some work then you can write to my email: e.yllanescucho@gmal.com.

              1 Reply Last reply
              0
              • D Offline
                D Offline
                davecotter
                wrote on 11 Jan 2021, 21:07 last edited by davecotter 1 Nov 2021, 21:09
                #7

                macOS, Qt Creator 4.13, Qt 5.15
                i'm using a .pro file, so i guess that's qmake?

                where is it "in my application"?
                wouldn't it be in the QT libs folder or something?
                literally what is the path?
                and how do i set up my .pro file to link to it?

                E 1 Reply Last reply 11 Jan 2021, 22:08
                0
                • D davecotter
                  11 Jan 2021, 21:07

                  macOS, Qt Creator 4.13, Qt 5.15
                  i'm using a .pro file, so i guess that's qmake?

                  where is it "in my application"?
                  wouldn't it be in the QT libs folder or something?
                  literally what is the path?
                  and how do i set up my .pro file to link to it?

                  E Offline
                  E Offline
                  eyllanesc
                  wrote on 11 Jan 2021, 22:08 last edited by eyllanesc 1 Nov 2021, 22:11
                  #8

                  @davecotter

                  First you have to download the same version of sqlite3 amalgation that was used to compile the Qt you use and that can be found by checking the source code: https://github.com/qt/qtbase/tree/5.15.0/src/3rdparty/sqlite.

                  Then to handle it in a simple way you can create a .pri and you must copy the sqlite3.c and sqlite3.h in the folders shown in the following structure:

                  ├── your_app.pro
                  ├── 3rdParty
                  │   ├── sqlite
                  │   │   ├── sqlite3.c
                  │   │   └── sqlite3.h
                  │   └── sqlite3.pri
                  ├── other files
                  └── main.cpp
                  

                  sqlite3.pri

                  SOURCES += $$PWD/sqlite/sqlite3.c
                  HEADERS += $$PWD/sqlite/sqlite3.h
                  

                  Then in the .pro add the following to include the files:

                  include(3rdParty/sqlite3.pri)
                  

                  Then you can include the header and do the cast:

                  #include "sqlite3.h"
                  
                  // ...
                  
                  QVariant v = db.driver()->handle();
                  if (v.isValid() && (qstrcmp(v.typeName(), "sqlite3*") == 0)) {
                      // v.data() returns a pointer to the handle
                      sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
                      if (handle) {
                          sqlite3_busy_handler(handle, callback, ...);
                      }
                  }
                  

                  Note: In linux I had to add unix: LIBS += -ldl in the .pro.

                  If you want me to help you develop some work then you can write to my email: e.yllanescucho@gmal.com.

                  C 1 Reply Last reply 12 Jan 2021, 05:48
                  0
                  • D davecotter
                    11 Jan 2021, 20:41

                    i KNOW i have to link against the sqlite3 library..

                    but HOW?

                    and if i'm linking against the existing library then why would i need to recompile... the existing library?

                    J Offline
                    J Offline
                    JKSH
                    Moderators
                    wrote on 11 Jan 2021, 22:16 last edited by
                    #9

                    @davecotter said in SQLite: proper method for calling sqlite3 directly?:

                    can't i link directly to the existing sqlite3 that QSqlDatabase already provides?

                    Unfortunately not. The SQLite library itself is statically linked (embedded) into qsqlite.dll (or .so or .dylib) so the raw SQLite functions are no longer callable.

                    This contrasts with the PostgreSQL plugin: qsqlpsql.dll is simply a small wrapper that dynamically links to the raw PSQL library, so your app can link to it directly and bypass the Qt wrapper if you wish. This also means you must remember to bundle the PSQL library when you distribute your application.

                    I have tried instead just compiling sqlite3 into my app, but that seems AWEFULLY wasteful of space to include the lib again for JUST ONE CALL.

                    It's kind of like bundling a whole dynamic library with your app just to call one function from the library.

                    Qt Doc Search for browsers: forum.qt.io/topic/35616/web-browser-extension-for-improved-doc-searches

                    1 Reply Last reply
                    2
                    • D Offline
                      D Offline
                      davecotter
                      wrote on 11 Jan 2021, 22:35 last edited by
                      #10

                      It's kind of like bundling a whole dynamic library with your app just to call one function from the library.

                      right, but... it sounds like that's exactly what i must do???

                      i'm surprised the SQLite impl in Qt doesn't provide forwarding function wrappers for things like this?

                      J 1 Reply Last reply 12 Jan 2021, 01:46
                      0
                      • D davecotter
                        11 Jan 2021, 22:35

                        It's kind of like bundling a whole dynamic library with your app just to call one function from the library.

                        right, but... it sounds like that's exactly what i must do???

                        i'm surprised the SQLite impl in Qt doesn't provide forwarding function wrappers for things like this?

                        J Offline
                        J Offline
                        JKSH
                        Moderators
                        wrote on 12 Jan 2021, 01:46 last edited by
                        #11

                        @davecotter said in SQLite: proper method for calling sqlite3 directly?:

                        i'm surprised the SQLite impl in Qt doesn't provide forwarding function wrappers for things like this?

                        There are some database-specific settings available: https://doc.qt.io/qt-5/qsqldatabase.html#setConnectOptions You can set parameters like QSQLITE_BUSY_TIMEOUT but that doesn't let you register callback functions.

                        If you want, you can present a case for including this kind of functionality by posting at https://bugreports.qt.io/

                        Qt Doc Search for browsers: forum.qt.io/topic/35616/web-browser-extension-for-improved-doc-searches

                        1 Reply Last reply
                        2
                        • E eyllanesc
                          11 Jan 2021, 22:08

                          @davecotter

                          First you have to download the same version of sqlite3 amalgation that was used to compile the Qt you use and that can be found by checking the source code: https://github.com/qt/qtbase/tree/5.15.0/src/3rdparty/sqlite.

                          Then to handle it in a simple way you can create a .pri and you must copy the sqlite3.c and sqlite3.h in the folders shown in the following structure:

                          ├── your_app.pro
                          ├── 3rdParty
                          │   ├── sqlite
                          │   │   ├── sqlite3.c
                          │   │   └── sqlite3.h
                          │   └── sqlite3.pri
                          ├── other files
                          └── main.cpp
                          

                          sqlite3.pri

                          SOURCES += $$PWD/sqlite/sqlite3.c
                          HEADERS += $$PWD/sqlite/sqlite3.h
                          

                          Then in the .pro add the following to include the files:

                          include(3rdParty/sqlite3.pri)
                          

                          Then you can include the header and do the cast:

                          #include "sqlite3.h"
                          
                          // ...
                          
                          QVariant v = db.driver()->handle();
                          if (v.isValid() && (qstrcmp(v.typeName(), "sqlite3*") == 0)) {
                              // v.data() returns a pointer to the handle
                              sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
                              if (handle) {
                                  sqlite3_busy_handler(handle, callback, ...);
                              }
                          }
                          

                          Note: In linux I had to add unix: LIBS += -ldl in the .pro.

                          C Offline
                          C Offline
                          Christian Ehrlicher
                          Lifetime Qt Champion
                          wrote on 12 Jan 2021, 05:48 last edited by
                          #12

                          @eyllanesc This does not work. You then have two different static sqlite instances which will crash sooner or later.

                          Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                          Visit the Qt Academy at https://academy.qt.io/catalog

                          E 1 Reply Last reply 12 Jan 2021, 06:11
                          0
                          • C Christian Ehrlicher
                            12 Jan 2021, 05:48

                            @eyllanesc This does not work. You then have two different static sqlite instances which will crash sooner or later.

                            E Offline
                            E Offline
                            eyllanesc
                            wrote on 12 Jan 2021, 06:11 last edited by eyllanesc 1 Dec 2021, 06:15
                            #13

                            @Christian-Ehrlicher Are you sure? I have used that method many times and have never had a problem. The only thing I forgot to point out in my post was that after obtaining the handle: sqlite3 *handle = *static_cast<sqlite3 **>(v.data()); then the sqlite3_initialize() function must be called.

                            If you check the way sqlite is included in the plugin you will see that it is not linked statically or dynamically but as files (the .h and .c are added): https://github.com/qt/qtbase/blob/5.15.0/src/plugins/sqldrivers/sqlite/sqlite.pro . It is only dynamically linked if the "system-sqlite" setting is enabled.

                            If you want me to help you develop some work then you can write to my email: e.yllanescucho@gmal.com.

                            1 Reply Last reply
                            0
                            • C Offline
                              C Offline
                              Christian Ehrlicher
                              Lifetime Qt Champion
                              wrote on 12 Jan 2021, 06:24 last edited by
                              #14

                              @eyllanesc said in SQLite: proper method for calling sqlite3 directly?:

                              then the sqlite3_initialize() function must be called.

                              And that's exactly the problem - you then have two different instances running. sqlite3_initialize() creates a local storage for some stuff (see the sources)

                              Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                              Visit the Qt Academy at https://academy.qt.io/catalog

                              1 Reply Last reply
                              0
                              • C Offline
                                C Offline
                                ChrisW67
                                wrote on 12 Jan 2021, 08:27 last edited by
                                #15

                                A quick look at the Qt Sqlite plugin shows that it can be built against a system Sqlite (--system-sqlite).

                                Does this result in a static copy of Sqlite embedded in the plugin or does it dynamically link? If it is the latter, then you should be able to link to the same dynamic library.

                                1 Reply Last reply
                                0
                                • C Offline
                                  C Offline
                                  Christian Ehrlicher
                                  Lifetime Qt Champion
                                  wrote on 12 Jan 2021, 08:30 last edited by
                                  #16

                                  Since the system sqlite is a shared lib - no.

                                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                                  Visit the Qt Academy at https://academy.qt.io/catalog

                                  1 Reply Last reply
                                  0
                                  • D Offline
                                    D Offline
                                    davecotter
                                    wrote on 16 Jan 2021, 18:30 last edited by
                                    #17

                                    so i ask again: can i use the built in sqlite plugin, the one that comes with the install of Qt, and link against the sqlite.cpp code? or must i build it myself? if the latter: is there a "for dummies" step by step wiki about exactly how to build it?

                                    1 Reply Last reply
                                    0
                                    • SGaistS Offline
                                      SGaistS Offline
                                      SGaist
                                      Lifetime Qt Champion
                                      wrote on 16 Jan 2021, 18:45 last edited by
                                      #18

                                      Hi,

                                      It's in the documentation.

                                      Interested in AI ? www.idiap.ch
                                      Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                                      1 Reply Last reply
                                      1

                                      1/18

                                      11 Jan 2021, 20:30

                                      • Login

                                      • Login or register to search.
                                      1 out of 18
                                      • First post
                                        1/18
                                        Last post
                                      0
                                      • Categories
                                      • Recent
                                      • Tags
                                      • Popular
                                      • Users
                                      • Groups
                                      • Search
                                      • Get Qt Extensions
                                      • Unsolved