Bonita 7.1.5, MySQL insert query?
Hi,
I am new to bonita bpm, I am using v7.1.5 running on centos7+open jdk 1.8+ mysql 5.6, I am having tough time to find information about inserting data into database. the following is the condition..
I have a complex process variable (custom java object, with 5 attributes), the process update these attributes values, now I need to insert those attributes in to database table (each attribute is a column).
I got some info from bonita users..... like ${var_name}, but my requirement is object.getVar(), how to script this in to sql?
"insert into.........values('${object-name.getVar1()}', '${object-name.getVar2()}', '${object-name.getVar3()}')"
thanks for reading...
Here's my Answer as requested... :)
See link http://community.bonitasoft.com/answer-bonita-715-mysql-insert-query-0#comment-5966
regards
Seán
Sean, thank you so much I got fixed my error, this is my mistake in business variable initialization step... below is the corrected biz variable init script....
import org.bonitasoft.engine.bpm.process.ProcessInstance;
import com.company.model.Bag;
ProcessInstance processInstance = apiAccessor.getProcessAPI().getProcessInstance(processInstanceId);
Bag newBag = new Bag();
newBag.setProjectId(poolBagInput.get("projectId"));
newBag.setBagId(poolBagInput.get("bagId"));
newBag.setVersion(poolBagInput.get("version"));
newBag.setValidateResult(poolBagInput.get("validateResult"));
newBag.setInfo(poolBagInput.get("info"));
return newBag;
============================================
below is the script for inserting....
def script = ""
script = "INSERT INTO bag (projectId, bagId, version, validateResult, info) VALUES (' "+poolBag.getProjectId()+" ',' "+poolBag.getBagId()+" ',' "+poolBag.getVersion()+"', '"+poolBag.getValidateResult()+"', '"+poolBag.getInfo()+"')"
=====================
Result
mysql> select * from bag;
+-------------+-------------+------------+----------------+-----------+
| projectId | bagId | version | validateResult | info |
+-------------+-------------+------------+----------------+-----------+
| test data | test data | test data | test data | test data |
+-------------+-------------+------------+----------------+-----------+
1 row in set (0.00 sec)
I tried the following script, I am not sure whets wrong in this script...
def script = ""
script = "INSERT INTO bag (projectId, bagId, version, validateResult, info) VALUES ('"+poolBag.getProjectId()+" ',' "+poolBag.getBagId()+" ',' "+poolBag.getVersion()+"', '"+poolBag.getValidateResult()+"', '"+poolBag.getInfo()+"')"
my result is as follows..it insert the first column value but rest all insert as null.....any help would be much appreciated...
| hfghfgh | null | null | null | null |
| prabakar | null | null | null | null |
| scriptInsert | null | null | null | null |
| hfghgfhgf | null | null | null | null |
+---------------------------+-----------------------+-------------------------+--------------------------------+----------------------+
..
Comments
Well, are you sure the data is there?
before the def script = ""
why not print everything out to make sure there is a value there?
using my previous debug code
if(debug){dI++; logger.severe(dI+thisTrace+"poolBag.getBagId(): "+poolBag.getBagId());}
if(debug){dI++; logger.severe(dI+thisTrace+"poolBag.getVersion(): "+poolBag.getVersion());}
if(debug){dI++; logger.severe(dI+thisTrace+"poolBag.getValidateResult(): "+poolBag.getValidateResult());}
if(debug){dI++; logger.severe(dI+thisTrace+"poolBag.getInfo(): "+poolBag.getInfo());}
Sometimes the easiest thing to miss is the simple fact that there is nothing there!
regards
Seán
it is not reading the business variable attribute value (bizVar.getAttr1()) instead it is inserting the whole word as follows...
mysql> select * from bag;
+---------------------------+-----------------------+-------------------------+--------------------------------+----------------------+
| projectId | bagId | version | validateResult | info |
+---------------------------+-----------------------+-------------------------+--------------------------------+----------------------+
| ${poolBag.getProjectId()} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBag.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBag.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBagInput.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBagInput.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBagInput.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
| ${poolBagInput.projectId} | ${poolBag.getBagId()} | ${poolBag.getVersion()} | ${poolBag.getValidateResult()} | ${poolBag.getInfo()} |
+---------------------------+-----------------------+-------------------------+--------------------------------+----------------------+
Comments
It looks fine but have you looked at what is generated in the script that will hep you..
rather than using string for the Insert, build it in groovy, much better control and you can log what the results are...
Here is the code I use...
import java.util.logging.Logger;
int dI = 0;
boolean debug = true; //make false for no logging.
ProcessRuntimeAPI processRuntimeAPI = apiAccessor.getProcessAPI();
String processName = processRuntimeAPI.getProcessInstance(processInstanceId).getName();
//set the name of the routine
String thisTrace = " "+processName+ " myModuleName: "
Logger logger= Logger.getLogger("org.bonitasoft");
if(debug){dI++; logger.severe(dI+thisTrace+"Trace Start");}
//TODO - Code goes in here - START
//TODO - Code goes in here - END
if(debug){dI++; logger.severe(dI+thisTrace+"Trace End");}
return whatEverIWantToReturn;
regards
Seán