Using Native SQL within E 1 The SQL

Using Native SQL within E 1 – The SQL Injector SQL is an amazing tool. Developers fail to realize just how strong it can really be for them. For years, we have asked Oracle for the ability to create our own SQL Statements – and run those statements within a E 1 Apps So, Let’s make it happen…. drbohner@existinglight. net / http: //www. jderesearch. com

The OLD WAY, A Virtual Table is, simply, a JDE/TDA Table Specification that is defined to match an SQL View or Logical File Create an SQL View (or i. Series Logical) CREATE VIEW JDEDATA 900. F 55 THIS AS ( SELECT * FROM JDEDATA 900. F 0902 WHERE GBFY = '9‘); Create a JDE/TDA Specification that EXACTLY matches the SQL View’s Definition For this example – we exact-copy F 0902 drbohner@existinglight. net / http: //www. jderesearch. com

Recap … Virtual Table First, you have to define it: • If you can create an SQL Statement, that gives you exactly what you want, you can create it as a ‘Virtual Table’ • SELECT * FROM TESTDTA. F 0902 WHERE GBFY > ‘ 9’ Then, you can create it as an SQL View • Create View TESTDTA. F 55 THIS as (SELECT * FROM TESTDTA. F 0902 WHERE GBFY > ‘ 9’) Finally, Create the TDA Spec • The TDA Spec must EXACTLY Match the definition of the SQL View • Cheat wherever you can; If you can do a Select * from a Table, you can copy the TDA Spec with little or no changes • Copy F 0902 to F 56 THIS – make no additional changes to Table Design • If the Virtual Table is created, first, you cannot generate the table (hint) drbohner@existinglight. net / http: //www. jderesearch. com

What if you are using more than one table in an SQL View? There is a limitation to the number of tables that can be in an SQL View. Virtual Tables allow us to overcome that issue If you are using more than one table in an SQL View – you must rename columns such that all Column Prefixes Match SELECT T 2. *, NHFLOR NWFLOR FROM PRODDTA. F 15017 T 2 LEFT OUTER JOIN PRODDTA. F 1507 T 4 ON (NWMCU=NHMCU AND WUNIT = NHUNIT) * In the example, NHFLOR is renamed to NWFLOR – this allows the column prefix to be the same across all columns (it has to be this way!) drbohner@existinglight. net / http: //www. jderesearch. com

Now, you can use the SQL View – just like any other JDE Table Enterprise. One doesn’t care if the data is in an SQL View or in a traditional table – it cares about the formatting and structure, more than anything else. Generally speaking – don’t update values in a Virtual Table. There are many reasons why I suggest against it, more than I want to express within this document Go Play with the process, you might find it, sort of, interesting. drbohner@existinglight. net / http: //www. jderesearch. com

OK – LET’S GET TO WHAT YOU WANT TO KNOW ABOUT THE SQL INJECTOR PROCESS drbohner@existinglight. net / http: //www. jderesearch. com

The SQL Injector (well, sort of) Are you tired of using E 1 Data Selection? Do you want to ability to run an ACTUAL SQL Statement – embedded in a UBE? Is Your UBE/Version Data Selection Impossibly Complex? Do you have in-house Masters in SQL – that HATE (HATE) the way UBE Data Selection Occurs Then Stay in the room a few more minutes! drbohner@existinglight. net / http: //www. jderesearch. com

Case Study: The need for a SQL Injector • The client moves from hundreds of Access Databases to Enterprise. One. The users go from creating their own SQL Statements to… Version Data Selection – and the Revolt Begins • The Users Ask, why can’t we just create our own SQL Statement – and IT Panics • Then, the BA(s) start looking at the complexity of some of the requirements and agree – this is Crazy. Hades to create as Version Data Selection. There has to be a better way And there is: drbohner@existinglight. net / http: //www. jderesearch. com

A user can create a complex SQL Statement that Brings Back, exactly what they desire: -- BOM Explosion SQL Statement SELECT CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM, '/') OSGS 5 A, LEVEL OSBOML, A. IXMMCU OSMMCU, A. IXKIT OSKIT, A. IXKITL OSKITL, A. IXKITA OSKITA, A. IXCMCU OSCMCU, A. IXITM OSCPIT, A. IXLITM OSCPIL, A. IXAITM OSCPIA, A. IXUM OSUM, A. IXFORQ OSFORQ, A. IXITC OSITC, A. IXOPSQ OSOPSQ, A. IXCPNB OSCPNB, A. IXLNTY OSLNTY, A. IXEFFF OSEFFF, A. IXEFFT OSEFFT, A. IXBREV OSBREV, A. IXRVNO OSRVNO, A. IXCMRV OSCMRV, A. IXPRIC OSPRIC, A. IXUNCS OSUNCS, A. IXSCRP OSSCRP, A. IXECO OSECO, A. IXQNTY OSQNTY -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSQNTY, -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSXQNTY FROM JDEDATA 900. F 3002 A WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< 110090 AND IXEFFT> 110090 START WITH A. IXKIT = 700998 CONNECT BY NOCYCLE PRIOR A. IXITM=A. IXKIT AND PRIOR A. IXCMCU=A. IXMMCU There is no way to plant something like that into a UBE? Yeah, that’s what they tell us… it’s not true… * The commented, grey, lines contain logic that doesn’t work on the 9. 0 Standalone drbohner@existinglight. net / http: //www. jderesearch. com

