Sqlite User Define Functions



  • 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 !!!



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


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


  • Moderators

    @therj Did you check return value of sqlite3_create_function ?



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