Creating Recordset with a count

Databases
1

I have this statement: Select SECTOR, COUNT(*) from BUYLIST group by SECTOR

When run in a shell, mysql returns a listing of several Sectors, and a count for each sector. I need to create a recordset of this listing.

When I put this into a mysql statemement: Dim rstSectorCount = database.SqlSelect(“Select SECTOR, COUNT(*) from BUYLIST group by SECTOR”) I get a recordset with one record, and two fields.

How do I get the complete output into a recordset? Sectors may vary over time, so hard coding a count by sector may not work for long.

2

Are you calling MoveNext to get to the second record?

3

The following may work.

[code]SELECT SECTOR, COUNT(SECTOR) FROM BUYLIST GROUP BY SECTOR;

// Or maybe
SELECT ALL SECTOR, COUNT(*) FROM BUYLIST GROUP BY SECTOR;[/code]

4

How are you determining there is only one record? You should get one record for each unique value of sector.

5

@Tim Hare I put in a pause on the line AFTER the DIM statement… then I used the debug window to click on the recordset name and view the contents… it showed the two fields and one record.

6

@Syed Hassan Thanks, but neither of those lines made a change to the results.

7

The sample MySQL project in Xojo shows the same results as inside MySQL with following modifications in the “ShowDataButton.Action” event handler. Both Xojo v2014r2.1 on Windows 7 and MySQL show three records with a count of 1 for each team.

[code] If Not IsConnected Then
MsgBox(“Connect to the database first.”)
End If

DataList.DeleteAllRows

Dim sql As String
//sql = “SELECT * FROM Team”
sql = “SELECT NAME, COUNT(*) FROM test.team GROUP BY NAME;”

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF

  //DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, data.IdxField(3).StringValue, data.IdxField(4).StringValue)
  
  DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue)
  
  data.MoveNext
Wend
data.Close

End If[/code]

8

A recordset only shows the current record in the debugger
There may be hundreds
You need a loop
See the examples
http://documentation.xojo.com/index.php/RecordSet

9

Ok… Norman, you are correct… I was relying on what was in the debugger… I added some more code and found that it did read in more than one record…

Thanks everyone… guess it was not a ‘problem’ after all…

10

RecordSets are interesting since they COULD be only ever one record in memory and the database server is actually holding all the data and the client retrieves the rows one at a time from the server.
Or a bunch or retrieved in some bulk operation and you are shown one.
Since the underpinnings can / could vary the recordset in the debugger only shows “the current record”

11

why the xojo debugger doesnt show us the RecordCount property of a RecordSet ?
it show us the FieldCount but not RecordCount…
it would be soooo nice to have it when debugging !
along with moving next-forward in the recordset but this is another story…
soon the RecordCount property would be great.
thanks;

12

[quote=149257:@jean-yves pochez]why the xojo debugger doesnt show us the RecordCount property of a RecordSet ?
it show us the FieldCount but not RecordCount…
it would be soooo nice to have it when debugging !
along with moving next-forward in the recordset but this is another story…
soon the RecordCount property would be great.
thanks;[/quote]
Because some of the database engines don’t supply that info with a query. It’s quite expensive to calculate if there a lot of rows.

13

From the recordSet.recordCount documentation:

Currently only supported by these databases:
SQLiteDatabase
PostgreSQLDatabase
OracleDatabase
ODBCDatabase, although not all ODBC drivers implement this

So, below are the unsupported databases that don’t have this feature (according to Xojo documentation), but do support a COUNT function (although not as optimized as using @@ROWCOUNT):

MSSQLServerDatabase - supports the COUNT() query function
MySQLCommunityServer (includes mariaDb) - supports the COUNT() query function

As for ODBC, that you previously mentioned… that’s right. Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers; however, one way around this behaviour is to do a COUNT(*) in SQL instead.

Why couldn’t this be implemented under recordCount?

14

I don’t think it would be a good idea to try and implement count(*) under the hood. Aside from having to parse out the WHERE clause, I can just see some poor sap writing code like

for i = 1 to rs.RecordCount

and having it rerun the query every time through the loop.

15

[quote=149437:@Tim Hare]I don’t think it would be a good idea to try and implement count(*) under the hood. Aside from having to parse out the WHERE clause, I can just see some poor sap writing code like

for i = 1 to rs.RecordCount

and having it rerun the query every time through the loop.[/quote]

dim data as recordSet data = db.sqlSelect(sql)

Is the recordSet dynamic? I thought it was constructed such as the code above and retained until constructed against another/separate sqlSelect command. If that’s the case, isn’t the count established at that time only?

16

Perhaps I misunderstood you. For the databases that do not support RecordCount, you would have to do a separate SQLSelect to get the count. I thought you were suggesting that accessing RecordCount would do a SELECT COUNT() when you accessed it. I suppose if the SQLSelect itself did a COUNT() first and then the real sql to get the recordset, then it could store the count locally. But that’s still a lot of overhead on every select if you don’t really need the count.

17

On a really complex query running count just to get the count could get you hit by the DBA :stuck_out_tongue:

18

actually when I debug code that deals with database, I often add a line like
x = rs.recordcount
into my code, just to see the recordcount effectively, and see if I have the correct sql result
so I must do it quite often while debugging
it would be really nice to be able to see the recordcount in the recordset properties details
as much as we can see the fieldcount.
it’s not a matter of “takes 5 ms in each cycle to do it”
it’s a matter of “I must add a line to do it everytime”

if someone wants the details of the recordset properties, then the recordcount is a pertinent information
if not needed, then dont clic on the recordset to get it’s properties while debugging.