dBASE SQL Servers - Reading multiple rowsets returned by a stored procedure

Author

Reading multiple rowsets returned by a stored procedure

Anwar

2006-04-04, 8:23 pm

Hi,

Using DBASE SE 2.0, INTERBASE WI-V6.0.1.0, BDE 5.2.0.2

I am trying to find a way to read multiple rowsets returned by a Interbase/Firebird stored procedure

I want to execute the stored procedure by calling thru the StoredProc() class and writing the values into an array or to a query rowset.

A stored procedure similar to the one below:

----------------------------------------------------------------------
SET TERM ^
CREATE PROCEDURE PRUSPE
RETURNS
(
REMARKS VARCHAR(40)
)
AS
BEGIN
FOR SELECT REMARKS FROM USPEDET INTO :REMARKS
DO SUSPEND ;
END ^

----------------------------------------------------------------------

Executing "SELECT * FROM PRUSPE" on the ISQL returns all the rows in the Interbase ISQL window.

Executing "SELECT COUNT(*) FROM PRUSPE" on ISQL returns the number of records

executing the following in dBase returns only the first row value
-----------------------------------------------------------------------------------------
p = new StoredProc()
p.database = cdata
p.procedureName = "PRUSPE"
p.execute()

? p.rowset.count() <--- returns 0
? p.params["REMARKS"].value <---- returns the first row value

Is there any way to loop through each of the row value and read the values ?

I cannot seem to work out the dBL code how to get the each
of the returned values into an array

Is it possible at all in dBase to read multiple rowsets returned by a stored procedure (if mutiple rowsets can be returned) ?

Can anyone show me the code if it is possible ?

Thanks,
Anwar

*Lysander*

2006-04-05, 3:24 am

Anwar schrieb:

> Is it possible at all in dBase to read multiple rowsets returned by a stored procedure (if mutiple rowsets can be returned) ?

and _how_ they can... you would be surprised :)

I started with Interbase, but am now using Firebird already for a long
time. But the basics should still the same.

> p.procedureName = "PRUSPE"
> p.execute()

p.execute() is only meant for executing a procedure on the server.
Not all servers allow multiple select from stored procedures.

instead try:
> p.procedureName = "PRUSPE"
> p.active = true

this _should_ fill the RowSet-object of the StoredProc-Object with all
records delivered back by the serverside stored procedure.

form.editor1 = new editor()
form.editor1.value = "returned by procedure:"+chr(13)
form.editor1.value += " ====================
=="+chr(13)
p.rowset.first()
do
form.editor1.value += p.rowset.fields[1].value+chr(13)
until not p.rowset.next()

I am using selectable stored procs very intensely with Firebird.
You can take a look here:

http://dbdach.sh-ekd.de/cgi-bin/dbdach.dbw

don't be disturbed by the German language...

what you see is a _dynamically_ created list of topics for our annual
conference. This list is fed by a "multiple select" stored procedure of
a Firebird table.
The website is created using _dBASE_ (!)

Whenever we enter a new topic, or change an already existing topic, the
stored proc will deliver back the new list.
The dbase program on the website calls this stored procedure evertime
you reload the website.

ciao,
André

Ivar B. Jessen

2006-04-05, 3:24 am

On Tue, 04 Apr 2006 16:21:01 -0400, in dbase.sql-servers,
Subject: Reading multiple rowsets returned by a stored procedure,
Message-ID: <UoMnKVCWGHA.416@news-server>,
Anwar <anwarhu@hotmail.com> wrote:

>Using DBASE SE 2.0, INTERBASE WI-V6.0.1.0, BDE 5.2.0.2
>
>I am trying to find a way to read multiple rowsets returned by a Interbase/Firebird stored procedure
>
>I want to execute the stored procedure by calling thru the StoredProc() class and writing the values into an array or to a query rowset.
>
>A stored procedure similar to the one below:
>
>----------------------------------------------------------------------
>SET TERM ^
>CREATE PROCEDURE PRUSPE
>RETURNS
>(
> REMARKS VARCHAR(40)
> )
>AS
>BEGIN
>FOR SELECT REMARKS FROM USPEDET INTO :REMARKS
>DO SUSPEND ;
>END ^
>
>----------------------------------------------------------------------
>
>Executing "SELECT * FROM PRUSPE" on the ISQL returns all the rows in the Interbase ISQL window.
>
>Executing "SELECT COUNT(*) FROM PRUSPE" on ISQL returns the number of records
>
>executing the following in dBase returns only the first row value
>-----------------------------------------------------------------------------------------
>p = new StoredProc()
>p.database = cdata
>p.procedureName = "PRUSPE"
>p.execute()
>
>? p.rowset.count() <--- returns 0
>? p.params["REMARKS"].value <---- returns the first row value
>
>Is there any way to loop through each of the row value and read the values ?
>
>I cannot seem to work out the dBL code how to get the each
>of the returned values into an array
>
>Is it possible at all in dBase to read multiple rowsets returned by a stored procedure (if mutiple rowsets can be returned) ?
>
>Can anyone show me the code if it is possible ?

I don't know about returning multiple _rowsets_, but you can return multiple
_rows_ from a stored procedure in a Firebird table as demonstrated in the code
below.

The table is the Fish table from the Samples directory copied to a Firebird
database named "FDBFish".

The stored procedure is the following,

begin
/*
Name of procedure
GET_NAME_FROM_ID
retrieves names for given ID
*/
FOR SELECT "NAME"
FROM "FISH"
WHERE ID > :ID
INTO :NAME
DO
suspend;
end

There are 10 rows in the table with ID's from 1 to 10.

In the code below the parameter ID is set to 7 and the code returns the
corresponding 3 rows in the rowset named 'p.rowset' having a single column
'Name' containg the Name values.

See also the example in the OLH on 'class Stored Procedure' or OLH on 'type
[Parameter]' for more information on use of the 'type' value.

Ivar B. Jessen

//-----
d = new Database()
d.databaseName = "FDBFish"
d.active = true

p = new StoredProc()
p.database = d
p.procedureName = "GET_NAME_FROM_ID"
p.params[ "ID" ].type = 0
p.params[ "ID" ].value = 7
p.active = true

?
p.rowset.first()
do while not p.rowset.endofset
?? ""+p.rowset.fields["Name"].value,
?
p.rowset.next()
enddo

p.active = false
d.active = false
//-----

Sponsored Links