E9.2 Connect to another database and insert into JDE - ORCH

[Lennon2]

Hi , I'm creating a small ORCH to connect with other database, this step was working ok
but Insert in JDE I get an error , I think that the problem is how clean the JSON.


EXERCISE
Get the largest number from a table in another database and insert it into F55xxxx JDE


========== Step 1 ==========

Create Connector and connection to another database - OK

Groovy Script Connector - with Query SQL

import groovy.transform.CompileStatic;
import com.oracle.e1.common.OrchestrationAttributes;
import java.sql.*;

@CompileStatic

HashMap<String, Object> main(OrchestrationAttributes orchAttr, Connection sqlConnection, HashMap inputMap)

{

HashMap<String, Object> returnMap = new HashMap<String, Object>();

//define the select statement
def selectStmt = "SELECT MAX (ALHONB) FROM XXXXXXX.XXXXXXX WHERE ALC0ST= '9'";
def firstDockID = "";

//create a prepared statement with the SQL
PreparedStatement preparedStmt = sqlConnection.prepareStatement(selectStmt);

//execute the query returning the result set
ResultSet resultSet = preparedStmt.executeQuery();

//use the helper to set the records from the result set to the array variable
returnMap = orchAttr.mapResultSetToDataSet(resultSet,"ALHONB");

List<String> dataset = (List<String>) returnMap.get("ALHONB");
firstDockID = dataset.get(0);

//close the result and statement
resultSet.close();
preparedStmt.close();

return returnMap;
}

Output Connector

======== STEP 2 ========
Run the Orch - OK

response

The number obtained is correct, it is the 682217



===== STEP 3 ========
Insert this Number in for example a table F55xxxx like DOCO

I created a NER to insert the number and the parameter is DOCO , add the function on the ORCH and setup the parameter.

Mapping in the Function the output Conector (ALHONB)

========STEP 4 =========

What is the best way to clean the JSON to get the DOCO and be able to insert it without errors what am I doing wrong?
thanks in advance

 

[Kevin Long]

The output from your database connector is what will be returned to the orchestration. You are defining your returnMap as:

//use the helper to set the records from the result set to the array variable
returnMap = orchAttr.mapResultSetToDataSet(resultSet,"ALHONB");

So what you are sending back to the orchestration is the entire result set in an array called ALHONB.

What I would do is not map the resultSet to the returnMap, but to a different variable (let's use dataset for example).
dataset = orchAttr.mapResultSetToDataSet(resultSet,"ALHONB")

Then I would add:
returnMap.put("ALHONB",dataset["ALHONB"][0])

This will add ALHONB to returnMap with the value you are looking for.

This code is not needed, though you were heading in the right direction.
List<String> dataset = (List<String>) returnMap.get("ALHONB");
firstDockID = dataset.get(0);

Kevin

 

[Lennon2]

Thanks Kevin for your reply.
I tried to make those changes and it gives me an error anyway.
Since I only need 1 record, I'm going to look for a way to make the Json I receive have less structure for to be able to work it more easily