When querying a MS SQL table from datamapper to enhance my data, if my record can have more than one row returned by the DB query, what steps are needed to create a detail table of the resultSet values so 1 record may have many detail items?
1 Like
try to do the database query in a Datamapper loop, this should establish a detail table.
Use an array and follow the steps in the Create a Detail Table from a Linear Data Structure article.
Thanks Rod, but how do I set the end value of the loop? Getting the exact number of rows returned from the query doesn’t seem possible when the cursor is “forward only”.
I tought you might ask that question. Please take a look at this other article: Extracting Database Information in a Data Mapping Configuration
I have not tested this but theoretcally, once you have a resultSet (rs), you may get the number of records in your resultSet with the rs.RecordCount property.
You could create a nested for loop which loops through each record in the resultSet and then loop through each field (using rs.Fields.Count and rs.Fields(i)) in each record to generate (or push to) a multi dimensional array.
Once you have your array and have closed the SQL connection, you can then call the Extraction step within a Repeat loop, create the detail table using the example in the link sent previously.
So basically, you will have an Action step which queries SQL and retrieves resultSet, then constructs the array and closes the SQL connection. After that, call a Repeat loop to created the detail table. The end value of the loop is the length of your array.
The problem appears to be the goto step within the loop. I’m attempting to use a PDF in my data mapper as the data, and enhance it with the data I’m retrieving from SQL. The SQL part works, and I can populate the arrays that hold the results but there is no option to move by 1 in the loop, only physical distance, page or next occurrence of.
The GoTo step in this case doesn’t really have to go anywhere as you already have all the data you need in the array. So there is really no need to move up or down the page.
Try configuring the GoTo step in the following way:
Target Type: Physical distance
From: Top of record
Move to: 0.0
thanks, using from physical position and the value of .001 did work to trick the datamapper into looping enough times to extract the data from my arrays. Thanks!
Hi Rod,
I realise this is 2 years old. The article you linked to is gone. Any other resource that might assist? I’ve done this one before but can’t find my references.