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. NULL values in SQLite
Forum Updated to NodeBB v4.3 + New Features

NULL values in SQLite

Scheduled Pinned Locked Moved Solved General and Desktop
8 Posts 4 Posters 2.3k Views 2 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.
  • bart.hollisB Offline
    bart.hollisB Offline
    bart.hollis
    wrote on last edited by VRonin
    #1

    Hello All,
    New to QT. Using QT5 version 5.6.2.x on openSUSE 42.3. I am creating a database application using SQLite. One column in the tables will be used as an id and should have a unique number assigned to each row. SQLite allows that by setting a column to type integer and, if no information is provided, an integer will be inserted automagically. I simply cannot figure how to get this to work.

    The structure for the table is here:

    QSqlQuery q;
        if (!q.exec(QLatin1String("create table brass(id integer primary key, lot integer, caliber varchar, brand varchar, fired integer, amount integer, cost real)")))
    

    The prepare statement is here:

    if (!q.prepare(QLatin1String("insert into brass(id, lot, caliber, brand, fired, amount, cost) values(?, ?, ?, ?, ?, ?, ?)")))
            return q.lastError();
    
         addBrass(q, 1, 111, QLatin1String("30"), QLatin1String("Winchester"), 1, 50, 15.00);
         addBrass(q, 2, 122, QLatin1String("6.5 Creedmoore"), QLatin1String("Nosler"), 0, 50, 90.50);
         addBrass(q, 3, 133, QLatin1String("223"), QLatin1String("Federal"), 0, 50, 35.00);
    

    And the insert statement is here:

    void addBrass(QSqlQuery &q, int id, int lot, const QString &caliber, const QString &brand, int fired, int amount,  float cost)
    
    {
        q.addBindValue(id);
        q.addBindValue(lot);
        q.addBindValue(caliber);
        q.addBindValue(brand);
        q.addBindValue(fired);
        q.addBindValue(amount);
        q.addBindValue(cost);
        q.exec();
    }
    
    

    If I pass a NULL by putting it in the call to the addBrass line, a 0 is inserted in the first row and the following rows do not function. There is only one row in the table.

    I tried using QVariant(Qvariant::int) in several places in the code and none of them worked. I tried creating a variable in the addBrass method but that didn't work.

    I believe the secret lies in getting the addBrass method to skip entering anything in the first column, but if I try to not pass anything to it, I either get errors because the table doesn't match, of the method places the values in the wrong column.

    Would someone please tell me what I am doing wrong?
    Bart

    1 Reply Last reply
    0
    • F Offline
      F Offline
      Fuel
      wrote on last edited by
      #2

      If you have a ID Column defined as Primary Key you dont need to set the ID in every Query. In your Insert Query and any other Query you dont need to use id. Everytime you insert Rows, the Database adds IDs automatically.

      1 Reply Last reply
      2
      • bart.hollisB Offline
        bart.hollisB Offline
        bart.hollis
        wrote on last edited by
        #3

        I guess I wasn't clear. Sorry. My question is: How can I add information to a row Without adding anything to the id column?

        A 1 Reply Last reply
        0
        • bart.hollisB bart.hollis

          I guess I wasn't clear. Sorry. My question is: How can I add information to a row Without adding anything to the id column?

          A Offline
          A Offline
          ambershark
          wrote on last edited by
          #4

          @bart.hollis Do you mean like updating a row that is there?

          I'm assuming that is what you mean, in which case you use a different query:

          UPDATE brass SET lot=?, caliber=?, brand=?, fired=?, amount=?, cost=? WHERE id=?

          You will need to know the id of the row you want to update, so query that before.

          My L-GPL'd C++ Logger github.com/ambershark-mike/sharklog

          1 Reply Last reply
          0
          • bart.hollisB Offline
            bart.hollisB Offline
            bart.hollis
            wrote on last edited by
            #5

            The code I was using was pretty much copied from the books tutorial. The system didn't use the autoinsert of the integer into a column. I have since discovered a way to do what I need to do. Thanks to both who replied!

            My version of the function:

            //your void addBrass(QSqlQuery &q, int id, int lot, const QString &caliber, const QString &brand, int fired, int amount,  float cost)
            
            {
                q.bindValue(1, id);         // Stuff the id value into the lot column.
                q.bindValue(1, lot);        // Then, overwrite it with the lot information.
                q.bindValue(2, caliber);
                q.bindValue(3, brand);
                q.bindValue(4, fired);
                q.bindValue(5, amount);
                q.bindValue(6, cost);
                q.exec();
            }
            code here
            

            I consider my question solved! Yipee!
            Bart

            A 1 Reply Last reply
            2
            • bart.hollisB Offline
              bart.hollisB Offline
              bart.hollis
              wrote on last edited by
              #6

              The "// your" was not supposed to be in my code! <smile> Just gotta learn this forum software!

              Hmmm! I can't edit the post! I guess the moderators think we're so good we don't make typos!

              Uuugh! And I can't add another post until 5 minutes!

              ???
              Bart

              K 1 Reply Last reply
              0
              • bart.hollisB bart.hollis

                The "// your" was not supposed to be in my code! <smile> Just gotta learn this forum software!

                Hmmm! I can't edit the post! I guess the moderators think we're so good we don't make typos!

                Uuugh! And I can't add another post until 5 minutes!

                ???
                Bart

                K Offline
                K Offline
                kenchan
                wrote on last edited by kenchan
                #7

                @bart.hollis click the 3 vertical dots at the bottom of a posts text. looks like this
                "reply quote ^0v <3 vertical dots here>"
                without the quotation marks and where <3 vertical dots here> is actually 3 vertical dots :-)
                That is the Tools menu, you might need to click it twice to get it to show depending on your browser??

                1 Reply Last reply
                1
                • bart.hollisB bart.hollis

                  The code I was using was pretty much copied from the books tutorial. The system didn't use the autoinsert of the integer into a column. I have since discovered a way to do what I need to do. Thanks to both who replied!

                  My version of the function:

                  //your void addBrass(QSqlQuery &q, int id, int lot, const QString &caliber, const QString &brand, int fired, int amount,  float cost)
                  
                  {
                      q.bindValue(1, id);         // Stuff the id value into the lot column.
                      q.bindValue(1, lot);        // Then, overwrite it with the lot information.
                      q.bindValue(2, caliber);
                      q.bindValue(3, brand);
                      q.bindValue(4, fired);
                      q.bindValue(5, amount);
                      q.bindValue(6, cost);
                      q.exec();
                  }
                  code here
                  

                  I consider my question solved! Yipee!
                  Bart

                  A Offline
                  A Offline
                  ambershark
                  wrote on last edited by
                  #8

                  @bart.hollis said in NULL values in SQLite:

                  //your void addBrass(QSqlQuery &q, int id, int lot, const QString &caliber, const QString &brand, int fired, int amount, float cost)

                  {
                  q.bindValue(1, id); // Stuff the id value into the lot column.
                  q.bindValue(1, lot); // Then, overwrite it with the lot information.

                  So on this you can just get rid of id entirely. You can just write lot directly and ignore id since binding it twice does nothing. So you can modify addBrass to not take an id. This has the effect of the sql db just using the primary key auto incrementing id as it is supposed to. Your way works but the extra line of code just waste CPU cycles and does nothing. :)

                  My L-GPL'd C++ Logger github.com/ambershark-mike/sharklog

                  1 Reply Last reply
                  1

                  • Login

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