[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Informix stored procedure caller

Posted on 2001-09-13
Medium Priority
Last Modified: 2012-06-27
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.
Question by:sengchoon
  • 4
  • 3

Expert Comment

ID: 6860698
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.


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

Hope this gives you a starter.

Expert Comment

ID: 6860804
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!
LVL 54

Expert Comment

ID: 7284113

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.


Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 7284277
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.
LVL 54

Expert Comment

ID: 7284426
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...


Expert Comment

ID: 7284494
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.
LVL 54

Expert Comment

ID: 7284516
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


(And don't "shut up", we can use your comments ;-)

Accepted Solution

ComTech earned 0 total points
ID: 7315783
This question will be placed in PAQ and points refunded.

Best regards,
Community Support
Administrator @ EE

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question