Remember, if you can put it in an SQL Statement – you can create an SQL View and Virtual Table AND, sometimes you only need the data temporarily – like for a single run of a UBE Why not dump the results from an SQL Statement into a Temporary Table, instead of tying up an SQL View Create an E 1 Table, that exactly matches the “Results of the Query”, then alter that original query to insert into the E 1 Table INSERT INTO JDEDATA 900. F 55 BOMX ( SELECT CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM, '/') OSGS 5 A, LEVEL OSBOML, A. IXMMCU OSMMCU, A. IXKIT OSKIT, A. IXKITL OSKITL, A. IXKITA OSKITA, A. IXCMCU OSCMCU, A. IXITM OSCPIT, A. IXLITM OSCPIL, A. IXAITM OSCPIA, A. IXUM OSUM, A. IXFORQ OSFORQ, A. IXITC OSITC, A. IXOPSQ OSOPSQ, A. IXCPNB OSCPNB, A. IXLNTY OSLNTY, A. IXEFFF OSEFFF, A. IXEFFT OSEFFT, A. IXBREV OSBREV, A. IXRVNO OSRVNO, A. IXCMRV OSCMRV, A. IXPRIC OSPRIC, A. IXUNCS OSUNCS, A. IXSCRP OSSCRP, A. IXECO OSECO, A. IXQNTY OSQNTY -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSQNTY, -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSXQNTY FROM JDEDATA 900. F 3002 A WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< 110090 AND IXEFFT> 110090 START WITH A. IXKIT = 700998 CONNECT BY NOCYCLE PRIOR A. IXITM=A. IXKIT AND PRIOR A. IXCMCU=A. IXMMCU ) drbohner@existinglight. net / http: //www. jderesearch. com

OH – we want to be able to make this Query ‘dynamic’ – we can use Parameters to pass-in / replace those values INSERT INTO JDEDATA 900. F 55 BOMX ( SELECT &1 OSUKID, CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM, '/') OSGS 5 A, LEVEL OSBOML, A. IXMMCU OSMMCU, A. IXKIT OSKIT, A. IXKITL OSKITL, A. IXKITA OSKITA, A. IXCMCU OSCMCU, A. IXITM OSCPIT, A. IXLITM OSCPIL, A. IXAITM OSCPIA, A. IXUM OSUM, A. IXFORQ OSFORQ, A. IXITC OSITC, A. IXOPSQ OSOPSQ, A. IXCPNB OSCPNB, A. IXLNTY OSLNTY, A. IXEFFF OSEFFF, A. IXEFFT OSEFFT, A. IXBREV OSBREV, A. IXRVNO OSRVNO, A. IXCMRV OSCMRV, A. IXPRIC OSPRIC, A. IXUNCS OSUNCS, A. IXSCRP OSSCRP, A. IXECO OSECO, A. IXQNTY OSQNTY, '&4' OSUSER, '&5' OSPID, '&6' OSJOBN, &7 OSUPMJ, &8 OSUPMT -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSQNTY, -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSXQNTY FROM JDEDATA 900. F 3002 A WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< &3 AND IXEFFT> &3 START WITH A. IXKIT = &2 CONNECT BY NOCYCLE PRIOR A. IXITM=A. IXKIT AND PRIOR A. IXCMCU=A. IXMMCU ) Notice that I put “&1 OSUKID” in the statement – this allows us to embed a Unique ID into the insert. The UKID will allow us to run multiple UBEs at the same time The Select Statement has some Parameters embedded - &1, &2, &3 …. we will pass the values in via command statement…. drbohner@existinglight. net / http: //www. jderesearch. com

