Hi 2011

Database Connectors can connect to any database, you just need to have a connection defined. Orchestrator provides the Data Service Request for accessing JDE tables and Business Views, but there are limitations. For example you can't mix "and" and "or" when filtering data.

With the Database Connector it's purely SQL so as long as you can connect to the database you can run the SQL over it. Custom SQL gives more control over the query and allows more powerful joins, grouping, etc, but has the caveat that it doesn't understand JDE paths.

What this means is that if, like us, you have your security runs across all environments then your System Schema (SY920) likely resides on your production database server (for us ORCLPROD). If we want to run SQL in the DV environment using a Database Connector it won't see SY920 on ORCLPROD, only the version on ORCLTEST which isn't maintained and is out of date. There are ways around this, but it's something to keep in mind.

Cheers
Jonathan

Hi 2011

The script below is an example of using SQL to retrieve data from the E1 database. In our case we use an Oracle Database. We have a standard template setup with "helpers" to make coding easier anda standard set of inputs and outputs. We pass in the various database schemas for the appropriate environment, e.g. TESTDTA, TESTCTL, etc:

We tend to write entries to the debug log as that's where we check most of the messages while developing. To write to the E1ROOT log we would just change the calls to "log" to "warn".

Hope it helps
Jonathan

// Standard imports
import groovy.transform.CompileStatic;
import com.oracle.e1.common.OrchestrationAttributes;
import java.sql.*;
import java.util.*;
import groovy.json.JsonOutput;

HashMap<String, Object> main(OrchestrationAttributes orchAttr, Connection sqlConnection, HashMap inputMap)
{
HashMap<String, Object> returnMap = new HashMap<String, Object>();

// Read Inputs...

// ---------------------------------------------------------------------

def env = inputMap.get("Environment");

def dta = inputMap.get("Business Data Schema");

def ctl = inputMap.get("Control Data Schema");

def sys = inputMap.get("System Data Schema");


// Build SQL

def sql ="select drsy, drrt, drky, drdl01, drdl02 from ${ctl}.f0005 where drsy = '00' and drrt = 'DT'";


// Write the SQL statement to the debug logs...

log(orchAttr, "SQL: ${sql}");


// Run the SQL...

def rows = fetchRows(sqlConnection, sql);


// Check SQL was successful...

if (rows.size() == 0) {

    returnMap.putAll(fail("SQL0404", "SQL returned no records"));

    returnMap.put("sqlCount", 0);

    log(orchAttr, "SQL: SQL returned no records");

    return returnMap;


// Data found so process it...

} else {


    // Write result set to output array...

    returnMap.put("sqlOutput", rows)

    returnMap.putAll(ok([:]));

    returnMap.put("sqlCount", rows.size());


    // Read through result set...

    for (dataRow in rows) {

        def sy = dataRow.DRSY;

        def rt = dataRow.DRRT;

        def ky = dataRow.DRKY;

        def dl01 = dataRow.DRDL01?.trim();

        def dl02 = dataRow.DRDL02?.trim();                     

        log(orchAttr, "UDC: ${sy} /${rt} / ${ky} - dl01=${dl01}, dl02=${dl02}");

    }

}


// Exit the script...

return returnMap;

}

// ********************************************************************
// ** S T A N D A R D H E L P E R M E T H O D S **
// ********************************************************************

// -----------------------------
// nowIso - Return ISO Timestamp
// -----------------------------
private static String nowIso() {
def fmt = new java.text.SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");
return fmt.format(new java.util.Date());
}

// -----------------------------------
// log - Write Entry to AIS Debug Logs
// -----------------------------------
private static void log(OrchestrationAttributes orchAttr, String msg) {
orchAttr.writeDebug("[DB-CONN] " + msg);
}

// ------------------------------------
// warn - Write Entry to AIS E1Root Log
// ------------------------------------
private static void warn(OrchestrationAttributes orchAttr, String msg) {
orchAttr.writeWarn("[DB-CONN] " + msg);
}
// -----------------------------------------------------------
// ok - Populate Standard Return Parameters for Successful SQL
// -----------------------------------------------------------
private static Map ok(Map payload) {
Map m = new LinkedHashMap();
m.put("sqlStatus", "OK");
m.put("sqlCode", "OK");
m.put("sqlMessage", "Success");
Map det = new LinkedHashMap();
det.put("timestamp", nowIso());
m.put("sqlDetails", det);
m.put("sqlData", (payload != null && !payload.isEmpty()) ? JsonOutput.toJson(payload) : "");
return m;
}

// ---------------------------------------------------------
// fail - Populate Standard Return Parameters for Failed SQL
// ---------------------------------------------------------
private static Map fail(String code, String message, Object details) {
Map m = new LinkedHashMap();
m.put("sqlStatus", "ERROR");
m.put("sqlCode", (code != null && code.length() > 0) ? code : "EXC0500");
m.put("sqlMessage", (message != null && message.length() > 0) ? message : "Unhandled error");
if (details == null || details instanceof String) {
m.put("sqlDetails", details);
} else {
m.put("sqlDetails", JsonOutput.toJson(details));
}
m.put("sqlData", "");
return m;
}

// --------------------------------------------
// fetchRows - Retrieve Multiple Rows Using SQL
// --------------------------------------------
private static List<Map> fetchRows(Connection conn, String sql, Object... params) {
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object p = params[i];
if (p instanceof Number) {
ps.setLong(i + 1, ((Number) p).longValue());
} else {
ps.setString(i + 1, (p == null) ? null : p.toString());
}
}
ResultSet rs = ps.executeQuery();
List<Map> rows = new ArrayList<Map>();
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
while (rs.next()) {
Map row = new LinkedHashMap();
for (int c = 1; c <= cols; c++) {
String label = md.getColumnLabel(c);
if (label == null || label.length() == 0) label = md.getColumnName(c);
String value = rs.getString(c);
row.put(label.toUpperCase(), value == null ? "" : value.trim());
}
rows.add(row);
}
rs.close();
ps.close();
return rows;
}