E9.2 JDE Orchestrator: Groovy Script for Table I/O and BSFN call

Deb_Ray

Member
Hello Team,
Could you please let me know how do I call a BSFN or perform any Table I/O from Groovy Script available in JDE Orchestrator Custom Request?

Any help on Groovy Class/method related to JDE is appreciable.

I know we have Custom Request/ Data request available in JDE Orchestrator to call a BSFN or perform any Table I/O. But I want to try these from Groovy only.

Thanks in advance
Deb
 

louisphicc

Member
Hello Deb,

I was recently looking to achieve something similar, for table I/O.
I have achieved it using a Groovy custom request: I could do SELECT, INSERT, UPDATE and DELETE statements.

I had to use the groovy.sql.Sql library and the most difficut part was to setup the database connexion.
Here I can't really help you unless your database is set up like mine (Oracle 19c sitting on Windows Server 2016).
You can begin with the general groovy.sql.Sql documentation here https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html and then look for examples with your specific DB architecture on other forums.

Once your connexion is setup, the execute(String statement) method allows you to permform INSERT, UPDATE and DELETE.
For SELECT, the rows(String statement) will return a list which is useful to iterate over each row and the self explanatory method firstRow(String statement) is useful to get a single value.

I hope this will put you on the right path.
 

tkim415

Active Member
Sample code I've used for testing. Sql user id and password is being passed using json. If you use the connector, you don't have to setup the database, user id, and password. The script gets the name from the address book with the search type also being passed.

====
import com.oracle.e1.common.OrchestrationAttributes;
import java.text.SimpleDateFormat;
import groovy.sql.Sql;

HashMap<String, Object> main(OrchestrationAttributes orchAttr, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();
def searchType = (String) inputMap.get("SearchType")
searchType = searchType.substring(0,1)
//searchType = 'C'

def arr = new ArrayList()
def dbURL="jdbc:sqlserver://yourserver:1433;databaseName=JDEDEV"
def dbUsername= (String) inputMap.get("user");
def dbPassword= (String) inputMap.get("pass");
def dbDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
def dbQuery = "SELECT abalph FROM JDEDEV.TSTDTA.F0101 WHERE ABAT1='${searchType}'"

Sql.withInstance(dbURL, dbUsername, dbPassword, dbDriver) { sql ->
//orchAttr.writeDebug(dbQuery)
sql.eachRow(dbQuery) { row ->
arr.add(row.ABALPH)
}
}

returnMap.put("Test", arr)
return returnMap;
 

Deb_Ray

Member
Hello Deb,

I was recently looking to achieve something similar, for table I/O.
I have achieved it using a Groovy custom request: I could do SELECT, INSERT, UPDATE and DELETE statements.

I had to use the groovy.sql.Sql library and the most difficut part was to setup the database connexion.
Here I can't really help you unless your database is set up like mine (Oracle 19c sitting on Windows Server 2016).
You can begin with the general groovy.sql.Sql documentation here https://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html and then look for examples with your specific DB architecture on other forums.

Once your connexion is setup, the execute(String statement) method allows you to permform INSERT, UPDATE and DELETE.
For SELECT, the rows(String statement) will return a list which is useful to iterate over each row and the self explanatory method firstRow(String statement) is useful to get a single value.

I hope this will put you on the right path.
This is really helpful. At least I can start something now. Will update you.
 

Deb_Ray

Member
Sample code I've used for testing. Sql user id and password is being passed using json. If you use the connector, you don't have to setup the database, user id, and password. The script gets the name from the address book with the search type also being passed.

====
import com.oracle.e1.common.OrchestrationAttributes;
import java.text.SimpleDateFormat;
import groovy.sql.Sql;

HashMap<String, Object> main(OrchestrationAttributes orchAttr, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();
def searchType = (String) inputMap.get("SearchType")
searchType = searchType.substring(0,1)
//searchType = 'C'

def arr = new ArrayList()
def dbURL="jdbc:sqlserver://yourserver:1433;databaseName=JDEDEV"
def dbUsername= (String) inputMap.get("user");
def dbPassword= (String) inputMap.get("pass");
def dbDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
def dbQuery = "SELECT abalph FROM JDEDEV.TSTDTA.F0101 WHERE ABAT1='${searchType}'"

Sql.withInstance(dbURL, dbUsername, dbPassword, dbDriver) { sql ->
//orchAttr.writeDebug(dbQuery)
sql.eachRow(dbQuery) { row ->
arr.add(row.ABALPH)
}
}

returnMap.put("Test", arr)
return returnMap;
Thanks for your help. Let me try this..
 

jolly

Reputable Poster
Hi all,

My goal is similar... with but with JDE database on Oracle ODA. I want to create Orchestration steps to perform select statements that are infeasible with DataRequests (such as WHERE NOT IN (sub-select) etc), and also to perform database updates. However I do not want to open up a big security hole. I see there's an EnterpriseOne JDBC driver that uses the E1 user and role, so hopefully that will go some way to alleviate the security concern.

Are there any examples out there for a developer without CNC expertise, in setting up a Connection and Connector to do simple SQLs against an E1 ODA database?
 

jolly

Reputable Poster
<crickets chirping>

Ok, finally nutted this out myself... am living with a generic Oracle jdbc driver for now. So in the Connection object I have:

User/Role: *PUBLIC Environment: JPY920
Connection: jdbc:_oracle:thin:_@risingsun-scan:1521/JDETEST (remove _ ... otherwise my post gets stupid emoticon smileys)
User: <Oracle User>
Password: <Oracle Password>
Driver: oracle.jdbc.driver.OracleDriver


And in the Database connector, something like:

import com.oracle.e1.common.OrchestrationAttributes;
import groovy.sql.Sql
HashMap<String, Object> main(OrchestrationAttributes orchAttr, Sql sqlInst, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();

def sqlString = inputMap.get("sqlString");

orchAttr.writeWarn("Execute" + sqlString);

def itemCount = sqlInst.firstRow(sqlString).get("ItemCount");

returnMap.put("returnCount", itemCount);
return returnMap;
}


In this example I just want to run a select count(*) as itemCount from crpdta.... type query and made it generic so I can re-use this connector for different counting type operations.

Yes, could typically use a Data Request with aggregation in simple cases, but not in this particular one as it has a WHERE NOT IN ( subquery ) clause.

So this works pretty well.

I need to check if the RowSet object that firstRow().get() can return a column by position, e.g. sqlInst.firstRow(sqlString).get(1) to return column 1 to remove dependence on a column name.

Also, where the heck can you find the output from orchAttr.writeWarn() calls?
 
Top