Inserting an integer value into the query.

[Danielpopo]

I have two inquires with this post:
Firstly I am trying to replace integer values in my query with declared integer variables but having trouble doing so.
if( !query.exec( "SELECT Image from imageTable where PatientId = 22 order by ImageId = 206 DESC limit 4"))

would like to replace it with :
if( !query.exec( "SELECT Image from imageTable where PatientId = patientid order by ImageId = imageid DESC limit 4")) and tried:

if( !query.exec( "SELECT Image from imageTable where PatientId = '"+patientid"' order by ImageId = '"+imageid+"' DESC limit 4")) and:

if( !query.exec( "SELECT Image from imageTable where PatientId = :patientid order by ImageId = :imageid1 DESC limit 4"))
QMessageBox::critical(this,tr("Images"),query.lastError().text());
query.bindValue(":patientid",patientid);
query.bindValue(":imageid1",imageid1);

but none seem to work. Also this is my imageTable:
query.exec( "CREATE TABLE IF NOT EXISTS imageTable (ImageId INTEGER PRIMARY KEY AUTOINCREMENT, PatientId varchar(16) NOT NULL, LastName varchar(30) NOT NULL, Image blob NOT NULL, foreign key(PatientId) references patientTable(PatientId))");

My second problem is the query itself. Whenever I call the hard coded version of my query :
if( !query.exec( "SELECT Image from imageTable where PatientId = 22 order by ImageId = 206 DESC limit 4"))
It is giving me the right first image (206) but wrong images for 205,204,203. I know they are wrong because in my constructor I use:
if( !query.exec( "SELECT ImageId,Image from imageTable where PatientId = 22 order by ImageId DESC limit 4"))
which gives me the last 4 images of the database which are 206,205,204,203 and shows them with their corresponding label. But whenever I call the first exec where 'order by imageid = 206' it gives me the wrong images for the labels other than the first one (206). Not sure what the problem here is.

[mrjj]

@Danielpopo said:

if( !query.exec( "SELECT Image from imageTable where PatientId = '"+patientid"' order by ImageId = '"+imageid+"' DESC limit 4")) and:

should have worked if you use QString::number() for imageid and patientid.

[SGaist]

Hi,

Unless I'm mistaken, you can't pass a parameter to ORDER BY only one or more column names.

[Danielpopo]

@SGaist Yea im looking it up and dont see anything regarding passing a parameter for **ORDER BY **. What I was trying to accomplish with this code is to use the arrows key (left, right) to filter through the pictures. What my original idea was to obtain a imageId from the original image and then either Increment (imageid++) for right key or decrement (imageid--) for left key. And then make a query that will select the next 3 images starting from the imageId given. So if I picked an image with an imageId 114 then it would give me imagein 113,112, and 111 as well. But if I cannot use order by and passing a parameter then I dont think I can accomplish this.
I think I will approach by if( !query.exec( "SELECT Image from imageTable where PatientId = "'+patientId+"' AND ImageId = '"+imageid"') and then grab that image and insert it into my label1 and then label2 will take label1 pictures and vice versa for the rest.

[hskoglund]

Hi, you could try with the TOP nn after select, say like this when you hit right key (increment):
"SELECT TOP 4 Image from imageTable where PatientId = " + patientid + " AND ImageId >= " + imageId + " order by ImageId"

and this when you decrement with the left key:
"SELECT TOP 4 Image from imageTable where PatientId = " + patientid + " AND ImageId <= " + imageId + " order by ImageId DESC"

Edit: forgot about you need to step both up and down, fixed now.

[kshegunov]

@hskoglund
SELECT TOP is not standard SQL, it's MS's interpretation of the LIMIT clause.

@Danielpopo

So if I picked an image with an imageId 114 then it would give me imagein 113,112, and 111 as well. But if I cannot use order by and passing a parameter then I dont think I can accomplish this.

It should be relatively simple (you should bind the values):

SELECT * FROM imageTable WHERE PatientId = :patientId AND imageId > :currentImageId ORDER BY imageId LIMIT 3

will give you the next 3 images. If you need the 3 previous images reverse the imageId > :currentImageId comparison. Additionally you can pull all the records in one go by making a UNION:

SELECT * FROM imageTable WHERE PatientId = :patientId AND imageId > :currentImageId ORDER BY imageId LIMIT 3
UNION
SELECT * FROM imageTable WHERE PatientId = :patientId AND imageId < :currentImageId ORDER BY imageId DESC LIMIT 3

In any case your original queries are wrong, and you should fix them.

Kind regards.

[hskoglund]

@kshegunov Actually it looks likeLIMIT suffers the same problem as TOP, both are non-standard SQL according to the Wikipedia page about SQL Select (but LIMIT seems more popular than TOP).

Also it says that according to the SQL standard (2008) you should use something like this:
SELECT * FROM T FETCH FIRST 4 ROWS ONLY

But of course not all DB's have implemented this yet :-(

[kshegunov]

@hskoglund said:

Actually it looks like LIMIT suffers the same problem as TOP, both are non-standard SQL according to the Wikipedia page about SQL Select

Yes, it seems so. Thanks for pointing that out.

but LIMIT seems more popular than TOP

Only marginally, if at all, it appears.

Also it says that according to the SQL standard (2008) you should use something like this

And cross your fingers that the DB will support it ... which, sadly, doesn't seem likely.

But of course not all DB's have implemented this yet

Of course. Only 8 years have passed from the mentioned standard's adoption and naturally there's no rush to implement one of the most common constructs in select statements ... why make the developers' lives easier ...

[Danielpopo]

@mrjj I was hoping to keep it and integer because I wanted to increment/ decrement (imageid++,imageid--) the value. When I use the QString::number() it is then an QString and cannot increment/ decrement the value.

@kshegunov yes this query works perfectly. Thank you, it was the query I was looking for. I forgot we can use comparison statements using select.

[Danielpopo]

@mrjj nevermind, figured it out! I just incremented the int then converted it to a QString::number every iteration. Thanks for the help!