Subject Re: Stored Procedure
From Mervyn Bick <invalid@invalid.invalid>
Date Tue, 10 Jul 2018 10:47:56 +0200
Newsgroups dbase.getting-started

On 2018-07-09 1:52 PM, vari wrote:
> Thanks Mervyn but I must use BDE.
I normally use ADO to access Firebird 2.5 but I've just tried this using
standard database and query objects via the BDE and it works without a
problem.

If your stored procedure doesn't expect parameters using the SP name in
the select statement of a standard BDE query should work.

Where a SP expects parameters a parameter driven ADOquery object works
but I couldn't get this to work via the BDE.  What did work though was
to use a storedproc object.

Using the Firebird 2.5 Employee sample database.


d = new DATABASE()
d.databasename ='employee'
d.active = true

p = new STOREDPROC()
p.database = d
p.procedureName = "MAIL_LABEL"
p.active = true

p.params['CUST_NO'].value = 1008
p.requery()

do while not p.rowset.endofset
   for n = 1 to p.rowset.fields.size
      ?? p.rowset.fields[n].value
   next
   ?
  p.rowset.next()
enddo

p.active = false
d.active=false

It is important that the case of the value in the params assocArray
matches the case of the fieldname in the table.  As the fieldname in the
table is CUST_NO params['cust_no'] would give an error.

Try the following.

d= new Mydb()
p= new StoredProc()
p.database = d
p.procedureName = "prova_procedura"
p.active=true

As no parameter has been supplied no rowset will be returned.  To fetch
a rowset

p.params["columm"].value = 123 //or "ABC" if the column field is charcter.
p.requery()   //This is important. Without it the parameter won't be
passed to the SP.

Note. column must be the fieldname in the table and its case must match.
  If necessary you can set several parameters before the requery()

For a different returned value

p.params["columm"].value = 234
p.requery()

In your original message you said that you were only getting one record.
  The number of records returned depends on how the SP was written.  Any
records returned (whether 1 or many) will be held in the SP's rowset object.

You can list any rows returned using the following code.

do while not p.rowset.endofset
    for n = 1 to p.rowset.fields.size
       ?? p.rowset.fields[n].value
    next
    ?
    p.rowset.next()
enddo

Mervyn.