PDA

View Full Version : Problem using SQLite3 with regular expressions



Yakuza_King
15th October 2010, 15:20
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;
...


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
15th October 2010, 15:39
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 Building QSQLITE driver with AES-256 encryption support as a guideline.


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

Yakuza_King
18th October 2010, 10:43
Little misunderstanding, the SQLite dokumentation said that SQLite support regular expressions.

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