How to read data using db connector into business variable?

1
+2
-1

I would like to read data from an external MS SQL database using a connector and map the result into a business variable.

I have created a new Business Object called 'MyObject' with attributes 'ID' (integer) and 'Status' (String).
Then I created a Pool Business variable called 'loadedMyObjects' of type 'MyObject' and I set the flag 'IsMultiple' as true so it should be a collection of 'MyObject' instances.

My workflow diagram consists of a Start event connected to a Service Task and finally an End event.

In the Service task I added a new Connector In to load data from MS SQL database table with columns being same as the attributes in the business object 'MyObject'.
The sql query to retrieve the data is:

SELECT TOP 10 ID, Status FROM MyObject

When I click the test button for the connector I get the data I want back. Here is a screenshot:
2nk3s5s.jpg

Then I get to the 'Output operations definition' page and here I select to store the result in 'loadedMyObjects' pool variable and the operation to be 'Takes value of' and on the right side I choose to get the result as a expression in Script as seen in screenshot bellow:
wqzvdf.png

But here is where I got stuck and don't know what should be in the script to get the data in the 'resultset' to my pool business variable 'loadedMyObjects'.

I guess I will need to read the resultset and for each row create an 'MyObject' instance and set it's properties but so far nothing worked for me and any help would be greatly appreciated.

Thanks

EDIT:

I found a solution and here is the groovy snippet for reference:

List myObjects = new ArrayList();
while(resultset.next()) {
def myObject = myObjectDAO.newInstance();
myObject.ID = resultset.getInt("ID");
myObject.Status = resultset.getString("Status");
myObjects.add(myObject);
}
return myObjects;

Comments

Submitted by delinare.its on Wed, 12/28/2016 - 03:35

Hello, I want to know what type did you use in LoadedMyObject object?
and I've put the script on groovy and when I clicked 'evaluate', the screen show me that it's need to be define value for resultset and myObjectDAO. What should I do?

Thanks before,

1 answer

1
0
-1

You answered it...

Well found, regards

Comments

Submitted by aduthoy on Fri, 12/04/2015 - 04:00

Hello Edit.

I have the same problem as you, As i can see from Sean you have the solution, but I don't understand the way to send the information to your BDM. Could you send me your example please?. thanks in advance.

Notifications