Running command ("separator ',' " and ".import test.csv foo") on SQLite database directly from Qt

asjafjell

I have a SQLite database which I do queries on by using QsqlQuery. If I want to enforce the 'foreign_key' constraint on the database I can set the query to be "PRAGMA foreign_key = 1". Checking afterwards by creating a "PRAGMA foreign_key" query, the first value of next() is 1, so this does work as expected.

My problem is now if i want to configure the database in other ways like:
@.separator ','
.import test.csv foo@
, where the last line reads a .csv file and inserts it into the 'foo' table of the currently opened database.

In command line this is straight forward. I just open my SQLite database and run those two lines. Is it possible to run this on the SQLite database using QSqlQuery, or must I use QProcess or System()? System() works to fire up the command prompt, but I don't know how to run the commands after this. Should i create a .bat file? Right now I am clueless and a little help would be greatly appreciated.

Thanks for the patience.

panosk

Hi and welcome,

From SQLite's manual:

bq. And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like sqlite3_prepare() or sqlite3_exec().

You can change some internals with PRAGMA commands, but not the separator.

Anyway, since you use Qt and not the sqlite command line utility, you'd better do what you want the Qtish way :-) . Something like that (untested):

@
QFile f("YourFile.csv");
if(f.open (QIODevice::ReadOnly)){
QSqlQuery query;
QTextStream stream (&f);
while(!stream.atEnd()){
QString statement = "INSERT INTO YourTable VALUES("; //start building your INSERT statement

    QStringList line = stream.readLine().split(';'); //here you define your separator
    for(int i=0; i<line.length(); ++i){
        statement.append(line.at(i));
        statement.append(",");
    }
    statement.chop(1); //remove trailing comma
    statement.append(");"); //close value group
    query.exec&#40;statement&#41;;
}
f.close ();

@

You may probably have to take care of few other things while constructing your insert statements (like single quotes).

If your file is large enough, then you could enclose all your inserts in a transaction to speed things up.