getting resultset from sp within a macro

[mid181]

hi
Am new to cognos Macros ...
currently i am writing a macro which calls a sybase sp(there are no input or output parameters ) .. the calling and executing part is fine .. but i would like to know how to manipulate the resultset which i get from my sp..
It's a simple sp .. which returns some rows .. i want to access these rows within the macro...
thanks in advance

 

[goatnorm]

Have you tried pulling each row in as an element of an array?

 

[griffindm]

mid181,

Don't know of any way to do this directly. I would suggest rewriting the sp to put the output into a table, then run the sp from the macro with the SQLExec command, and querying this with the SQLRetrieve command to put the data into a macro array.

Regards,

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[fivenine]

Read help....

 

[mid181]

friends thanks for ur contribution..
here's the complete code for an sp (with parameter) called within a macro. The macro will loop thru the entire resultset returned by the sp:

Sub main
' Declarations
'
Dim connection As Long
Dim destination(1 To 50, 1 To 125) As Variant
Dim retcode As long
Dim strParam as String

' open the connection
connection = SQLOpen("DSN=xyz; UID=a; PWD=b&quot

' Execute the query
strParam = "'A%'" /* defining the parameter to the sp*/
query = "exec test " & strParam /*calling the sp with the parameter*/


retcode = SQLExecQuery(connection,query)
retcode = SQLRetrieve(connection:=connection,destination:=destination, columnNames:=0,rowNumbers:=0,maxRows:=10, maxColumns:=2, _
fetchFirst:=0)
msgbox "retcode = " & retcode /* retcode has the no of rows returned - at a time fetching 10 rows*/


do while retcode <> 0
For i = 1 to retcode
for j = 1 to 2
msgbox &quot;Row no &quot; & i & &quot; : &quot; & destination(j,i)
next j
next i
retcode = SQLRetrieve(connection:=connection,destination:=destination, columnNames:=0,rowNumbers:=0,maxRows:=10, maxColumns:=2, _
fetchFirst:=0)
loop

' Close the connection
retcode = SQLClose(connection)
End Sub