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.

5

Hi Phil,

I’m able to exctract fields following your suggestion by creating the array and then extracting the fields.
The problem is that i have a specific use case where i loop through a table populating its content which is inside another table, meaning that if i switch between items of the parent table also the child table is updating(data all extracted from sqlserver). It seems like when i try to extract the fields from the nested table by following your suggestion each time i go on the next loop cycle it loses the last item from the composed array containg the data.

Is there a way to not lose items from the declared Array when the parent item iterates to the next loop cycle?

Thanks.

M

6

Hi Muno1, and welcome to the community!

This post is fairly old and since that time, there have been a number of improvements to the scripting methods that allow you to add multiple rows to a detail table. Search the Forum for the keyword addrow, and you’ll find a few posts and examples (in particular, this one).

7

Hi Phil,

That solved the problem right away!!

Thank you!