OL Learn

SQL: Add more than one result to datamapper

Hello,

I actually try to add more than one sql result to a detailed table.
The action step is as follows:

var connectionURL = ‘jdbc:odbc:Driver={iSeries Access ODBC Driver};System=**...;Uid=***;Pwd=***;’;

var sqlConnection = db.connect(connectionURL,"","");
var sqlQuery = “SELECT * FROM . WHERE *** = ‘xyz’”;
resultSet = sqlConnection.createStatement().executeQuery(sqlQuery);
resultSet.next();
resultSet.getRow();

The extraction step then looks as follows (detail.table):
let testExtract;
resultSet.next();
testExtract = resultSet.getRow() ? resultSet.getString(“WB_COLUMN”) : “”;

I know that the return of the select has more than one record, how can I add them to a detailed table?

Many thanks.

NBecker

You can’t add multiple rows using a script (that feature is on our roadmap, however).
So in the meantime, you’ll have to store your rows in an array and then use a Loop step to add each element of the array to the detail table.

Thanks for your answer, is there an example how this works?

Loop through your record like this, adding each record to an array:

// ...
var myArray = [];
resultSet = sqlConnection.createStatement().executeQuery(sqlQuery);
while( resultSet.next() ) {
   myArray.push({
      lName : resultSet.getString("LastName"),
      fName : resultSet.getString("FirstName"),
   });
}
// ...

Now in your config, use a Repeat Step inside which you add an Extraction Step. To extract all the “LastName” fields, as in the example above, set the mode to JavaScript and user the following code:

myArray[steps.currentLoopCounter-1].lName;

In your Repeat step, make sure you set the Maximum iterations on each line value to

myArray.length

This should get your going.