Tips / Traps‎ > ‎

SQL Injection

For years, we developers (and everyone else) using JD Edwards EnterpriseOne - have hated the way Data Selection occurs in Report Versions.  It can be the most-cumbersome aspect of the entire tool, at times.  There is no such thing as grouping Data Selection and don't even get me started on the 'OR' syntax.

Recently, I found a very simple way to 'inject' a fully-qualified SQL Statement - into an E1 UBE.  Let's get started

In the simplest terms, here's what you do: 
  1. Create an E1 Table that will be used as the Primary Table for your UBE (this is a Work-Table)
  2. Create an SQL Statement that will Insert/populate that Work-Table
  3. Parameterize the SQL Statement and save as 'something'.sql
  4. Create a Batch Script, that will log into sql and call the parameterized .sql - passing in the values
    1. Oracle allows a script to be called from the Command Line of every OS
    2. Make sure that JDE has the security to call externally
    3. Hint:  Keep the scripts in the same folder - so security is simpler
    4. You can either use a UKID in the script/table or Truncate the table
      1. UKID allows multiple user to be running the UBE
      2. Truncate would be used for a single dataset (nightly job?)
  5. Call the Batch Script from the "REPORT INITIALIZATION" section
    1. Use Execute External Command to call the script
    2. This will run the script and load the data - before any other activities occur in the UBe
  6. Use the Work-Table in your UBE
  7. Additional Data Selection can be applied at the Report/Version level - since the data will have been loaded, already
The PowerPoint, below, does a fair job walking through the process.  Feel free to contact me, making suggestions / recommendations.  I realize the instructions are a bit hasty - I will work to make them a little more clear, with the help of your suggestions.

If you have a specific example for the use of the SQL Injector - that makes a lot of sense, forward it to me and I may rewrite the instructions to use it.  The BOM Explosion thing was just a proof of concept.

Notes for Standalone"
 Standalone 9.0 User/Pass - JDE/JDE
 Standalone 9.1U2 User/Pass - JDEData900/JDEData900

Daniel Bohner,
May 6, 2014, 12:20 PM