Informix stored procedure caller

I'm writting a Store Procedure and run it into the database. But when comes to the 4gl file, how to run it
from there? How to call the Store Procedure from the 4GL file? The store procedure can be run in DBACCESS.
sengchoonAsked:
Who is Participating?
 
ComTechConnect With a Mentor Commented:
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0
 
imijltdCommented:
Here are a couple of ways:

The "prepare and execute" method for a procedure that doesn't return a result:

let lvSQL = "execute procedure MyProcedure(?,?)"
prepare eMyProcedure from lvSQL

execute eMyProcedure using lvVar1, lvVar2

Do your usual status catching etc around the execute.


The "prepare, declare and fetch/foreach" method for a procedure that returns one or more results:

let lvSQL = "execute procedure MyProcedure(?,?)"
prepare pMyProcedure from lvSQL
declare cMyProcedure cursor for pMyProcedure

open cMyProcedure using lvVar1, lvVar2
fetch cMyProcedure into lvResult
close cMyProcedure

Do your usual status catching etc around the fetch.

OR

foreach cMyProcedure using lvVar1, lvVar2 into lvResult
.
.
.
end foreach -- cMyProcedure

Hope this gives you a starter.
0
 
imijltdCommented:
Oops, hit the submit a little early, the really good stuff comes in IDS 7+:

let lvSQL = "select a.a, MyProcedure(a.b, a.c)"
    , " from a_table a"
    , " where a.blah = ?"
    , " and a.blah2 = MyProc2(?)"

prepare pSelSomething from lvSQL
declare cSelSomething cursor for pSelSomething

foreach cSelSomething using lvBlah, lvBlah2
into lvResult1, lvResult2
.
.
.
end foreach -- cSelSomething

Have fun!
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
imijltdCommented:
So, I give 4 clear examples that answer the question and don't get any points?

This will be the third time this has happend to me now.
0
 
nico5038Commented:
Hi imijltd,

I'm trying to cleanup these old Q's, not having specific knowledge of Informix and DBACCESS.
In such cases I try to use other expert comments and the askers comments to make a descision for a proposed answer.

I missed in your comment a reference to the implementation in the DBACCESS and without more Informix knowledge I can't judge how well your comment "fits".

I've experienced too many Q's where I thought my comment was OK, but prooved to be just not the answer needed, so I became a bit cautious.
Main objective for me is to have the comment PAQ'd so it's at least available for question buyers and don't forget that without my cleaning these Q's (this one being over a year old) won't generate any points either.

Finally, I'm just doing a recommandation and the real descision is upto the moderator that will finalize the Q when the asker doesn't show the decency to finalyze the Q as he/she should have done...

Nic;o)
0
 
imijltdCommented:
dbaccess is the standard tool that comes with the Informix RDBMS, and is used for general admin of the database and testing SQL.

What the question was asking was how to run a stored procedure from within a Informix 4GL program, which is a proprietry high level programming language for Informix databases. The question was just saying that the stored procedure was known to work through dbaccess, but they didn't know how to run the same procedure from 4GL.

I gave 4 examples of using stored procedures through 4GL, shame I won't get any expert points for this, as I'm very sure this answers the question very well (I've been using SPL with 4GL for quite some time with great success).

I'll shut up now, just had to have a little rant. Wasn't aimed at you, just annoyed that so many questions that have good comments or answers don't seem to get accepted and the points awarded correctly.
0
 
nico5038Commented:
I agree with you on this annoyance, I always hate it when there's no response from the asker on a Q.
But your explanation of the DBACCESS did convince me that your comment is really to the point so I'll change my recommandation for the moderator to:

 - answered by: imijltd

Nic;o)

(And don't "shut up", we can use your comments ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.