How to read data using db connector into business variable?
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:
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:
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.
I found a solution and here is the groovy snippet for reference:
List myObjects = new ArrayList();
def myObject = myObjectDAO.newInstance();
myObject.ID = resultset.getInt("ID");
myObject.Status = resultset.getString("Status");
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?