• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

E1 application or BSFN calling DB2 stored procedure on iSeries

mirceag

Member
I am trying to find a way to call a DB2 stored procedures from an E1 BSFN passing input parameters and consuming multiple return parameters grouped in one record set (multiple records)
E1 910 residing on a AIX Unix Box
DB2 Stored procedure residing on iSeries box
Does anyone have any experience with such call ? Is it doable ?
I am thinking there' s got to be a way to massage returned records into a cache and manipulate that cache using the handle from the calling application.
Thank you for any kind of hint or input - some sample C code will definitely help
Mircea
 

johndanter

Legendary Poster
Get your E1 BSFN to write a text file to a network location you have monitoring software sniffing for. When a file is detected it can call your stored procedure.
These kinds of software are very common
Maybe add your input parameters into the text file?
Use the same text file to add the return values? Bit flakey I know, but a theme around this principle could work

Or

B34A1030 Execute External Program may work.....?

It uses the CreateProcess API to call the string you pass into the CMD field. I'm not sure if this is windows only or not though
Also you won't be able to pass back values
 
Last edited:

Chan Rana

Legendary Poster
I am trying to find a way to call a DB2 stored procedures from an E1 BSFN passing input parameters and consuming multiple return parameters grouped in one record set (multiple records)
E1 910 residing on a AIX Unix Box
DB2 Stored procedure residing on iSeries box
Does anyone have any experience with such call ? Is it doable ?
I am thinking there' s got to be a way to massage returned records into a cache and manipulate that cache using the handle from the calling application.
Thank you for any kind of hint or input - some sample C code will definitely help
Mircea
I wish it would be so easy to pass info. between two different systems. As John said you might have to exploit Execute External Command to full extend.

Chan
 

Larry_Jones

Legendary Poster
Here's a generic (non DB2 specific) answer. Complicated a bit by the multiple rows to be returned

1. Create Dummy Table 1 (F55XX01). Columns to match your Input Parameters, a Unique Identifier (Job#) that you create and pass in, and possibly a Return Code.

2. Create Results Table 2 (F55XX02). Columns to match the return values desired from the Stored Procedure plus the Unique Identifier (Job#).

3. Create DBMS Insert Trigger on F55XX01 that triggers when row inserted (by JDE app). Insert Trigger should invoke Stored Procedure and write results (with Job #) into F55XX02.

4. JDE Application can then do a Select / Fetch Next loop against F55XX02 for results.

The trick that I'll leave up to you is determining the best method of signaling back to the JDE app that the Stored Procedure is done.
 

johndanter

Legendary Poster
What do these BSFNs do?? B9600445
....And these tables F9646/7/8?


They look like an afternoon load of fun :)
 

Larry_Jones

Legendary Poster
Are you really asking what the BSFNs / Tables do or do you know?

I can't find any documentation on them.
 

mirceag

Member
Thank you Larry, this might work assuming that the Stored procedure running on the iSeries DB2 can write back to the Oracle DB on the AIX machine. Doable using a JDBC connection - however, if the client decide not to use a ODBC/JDBC connection this will not work. In that case the solution (one of the solutions) will be calling the stored procedure from a C++ BSFN - retrieving returned result in a recordset and Loop through recordset using IBM API' s for DB2 ( Select - Fetch Next - Close recordset etc ) . Put the results into C++ variables - convert them to JDE Data types - store them into cache - create custom cache manipulation BSFN using handle to pass back to calling application or other application.
 

mirceag

Member
should be no problem running a stored procedure from the AIX. We can call it using a custom C++ BSFN or even a Unix script. The problem is returning multiple rows back to E1. Thank you, Mircea
 

mirceag

Member
Yes - possible we can call the stored procedure from a Unix Shell script but how do we call that from E1 ? From what I read ExecuteExternalProgram does not currently work on the AIX. And then how would I handle the return recordset inside E1..
 

DBohner-(db)

Legendary Poster
Anything that can be run from the command line - can be run from E1 as a script (at least I haven't found anything that couldn't???)

That said - Figure out how to initiate your Stored Procedure from the command line, and how to do it passing in parameters (it's, usually, fairly straightforward).

Have your Stored Procedure write to a custom table, that is accessible to E1. Make sure the records in the custom table are stamped or identifiable (BTW).

You could, sort of, identify with the SQL Injector - posted here, with a PowerPoint:
http://www.jderesearch.com/tips-traps/sql-injection

(db)
 
Top