Sqlite User Define Functions

[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]

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

[therj]

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

[jsulm]

@therj Did you check return value of sqlite3_create_function ?

[therj]

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

[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. :)