[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.


Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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 Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
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…
Suggested Courses

650 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