how to check the resultset contains records or not ?

[robert brown]

Hi All

In .NET after executing the SQL query we use rs.recordcount()>0 condition to check whether the resultset is empty or not.So In ALPHA FIVE how we can check whether the resultset contains records or not.

For example

t.execute("select * from empmaster where empno='007'")
rs=t.resultset

'now i have to perform certain operation if the above query returns value and have to do some other set of operation if the above query doesn't returns any value.

Hope my question is clear

Thank for any help
--Robert--

[JerryBrightbill]

There is a "RowCount" value for the result set, but it may be unreliable as not all platforms will return a value. However, when a result set is returned in AlphaDAO, the row pointer is not set. The result set function "NextRow()" will attempt to move the cursor to the next row, which initially is the first row row. If there is no first row, it returns false. If the result set has any values, it will always return true the first time it is run.

This will always work to check for any returned values. A typical code snippet would be as follows.

dim cn as sql::connection
dim FlagResult as l
dim SelectStatement as c 
SelectStatement ="SELECT * FROM Customers"
FlagResult = cn.open("::name::nw")
if FlagResult = .T.
	cn.PortableSQLEnabled = .t. 
	flagResult = cn.Execute(SelectStatement)
	if flagResult = .T.
		dim rs as sql::resultset
		rs = cn.ResultSet
		if rs.NextRow() = .T 
			' something was returned, so process the result set 
		else 
			' nothing was returned, so do something else 
		end if 
	end if 
	cn.close()
end if

[robert brown]

Thank you :)

[DOntquIT]

if FlagResult = .T.
cn.PortableSQLEnabled = .t.
flagResult = cn.Execute(SelectStatement)
if flagResult = .T.
dim rs as sql::resultset
rs = cn.ResultSet
if rs.NextRow() = .T
' something was returned, so process the result set
else
' nothing was returned, so do something else
end if
end if
cn.close()
end if
[/CODE]

Hi Jerry,
Im also facing the same problem like robert.After readin this post i quickly replaced my codes with rs.nextrow() , i found one draw back here.
lets say my sql query returns 10 records as my resultset.
after i check that whether record existing or not using rs.nextrow() function.Then when i perform While loop it loop through 9 records only since cursor already pointing through 2nd row.so i have to replace while rs.nextrow() with some alternate codings.

[JerryBrightbill]

The first time NextRow is run, you are left on the first record, so you can process the first row. Then you can use a while loop using NextRow() to process the rest of the rows after you process the first row.

Depending on what you want to do with the values, there are alternative ways to get data from a result set if you have a number of rows. Typically, you want to populate a list or array. You can use rs.ToString() to place the values as character values in a crlf() delimited list, or you can use rs.ToPropertyArray() to populate an array. Unlike most array functions, you don't need to know how many records exist as the function will build an array of the correct size.

[robert brown]

The first time NextRow is run, you are left on the first record, so you can process the first row. Then you can use a while loop using NextRow() to process the rest of the rows after you process the first row.

Depending on what you want to do with the values, there are alternative ways to get data from a result set if you have a number of rows. Typically, you want to populate a list or array. You can use rs.ToString() to place the values as character values in a crlf() delimited list, or you can use rs.ToPropertyArray() to populate an array. Unlike most array functions, you don't need to know how many records exist as the function will build an array of the correct size.

I tried this way as per your sugession but still when we use rs.nextrow()=.t. the pointer is moving to second record. No i cannot access the first record. All Alpha five Programmers in my company are in search of solution for the problem mentioned above.


Thanks
Robert--

[Selwyn Rabins]

the following script shows how you can loop over all of the records in a resultset without skipping the first record.

dim cn as sql::Connection
cn.open("::Name::sqlserver2012_northwind")
sql = "select customerId from customers where country = 'mexico'"

dim flag as l 
flag = cn.Execute(sql)
rs = cn.ResultSet


txt = rs.tostring()

showvar(txt,"" + line_count(txt))


flag = cn.Execute(sql)
rs = cn.ResultSet

'if there are no records, then flag will be .f. and the loop will not execute
flag = rs.nextRow()

txt = ""
while flag 
	txt = txt + rs.data(1) + crlf()
	flag = rs.nextRow()
end while 

showvar(txt,"" + line_count(txt))

[nlights]

I found that conn.resultset.CurrentRowIndex can be used to determine whether or not conn.Execute() found any rows. If not, CurrentRowIndex=0, if yes, CurrentRowIndex=1 right after the query is run. I have tested this on SQL Server 2008, and it works great every time.

I must say, though, that the documentation when it comes to this is misleading at best. The example given in the documentation shows how you can (supposedly) list returned row values using rs.NextRow(), but if you follow this example you always skip the first row! Responses by Alpha Software engineers to rs.NextRow() -related questions here on the forum claim that rs.NextRow() will find the first row in the resultset when it is run for the first time, but this is simply not true. Selwyn FINALLY clarified this in his above post, but the documentation remains wrong. I don't know, maybe SQL Server 2008 resultsets are somehow different when it comes to NextRow() behavior, but I kind of doubt it. Do the people writing A5 documentation bother to check whether or not the examples they give actually work? It took me a while scratching my head with this one until I finally realize that I've been mislead by the A5 documentation.

