Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Qt Creator, SQLite3, and JSON1 example?



  • Hello. I hope I'm placing this post in the appropriate group.

    I am using Qt Creator 4.10, with SQLite3 (v. 3.32), with no problem. Now, I recently added the JSON1 extension to the SQLite3 dll with:

    `gcc -g -shared -DSQLITE_ENABLE_JSON1 json1.c sqlite3ext.h -o json1.dll
    

    I started implementing the JSON1 extension in my Qt Creator project by adding this code to my Qt main.cpp file; it compiles still, and writes the standard SQL database file in

    //-------------------------------------------------------------------------------------------------
    #include <QCoreApplication>
    #include <QtDebug>
    #include <QtSql/QtSql>
    #include <QString>
    
    //-------------------------------------------------------------------------------------------------
    //https://sqlite.org/loadext.html
    /* Add your header comment here */
    #include <C:/sqlite3/sqlite-amalgamation-3320200/sqlite3ext.h> /* Do not use <sqlite3.h>! */
    
    SQLITE_EXTENSION_INIT1
    
    /* Insert your extension code here */
    
    #ifdef _WIN32
    __declspec(dllexport)
    #endif
    /* TODO: Change the entry point name so that "extension" is replaced by  text derived from the shared library filename as follows:  Copy every  ASCII alphabetic character from the filename after the last "/" through the next following ".", converting each character to lowercase, and discarding the first three characters if they are "lib".
    */
    
    int sqlite3_extension_init(
      sqlite3 *db,
      char **pzErrMsg,
      const sqlite3_api_routines *pApi
    )
    
    {
      int rc = SQLITE_OK;
      SQLITE_EXTENSION_INIT2(pApi);
    
      /* Insert here calls to  sqlite3_create_function_v2(), sqlite3_create_collation_v2(), sqlite3_create_module_v2(), and/or sqlite3_vfs_register() to register the new features that your extension adds.
      */
      return rc;
    }
    //------------------------------------------------------------------------------------------------------
    //my original Qt project code...with credits to various, useful YouTube tutorials (available listing)
    
    const QString pathName = "C:\\Users\\firstName_lastName\\Qt_database_QtSql\\";
    const QString databaseName = "new.db";
    const QString pathAndFileName = pathName + databaseName;
    const QString hostName = "bigBlue"; //"112.17.98.18"); // fake ip ;
    const QString userName = "firstName_lastName";
    const QString userPassword = "i_dunno";
    const int portNumber = 3306;
    
    void addValues( int ID, QString firstName, QString lastName, QString  birthDate, double weight  );
    
    int main(int argc, char *argv[])
    {
        QCoreApplication a(argc, argv);
        qDebug() << "start";
        //------------------------------------------------------------------------------------------------------------------
        //  set up database object and db file path and name
        QSqlDatabase db;
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(pathAndFileName);
        db.setHostName(hostName);
        db.setPort(portNumber);
        db.setUserName(userName);
        db.setPassword(userPassword);
        //------------------------------------------------------------------------------------------------------------------
        //  open db with if() statement to catch errors
        if(!db.open())
        {
            qDebug()<<"problem opening database";
        }
        //------------------------------------------------------------------------------------------------------------------
        //  create the db table with fields and field types
        QString query = "CREATE TABLE testTable ("
                "ID integer PRIMARY KEY NOT NULL,"
                "firstName VARCHAR(20),"
                "lastName VARCHAR(20),"
                "birthDate DATETIME,"
                "weight double);";
    
        QSqlQuery qry;
    
        if(!qry.exec(query))
        {
            qDebug()<<"error creating table";
        }
        //------------------------------------------------------------------------------------------------------------------
        // adding values to table
    
        addValues(1, "John", "Parker", "01-01-1984", 156.5);
        addValues(2, "John", "Emdall", "02-02-1995", 181.3);
        addValues(3, "John", "Whorfin", "01-01-1995", 181.5);
        addValues(4, "John", "O'Connor", "01-01-1995", 181.5);
    
        db.close();
    
        qDebug() << "stop";
        return a.exec();
    }
    //------------------------------------------
    void addValues( int ID, QString firstName, QString lastName, QString  birthDate, double weight  )
    {
        QSqlQuery qry;
        qry.prepare("INSERT into testTable ("
                    "ID,"
                    "firstName,"
                    "lastName,"
                    "birthDate,"
                    "weight) "
                    "VALUES (?, ?, ?, ?, ?);");
    
        qry.addBindValue(ID);
        qry.addBindValue(firstName);
        qry.addBindValue(lastName);
        qry.addBindValue(QDateTime::fromString( birthDate, "mm-dd-yyyy"));
        qry.addBindValue(weight);
    
        if(!qry.exec())
        {
            qDebug()<<"error adding values to table";
        }
    }
    

    The SQLITE_EXTENSION_INIT1 statement appears to be a problem:

    SQLITE_EXTENSION_INIT1
    

    gives:

    main.cpp:10:1: warning: zero as null pointer constant
    sqlite3ext.h:647:77: note: expanded from macro 'SQLITE_EXTENSION_INIT1'
    

    Am I on the right track?

    Are there any assets or examples for the use of JSON1 in Qt Creator? I have found none on-line yet.

    I have found lots of information on-line on using the JSON1 extension in SQLite3 at its command-line level, and all the documentation on the JSON1 extension functions and features.


  • Lifetime Qt Champion

    Don't know what you really want to achieve by including the sqlite3 sources but Qt will not use it - it will use the QSqlite3 plugin provided by your Qt installation. And there json support is enabled since Qt 5.13


Log in to reply