.NextResult()

[Pat Bremkamp]

Jerry,

Can you show how to use .NextResult() ? I need to iterate through result sets to get to the last one, but don't know how many there will be. This seems like the answer, but I can't get the syntax right.

Pat

[coleresources]

Re: .NextResult()

Can you show use your code?

[Pat Bremkamp]

Re: .NextResult()

To put this in context, this is what I'm after:
Capture.PNG
Column at left is what the analyst has entered, column at right is what will print on the report. In this project, there are 301 lines of various types. What I want to do is display a message box that shows how many lines were calculated.

An action button calls the following Alpha code:
vCall = "CALL calc_lines_display_values(:argJobno, @rslt @kount)"
exe_OK = cnx.execute(vCall,args)
crs = cnx.ResultSet

if crs.NextRow() = .f. 'there were no results
vMsgTitle = "ERROR"
vMsgText = "No results in processing display values"
goto PLErrorOut
end if

while crs.NextResult() 'check for more result sets
if crs.NextResult() = .t.
crs=cnx.ResultSet
end if
end while

vPLResult = crs.data(1)
vPLineCount = round(crs.data(2),0)

The calc_lines_display_values routine loops through all the lines and calls other routines that return the display values for various type of lines

This routine processes a total of 191 lines, and the first result set from a sub-routine is 'OK', 3.9, .15, '#22 | 3.9mm | .15 in'

But, the vPLineCount ends up with a value of 4 instead of the 191 I want.

[rogerrabbit222]

Re: .NextResult()

Hey Pat,

You could try using .NextRow() with a WHILE LOOP

sqlstr = "SELECT * FROM EMPLOYEE"
cn.execute(sqlstr)
rs = cn.ResultSet

dim recCnt as n = 0
while rs.nextrow()
recCnt = recCnt + 1
end while

if recCnt = 0 then
? "NO RECORDS FOUND"
else
? recCnt + " records FOUND"
end if

[Pat Bremkamp]

Re: .NextResult()

Thanks, KayBee, but that's my point. The problem is not rows in a result set, it is multiple result sets. I need to iterate through the result sets to get to the last one, then I can read the row value in that set.

[rogerrabbit222]

Re: .NextResult()

You can always do nested loops.

[[email protected]]

Re: .NextResult()

To put this in context, this is what I'm after:
[ATTACH=CONFIG]38467[/ATTACH]
Column at left is what the analyst has entered, column at right is what will print on the report. In this project, there are 301 lines of various types. What I want to do is display a message box that shows how many lines were calculated.

An action button calls the following Alpha code:
vCall = "CALL calc_lines_display_values(:argJobno, @rslt @kount)"
exe_OK = cnx.execute(vCall,args)
crs = cnx.ResultSet

if crs.NextRow() = .f. 'there were no results
vMsgTitle = "ERROR"
vMsgText = "No results in processing display values"
goto PLErrorOut
end if

while crs.NextResult() 'check for more result sets
if crs.NextResult() = .t.
crs=cnx.ResultSet
end if
end while

vPLResult = crs.data(1)
vPLineCount = round(crs.data(2),0)

The calc_lines_display_values routine loops through all the lines and calls other routines that return the display values for various type of lines

This routine processes a total of 191 lines, and the first result set from a sub-routine is 'OK', 3.9, .15, '#22 | 3.9mm | .15 in'

But, the vPLineCount ends up with a value of 4 instead of the 191 I want.

use a flag to indicate if there are more results and use that in the while loop
dim flagresult as l
flagresult = crs.NextResult()
while flagresult
do what you want to do
flagresult = crs.NextResult()
end while

hth

Pieter

[mumfie]

Re: .NextResult()

Thanks, KayBee, but that's my point. The problem is not rows in a result set, it is multiple result sets. I need to iterate through the result sets to get to the last one, then I can read the row value in that set.

The only example I have seen was from the v12/v10 alpha release notes in a discussion about AlphaDAO - SQL Server - Stored Procedures - Output Arguments
The following is an extract of example from the release notes

Now, From the interactive window:



dim cn as sql::Connection
cn.open("::Name::sqlserver")
delete args
dim args as sql::Arguments
dim Message as c = "test message"

'need to pad out the argument so it matches the definition in the stored procedure
message = padr(Message,50," ")
args.set("InParam" , "1234567890")
args.set("OutMessage",Message,sql::ArgumentUsage::InputOutputArgument)
?cn.Execute("{CALL TestSP(:InParam, :OutMessage)}",args)
= .T.




Now try to read the output parameter value

?args[2].data
= "test message"


This is not what we expect!. The output parameter cannot yet be read because we have not yet looped through all of the result sets returned by the stored procedure.



So, execute:

?cn.ResultSet.NextResult()

= .t.

and again:

?cn.ResultSet.NextResult()

= .t.

and again (the .f. return value tells you that there are no more resultsets):

?cn.ResultSet.NextResult()

= .f.



Now, that there are no more resultsets, you can read the output arguments:

?args[2].data

= "initialized"

[shinra]

Re: .NextResult()

I know this has been while but did you ever figure this out? I'm having the same issue.

[Pat Bremkamp]

Re: .NextResult()

No, I ended up writing the results to a temporary table and taking the last entry. I talked to Jerry and he said he never uses it. So, it probably doesn't work.