Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. SQL script and SQLite
Forum Updated to NodeBB v4.3 + New Features

SQL script and SQLite

Scheduled Pinned Locked Moved General and Desktop
12 Posts 3 Posters 8.8k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • P Offline
    P Offline
    p-himik
    wrote on last edited by
    #3

    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.

    1 Reply Last reply
    0
    • P Offline
      P Offline
      p-himik
      wrote on last edited by
      #4

      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.

      1 Reply Last reply
      0
      • P Offline
        P Offline
        p-himik
        wrote on last edited by
        #5

        Just did a "bugreport":https://bugreports.qt.nokia.com/browse/QTBUG-21884

        1 Reply Last reply
        0
        • G Offline
          G Offline
          goetz
          wrote on last edited by
          #6

          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.

          http://www.catb.org/~esr/faqs/smart-questions.html

          1 Reply Last reply
          0
          • L Offline
            L Offline
            lgeyer
            wrote on last edited by
            #7

            [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();
            }
            

            @

            1 Reply Last reply
            0
            • P Offline
              P Offline
              p-himik
              wrote on last edited by
              #8

              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.

              1 Reply Last reply
              0
              • G Offline
                G Offline
                goetz
                wrote on last edited by
                #9

                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
                );
                @

                http://www.catb.org/~esr/faqs/smart-questions.html

                1 Reply Last reply
                0
                • G Offline
                  G Offline
                  goetz
                  wrote on last edited by
                  #10

                  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.

                  http://www.catb.org/~esr/faqs/smart-questions.html

                  1 Reply Last reply
                  0
                  • L Offline
                    L Offline
                    lgeyer
                    wrote on last edited by
                    #11

                    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.

                    1 Reply Last reply
                    0
                    • P Offline
                      P Offline
                      p-himik
                      wrote on last edited by
                      #12

                      [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.

                      1 Reply Last reply
                      0

                      • Login

                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • Users
                      • Groups
                      • Search
                      • Get Qt Extensions
                      • Unsolved