SQL: Add more than one result to datamapper

OL Connect Professional DataMapper
1

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

2

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.

3

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

4

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.