So it came as a bit of surprise to me the other day when I was looking through the language reference and found that recordset.count does not work with Postgres DB’s. I was surprised because it’s been working for me for some time. That said I’m always cautious when things work that aren’t supposed to. So the question is what’s going on under the hood that allows record count to work or not work. If it’s a limitation of the DB, is it that Postgres has added some functionality that has allowed the call to recordset.count to start to work? I’ve been using v 9.x.x( currently 9.2.4 )
Can anyone else confirm their success/failure with this call. Also if it is working should I be worried that some future change to the Xojo framework will break this?
I don’t know why they have this limit, maybe for future use. I believe that Xojo Recordsets are just of the client side type, and only working as this, it’s just a kind of “array”, and so, they always should know the “Array” size (RecordCount) for ANY DB after populating it. The RecordCount with unknown size (value -1) could have sense only for server side recordsets.
As you, I avoid trying/using not documented things.
Unfortunately I’m already using it. When I added support for Postgres, in addition to Sqlite, I never found an issue with .count not working. It wasn’t until yesterday that I even noticed that it wasn’t “supported” for postgres. Now I’m in a bind as it would be a bit of a challenge to go through legacy code and add a workaround for something that’s working. Also, count is in some situations a ridiculously handy method to have. Making two calls to the server sucks.
I will probably upgrade my DB code to add another layer of abstraction wrapping the recordset code at some point, but I’m in later stages of beta right now working on a v1 release. Not what I want to do.
I think christian has a record set.clone option in his SQL plugin, I could use that to create a custom recordset.countcustom that would work for all db’s by just iterating through Record sets until EOF and returning the count, but I’d like a more elegant solution, since that would be my only use of that plugin.
Please, don’t. 
The fast record count is SELECT COUNT(1) as RECCOUNT FROM MYTABLE // WHERE my conditions
Yeah,
…Although it does require reaching out to the server again. I guess that’s not the worst issue in most situations since you’re only returning the count it’s a pretty small amount of data.
I’ll ping Xojo directly too and see if someone can weigh in on the reasons for the lack of blessing in .count for postgres. If it’s related to older versions of the DB then I feel okay going forward I guess in the mean time it makes sense to clean up the “for x to rs.count-1” statements and move then all to “while not rs.eof” … This project has spanned several years and there’s legacy code that certainly has some newbie mistakes in it.
thanks for your advise rick
Julian, if your objective is just replacing loops like “For i as integer = 1 to rs.RecordCount … Next”, I would extremely suggest you do what you told, substitute it by a “Do until rs.EOF … Loop”.
Yeah loops are no big deal. It’s nicer to have “for i as integer = 0 to rs.recordcount -1” when working with specific records, saves having to put in another i = i + 1, but no biggie.
The problem is when I go and get a lot of data from the server, and I need to instantiate some classes to receive that data, I sometimes need to know how many records I will be dealing with. In this case it’s nice to have .recordcount. Some of my select statements are quite complex so even just getting the count() of the returned records takes time at the DB level to run the search. Then I have to make another call to the database and get the actual data.
I use that call with PG since many years without issues. I think I just relied on the documentation which says: “For databases that do not support this function, RecordCount returns -1.” Probably they just forgot to add PG to the supported databases.
The problem with counting the record via looping through the recordset is that once you did that, you can’t use the recordset for anything else, as there is no way to go back to the beginning (.movefirst is not supported).
I just go a response from Xojo and they confirmed that it IS supported. That’s good news.