Using SQLite custom functions with Qt

[Luc4]

Hi! I'm trying to create a custom function for a SQLite database I'm using with Qt. I found information on how to create the function and it seems to work correctly on a x86 system.

Instead, it seems to be failing with a segfault on an ARM device. This is the code I wrote:

static bool createSQLiteFunctions(const QSqlDatabase& db)
{
	// Get handle to the driver and check it is both valid and refers to SQLite3.
	QVariant v = db.driver()->handle();
	if (!v.isValid() || qstrcmp(v.typeName(), "sqlite3*") != 0) {
		LOG_WARNING("Cannot get a sqlite3 handle to the driver.");
		return false;
	}
 
	// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}
 
	// Check validity of the state.
	if (!db.isValid()) {
		LOG_ERROR("Cannot create SQLite custom functions: db object is not valid.");
		return false;
	}
 
	if (!db.isOpen()) {
		LOG_ERROR("Cannot create SQLite custom functions: db object is not open.");
		return false;
	}
 
	if (sqlite3_create_function(handler, "_deleteFile", 1, SQLITE_ANY, 0, &_sqlite3DeleteFile, 0, 0))
		LOG_ERROR("Cannot create SQLite functions: sqlite3_create_function failed.");
 
	return true;
}

static bool createSQLiteFunctions(const QSqlDatabase& db)
{
	// Get handle to the driver and check it is both valid and refers to SQLite3.
	QVariant v = db.driver()->handle();
	if (!v.isValid() || qstrcmp(v.typeName(), "sqlite3*") != 0) {
		LOG_WARNING("Cannot get a sqlite3 handle to the driver.");
		return false;
	}

	// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}

	// Check validity of the state.
	if (!db.isValid()) {
		LOG_ERROR("Cannot create SQLite custom functions: db object is not valid.");
		return false;
	}

	if (!db.isOpen()) {
		LOG_ERROR("Cannot create SQLite custom functions: db object is not open.");
		return false;
	}

	if (sqlite3_create_function(handler, "_deleteFile", 1, SQLITE_ANY, 0, &_sqlite3DeleteFile, 0, 0))
		LOG_ERROR("Cannot create SQLite functions: sqlite3_create_function failed.");

	return true;
}

To copy to clipboard, switch view to plain text mode 

The db object is instantiated as a member of another object, which is calling this function in the constructor, where the connection to the db is estabilshed:

...
        // Create the connection to the database. The connection has to be different
	// for each thread requiring one.
	db = QSqlDatabase::addDatabase("QSQLITE", connectionName);
 
	// Setup the database to avoid lockings.
	db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=10000");
 
	db.setDatabaseName(MEDIASTORE_DATABASE_FILENAME);
	if (!db.open()) {
        LOG_ERROR("Failed to open the database.");
		// TODO: What to do in case of errors??
    }
 
	// Create custom defined functions.
	if (!createSQLiteFunctions(db)) {
		LOG_ERROR("Failed to create custom functions.");
		// TODO: What to do in case of errors??
	}

...
        // Create the connection to the database. The connection has to be different
	// for each thread requiring one.
	db = QSqlDatabase::addDatabase("QSQLITE", connectionName);

	// Setup the database to avoid lockings.
	db.setConnectOptions("QSQLITE_BUSY_TIMEOUT=10000");

	db.setDatabaseName(MEDIASTORE_DATABASE_FILENAME);
	if (!db.open()) {
        LOG_ERROR("Failed to open the database.");
		// TODO: What to do in case of errors??
    }

	// Create custom defined functions.
	if (!createSQLiteFunctions(db)) {
		LOG_ERROR("Failed to create custom functions.");
		// TODO: What to do in case of errors??
	}

To copy to clipboard, switch view to plain text mode 

It seems that no error log is printed and, but the sqlite3_create_function function gives a segfault. If I remove the call to createSQLiteFunctions everything works fine.

I suspect this may be related to http://bugreports.qt.nokia.com/browse/QTBUG-16586. Any idea how I can solve this?
Thanks!

[levi]

Hi,

I'm trying to create a custom function for a SQLite database with Qt aswell. I have a table with items stored with geographic locations as latitude and longitude in degrees. I want to be able to perform a SELECT on this table and ORDER BY each row's distance from an arbitrary point. Hence, I need to create a distance function.