Remember – you have to have an Enterprise. One table: drbohner@existinglight. net / http: //www. jderesearch. com

UBE: Universal Batch Engine – - Think of it like a Batch Scripting Tool, that can be used to run Operating System Commands - Using a UBE, we can do almost anything that can be done from the command line (as long as JDE has permissions) - Did you know that you can run an SQL Statement from the Command Line? Save the SQL Statement on the prior screen as BOMExpl. SQL From a DOS command line, call it – similar to this: sqlplus jde/jde @C: COLLABORATE 14BOMExpl. SQL 15 700998 110090 DEMO R 55 BOMX VPCDEFAULT 114094 160701 sqlplus (The Command Line) jde/jde (User / Pass for Standalone) @C: COLLABORATE 14BOMExpl. SQL (The Location and name of the SQL Script) 15 (Unique ID) 700998 (Kit Item Number) 110090 (Effectivity Date) DEMO (User ID) R 55 BOMX (Report ID) VPCDEFAULT (Job Number) 114094 (Current Date) 160701 (Current Time) drbohner@existinglight. net / http: //www. jderesearch. com

The SQL Statement should be embedded in a. SQL file – this just makes the whole process easier The BOMExpl. SQL sql statement would look like (notice the &# Parameters: ------------------------------------ Filename: BOMExpl. sql -- Purpose: Run SQL*Plus script from DOS batch file -- Date: 02 -Apr-2014 -- Author: Daniel Bohner - JDEResearch. Com -- Notes: Script Example to run any SQL Script from UBE ------------------------------------ Whenever possible, Truncate. Much quicker than Deleting TRUNCATE TABLE JDEDATA 900. F 55 BOMX; COMMIT ; -- For this dummy example, we are being very simple INSERT INTO JDEDATA 900. F 55 BOMX (SELECT &1 OSUKID, CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM , '/') OSGS 5 A, LEVEL OSBOML, A. IXMMCU OSMMCU, A. IXKIT OSKIT, A. IXKITL OSKITL, A. IXKITA OSKITA, A. IXCMCU OSCMCU, A. IXITM OSCPIT, A. IXLITM OSCPIL, A. IXAITM OSCPIA, A. IXUM OSUM, A. IXFORQ OSFORQ, A. IXITC OSITC, A. IXOPSQ OSOPSQ, A. IXCPNB OSCPNB, A. IXLNTY OSLNTY, A. IXEFFF OSEFFF, A. IXEFFT OSEFFT, A. IXBREV OSBREV, A. IXRVNO OSRVNO , A. IXCMRV OSCMRV, A. IXPRIC OSPRIC, A. IXUNCS OSUNCS, A. IXSCRP OSSCRP, A. IXECO OSECO, A. IXQNTY OSQNTY, '&4' OSUSER, '&5' OSPID, '&6' OSJOBN, &7 OSUPMJ, &8 OSUPMT -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSQNTY, -- Issue with Database: DECODE(IXFORQ, 'F', IXQNTY, DBMS_AW. EVAL_NUMBER('1' ||SYS_CONNECT_BY_PATH(a. IXQNTY/10000, '*'))) OSXQNTY FROM JDEDATA 900. F 3002 A WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< &3 AND IXEFFT> &3 START WITH A. IXKIT = &2 CONNECT BY NOCYCLE PRIOR A. IXITM=A. IXKIT AND PRIOR A. IXCMCU=A. IXMMCU); COMMIT; drbohner@existinglight. net / http: //www. jderesearch. com

When you run that command, from a DOS Prompt, the results will look similar to: Microsoft Windows XP [Version 5. 1. 2600] (C) Copyright 1985 -2001 Microsoft Corp. C: Documents and SettingsDemo>cd C: >sqlplus jde/jde @C: COLLABORATE 14BOMExpl. SQL 15 700998 110090 DEMO R 55 BOMX VPCDEFAULT 114094 160701 SQL*Plus: Release 10. 2. 0. 3. 0 - Production on Fri Apr 4 16: 18: 38 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10 g Enterprise Edition Release 10. 2. 0. 3. 0 - Production With the OLAP and Data Mining options Table truncated. Commit complete. old new 2: 2: 8: 8: 12: 13: SELECT &1 OSUKID, CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM, '/') OSGS 5 A, LEVEL OSBOML, SELECT 15 OSUKID, CONNECT_BY_ROOT(IXKIT) OSZ 56 ITMP, CONNECT_BY_ROOT(IXMMCU) OSZ 56 MCUP, SYS_CONNECT_BY_PATH(A. IXITM, '/') OSGS 5 A, LEVEL OSBOML, A. IXECO OSECO, A. IXQNTY OSQNTY, '&4' OSUSER, '&5' OSPID, '&6' OSJOBN, &7 OSUPMJ, &8 OSUPMT A. IXECO OSECO, A. IXQNTY OSQNTY, 'DEMO' OSUSER, 'R 55 BOMX' OSPID, 'VPCDEFAULT' OSJOBN, 114094 OSUPMJ, 160701 OSUPMT WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< &3 AND IXEFFT> &3 WHERE A. IXTBM='M' AND RTRIM(LTRIM(A. IXMMCU)) IN ('M 30', 'M 40') AND IXEFFF< 110090 AND IXEFFT> 110090 START WITH A. IXKIT = &2 START WITH A. IXKIT = 700998 27 rows created. Commit complete. SQL> Disconnected from Oracle Database 10 g Enterprise Edition Release 10. 2. 0. 3. 0 - Production With the OLAP and Data Mining options C: > drbohner@existinglight. net / http: //www. jderesearch. com

