rowcount for sql resultset

[Dawn Wolthuis]

This is not mv-specific other than the syntax, but I just want to be
sure we are not missing something obvious. How do we get the rowcount
for a resultset, something like @selected?

Right now to determine if we have no rows in a resultset, we do the following:

sqlStatement = ...
sqlrs = "%ResultSet"->%New()
sqlrs->Prepare(sqlStatement)
sqlrs->Execute()
if NOT(sqlrs->Next()) then
* we have no rows from this query
...

Other than iterating through the entire resultset and counting it, we
are not seeing how to get the number of rows.

Thanks in advance. --dawn

--
Dawn M. Wolthuis

Take and give some delight today

[Dawn Wolthuis]

a) It was a dumb question, Dawn
b) No one here knows the answer
c) The answer is that there is no way to get the row count other than
iterating through the resultset
d) Other. Explain

Thanks in advance. --dawn

[Jason Warner]

Dawn,

I'm just getting back from vacation, but I'll take a swing at this. In
most SQL languages, there isn't really a way to get the number of rows
selected because they do the reads in chunks as needed. I'm not sure if
Cache is the same, but it seems to behave the same way. There are two
ways to get the number of rows:

1. You do a select count(1) from table where.... before you execute your
query. This requires two queries. One to get the count and then one to
get the actual data. Unfortunately, in cases where you are providing a
status bar, this can't be avoided.
2. If you don't need the number of rows selected before processing the
result set, just set a counter variable and increment it in a loop.

myCnt = 0

loop while sqlrs->Next()
myCnt++
repeat

crt myCnt : " row(s) selected."

This is from my experience with other SQL based databases. There may be
a way to set up the ResultSet to fetch all rows on execute and then you
can possibly get the number of rows selected, but I will be honest and
tell you I haven't done the research on that.

Jason

[Precy Wakayama]

Since, this syntax is similar to any object reference to a relational SQL, there should be a

sqlrs->%Count method to give you the number of items or rows returned after you Execute() the sqlStatement.  The return value of the %Count method should be an Integer.

Hope this helps...

precyw

 

--
You received this message because you are subscribed to the Google Groups "InterSystems:  MV Community" group.
To post to this group, send email to Cac...@googlegroups.com
To unsubscribe from this group, send email to CacheMV-u...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/CacheMV?hl=en

[Dawn Wolthuis]

Yes, there is a %RowCount property that does not seem to work, but
just looking that up again makes me think I have asked this question
before. I was thinking that somewhere under the covers of cmql it was
grabbing the rowcount from sql, so there might be some other trick
than to iterate through the resultset. In this case we can issue a
cmql statement instead of sql, thereby getting the count. I wanted to
make sure we were not missing the magic formula.

Thanks. --dawn

[Ed Clark]

Sadly, there's no magic :(
CMQL just does what you would have to do--executes the sql query, and then iterates through the result set . By the time you get the cmql query result, the iteration is complete. Even if you use the LISTCSV sample program to process the cmql results a line at a time, those results aren't created until after the sql has been iterated.

[Dawn Wolthuis]

Thanks Ed. --dawn