Problem using SQLite3 with regular expressions

[Yakuza_King]

Hello!
Facts:

SQLite3-Database -> created by myself
used driver -> QSQLITE
connection -> ok, because statments before and after the problem are working fine

What do I do:

I try to delete some data, which doesn't fit into my regular expression

#include <QRegExp>
#include <QSqlError>
#include <QString>
#include <QStringList>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include "cb_database/cb_database.h"
 
#define DB_NMEA "nmea"
...
 
cb_database db; //Class which handles a SQLite3-Database
db.open("xyz.db"); //Path to database
QRegExp dateFormat;
QRegExp timeFormat;
 
//Format of a correct date -> ddmmyy
dateFormat.setPattern("([0][1-9]|[1-2][0-9]|[3][0-1])"
		"([0][1-9]|[1][0-2])[0-9]{2}");
//Format of a correct time -> hhmmss.00
timeFormat.setPattern("([0-1][0-9]|[2][0-3])([0-5][0-9]){2}\\.[0]{2}");
 
QString reg;
reg=QObject::tr("DELETE FROM %0 WHERE "
	"(time REGEXP '%1')=0 "
	"OR (date REGEXP '%2')=0 "
	"OR date='010680'")
	.arg(DB_NMEA)
	.arg(timeFormat.pattern()).arg(dateFormat.pattern());
db.query(reg);
QSqlQueryModel *test;
test=db.lastQuery();
qDebug() << test->lastError();
qDebug() << reg;
...

#include <QRegExp>
#include <QSqlError>
#include <QString>
#include <QStringList>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include "cb_database/cb_database.h"

#define DB_NMEA "nmea"
...

cb_database db; //Class which handles a SQLite3-Database
db.open("xyz.db"); //Path to database
QRegExp dateFormat;
QRegExp timeFormat;

//Format of a correct date -> ddmmyy
dateFormat.setPattern("([0][1-9]|[1-2][0-9]|[3][0-1])"
		"([0][1-9]|[1][0-2])[0-9]{2}");
//Format of a correct time -> hhmmss.00
timeFormat.setPattern("([0-1][0-9]|[2][0-3])([0-5][0-9]){2}\\.[0]{2}");

QString reg;
reg=QObject::tr("DELETE FROM %0 WHERE "
	"(time REGEXP '%1')=0 "
	"OR (date REGEXP '%2')=0 "
	"OR date='010680'")
	.arg(DB_NMEA)
	.arg(timeFormat.pattern()).arg(dateFormat.pattern());
db.query(reg);
QSqlQueryModel *test;
test=db.lastQuery();
qDebug() << test->lastError();
qDebug() << reg;
...

To copy to clipboard, switch view to plain text mode 

Problem:

The statement seems to be correct, because I have tested this one with a opensource SQLite-Database-browser. I happens exactly what I expected. But when I try the same statement in my Qt-Application, nothing happens. The count of the rows in the table doesn't change. So the Problem must be in the code or maybe QSQLite-Driver.

Result:

qDebug Statement-> "DELETE FROM nmea WHERE (time REGEXP '([0-1][0-9]|[2][0-3])([0-5][0-9]){2}\.[0]{2}')=0 OR (date REGEXP '([0][1-9]|[1-2][0-9]|[3][0-1])([0][1-9]|[1][0-2])[0-9]{2}')=0 OR date='010680'"
qDebug ERROR -> QSqlError(1, "Unable to execute statement", "no such function: REGEXP")

The Error shows that the function I use doesn't exist, but the documentation says that it exists.

Question:

Does somebody has an idea what to do now?

[Lykurg]

As far as I remember you have to turn ICU on to have regular expression support in SQLite. Obviously you haven't done that or you are using the "wrong" driver.

To be sure, you are using the driver you compiled, use an other identifier than QSQLITE. To turn ICU on use the SQLITE_ENABLE_ICU switch. On how to build you can use the our wiki article [WIKI]Building QSQLITE driver with AES-256 encryption support[/WIKI] as a guideline.


EDIT: Which documentation says, that the SQLite driver has default regular expression support?

[Yakuza_King]

Little misunderstanding, the SQLite dokumentation said that SQLite support regular expressions.

Thanks for the advice, I will try that...