Qt Forum

    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    • Unsolved

    Solved Sqlite User Define Functions

    General and Desktop
    sqlite sqlite3 udf custom function
    3
    6
    3425
    Loading More Posts
    • 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.
    • Dong
      Dong last edited by Dong

      Hi Everyone !

      I already used this approach before with C#.Net but I don't know
      "How to create & use a Sqlite User Define Function in QT"

      After Googling, I found some topic said about 3rd party lib (sqlite3...)
      I also found sqlite3.h, sqlite3ext.h in Qt Folder (C:\Qt\Qt5.4.2\Tools\mingw491_32\opt\include)

      I tried this but got runtime error at query.exec();
      it said no function...

      Anyone ever tried this approach and success ?!

      Please help ! Many thanks !!!

      Dong 1 Reply Last reply Reply Quote 0
      • Dong
        Dong @Dong last edited by

        This is my sample code !

        Error occurred at the line:
        bool execRet = query.prepare("select qtAverage(1, 3, 9);");

        Here is the Error message:
        "ExecuteQuery error: " QSqlError("1", "Unable to execute statement", "no such function: qtAverage")

        What I'm wrong ?!

        void qtAverage(sqlite3_context* ctx, int argc, sqlite3_value** argv)
        {
            QString str1((const char*)sqlite3_value_text(argv[0]));
            QString str2((const char*)sqlite3_value_text(argv[1]));
            QString str3((const char*)sqlite3_value_text(argv[2]));
        
            sqlite3_result_int(ctx, 123456);
        }
        
        void MainViewModel::testSqlUserFunction()
        {
            sqlite3_initialize();
            QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
            db.setDatabaseName("test.db");
            db.open();
        
            QVariant v = db.driver()->handle();
            if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) {
                sqlite3 *db_handle = *static_cast<sqlite3 **>(v.data());
                if (db_handle != 0) { // check that it is not NULL
                    // This shows that the database handle is generally valid:
                    qDebug() << sqlite3_db_filename(db_handle, "main");
                    sqlite3_create_function(db_handle, "qtAverage", 3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, NULL, &qtAverage, NULL, NULL);
                    
                    QSqlQuery query(db);
        
                    //ERROR occurred HERE !!!
                    bool execRet = query.prepare("select qtAverage(1, 3, 9);");
        
                    if(!execRet){
                        qDebug()    << QString("ExecuteQuery error: ")
                                    << query.lastError();
                        qDebug()    << QString("SQL error: ")
                                    << query.lastQuery();
                    }
        
                    execRet = query.exec();
        
                    if(!execRet){
                        qDebug()    << QString("ExecuteQuery error: ")
                                    << query.lastError();
                        qDebug()    << QString("SQL error: ")
                                    << query.lastQuery();
                    }
                    else{
                        query.next();
                        qDebug() << query.value(0).toString();
                    }
                }
            }
            db.close();
        }
        
        1 Reply Last reply Reply Quote 0
        • T
          therj last edited by

          Were you able to solve this issue? I am having the same problem.

          jsulm Dong 2 Replies Last reply Reply Quote 0
          • jsulm
            jsulm Lifetime Qt Champion @therj last edited by

            @therj Did you check return value of sqlite3_create_function ?

            https://forum.qt.io/topic/113070/qt-code-of-conduct

            1 Reply Last reply Reply Quote 0
            • T
              therj last edited by

              Yes, it returns 0, which I believe is SQLITE_OK.

              1 Reply Last reply Reply Quote 0
              • Dong
                Dong @therj last edited by Dong

                @therj

                It's may be too late but... better than not.

                I found the problem is sqlite version i'm using is not match with the version Qt used in the core of Qt Framework.

                So, I'm check the compatible of sqlite.h (3rd party) with Qt Sqlite driver to make sure it is the same version like this:

                //Qt QSqlDatabase driver for Sqlite
                query = db.PrepareQuery("SELECT sqlite_version()");

                //Vs 3rd Party driver (included sqlite.h)
                qDebug() << "sqlite3_libversion() =" << sqlite3_libversion();

                there are few note to remember:

                • Cannot create SQLite custom functions when db object is not open.
                • Need to call sqlite3_initialize() before call sqlite3_create_function()

                If you have more questions, contact me via facebook Lã Đại Đồng. :)

                1 Reply Last reply Reply Quote 0
                • First post
                  Last post