• 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!

Running SQL Stored procedure via JDE

smenon69

Active Member
Hi Everyone,

I have a requirement to call SQL Stored procedure from JDE application and return the result of the procedure in a grid (JDE application).

Any input would be appreciated.

Thanks
SM
 

peterbruce

Legendary Poster
SM,
There have been a lot of posts on stored procedures over the years. Here are a few:

https://www.jdelist.com/vb4/showthr...-BSFN-calling-DB2-stored-procedure-on-iSeries
https://www.jdelist.com/vb4/showthr...input-Parameters-to-Stored-procedure-from-JDE
https://www.jdelist.com/vb4/showthread.php/40646-Call-Stored-Procedure

The hard part will be notifying the JDE application or the user that the procedure is finished. If running a script using execute external program business function, the function may not return until the script is complete.
Anyway, have a look at the threads above (or do your own search) and use what is applicable to your case.
 

craig_welton

Legendary Poster
Another angle is to us a BSSV that connects to the database with JDBC to call the procedure, then writes the results to the JDE table. It's a bit cleaner than the external calls or triggers, and when the function returns, you know the procedure is complete.

Craig
 

Larry_Jones

Legendary Poster
I really like the approach of a custom table with a DB insert trigger on it.
JDE Application inserts a row into the table with parameter data in the fields.
DB Insert trigger fires, calls Stored Procedure, Stored Procedure does its thing and writes data into Custom Table #2
JDE Application reads data from Custom Table #2 and writes to Grid.

I have not actually done all of this but it should work and be a fairly easy way to accommodate the OPs request.
 

Kim Schmidt

Active Member
We used an approach similar to Larry's suggestion. Your JDE App or UBE writes to a table "A" having a database insert trigger, which reads the newly inserted record to get what it needs to do, which can include the procedure name and possibly run-time parameters. The database then executes the stored procedure, which would usually write data to another table, then flips a status flag on the table "A" record to indicate it's done. Meanwhile, your JDE app can optionally wait for the flag to be flipped before continuing. One thing to keep in mind - if you ever need to regenerate table "A" from JDE, it will probably drop your trigger(s), so be prepared to be able to recreate them!
 

smenon69

Active Member
I really like the approach of a custom table with a DB insert trigger on it.
JDE Application inserts a row into the table with parameter data in the fields.
DB Insert trigger fires, calls Stored Procedure, Stored Procedure does its thing and writes data into Custom Table #2
JDE Application reads data from Custom Table #2 and writes to Grid.

I have not actually done all of this but it should work and be a fairly easy way to accommodate the OPs request.
Thanks Larry
 
Top