• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3744
  • Last Modified:

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.
0
sengchoon
Asked:
sengchoon
  • 4
  • 3
1 Solution
 
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
ComTechCommented:
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now