MS Sql row count confusion.

Databases
1

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.

Thanks

2

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)

3

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(*).

4

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?

5

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

6

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

7

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.

8

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?

9

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.

10

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

12

OK that worked. Why does it work though?

13

Please test :

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

14

Console now states:
Total records found: 5485114

DebugView shows:
[12428] 5485114record count

Why does it work that way?

15

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

17

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.