[Selwyn Rabins]

I found that conn.resultset.CurrentRowIndex can be used to determine whether or not conn.Execute() found any rows. If not, CurrentRowIndex=0, if yes, CurrentRowIndex=1 right after the query is run. I have tested this on SQL Server 2008, and it works great every time.

I must say, though, that the documentation when it comes to this is misleading at best. The example given in the documentation shows how you can (supposedly) list returned row values using rs.NextRow(), but if you follow this example you always skip the first row! Responses by Alpha Software engineers to rs.NextRow() -related questions here on the forum claim that rs.NextRow() will find the first row in the resultset when it is run for the first time, but this is simply not true. Selwyn FINALLY clarified this in his above post, but the documentation remains wrong. I don't know, maybe SQL Server 2008 resultsets are somehow different when it comes to NextRow() behavior, but I kind of doubt it. Do the people writing A5 documentation bother to check whether or not the examples they give actually work? It took me a while scratching my head with this one until I finally realize that I've been mislead by the A5 documentation.

conn.resultset.CurrentRowIndex IS NOT RELIABLE AND SHOULD NOT BE RELIED ON TO TEST IF A RESULTSET HAS DATA.

the value in this property will depend on the type of sql database you are connected to. for some backends, it might be populated, and for others it might not be.

the only way to check reliably if a result set has data is to call the .nextRow() method.


for example

dim cn as sql::connection 
flag = cn.open("::Name::sqlserver2012_northwind")
flag = cn.Execute("select * from customers where country = 'xx'")
?flag 
= .T.
dim rs as sql::ResultSet
rs = cn.ResultSet
?rs.nextrow()
= .F.

.nextRow() does NOT 'consume' the first row of data as you might think. so the following pattern will reliably return all of the data in the resultset

delete cn 
dim cn as sql::connection 
flag = cn.open("::Name::sqlserver2012_northwind")
flag = cn.Execute("select * from customers where country = 'france'")
dim rs as sql::ResultSet
rs = cn.ResultSet

flag = rs.nextrow()

dim cities as c 
cities = ""
while flag 
	cities = cities +rs.data("city") + crlf()
	flag = rs.nextrow()
end while

for a complete description of how to use sql tables with xbasic i suggest you read the excellent tutorial on the subject.
open the documentation viewer, and search for "Learning Xbasic - Using Xbasic with SQL Tables"

[nlights]

I saw that warning about CurrentRowIndex in the documentation, but since my SQL Server 2008 seems to reliably return the appropriate value, I figured I could use this, given that I use SQL Server 2008 exclusively for my data.

In my testing (again with SQL Server 2008) I see that the .nextRow() WILL skip the first result row every time.

If I do something like this:

qry = "select EI_ID FROM EVENTITEMS WHERE EI_ID = 2000"  'this PK ID exists in the table
conn.Execute(qry)
rs = conn.ResultSet
' now ?rs.data("EI_ID") would show 2000
success = rs.nextrow()
' now ?success would show .F., meaning that nextrow() actually DID skip the first result row

Maybe it is a SQL Server 2008 -specific thing, although that would be quite surprising to me...

The further problem here is that if the query doesn't return anything, testing for rs.data("EI_ID") before testing for .nextrow() will error out. So there is no real good way to simply see if a query returned anything. Hence the (desperate) grab for CurrentRowIndex.

conn.resultset.CurrentRowIndex IS NOT RELIABLE AND SHOULD NOT BE RELIED ON TO TEST IF A RESULTSET HAS DATA.

the value in this property will depend on the type of sql database you are connected to. for some backends, it might be populated, and for others it might not be.

the only way to check reliably if a result set has data is to call the .nextRow() method.


for example

dim cn as sql::connection 
flag = cn.open("::Name::sqlserver2012_northwind")
flag = cn.Execute("select * from customers where country = 'xx'")
?flag 
= .T.
dim rs as sql::ResultSet
rs = cn.ResultSet
?rs.nextrow()
= .F.

.nextRow() does NOT 'consume' the first row of data as you might think. so the following pattern will reliably return all of the data in the resultset

delete cn 
dim cn as sql::connection 
flag = cn.open("::Name::sqlserver2012_northwind")
flag = cn.Execute("select * from customers where country = 'france'")
dim rs as sql::ResultSet
rs = cn.ResultSet

flag = rs.nextrow()

dim cities as c 
cities = ""
while flag 
	cities = cities +rs.data("city") + crlf()
	flag = rs.nextrow()
end while

for a complete description of how to use sql tables with xbasic i suggest you read the excellent tutorial on the subject.
open the documentation viewer, and search for "Learning Xbasic - Using Xbasic with SQL Tables"

[Steve Wood]

Selwyn,

