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. QSqlQuery::size() not returning actual number?
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery::size() not returning actual number?

Scheduled Pinned Locked Moved Solved General and Desktop
26 Posts 8 Posters 5.7k Views 5 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.
  • D Offline
    D Offline
    davecotter
    wrote on last edited by
    #1

    i have execute this select statement:

    "SELECT * FROM sqlite_master WHERE type = 'table' AND name = '[tablename]'"
    

    then i ask how many rows i got back (i want to know if this table exists)
    but size() returns -1.
    here's my workaround, which works, but WHY?

    int	Result::CountRows()
    {
    	int	resultI(0);
    
    	if (IsOk()) {
    
    		if (i_qquery.isSelect()) {
    		
    			if (i_qquery.isActive()) {
    				int	sizeI = i_qquery.size();
    				
    				if (sizeI >= 0) {
    					resultI = sizeI;
    					
    				} else {
    					i_qquery.last();
    			
    					sizeI = i_qquery.at();
    					
    					if (sizeI >= 0) {
    						resultI = sizeI + 1;
    					}
    				}
    
    			} else {
    				CF_ASSERT(0);
    			}
    		} else {
    			resultI = i_qquery.numRowsAffected();
    		}
    	}
    
    	return resultI;
    }
    
    JonBJ 1 Reply Last reply
    0
    • D Offline
      D Offline
      davecotter
      wrote on last edited by
      #20

      see @JonB 's answer above! thanks!

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

        Hi,

        I don't remember if it's still the case for SQLite but IIRC not all database engines support returning the query size. You should check that before going further.

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

        1 Reply Last reply
        4
        • D davecotter

          i have execute this select statement:

          "SELECT * FROM sqlite_master WHERE type = 'table' AND name = '[tablename]'"
          

          then i ask how many rows i got back (i want to know if this table exists)
          but size() returns -1.
          here's my workaround, which works, but WHY?

          int	Result::CountRows()
          {
          	int	resultI(0);
          
          	if (IsOk()) {
          
          		if (i_qquery.isSelect()) {
          		
          			if (i_qquery.isActive()) {
          				int	sizeI = i_qquery.size();
          				
          				if (sizeI >= 0) {
          					resultI = sizeI;
          					
          				} else {
          					i_qquery.last();
          			
          					sizeI = i_qquery.at();
          					
          					if (sizeI >= 0) {
          						resultI = sizeI + 1;
          					}
          				}
          
          			} else {
          				CF_ASSERT(0);
          			}
          		} else {
          			resultI = i_qquery.numRowsAffected();
          		}
          	}
          
          	return resultI;
          }
          
          JonBJ Offline
          JonBJ Offline
          JonB
          wrote on last edited by JonB
          #3

          @davecotter
          As @SGaist has just posted, size() returns -1 if the driver does not support returning the count of the result set.

          Assuming that's the case,

          then i ask how many rows i got back (i want to know if this table exists)

          "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '[tablename]'"
          

          will just return one row containing the number you want (I would assume either 0 or 1 from your query), you might prefer to use that?

          D 1 Reply Last reply
          5
          • SGaistS Offline
            SGaistS Offline
            SGaist
            Lifetime Qt Champion
            wrote on last edited by
            #4

            You can use QSqlDriver::hasFeature to check for that.

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

            1 Reply Last reply
            3
            • JonBJ JonB

              @davecotter
              As @SGaist has just posted, size() returns -1 if the driver does not support returning the count of the result set.

              Assuming that's the case,

              then i ask how many rows i got back (i want to know if this table exists)

              "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '[tablename]'"
              

              will just return one row containing the number you want (I would assume either 0 or 1 from your query), you might prefer to use that?

              D Offline
              D Offline
              davecotter
              wrote on last edited by
              #5

              @JonB I’m not sure I understand your reply, I already am using that select statement. My q is: what is the proper way to ask how many rows were returned?

              JonBJ 1 Reply Last reply
              0
              • Kent-DorfmanK Offline
                Kent-DorfmanK Offline
                Kent-Dorfman
                wrote on last edited by Kent-Dorfman
                #6

                As Gaist mentioned, getting the number of rows from a query response is a very haphazard process when you try to implement it for any ole generic database. MANY DBC backends cache returned rows in manageable groups and use something called a cursor to traverse through them. It is possible, and probable, that the real number of rows returned is far more than is in the currently cached response.

                I recommend you used the count() SQL aggregate function to find out how many rows would be returned from your query. It is always guaranteed to return a 1 row result. Looks like JonB also recommended this.

                Worst case is that you have to do two queries. First one to find out the size of your result set.

                D 1 Reply Last reply
                3
                • Kent-DorfmanK Kent-Dorfman

                  As Gaist mentioned, getting the number of rows from a query response is a very haphazard process when you try to implement it for any ole generic database. MANY DBC backends cache returned rows in manageable groups and use something called a cursor to traverse through them. It is possible, and probable, that the real number of rows returned is far more than is in the currently cached response.

                  I recommend you used the count() SQL aggregate function to find out how many rows would be returned from your query. It is always guaranteed to return a 1 row result. Looks like JonB also recommended this.

                  Worst case is that you have to do two queries. First one to find out the size of your result set.

                  D Offline
                  D Offline
                  davecotter
                  wrote on last edited by
                  #7

                  @Kent-Dorfman sorry if I wasn’t clear at the start, this is only SQLite and will never change

                  Kent-DorfmanK 1 Reply Last reply
                  0
                  • D davecotter

                    @Kent-Dorfman sorry if I wasn’t clear at the start, this is only SQLite and will never change

                    Kent-DorfmanK Offline
                    Kent-DorfmanK Offline
                    Kent-Dorfman
                    wrote on last edited by
                    #8

                    @davecotter said in QSqlQuery::size() not returning actual number?:

                    @Kent-Dorfman sorry if I wasn’t clear at the start, this is only SQLite and will never change

                    doesn't matter...using the count() method is the more universal way of doing it.

                    D 1 Reply Last reply
                    1
                    • Kent-DorfmanK Kent-Dorfman

                      @davecotter said in QSqlQuery::size() not returning actual number?:

                      @Kent-Dorfman sorry if I wasn’t clear at the start, this is only SQLite and will never change

                      doesn't matter...using the count() method is the more universal way of doing it.

                      D Offline
                      D Offline
                      davecotter
                      wrote on last edited by
                      #9

                      @Kent-Dorfman I’m not at all versed at this: is there example code showing how to use count()?

                      1 Reply Last reply
                      0
                      • Kent-DorfmanK Offline
                        Kent-DorfmanK Offline
                        Kent-Dorfman
                        wrote on last edited by Kent-Dorfman
                        #10

                        yes, plenty, but I have no resources off the top of my head. look at any Qt SQL examples you can find. The key thing is that your quere should be:

                        select count(*) as cnt from sqlite_master WHERE type = 'table' AND name = '[tablename]'
                        

                        and grab the cnt field from the response

                        D 1 Reply Last reply
                        3
                        • Kent-DorfmanK Kent-Dorfman

                          yes, plenty, but I have no resources off the top of my head. look at any Qt SQL examples you can find. The key thing is that your quere should be:

                          select count(*) as cnt from sqlite_master WHERE type = 'table' AND name = '[tablename]'
                          

                          and grab the cnt field from the response

                          D Offline
                          D Offline
                          davecotter
                          wrote on last edited by
                          #11

                          @Kent-Dorfman I’m trying to understand “grab the field from the response” ...

                          1 Reply Last reply
                          0
                          • Kent-DorfmanK Offline
                            Kent-DorfmanK Offline
                            Kent-Dorfman
                            wrote on last edited by
                            #12

                            a single field row is returned (called cnt). look at the examples and find one where you get the value of a returned field. there are a few ways to do this.

                            Executing your QSqlQuery will return a QResultSet object. The field can be accessed using boundValue(0) method of QResultSet. Ya gotta do some googling and research if you want any more detail than that.

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

                              hi
                              One way could be. ( note my table is called person. change to yours)

                              QSqlQuery query("select count(*) as cnt from sqlite_master where type='table' and name='person'");
                              if (query.next())
                                  qDebug() << query.value(0).toInt();
                              

                              However, in org post you said (i want to know if this table exists)
                              so just as a note
                              using SQLite version 3.3+
                              you can say
                              "create table if not exists TableName ..:"
                              if you want to check if table exits and create it if not.
                              Might not be your use case, but its good to know.

                              1 Reply Last reply
                              2
                              • D Offline
                                D Offline
                                davecotter
                                wrote on last edited by davecotter
                                #14

                                sorry, i guess i should have made my question more generic than "how many rows will be returned from this specific statement". what i should have asked is:

                                if i've already performed some statement, and i only have access to the QSqlQuery at this point and no longer know what the actual statement was, how do i ask how many rows that returns? I want it to work for any statement, not just one that asks if a table exists. is my solution just grossly inefficient? is there a better way than this:

                                  			i_qquery.last();
                                  	
                                  			sizeI = i_qquery.at();
                                  			
                                  			if (sizeI >= 0) {
                                  				resultI = sizeI + 1;
                                  			}
                                
                                1 Reply Last reply
                                0
                                • Christian EhrlicherC Offline
                                  Christian EhrlicherC Offline
                                  Christian Ehrlicher
                                  Lifetime Qt Champion
                                  wrote on last edited by
                                  #15

                                  As @SGaist already mentioned this only works (and unrelated from Qt) when the underlying database supports this. You can check it with QSqlDriver::hasFeature ...

                                  Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                                  Visit the Qt Academy at https://academy.qt.io/catalog

                                  D 1 Reply Last reply
                                  0
                                  • Christian EhrlicherC Christian Ehrlicher

                                    As @SGaist already mentioned this only works (and unrelated from Qt) when the underlying database supports this. You can check it with QSqlDriver::hasFeature ...

                                    D Offline
                                    D Offline
                                    davecotter
                                    wrote on last edited by
                                    #16

                                    yes we have determined that "size()" doesn't work on SQLite, i understand that.

                                    my question has evolved into: is the way i'm doing it now a bad / terribly inefficient way to do it?
                                    for reference, this is what i'm now doing:

                                    i_qquery.last();
                                    		
                                    sizeI = i_qquery.at();
                                    				
                                    if (sizeI >= 0) {
                                    	resultI = sizeI + 1;
                                    }
                                    

                                    thanks

                                    Kent-DorfmanK 1 Reply Last reply
                                    0
                                    • Christian EhrlicherC Offline
                                      Christian EhrlicherC Offline
                                      Christian Ehrlicher
                                      Lifetime Qt Champion
                                      wrote on last edited by
                                      #17

                                      It's not very efficient but when you really need the result size for whatever reason you've no other chance.

                                      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                                      Visit the Qt Academy at https://academy.qt.io/catalog

                                      1 Reply Last reply
                                      0
                                      • D davecotter

                                        yes we have determined that "size()" doesn't work on SQLite, i understand that.

                                        my question has evolved into: is the way i'm doing it now a bad / terribly inefficient way to do it?
                                        for reference, this is what i'm now doing:

                                        i_qquery.last();
                                        		
                                        sizeI = i_qquery.at();
                                        				
                                        if (sizeI >= 0) {
                                        	resultI = sizeI + 1;
                                        }
                                        

                                        thanks

                                        Kent-DorfmanK Offline
                                        Kent-DorfmanK Offline
                                        Kent-Dorfman
                                        wrote on last edited by
                                        #18

                                        @davecotter said in QSqlQuery::size() not returning actual number?:

                                        yes we have determined that "size()" doesn't work on SQLite, i understand that.

                                        my question has evolved into: is the way i'm doing it now a bad / terribly inefficient way to do it?

                                        That's already been answered as well...

                                        1 Reply Last reply
                                        0
                                        • D davecotter

                                          @JonB I’m not sure I understand your reply, I already am using that select statement. My q is: what is the proper way to ask how many rows were returned?

                                          JonBJ Offline
                                          JonBJ Offline
                                          JonB
                                          wrote on last edited by JonB
                                          #19

                                          @davecotter said in QSqlQuery::size() not returning actual number?:

                                          @JonB I’m not sure I understand your reply, I already am using that select statement. My q is: what is the proper way to ask how many rows were returned?

                                          No, you must look at what I wrote: it's SELECT COUNT(*) instead of SELECT *. And you retrieve that single result number as the first column in the one & only row returned as the result set, and you must know how to read stuff out of the result set else you couldn't be using queries for much.

                                          Let's just summarise something else:

                                          if i've already performed some statement, and i only have access to the QSqlQuery at this point and no longer know what the actual statement was, how do i ask how many rows that returns? I want it to work for any statement, not just one that asks if a table exists.

                                          Yes, if you have previously executed an arbitrary query and now wish to know how many rows it returned you have no choice but to use your method.

                                          is my solution just grossly inefficient? is there a better way than this:

                                          Well, yes, it is inefficient (though unavoidable if you want the apples for other purposes too). Think of this: the SQL server has a bunch of apples. You ask the server to send you all the apples, and you sit & count how many have arrived to get the answer. (It's not the counting per se which is slow, it's the having to send & receive all the apples in order to count them which is bad.) It's not perfect, is it? Let's hope it has not sent you a lot of apples to count!

                                          A much more efficient way is to ask the server to do the counting at its end, not send all the apples, but send a single number saying how many there are. That is what SELECT COUNT(...) does. It's great for getting a quick total of the apples, but quite useless if you want to look at each one for some other reason.

                                          kshegunovK 1 Reply Last reply
                                          5
                                          • D Offline
                                            D Offline
                                            davecotter
                                            wrote on last edited by
                                            #20

                                            see @JonB 's answer above! thanks!

                                            1 Reply Last reply
                                            2

                                            • Login

                                            • Login or register to search.
                                            • First post
                                              Last post
                                            0
                                            • Categories
                                            • Recent
                                            • Tags
                                            • Popular
                                            • Users
                                            • Groups
                                            • Search
                                            • Get Qt Extensions
                                            • Unsolved