SQL script and SQLite



  • I've noticed that SQLite have unpleasant limitation - you can't feed it with a script containing sequential CREATE TABLE statements (only the first statement will be executed). Is there any way to bypass it with Qt's means or do i have to pass to QSqlQuery::exec() separate statements?
    I really don't want to parse my .sql file (another bicycle, don't need it) or create artificial separators of statements (trying not to rely on my memory).
    Also i can't find any information of this limitation with Google.

    [edit : fixed typo in title, eddy]



  • Just did some digging into SQLite sources. ".read" command of sqlite3 parses all .sql file and feed to sqlite3_exec() separate statements.



  • And QSQLiteResult doesn't seem to use sqlite3_exec(). As i can see from it's sources for exec(QString) is uses sequential calls for prepare(QString) and exec(). And former uses sqlite3_prepare16_v2() for queries. I'll try to code with SQLite's API and figure out what's wrong.



  • And bingo! sqlite3_exec() can handle sequential CREATE TABLE statements. And sqlite3_prepare16_v2() as it's fifth argument takes a pointer to a pointer where it can store unprepared part of the query! And QSQLiteResult::prepare(const QString &query) just passes there null! Why? Is it a bug?
    Sorry for slander of SQLite.

    Offtopic: didn't ever think that rather intensive coding implies so much digging in someone else's code.





  • I wouldn't call it a bug. The exec call made this way is compatible to the other Qt SQL drivers, which may not support this feature.



  • [quote author="p-himik" date="1317963986"]I really don't want to parse my .sql file (another bicycle, don't need it) or create artificial separators of statements (trying not to rely on my memory).[/quote]

    In case you decide to do so it -is- can be as easy as
    @
    if(_database.tables().isEmpty())
    {
    QFile schemaFile(":/sql/schema.sql");

        schemaFile.open(QFile::ReadOnly);
    
        QStringList schemaTableList = QString(schemaFile.readAll()).split(";");
        foreach(const QString schemaTable, schemaTableList)
        {
            if(!schemaTable.trimmed().isEmpty())
            {
                _database.exec(schemaTable);
            }
        }
    
        schemaFile.close();
    }
    

    @



  • Volker, than how can you call this feature? I'm not an expert in SQL so i'm just curios. And why doesn't QSqlDriver::DriverFeature include such feature? Also, QPSQL driver successfully executes query with multiple CREATE TABLE so i'm not so sure about compatibility.

    Lukas, what about CREATE TRIGGER? It may include ";" inside statement.



  • Be aware that this solution only works if there is no semikolon (";") contained in your SQL workload. It will fail on the following SQL:

    @
    CREATE TABLE qdn1 (
    id int default 0,
    name varchar(20) default 'not given;' -- it fails on the semicolon of the default value!
    );

    CREATE TABLE qdn2 (
    id int
    );
    @



  • I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.



  • Well, that's true. The snippet posted will not work in all cases and might require some adjustments in cases where not only simple CREATE TABLE statements are used.



  • [quote author="Volker" date="1317979869"]I didn't look into the sources, but it may be that postgresql can handle this on the server side or does the parsing and splitting internally in its own libs.[/quote]

    Yes, maybe it is so. But i don not understand why exec(QString) is made from prepare(QString) and exec(). As i can see from sources, the only reason for this is unwillingness to write similar code in exec(QString) and prepare(QString). Actually i can write exec(QString) with native SQLite's sqlite3_exec() which, as i mentioned above, can handle multiple statements in a single query.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.