MS Sql row count confusion.

I’m trying to get a row count out of a recordset so that I can make sure I have actually data to work with. The table is fed from another source and sometimes they have problems updating the table, hence I need to check I have something there before I continue with the data.

The server is SQL Server 2012. I can get the connection without any issues. I have the following code:

Dim rec_count as Int64
Dim rs2 As RecordSet
rs2 = mDb.SQLSelect(“SELECT COUNT_BIG(*) AS recordcount FROM Product_Local;”)
rec_count = rs2.Field(“recordcount”).Int64Value

When I run “SELECT COUNT_BIG(*) AS recordcount FROM Product_Local;” in Microsoft SQL Server Management Studio I get the following result:
|___| recordcount|
| 1 | 5485114 |

According to Microsoft, COUNT and COUNT_BIG return the value as bigint.
I has assumed I could read that field just like any other recordset but when I try get a NilObjectException but I appear to be missing something. I have tried COUNT with the same results.


are you saying RS2 is NIL?
if so that indicates the SQLQuery is not valid (not that it didn’t return a result, but that it COULDN’T)
perhaps the database plugin doesn’t recognize COUNT_BIG? (oh… I see you tried just COUNT)

That how is how I am reading the error, that the recordset is NIL or at least the field “recordcount” is NIL.

I have another recordset based off the same connection and I’m getting a correct FieldCount, so I know that part works.

And correct, I get the same results with COUNT(*).

if RS2 is nil the Query is not valid… if the RESULTS of the query is NIL that is another issue.
What database error is being returned?

What I think I’m seeing is that results of the query is NIL.

I don’t get an error, in fact I have a catch setup if there was an error.

The variables view shows:
Name Value
args String(0)
Exception NilObjectException
mDb MSSQLServerDatabase
rec_count 0
Return 0
rs RecordSet
rs2 Nil

that is strange… as a COUNT(*) should always return a non-nil value even if the table in question is empty, and if the table didn’t exist you would get an error

That is what my thought was as well. From reading around the forums, I thought this was the only way to get record counts from MSSQL into Xojo.

Yet there was one.
What does that catch code look like?

two questions:
Is the table actually called Product_Local
Does it work if you remove the ; character, since that may be causing the database to begin a new statement?

The catch code is as follows:

If mDb.Error Then
StdOut.WriteLine("Error: " + mDb.ErrorMessage)
Return 1
End If

The table is called Product_Local and I tried it with the “;” and without. Works either way.

Can you do an error catch on a recordset? Like if rs2.error then…

OK that worked. Why does it work though?

Please test :

System.DebugLog(str(rs2.Field(“recordcount”).Value) + " record count")

Console now states:
Total records found: 5485114

DebugView shows:
[12428] 5485114record count

Why does it work that way?

Solution ?

Dim rec_count as Integer
Dim rs2 As RecordSet
rs2 = mDb.SQLSelect(“SELECT COUNT_BIG(*) AS recordcount FROM Product_Local;”)
rec_count = rs2.Field(“recordcount”).Value

I put:
System.DebugLog(str(rs2.Field(“recordcount”).Value) + “record count”)

and the program gave me the results.
I then commented out “System.DebugLog(str(rs2.Field(“recordcount”).Value) + “record count”)” and it still runs correctly now.

I don’t get it.