How to get RowCount on MySQL?

  1. 3 months ago

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

  2. Jean-Yves P

    Jan 20 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    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. Neil B

    Jan 20 Pre-Release Testers

    @HectorMarroquin I'm regretting not choosing PostgreSQL :(

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

  4. Jürg O

    Jan 20 Pre-Release Testers, Xojo Pro

    @HectorMarroquin Reading the documentation I found

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

    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:

    SELECT COUNT(*) As RowCount FROM TableName WHERE value1 = x AND value2 = y

  5. Thanks Jean-Yves and Jurg! That's what I was doing :)

    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. Norman P

    Jan 20 Pre-Release Testers, Xojo Pro outside enjoying the fresh air

    @Neil B I've never regretted switching to Postgres. I use it whenever possible.

    PostgreSQL is free for any use - commercial or free

    maria and mysql not so much

  8. Jean-Yves P

    Jan 20 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    @Norman P maria and mysql not so much

    anyway so many people use them !

  9. Norman P

    Jan 20 Pre-Release Testers, Xojo Pro outside enjoying the fresh air

    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

or Sign Up to reply!