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.8k 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.
  • 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 Online
                Christian EhrlicherC Online
                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 Online
                    Christian EhrlicherC Online
                    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 Online
                        JonBJ Online
                        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
                          • JonBJ JonB

                            @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 Offline
                            kshegunovK Offline
                            kshegunov
                            Moderators
                            wrote on last edited by
                            #21

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

                            It's great for getting a quick total of the apples

                            Yes, with some exceptions where a "quick total" isn't exactly quick ... ;)

                            Read and abide by the Qt Code of Conduct

                            JonBJ 1 Reply Last reply
                            0
                            • kshegunovK kshegunov

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

                              It's great for getting a quick total of the apples

                              Yes, with some exceptions where a "quick total" isn't exactly quick ... ;)

                              JonBJ Online
                              JonBJ Online
                              JonB
                              wrote on last edited by JonB
                              #22

                              @kshegunov
                              That's unfair! :) Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count? Give me any situation where COUNT() at server is not vastly quicker than code at server to iteratively count rows? Care to comment on whether SELECT COUNT(*) FROM table, with no WHERE condition like OP's example, allows provider to use huge optimization like it has already maintained total table row count and just returns that? Look, there's "quick" and there's "quick", this one is "quick" but I did not say "instantaneous" or "like lightning" :)

                              kshegunovK 1 Reply Last reply
                              0
                              • JonBJ JonB

                                @kshegunov
                                That's unfair! :) Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count? Give me any situation where COUNT() at server is not vastly quicker than code at server to iteratively count rows? Care to comment on whether SELECT COUNT(*) FROM table, with no WHERE condition like OP's example, allows provider to use huge optimization like it has already maintained total table row count and just returns that? Look, there's "quick" and there's "quick", this one is "quick" but I did not say "instantaneous" or "like lightning" :)

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

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

                                Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count?

                                That'd be all the situations where the server is running a InnoDB engine, for example, and the table is much larger than the resultset.

                                Give me any situation where COUNT() at server is not vastly quicker than code at server to iteratively count rows?

                                No such situation exists, that's why aggregates exist. Unless you have a stored procedure that does something else, that is, then the counting can be a "byproduct".

                                Care to comment on whether SELECT COUNT(*) FROM table, with no WHERE condition like OP's example, allows provider to use huge optimization like it has already maintained total table row count and just returns that?

                                See above. InnoDB doesn't have the real row count at hand. It can give you an approximate amount of rows based on the allocated pages quickly, but for a real row count time of execution is significant, compared to some other engines like (my)ISAM. Anyway, it's more complicated than to claim speed directly.

                                Look, there's "quick" and there's "quick"

                                Yes, we agree on that one.

                                Read and abide by the Qt Code of Conduct

                                JonBJ 1 Reply Last reply
                                0
                                • kshegunovK kshegunov

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

                                  Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count?

                                  That'd be all the situations where the server is running a InnoDB engine, for example, and the table is much larger than the resultset.

                                  Give me any situation where COUNT() at server is not vastly quicker than code at server to iteratively count rows?

                                  No such situation exists, that's why aggregates exist. Unless you have a stored procedure that does something else, that is, then the counting can be a "byproduct".

                                  Care to comment on whether SELECT COUNT(*) FROM table, with no WHERE condition like OP's example, allows provider to use huge optimization like it has already maintained total table row count and just returns that?

                                  See above. InnoDB doesn't have the real row count at hand. It can give you an approximate amount of rows based on the allocated pages quickly, but for a real row count time of execution is significant, compared to some other engines like (my)ISAM. Anyway, it's more complicated than to claim speed directly.

                                  Look, there's "quick" and there's "quick"

                                  Yes, we agree on that one.

                                  JonBJ Online
                                  JonBJ Online
                                  JonB
                                  wrote on last edited by JonB
                                  #24

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

                                  Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count?

                                  That'd be all the situations where the server is running a InnoDB engine, for example, and the table is much larger than the resultset.

                                  I admit I know nothing about InnoDB [I use MySQL, so I guess I use that?], so your points are fair enough. (For the avoidance of doubt, I am talking about where there is a database "server" involved.) But I don't get this one at all? How can it possibly be quicker to pass whatever all the results are (with or without a condition) to the client to count than to count them at the server side? If you're going to go:

                                  SELECT * FROM table WHERE smaller-resultset-condition
                                  

                                  and then send to count them at the client, why is that quicker than

                                  SELECT COUNT(*) FROM table WHERE smaller-resultset-condition
                                  

                                  ?

                                  kshegunovK 1 Reply Last reply
                                  0
                                  • JonBJ JonB

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

                                    Give me any situation where COUNT() at the server is not vastly quicker than passing row sets to client to count?

                                    That'd be all the situations where the server is running a InnoDB engine, for example, and the table is much larger than the resultset.

                                    I admit I know nothing about InnoDB [I use MySQL, so I guess I use that?], so your points are fair enough. (For the avoidance of doubt, I am talking about where there is a database "server" involved.) But I don't get this one at all? How can it possibly be quicker to pass whatever all the results are (with or without a condition) to the client to count than to count them at the server side? If you're going to go:

                                    SELECT * FROM table WHERE smaller-resultset-condition
                                    

                                    and then send to count them at the client, why is that quicker than

                                    SELECT COUNT(*) FROM table WHERE smaller-resultset-condition
                                    

                                    ?

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

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

                                    If you're going to go

                                    Because this is not what you usually do when dealing with mid-size to large datasets.
                                    Say for the sake of argument you count the number of rows to provide pagination of some sort, which is a typical case. You count, init the pages' count, current page number or w/e and then retrieve only a single small result set from the table. Right, so you count all relevant records just for the simple paging.
                                    Now, this is okay most of the time, at the end of the day that's why you have a database server (or engine, or w/e) - to provide storage. It gets a bit more involved when you have concurrent transactions over complex queries, however; i.e. you have snapshots all over the place.
                                    Say you count the rows of a query that has a nested query and altogether them queries span 8-10 tables over some JOINs. The count can get real expensive in these cases, especially if you consider why you do it to begin with - to fetch some meta information about the dataset and just provide some small part of it afterwards as actual data. That's what I referred to in my tease.

                                    Read and abide by the Qt Code of Conduct

                                    1 Reply Last reply
                                    1
                                    • F Offline
                                      F Offline
                                      fanxinglanyu
                                      wrote on last edited by
                                      #26

                                      I think you can use

                                       if(query.next()){//judge if has data
                                                          query.seek(-1);
                                      }
                                      
                                      1 Reply Last reply
                                      0

                                      • Login

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