NULL values in SQLite

bart.hollis

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

Fuel

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.

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?

ambershark

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

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

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

kenchan

@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??

ambershark

@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. :)