It seems whether rs.nextrow() "consumes" the first record or not, depends on how you use the result set after you first fire rs.nextrow(). See the two scripts below (client_id's are 10000, 10001, 10002, etc.)

This script is the same as your example and it does show that the first use of rs.nextrow() leaves the cursor on record 1.

dim cn as sql::Connection
dim rs as sql::ResultSet
cn.open("::name::conn")
vSql = "SELECT * FROM client"
cn.execute(vSql)
rs = cn.resultset
flag = rs.nextrow()
?rs.data("client_id")
= 10000
dim client as c = ""
while flag
client = client + rs.data("client_id") + crlf()
flag = rs.nextrow()
end while
?client
= 10000
10001
10002

But this script, which does not use a while loop, shows that the first time rs.nextrow() is fired the cursor is on the SECOND record.

dim cn as sql::Connection
dim rs as sql::ResultSet
cn.open("::name::conn")
vSql = "SELECT * FROM client"
cn.execute(vSql)
rs = cn.resultset
?rs.data("client_id")
= 10000
rs.nextrow()
?rs.data("client_id")
= 10001

Note: it did not matter if I used "flag=rs.nextrow()" or just "rs.nextrow()" wherever either were used.


Edit: Adding this script below. I note that this script does leave the cursor on record 1 after rs.nextrow() is fired, and that f1 below contains the full resultset, starting with record 1. This makes it seem like my script 2 on this page is an exception to the rule, or I am doing something wrong. Clearly scripts 1 and 3 leave the cursor on record 1, while script 2 leaves the cursor on record 2.

delete rs
dim cn as sql::Connection
dim rs as sql::ResultSet
cn.open("::name::conn")
vSql = "SELECT * FROM client"
cn.execute(vSql)
rs = cn.ResultSet
rs.nextrow()
f1 = rs.tostring()
?f1
= 10000
10001
10002

[nlights]

Interesting... Maybe my situation is a bit unique: I was simply trying to find out if a query returned 0 or 1+ rows. So, first I executed the query. At that point I was wondering if anything was returned. Testing the zero return case, I queried for a PK value that doesn't exist in the table. I couldn't evaluate rs.data('EI_ID') because since there are no results that evaluation throws an error (I suppose I could find out if rs.data("EI_ID") exists, and base my decision on that -- if not, there were no result rows, if yes, there was at least one.) I can't use nextrow() because that doesn't really tell me if I found just one record. Apparently, I shouldn't rely on the CurrentRowIndex either, at least if I want to keep my code backend-agnostic, which is generally a good idea.

dBase/Foxpro has the system variable _TALLY that contains the number of rows returned by the most recent query, and it is a breeze to work with (takes less than a minute to understand and deploy.) Is there a variable I can use in the resultset that reliably tells me the number of rows in that set? I couldn't find any.

Thanks.

[nlights]

OK, finally figured it out, and thought I would share the love with those who care

As it turns out, .resultset.CurrentRowIndex is highly unreliable indeed. Sometimes it works, sometimes not. It seems that in ajaxCallBack launched from some events, such as onMouseOver, .resultset.CurrentRowIndex may or may not be accurate. What makes things even harder is that it is always accurate if the debugger is invoked, but if the debugger doesn't get focus until .resultset.CurrentRowIndex is evaluated, all bets are off. The same goes for .resultset.CallResult.Code -- if debugger is invoked, the code shoes 103 (= set has no rows) on an empty set and 0 (=success - set has row(s)) on a non-empty one. If the debugger is not invoked, however, both empty and non-empty sets produce code 0. Talk about aggravating discrepancies and difficult debugging!

The most reliable way (at least that I've found) to determine whether or not there are 0 or 1+ result rows is by examining .resultset.data(1). If NO results were returned, .resultset.data(1) is unknown/not present. Using EVAL_VALID("conn.resultset.data(1)") will figure out reliably whether or not the query returned any results -- with or without debugger invokation. The code looks something like this:

	
	qry = "select * from EventItems where EI_ID = "+vcEI_ID
        vFound = .F.

	if conn.Execute(qry)
	   vFound = EVAL_VALID("conn.resultset.data(1)")   'Returns .F. if query returned nothing, .T. if it returned at least one row
       end if
...
...
...

This also bypasses one potential problem with some of the above code suggestions: If the query doesn't return anything, the statement conn.resultset.data("ID") will definitely crash the program because of the implicit assumption that there will always be at least one row in the resultset. Yeah, it's the outliers that get you almost every time...

Using this hard earned knowledge I have now created a simple global getTally(vrsResultSet) -function that returns the number of rows in the resultset, including zero rows. It sort of mimics the very useful dBase/FoxPro _TALLY -global variable.

[aburningflame]

If you need it before records process, execute a SELECT COUNT(*) (and the rest of hte sql statement)

If you need the record count after you process records, just do this:

dim records_found as N=0
while conn.ResultSet.NextRow()
       'do some processing
        records_found=records_found+1
end while


if records_found==0 then
    ?"NO RECORDS WERE FOUND"
end if

[mcohen]

I wasted a huge amount of time trying to solve this issue, including wondering why it only worked with debug activated Then I came across your solution nlights - many thanks!