Calling Stored Procedures from 4GL

[Mike Lemon]

st...@nezsdc.fujitsu.co.nz (Steve Chell) wrote:
>I've been reading up about stored procedures recently, and can see how
>to use them with SQL. One thing that is not clear, however, is how to
>call them from 4GL programs. Is this possible using 4GL version 4.1x
>in conjunction with a 5.0x engine (Online or SE)? If so how?

Actually, I've been laboring for quite a while now under the assumption
that it wasn't, but I recently discovered I was wrong.

All you have to do is prepare and execute the statement. Put the normal
? parameter place-holders in the statement, and open/fetch the cursor if
there are return values. Works bloody good.

[Dennis Pimple]

Mike Lemon (Mike_...@mdl.pdial.interpath.net) wrote:

: st...@nezsdc.fujitsu.co.nz (Steve Chell) wrote:
: >I've been reading up about stored procedures recently, and can see how
: >to use them with SQL. One thing that is not clear, however, is how to
: >call them from 4GL programs. Is this possible using 4GL version 4.1x
: >in conjunction with a 5.0x engine (Online or SE)? If so how?

: All you have to do is prepare and execute the statement. Put the normal

: ? parameter place-holders in the statement, and open/fetch the cursor if
: there are return values. Works bloody good.

To clarify and kibitz:

PREPARE st_proc FROM "EXECUTE PROCEDURE whatever(?,?)"

If the SPL does *not* return anything:
EXECUTE st_proc USING var1, var2

If the SPL *does* return anything:
DECLARE c_proc CURSOR FOR st_proc
OPEN c_proc USING var1, var2

WHILE TRUE
FETCH c_proc INTO whatever
IF status = NOTFOUND THEN
EXIT WHILE
END IF
END WHILE

CLOSE c_proc
FREE c_proc

--

=======================================================================
Dennis J. Pimple den...@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.

[Andy Kent]

> st...@nezsdc.fujitsu.co.nz (Steve Chell) wrote:
> >I've been reading up about stored procedures recently, and can see how
> >to use them with SQL. One thing that is not clear, however, is how to
> >call them from 4GL programs. Is this possible using 4GL version 4.1x
> >in conjunction with a 5.0x engine (Online or SE)? If so how?
>

> Actually, I've been laboring for quite a while now under the assumption
> that it wasn't, but I recently discovered I was wrong.
>

> All you have to do is prepare and execute the statement. Put the
normal
> ? parameter place-holders in the statement, and open/fetch the cursor
if
> there are return values. Works bloody good.

Also worth noting that, used in conjunction with the RETURN...WITH RESUME
feature of SPL, you can use the cursor in the 4gl prog like any other
cursor and return multiple rows from the procedure.

ak...@cix.compulink.co.uk (Andy Kent)
------------------------------------------------
Freelance Informix Database Specialist,
Redland, Bristol, England