Trying to get data from Postgres into BDM

Q&A
1

My goal - read the entire postgres table customers and put it into a BDM table.

BOS file, log output, and Postgres table definition are in the “CASE1” folder in my Google Drive public folder. I know that the test is nt BPMN2 compliant.

https://drive.google.com/open?id=0B7ig1ZvVKJMid3BObkdSYkxkMWM

I’ve created two BDM tables. One a working table, the other a reference table (bdocustomer and bdoPGcustomer respectively).

I have a connector in step one that successfully retrieves that data from Postgres.

I have a process variable defined - pvar_PGcustomer_temp - that …should… receive the data from the connector. I think.

The form, when this works, right now will just display the contents of the table that will become the working file - bdocustomer, and the retrieved Postgres data - bdoPGcustomer.

To achieve my goal, the connector - out - will write the temporary process variable contents to the bdoPGcustomer table.

Thanks… I’d be enjoying this process a lot more if I weren’t under-the-gun to deliver.

2

I downloaded your bos file, updated postgresql host etc, and got same error as you have.

I dig into your Connector In code, and found you are using Groovy script to retrieve data, then converted all records into a single String. However, in next page of connector setup, you can find this is expecting a list of list object. Application tries to convert String obj to list obj, but fails to find a suitable converter, therefore you got ConversionException exception.

Actually there’s no needs to write any Groovy code to retrieve data. Just use a constant SQL string is good enough to your case.

I made some changes on top of your code, using Graphical mode. loc_PGJSONcustomer is changed to a java.util.list with “Is multiple” selected. Of course Default Value of this local parameter is removed.

After start a case, you can use REST API to check local variable is properly set:
http://localhost:8080/bonita/API/bpm/activityVariable/40042/loc_PGJSONcustomer

{“name”:“loc_PGJSONcustomer”,“description”:null,“transientData”:false,“className”:“java.util.Collection”,“containerType”:“ACTIVITY_INSTANCE”,“tenantId”:0,“tenantId_string”:“0”,“id”:10035,“id_string”:“10035”,“containerId”:40042,“containerId_string”:“40042”,“value”:[[1,“a”,“a”,“a”,null,null,null,null,null,null,null],[2,“b”,“b”,“b”,null,null,null,null,null,null,null]]}

You can download bos file from: https://drive.google.com/file/d/0B4PSuBFeVF-aLUhIZFFCQVlQLWM/view?usp=sharing

BTW, although I made it works, I have doubt why you would like to retrieve whole table data and keep in local variable or BDM? It is not good approach.

If you want to refer to some data, you could always refer to necessary data on the fly using web service or REST service, there’s no need to keep data alone with case instance if only for reference purpose.

3

Re: your comment “BTW, although I made it works, I have doubt why you would like to retrieve whole table data and keep in local variable or BDM? It is not good approach.”

AGREED!

I want to put the data into the bdoPGcustomer table.

I see you made it work but I still can’t copy the postgres table into the BDO table.

What do I have to do?

4

Here is a sample shows how to copy data into BDO table:

https://drive.google.com/file/d/0B4PSuBFeVF-aWXlJX2pxNDBZZ1U/view?usp=sharing

I added an extra step before human task to copy data from table into pool BDM variable, and save to table automatically. If you want to manipulate the data before saving, you could refer to contract variable in your form, copy data from local variable to contract variable using java script and REST API, and set changes to BDM variables in Operation.

Here is result we can find using REST API:
Check which persistent ids are created for BDM data and bind to case:
/API/bdm/businessDataReference/4004/pool_bdoPGcustomer:
Result: {“name”:“pool_bdoPGcustomer”,“type”:“com.company.model.bdoPGcustomer”,“link”:“API/bdm/businessData/com.company.model.bdoPGcustomer/findByIds?ids=33,34”,“storageIds”:[33,34],“storageIds_string”:[“33”,“34”]}

Check details of BDM data:
/API/bdm/businessData/com.company.model.bdoPGcustomer/33
Result: {“persistenceId”:33,“persistenceId_string”:“33”,“persistenceVersion”:0,“persistenceVersion_string”:“0”,“customerid”:1,“customerid_string”:“1”,“customer”:“a”,“password_open”:“a”,“password_encrypted”:“a”,“buyfor1”:null,“buyfor1_string”:null,“buyfor2”:null,“buyfor2_string”:null,“buyfor3”:null,“buyfor3_string”:null,“buyfor4”:null,“buyfor4_string”:null,“buyfor5”:null,“buyfor5_string”:null,“lastchanged_date”:null,“created_date”:null}

5

Thanks very much!