How to get RowCount on MySQL?

Databases
1

Hello!

Reading the documentation I found that .RowCount is not supported for MySQL. Is there a way to get the number of records without actually having to count them one by one with a FOR… NEXT loop?

I’m regretting not choosing PostgreSQL :frowning:

2

in API 1 :

rs = mydatabase.sqlselect( "select count(*) from mytable") rowcount = rs.idxfield(1).integervalue

works for all databases
easy exercice to translate it to API 2

3

I’ve never regretted switching to Postgres. I use it whenever possible.

4

Xojo Docs - Getting Started: Working with Databases
How do I get the number of rows in a RecordSet?

[quote]Alternatively, you can create a SQL SELECT statement to return the number of rows in a query. This is done using the COUNT method. The exact syntax varies by database, but is usually something like this:
[/quote]SELECT COUNT(*) As RowCount FROM TableName WHERE value1 = x AND value2 = y

5

Thanks Jean-Yves and Jurg! That’s what I was doing :slight_smile:

I was thinking hoping for some super secret Xojo method LOL

6

Actually the hope was for something that didn’t required a new trip to the server. You know what I mean.

7

PostgreSQL is free for any use - commercial or free

maria and mysql not so much

8

anyway so many people use them !

9

and oracle knows this
just wait until they come to you asking for money

that said rewriting a query to get the count at the same time you get the actual results is one option
its not always possible since it may affect other aggregates with group by etc

so sometime running the query twice is the only opton

or just NOT writing your code / UI to show something that can be a pain or expensive to get

10

In this case I do need to get the data so no chance of not having to deal with the pain of the latency. I’m working on a Web App so the purpose of getting the record count is to determine if the data is to be displayed on a WebListBox or create an HTML page to display the data, there are a few cases in which up to 3,000 records need to be read and not only does this take time to process but the WebListBox would most probably be turned useless with that many rows.

Thanks for the input!

11

Hello Hector

windows function!

Select field1, fiel2, field3, count(*) OVER() As row_count
From MyTable
where field1= ‘1760’

Regards
Mauricio

12

Thank you Mauricio

13

[quote=472008:@Jean-Yves Pochez]in API 1 :

rs = mydatabase.sqlselect( "select count(*) from mytable") rowcount = rs.idxfield(1).integervalue

works for all databases
easy exercice to translate it to API 2[/quote]
Thanks. The only problem I see is that I have to do two queries, one for checking if there are rows and another to actually get the data. In API 1 you had if rs.EOF then. Hopefully, something will be added.

14

Rowset has AfterLastRow instead of EOF - but the meaning is the same
see http://documentation.xojo.com/api/databases/rowset.html

15

[quote=490986:@Norman Palardy]Rowset has AfterLastRow instead of EOF - but the meaning is the same
see http://documentation.xojo.com/api/databases/rowset.html[/quote]

Thanks! I got this figured out. At the time I asked the question I was recently returning to Xojo and was still thinking in the “old” ways.

Nice to see you are back!