SQLite select and update statements



  • Good afternoon, I am having a problem with selecting and updating queries in sqlite.
    Basically I use the select and update commands in my sqlite command shell and it gives me
    the correct output but if I use the same statement in my QT creator it gives
    me an error about unable to fetch query row.
    The select and update function only works if I have one string picked out
    rather than a whole bunch. Let me show you.
    qry.prepare("update patientTable set lname='"+lname+"'where
    patientId='"+patientId+"'"); works and updates but when I try:

    qry.prepare("update patientTable set patientId='"+patientId+"',
    fname='"+fname+"',middle='"+middle+"',lname='"+lname+
    "',suffix='"+suffix+"',dob='"+dob+"',gender='"+g
    ender+"',physician='"+physician+"',description='"+descript
    ion+"',anatomy='"+anatomy+"',accession='"+accession+"
    ',datetime='"+datetime+"'where
    patientId='"+patientId+"'");
    it gives me an error about query fetch row. I then thought it would be an database error but then put the same
    command into my sqlite and it updated the corresponding values with both
    commands shown above. I am also having the same problem with my select
    statement where if I select one value then it works but if I try to select
    more than one then it gives me a query fetch error. I tried using :patientId
    and qry.bindValue(":patientId",patientId) method and it also didnt
    work. I can post my code for you if youd like but let me know what you think
    first.

    Here is my SQLite schema:

    create table patientTable(patientId INT NOT NULL, fname varchar(30) NOT NULL,
    midde varchar(30), lname varchar(30) NOT NULL, suffix varchar(15), dob
    varchar(10) NOT NULL, gender varchar(20) NOT NULL, physician varchar(40) NOT
    NULL, description varchar(50), anatomy varchar(20) NOT NULL, accession int
    NOT NULL, datetime varchar(20) NOT NULL, primary key(patientId));

    I also tried out a second one to make sure it wasnt the INT that was causing
    the error as shown:

    create table patientTable(patientId varchar(20) NOT NULL, fname varchar(30)
    NOT NULL, midde varchar(30), lname varchar(30) NOT NULL, suffix varchar(15),
    dob varchar(10) NOT NULL, gender varchar(20) NOT NULL, physician varchar(40)
    NOT NULL, description varchar(50), anatomy varchar(20) NOT NULL, accession
    varchar(20) NOT NULL, date varchar(12) NOT NULL, time varchar(12) NOT NULL,
    primary key(patientId));

    I can post the .h file and .cpp if needed.


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    Yes, sharing your code would be a good idea. It will allow us to better understand what you are trying to do.

    By the way, did you took a look a the request generated by QSqlQuery since it's failing ?


  • Qt Champions 2016

    @Danielpopo said:

    y.prepare("update patientTable set patientId='"+patientId+"',
    fname='"+fname+"',middle='"+middle+"',lname='"+lname+xxxxxxx

    Just a note:
    If any of the variables is not a QString, then please wrap
    QString::number(var) around it as str+ int + str , do not always do
    as you might expect.



  • @Danielpopo

    Why not use QSqlQuery::prepare with named parameters in your query you can then bind the values to the parameters with QSqlQuery::bindValue, see prepare and bindValue example.

    Just another note: if you post code here please put it between ` ` or ``` ``` so it will be more readable :)



  • @mrjj Thanks for the response,

    I solved my problem it was a misspelling in my schema with my sqlite. But what do you mean about wrapping it with QString::number, because I compiled it with a string and it worked but I can see what you mean that I am inputting a string to an integer slot.


  • Qt Champions 2016

    @Danielpopo
    Hi
    super.
    What i meant was that sometimes
    integers do not convert to strings as expected with +

    int number=888;
    QString a="a";
    QString b="b";
    QString result=a+number+b;
    qDebug() <<"-------" << result;

    expected a888b
    got axb

    so i was not sure if all was strings in your statement. Since it work, you didnt have such case :)


Log in to reply
 

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