Solved

Informix stored procedure caller

Posted on 2001-09-13
8
3,517 Views
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.
0
Comment
Question by:sengchoon
  • 4
  • 3
8 Comments
 

Expert Comment

by:imijltd
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.

OR

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

Hope this gives you a starter.
0
 

Expert Comment

by:imijltd
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!
0
 
LVL 54

Expert Comment

by:nico5038
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Expert Comment

by:imijltd
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 54

Expert Comment

by:nico5038
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...

Nic;o)
0
 

Expert Comment

by:imijltd
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.
0
 
LVL 54

Expert Comment

by:nico5038
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

Nic;o)

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

Accepted Solution

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

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now