[solved] A little problem using qsql (qsqlite)

[helgur]

I am trying to fetch the key id from a record I've just inserted:

@ QSqlQuery qri(connection);
qri.prepare("INSERT INTO posts (stamp, date) VALUES (:stamp, :date)");
qri.bindValue(":stamp", postID);
qri.bindValue(":date",time);
if (!qri.exec() && !error) {
errorCode = 501;
error = true;
errorMessage = qri.lastError().text()+" "+postID;
}@

With:

@ QString SQL = "SELECT id FROM posts WHERE stamp = ':thestamp'";
QSqlQuery qriL(connection);
qriL.prepare(SQL);
qriL.bindValue(":thestamp", postID);
if (!qriL.exec() && !error) {
errorCode = 502;
error = true;
errorMessage = qri.lastError().text();
}
qriL.nextResult();
int stamp = qriL.value(0).toInt();@

But the value 'stamp' is always 0. What am I doing wrong?

For the record, instead of nextResult() I have also tried next() and seek(0) without result. Interesting enough using a while loop to loop with while (qriL.next()) gives me an integer with a completly different value than wanted/expected ...

[panosk]

Try without the single quotes around the placeholder (':thestamp') or use a positional placeholder (?). See http://qt-project.org/doc/qt-4.8/qsqlquery.html#approaches-to-binding-values.

[helgur]

I failed to mention I've already tried that

@ QString SQL = "SELECT id FROM posts WHERE stamp = ?";
QSqlQuery qriL(connection);
qriL.prepare(SQL);
qriL.bindValue(0, postID);
if (!qriL.exec() && !error) {
errorCode = 502;
error = true;
errorMessage = qri.lastError().text();
}
qriL.nextResult();
int stamp = qriL.value(0).toInt();@

doesn't work

I also tried without single quotes in SQL statement, although it is a varchar (int stamp and database record stamp is not the same, my naming is a bit confusing, sorry) so it should be enclosed with quotes, yes?

[panosk]

Is the id field included in your schema or are you trying to fetch the "hidden" rowid from sqlite? If you want sqlite's id, use "rowid" and not "id". Otherwise, you could post your schema.

[helgur]

@
-- Table: posts
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL
UNIQUE,
stamp CHAR( 12 ) NOT NULL
UNIQUE,
date INT( 20 ) NOT NULL
);
@

id is the field I am trying to fetch

[panosk]

That's not table "posts" :)

[helgur]

sorry, I went a bit fast there. fix'd

[helgur]

Ok, I managed to solve it. I don't know why but reverting my code to:

@ QString SQL = "SELECT id FROM posts WHERE stamp = ?";
QSqlQuery qriL(connection);
qriL.prepare(SQL);
qriL.bindValue(0, postID);
if (!qriL.exec() && !error) {
errorCode = 502;
error = true;
errorMessage = qri.lastError().text();
}
/*
qriL.nextResult();
int stamp = qriL.value(0).toInt();
*/
int stamp;
while (qriL.next()){
stamp = qriL.value(0).toInt();
}

    std::cout <<stamp;
    std::cout <<"<br />";@

Seems to have fixed the problem as it printed the correct id number in browser, before it just generated an obscure integer out of nowhere. I have a feeling I need to revisit this, but for now it works

[panosk]

Well, then check these:

• Make sure your insert statement is working ok and the postID is not indeed 0
• Check the connection you pass to your query to make sure the database is open
• Try to fetch the record without your error code block with sth like
  @
  int stamp;
  if(qriL.first()) {
  stamp = qriL.value(0).toInt();
  }
  @

[helgur]

Well as you can see from the schema, id is set to auto increment so if it was indeed set to 0 something would be very off. Beside that as I have reported, it now returned with the valid id so why do you ask me to check if my insert statement works when it clearly works?

Database is opened at object initialization and closed in the destructor and is shared within the scope of the object. Is that a good approach? Any pitfalls?

I will try your approach later, although I suspect it won't set int stamp at all as it didn't do it before.

Thank you for your reply and effort in trying to solve my issue :)

[panosk]

Hi,

My post was meant to be posted before yours, but anyway :)

I think the problem is improper use of "nextResult()":http://qt-project.org/doc/qt-4.8/qsqlquery.html#nextResult. I haven't ever used this function, but according to the docs you should use it for multiple result sets. This means that you still have to use next() to actually navigate through the rows of a result set and then fetch the values you need. Also, I'm not sure if this function is supported by the SQLITE driver.

Anyway, next() is what you need or first() for evaluating your simple test query.