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. Inserting an integer value into the query.
Forum Updated to NodeBB v4.3 + New Features

Inserting an integer value into the query.

Scheduled Pinned Locked Moved Unsolved General and Desktop
10 Posts 5 Posters 2.8k Views 3 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.
  • deleted511D Offline
    deleted511D Offline
    deleted511
    wrote on last edited by
    #1

    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.

    1 Reply Last reply
    0
    • mrjjM Offline
      mrjjM Offline
      mrjj
      Lifetime Qt Champion
      wrote on last edited by
      #2

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

      deleted511D 1 Reply Last reply
      1
      • SGaistS Offline
        SGaistS Offline
        SGaist
        Lifetime Qt Champion
        wrote on last edited by
        #3

        Hi,

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

        Interested in AI ? www.idiap.ch
        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

        deleted511D 1 Reply Last reply
        5
        • SGaistS SGaist

          Hi,

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

          deleted511D Offline
          deleted511D Offline
          deleted511
          wrote on last edited by
          #4

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

          1 Reply Last reply
          0
          • hskoglundH Offline
            hskoglundH Offline
            hskoglund
            wrote on last edited by hskoglund
            #5

            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.

            kshegunovK 1 Reply Last reply
            2
            • hskoglundH 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.

              kshegunovK Offline
              kshegunovK Offline
              kshegunov
              Moderators
              wrote on last edited by
              #6

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

              Read and abide by the Qt Code of Conduct

              deleted511D 1 Reply Last reply
              6
              • hskoglundH Offline
                hskoglundH Offline
                hskoglund
                wrote on last edited by
                #7

                @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 :-(

                kshegunovK 1 Reply Last reply
                2
                • hskoglundH 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 :-(

                  kshegunovK Offline
                  kshegunovK Offline
                  kshegunov
                  Moderators
                  wrote on last edited by
                  #8

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

                  Read and abide by the Qt Code of Conduct

                  1 Reply Last reply
                  0
                  • kshegunovK 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.

                    deleted511D Offline
                    deleted511D Offline
                    deleted511
                    wrote on last edited by
                    #9

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

                    1 Reply Last reply
                    1
                    • mrjjM 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.

                      deleted511D Offline
                      deleted511D Offline
                      deleted511
                      wrote on last edited by
                      #10

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

                      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