My problem is that I can't figure out where to get the definition for the sqlite3 struct. The code below will not compile as sqlite3 is not defined.

// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}

// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}

To copy to clipboard, switch view to plain text mode 

Any hint would be appreciated.

[wysota]

Include the sqlite header and link to the sqlite library.

[levi]

I'm using Qt 4.7.4 and MinGW 4.4 and currently I'm including QtSql and QSqlDriver.
Should I be able to include sqlite.h? I can't find it. Where can I find the sqlite library?

[wysota]

Should I be able to include sqlite.h? I can't find it. Where can I find the sqlite library?

Most likely in SQLite installation package... You really have no idea what you are doing, do you?

[levi]

Thanks for quick replies

Yes, I still consider myself a beginner in this area.
As I have used the Qt framework for creating and interacting with the SQLite databases I have only searched inside this framework on how to create SQLite custom functions. Are you saying that I need to install any additional packages to be able to do this?

BTW: Where was your SQLite hyperlink supposed to link to?

[wysota]

Thanks for quick replies

Yes, I still consider myself a beginner in this area.
As I have used the Qt framework for creating and interacting with the SQLite databases I have only searched inside this framework on how to create SQLite custom functions.

I would first think whether I needed a custom function at all. I would then check if it is possible to use a custom function in the ORDER BY clause. Then I would check if I could write such function as a stored procedure. Only then I would try to write it in C/C++.

Are you saying that I need to install any additional packages to be able to do this?

I'm saying that if you want to use type "x" in your code, you need to teach the compiler to handle it. It's a completely different issue whether you really want to use type "x" in your code.

BTW: Where was your SQLite hyperlink supposed to link to?

Nowhere, it was an automatically created (invalid) link.

[levi]

I would first think whether I needed a custom function at all. I would then check if it is possible to use a custom function in the ORDER BY clause. Then I would check if I could write such function as a stored procedure. Only then I would try to write it in C/C++.

In my database there is a table representing items with geographic locations. I have used MSSQL in a previos version of the SW I'm working on, and then I could SELECT the items with ORDER BY f(x). Where f(x) was a function of cos, sin, acos etc to calculate the distance from the item's location to an arbitrary point. That way I got a dataset ordered by the distance to the items. When I started using SQLite I learned that it is not possible to use triangular functions as sin, cos etc in the SQL queries. So that is basically my problem.

When I found this: http://www.thismuchiknow.co.uk/?p=71, I was expecting that sqlite3_create_function could be used within my Qt project to obtain the same functionality.

[wysota]

When I found this: http://www.thismuchiknow.co.uk/?p=71, I was expecting that sqlite3_create_function could be used within my Qt project to obtain the same functionality.

For that you need to link with the sqlite library. If you're not familiar with using 3rd party libraries in your programs, I suggest you find an alternative solution (or learn to use libraries).

[dh7892]

Sorry to dredge up an old post but I have recently had to add my own custom functions to Qt's SQLite drivers and had many of the same problems. Now that I have resolved them and got my code working, I thought I'd add to this post because I found it when searching and it didn't really help me solve the problem so I thought I'd include my solution(s) so anyone else arriving here might save some effort.

There are two ways to go about getting the custom functions to work:

