Results 1 to 8 of 8

Thread: Running SQL Stored procedure via JDE

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    21

    Running SQL Stored procedure via JDE

    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

  2. #2
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,109
    SM,
    There have been a lot of posts on stored procedures over the years. Here are a few:

    https://www.jdelist.com/vb4/showthre...ure-on-iSeries
    https://www.jdelist.com/vb4/showthre...edure-from-JDE
    https://www.jdelist.com/vb4/showthre...ored-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.
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  3. #3
    Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    911
    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
    Craig Welton
    PatWel Group Inc.
    http://www.patwel.com
    Home of the FREE JDE Object Browser, JDETrace and NERDup Tools

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  4. #4
    New Member
    Join Date
    Aug 2002
    Posts
    21
    Thanks Peter, I will go through the docs...

  5. #5
    New Member
    Join Date
    Aug 2002
    Posts
    21
    Craig thank you, I will try the BSSV.

  6. #6
    New Member
    Join Date
    Aug 2002
    Posts
    21
    Thanks Peter for the doc. links...

  7. #7
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,150
    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.
    Larry Jones
    E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
    Wintel, BI Publisher

    Migrating to JDE 9.2 and SQL Server 2016

  8. #8
    New Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    23
    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!
    Regards,
    Kim Schmidt

    - E1 9.1 ,TR 9.1.7 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.