So, if you can run an SQL Script from the Command Line, how do we run it in a UBE? And, how do you make it Dynamic? Did you notice that &# corresponds to the sequence of a Parameter? If you create a. bat (or other script file), you can call different. SQL Scripts – making one script, more useable! @echo off rem -----------------------------------rem Filename: Run. Sql. bat rem Purpose: Run SQL*Plus script from DOS batch file rem Date: 01 -Apr-2014 rem Author: Daniel Bohner - JDEResearch. Com rem Notes: This Standalone 'SQL Plus Login can be used to run any. sql rem Script rem -----------------------------------rem -- Accept command line arguments -rem Note: %1 is the first command line argument, %2 the second, etc. if "%1" == "" goto Usage set SCRIPTNAME=%1 sqlplus jde/jde @C: COLLABORATE 14%SCRIPTNAME%. SQL %2 %3 %4 %5 %6 %7 %8 %9 %10 goto End : Usage echo Usage: %0 TABLE_NAME_MISSING : End Now, you can call the Run. SQL. bat, using Execute External Command, from every UBE drbohner@existinglight. net / http: //www. jderesearch. com

BUT – How do you make this happen in a UBE? Well, have you ever looked at the “Report” level Event Rules? You can run the Execute External Command in the Report Initialization Event. The nice thing about this, that the data is inserted into the work file before the Section Level Initialization (in other words, you can perform additional Section/Version level Data Selection) drbohner@existinglight. net / http: //www. jderesearch. com

