Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Aug 16, 2018 11:20 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: How to find how many records in an ODBC recordset?
PostPosted: Sun Mar 10, 2013 5:01 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hello all,

The ODBC object does not appear to return a record count. How can I determine how many records are contained in a record set?

Thank you,
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Sun Mar 10, 2013 5:10 pm 
Offline
User avatar

Joined: Fri Sep 30, 2005 11:48 am
Posts: 3554
Location: Lenexa, KS
You may have to do a count(*) query first to get the record count.

_________________
Bob K.

A blog about being a Real Studio/Xojo developer at http://www.bkeeneybriefs.com


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Sun Mar 10, 2013 6:45 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hi Bob,

Thanks for your reply, will look into that.

Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 12:00 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
more precisely

SELECT count(*) as record_count FROM mytable

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 2:05 am 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Thank you Dave.

But neither of these will provide the record count returned from a query. These appear to provide the total record count in the table. Is this correct?
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 6:03 am 
Offline

Joined: Wed Mar 17, 2010 10:33 am
Posts: 258
Location: Sydney, Australia
as DaveS said;
Quote:
SELECT count(*) as record_count FROM mytable
but replace "myTable" with your query.
SELECT count(*) as record_count FROM (Select whatever);


I need this so often that I wrote a function that does the wrapping for me.

Regards
Denis


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 8:31 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
or query RS.RECORDCOUNT after you have executed your query

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 10:45 am 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
RecordCount is only supported for REALSQLdatabase and PostgresDatabase. For the others, if you need a count ahead of time, simply take your query and replace the field selection with count(*).

Select a, b, c from sometable where x = y and not z

Select count(*) from sometable where x = y and not z


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 10:49 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
Then you should also be able to say

SELECT a,b,c, count(*) as rec_count from sometable where x=y and not z

thus getting the recordset AND the count in ONE transaction

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to find how many records in an ODBC recordset?
PostPosted: Mon Mar 11, 2013 12:14 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
You can't mix regular columns and count that way, unless you are using group by. You could make it a subquery, though. It shouldn't add too much overhead, as the subquery should only be executed once and then grappved from cache for the second and subsequent rows. But you do have to repeat the WHERE condition in the subquery. It starts to get messy.

select a, b, c, (select count(*) from sometable where x = y and not z) as record_count from sometable where x = y and not z


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group