get the sqlite3* pointer from the db as described above. (sqlite3* handler = *static_cast<sqlite3**>(v.data()).

However, if you do it this way, there are some things you need to know:

- You must compile your code to link against sqlite3 (which you will have to install separately from Qt as it doesn't contain enough sqlite so that you could link against it). I just used the unified sqlite header and source files and added them into my project directly rather than linking to a library.
- It seems that you need to be very careful to ensure that the version of sqlite that you link to is exactly the same as the version that Qt used when it was built. Look at the sources for Qt if you need to check.
- When I compiled my code, I had to add the -DSQLITE_THREADSAFE=0 (this was for Qt 4.8) otherwise I got a seg fault in create_function.

The disadvantage of this approach is that you will need to change your source to match whatever version of Qt you are using.

The approach I ended up using was to create my own version of the SQLite database driver. I just copied the relevant code from the Qt source and modified it to use a my local copy of SQLite instead of Qt's version. I also added my custom functions in when the database opens in the driver.

I hope some of this info might help anyone that ends up looking at this page after trying to find help with this problem.

[scottaronbloom]

Sorry to dredge up an old post but I have recently had to add my own custom functions to Qt's SQLite drivers and had many of the same problems. Now that I have resolved them and got my code working, I thought I'd add to this post because I found it when searching and it didn't really help me solve the problem so I thought I'd include my solution(s) so anyone else arriving here might save some effort.

There are two ways to go about getting the custom functions to work:

get the sqlite3* pointer from the db as described above. (sqlite3* handler = *static_cast<sqlite3**>(v.data()).

However, if you do it this way, there are some things you need to know:

- You must compile your code to link against sqlite3 (which you will have to install separately from Qt as it doesn't contain enough sqlite so that you could link against it). I just used the unified sqlite header and source files and added them into my project directly rather than linking to a library.
- It seems that you need to be very careful to ensure that the version of sqlite that you link to is exactly the same as the version that Qt used when it was built. Look at the sources for Qt if you need to check.
- When I compiled my code, I had to add the -DSQLITE_THREADSAFE=0 (this was for Qt 4.8) otherwise I got a seg fault in create_function.

The disadvantage of this approach is that you will need to change your source to match whatever version of Qt you are using.

The approach I ended up using was to create my own version of the SQLite database driver. I just copied the relevant code from the Qt source and modified it to use a my local copy of SQLite instead of Qt's version. I also added my custom functions in when the database opens in the driver.

I hope some of this info might help anyone that ends up looking at this page after trying to find help with this problem.

Thanks for all the pointers...
Here is my summery..
1) the crash is DEFINATELY due to the SQLITE threading setup in combination with Qt, though dh7892 method is a bit extreme.

Here is what I found works JUST as well, and doesnt require ANY changes to Qt + sqlite

After you open your database, call this function

bool setSingleThreaded( QSqlDatabase & db )
{
QVariant v = db.driver()->handle();
if ( !v.isValid() || qstrcmp( v.typeName(), "sqlite3*" ) != 0 )
{
return false;
}

sqlite3 * handler = *static_cast<sqlite3**>( v.data() );
if ( !handler )
{
return false;
}

sqlite3_config( SQLITE_CONFIG_SINGLETHREAD );
return true;
}

Setting the sql3_config setting to SINGLETHREAD has the same net effect, but is a runtime choice.

Also, what I do to GET the definitions of sqlite3, is add the following to my cmake files

set(qtproject_SRCS
${QTDIR}/src/3rdparty/sqlite/sqlite3.c
)

set(qtproject_H
${QTDIR}/src/3rdparty/sqlite/sqlite3.h
)

[Ashkan_s]

I had the same problem, using

sqlite3_config( SQLITE_CONFIG_SINGLETHREAD );

sqlite3_config( SQLITE_CONFIG_SINGLETHREAD );

To copy to clipboard, switch view to plain text mode 

didn't help. So I thought maybe doing this way http://doc.qt.io/qt-4.8/qsqldatabase.html#addDatabase-2 can help. I had problems building the program, until I found this guide which solved the problems. Now everything is working fine

[Kangs]

Hi,

I'm trying to create a custom function for a SQLite database with Qt aswell. I have a table with items stored with geographic locations as latitude and longitude in degrees. I want to be able to perform a SELECT on this table and ORDER BY each row's distance from an arbitrary point. Hence, I need to create a distance function.

My problem is that I can't figure out where to get the definition for the sqlite3 struct. The code below will not compile as sqlite3 is not defined.

// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}

// Create a handler and attach functions.
	sqlite3* handler = *static_cast<sqlite3**>(v.data());
	if (!handler) {
		LOG_WARNING("Cannot get a sqlite3 handler.");
		return false;
	}

To copy to clipboard, switch view to plain text mode 

Any hint would be appreciated.

Include sqlite like this :

#define SQLITE_API extern
#define SQLITE_EXTERN
#include <sqlite3.h>

#define SQLITE_API extern
#define SQLITE_EXTERN
#include <sqlite3.h>

To copy to clipboard, switch view to plain text mode 

Use extern "C" to declare your function.
Work fine for me under linux system, i never try this on MS Windows.