R 55 BOMX UBE: Initialize Report Section (where you call the SQL Injection) UBE: R 55 BOMX Event: Initialize Report (future) Event GUID: 398 a 1 b 6 b-e 490 -4177 -b 548 -ca 4724883930 --------------------Event Level Variables --------------------evt_User. Id [USER] evt_Program. Id [PID] evt_Work. Station. Id [JOBN] evt_Time. Last. Updated [UPMT] evt_Date. Updated [UPMJ] evt_Run. Date [CFSTR 8] evt_Audit. Date [CFSTR 8] --------------------00001 // 00002 // 00003 // Get Audit Information 00004 Get. Audit. Info(B 9800100. Get. Audit. Info) VA evt_User. Id [USER] <- sz. User. Name [USER] VA evt_Date. Updated [UPMJ] <- jd. Date [DTE] VA evt_Time. Last. Updated [UPMT] <- mn. Time [TME 0] VA evt_Work. Station. Id [JOBN] <- sz. Workstation_User. Id [JOBN] 00005 VA evt_Program. Id [PID] = SL Report. Name 00006 // 00007 Get. Next. Unique. Key. ID(X 00022. Get. Next. Unique. Key. ID) "F 55 BOMX" -> sz. Object. Name [OBNM] VA rpt_Unique. Key. IDInternal [UKID] <- mn. Unique. Key. ID [UKID] 00012 00013 00014 00015 00016 00017 00018 00019 00020 al]) 00021 00022 t]) 00023 00024 00025 00026 00027 00028 00029 00030 00031 00032 00033 00034 00035 00036 00037 // // // Set the first half of the command RV Generic Description [ADS 2] = "C: COLLABORATE 14RUNSQL. BAT " RV Generic Description = concat([RV Generic Description], ' ') // Set which SQL Statement to run RV Generic Description = concat([RV Generic Description], [PO sz. SQLStatement]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA rpt_Unique. Key. IDIntern RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [PO mn. Item. Number. Short. Ki RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_Run. Date]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_User. Id]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_Program. Id]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_Work. Station. Id]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_Audit. Date]) RV Generic Description = concat([RV Generic Description], ' ') RV Generic Description = concat([RV Generic Description], [VA evt_Time. Last. Updated]) // // Call the comman to complete the process - Execute. External. Program(B 34 A 1030. Execute. External. Program) RV Generic Description [ADS 2] -> sz. Command. Line [NFLF] 00008 // 00009 // Convert Date Formats 00010 Convert. Date. To. Julian. Format(N 3401570. Convert. Date. To. Julian. Format) PO jd. BOMDate [UPMJ] -> jd. Date. To. Convert [DRQJ] VA evt_Run. Date [CFSTR 8] <- sz. Julian. Date. In. String [VC 06 A] 00011 Convert. Date. To. Julian. Format(N 3401570. Convert. Date. To. Julian. Format) VA evt_Date. Updated [UPMJ] -> jd. Date. To. Convert [DRQJ] VA evt_Audit. Date [CFSTR 8] <- sz. Julian. Date. In. String [VC 06 A] drbohner@existinglight. net / http: //www. jderesearch. com

In the Primary Section’s Initialization Section – Set the Internal Data Selection such that the Table’s UKID matches the Report Initialization’s UKID Section: SQL Injected BOM Processor [3] Event: Initialize Section Event GUID: d 07 ebb 4 f-20 a 3 -4 f 50 -aa 33 -4 e 53 af 04 da 16 00001 Set Selection Append Flag( <Yes>) 00002 Set User Selection(BC Unique Key ID (Internal) (F 55 BOMX) (UKID), <Equal To>, VA rpt_Unique. Key. IDInternal, <And>) drbohner@existinglight. net / http: //www. jderesearch. com

Recap – how to SQL Inject - Create an E 1 Table, that will be used as the SQL Target for the SQL Injector AND the basis for your UBE - Create an SQL Statement that inserts into the E 1 Table - Parameterize the SQL Statement, and save as a. sql file - Create a Batch / Script file that Logs Into SQL and Calls the. sql file - Call the Parameterized Batch / Script file from the Report Initialization section (this will load the E 1 Table) - Additional Data Selection can be applied to the Section(s) of the report – allowing for further user-friendliness As you come across issues with the example – ping me and I will fix them =D drbohner@existinglight. net / http: //www. jderesearch. com