Hi,
I try to insert data in a database mysql, but i don’t arrive to do it!
Can somebody explain step by step how to do it?
Use Bonitasoft 7.1.5 and mysql
Regards
BLE
1 Like
i made a video which may or may not help…
in my groovy script i did this to make it work , but only with static variable :
import java.text.SimpleDateFormat;
SimpleDateFormat formatter = new SimpleDateFormat(“yyyy-MM-dd”);
String date1 = formatter.format(date);
return “INSERT INTO totaldb.diagnosticdom (ID,NAME, SECONDNAME, DATE, INFORMATIONSUP) VALUES ('”+id+“', '”+name+“‘,’”+secondname+“‘,’”+date"‘,’“+informationSup+”')";
Hi Sean,
thank’s for reply…the field quantita should not be a quantity but a description of the dosage. For the time I will try to convert the format as explained in various posts but the real problem is that when I try to enter my java.util.List variable in mysql I have this result in the log file:
INSERT INTO scripts drugs (description, dosage, data_inizio_terapia, data_fine_terapia, codice_paz) VALUES ( '[start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity : dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 EDT 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 EDT 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘, (select codice_paz from patients where nome_paz =’ monica ‘and cognome_paz =’ megaro ')) connectorDefinitionName = inserimentoFarmaci] failed. The failure will be handled.
ie the repetition of my list 4 times and I could use a method to take from the list only the 4 elements: start, latedrugs, quantity. I do not know if I was clear
Regards
Monica
Hi,
Can you brief, what all you have tried till now, may be further I can assist you.
Regards,
Vivek
I have same issue , but i could only insert with static variable, not with a java.list.util.
Hi,
I have try your video, but nothing work…at 4:58 I have an error with resultset.
I’m continuing looking for a solution.
Regards
BLE
I would hard code the string to talk to the database first and get the “test” button to work that way.
The question says insert and the example shown is select, which is it?
You don’t show the groovy code taking the resultset to a list… (Scripting mode resultset)
And have you looked at the engine log to see what errors are generated? You should post that bit here also.
regards
Seán
Hi I have the same problem…I have a variable java.util.list in which I store my complex data of contract…the result is
listaFarmaci=[{inizio=Tue May 10 02:00:00 CEST 2016, fine=Sat May 14 02:00:00 CEST 2016, farmaci=tenormin, quantita=1compressa}] but I have problem to insert that in mysql db. I can’t take every single element of the list
Someone can help me?
Regards Monica
What do you mean I can’t take every element of the list? Surely you have to add every element if it is required for the database?
There are a couple of things you might want to consider though:
You have to convert the times to something that MySql will recognize (yyyy-mm-dd hh:mm:ss). Note from the MySql manual
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)
So looking at your date/times you are using CEST and not UTC so your database fields should be DATETIME.
I also note that quantita = 1compressa. You may want to check this field as it’s not just a quantity…
Otherwise the information above should help you with how to insert into the database.
if you can be a bit clearer in what you want to do…
regards
Seán
Hi Sean,
thank’s for reply…the field quantita should not be a quantity but a description of the dosage. For the time I will try to convert the format as explained in various posts but the real problem is that when I try to enter my java.util.List variable in mysql I have this result in the log file:
INSERT INTO scripts drugs (description, dosage, data_inizio_terapia, data_fine_terapia, codice_paz) VALUES ( '[start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity : dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 EDT 2016, late: Sat May 14 02:00:00 CEST 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘,’ [start: Tue May 10 02:00:00 CEST 2016, late: Sat May 14 02:00:00 EDT 2016, drugs: hwirhjp, quantity: dkwjrfpq] ‘, (select codice_paz from patients where nome_paz =’ monica ‘and cognome_paz =’ megaro ')) connectorDefinitionName = inserimentoFarmaci] failed. The failure will be handled.
ie the repetition of my list 4 times and I could use a method to take from the list only the 4 elements: start, latedrugs, quantity. I do not know if I was clear
Regards
Monica
so it works…?
Is there more to the problem?
regards
Seán
yes , when i try to do this with java.util.list it works but it adds all the rows to one row, exemple :
in my select form i select : John which is already on my database ( in my database table i have ''in my name row " john , steve gerard for exemple.)
And when i put submit in my database i get this in one row
[[john], [steve] , [gerard]]
what can be the issue ?
i hope i’ve been clear.
Yes, clear.
what it looks like you are doing is using an SQL connector, correct? The SQL connector doesn’t really work well with a list when using INSERT.
What you have to do is chain the return SQL meaning multiple inserts at the same time built up from the List items.
StringBuilder inserts = new StringBuilder();
for (each item in list){
inserts.append(“INSERT INTO totaldb.diagnosticdom (ID,NAME, SECONDNAME, DATE, INFORMATIONSUP) VALUES ('”+id+“', '”+name+“‘,’”+secondname+“‘,’”+date"‘,’“+informationSup+”');");
}
return inserts.toString();
this will give you something like this:
INSERT INTO totaldb.diagnosticdom (a,b, c, d, e) VALUES (“a”,“b”,“c”,“d”,“e”);INSERT INTO totaldb.diagnosticdom (a,b, c, d, e) VALUES (“a”,“b”,“c”,“d”,“e”);INSERT INTO totaldb.diagnosticdom (a,b, c, d, e) VALUES (“a”,“b”,“c”,“d”,“e”);
which should work to add the three rows as intended.
regards
Seán
PS: If this reply answers your question, please mark a resolved.
sorry but the groovy code didnt work for me it gives me an error in the for (each.
So if this work it will submit the value i selected from my database ? Output should be
john
steve
gerard
Really? The code failed…
I hope you didn’t just copy my code and hoped it would work 
I only gave you a sample of what to do - you have to change it to your needs and make it work…
a real for looks like(for a list of strings called names):
for (String name : names){
//do unto string name...
}
I can guide you to the solution, but I’m not writing the whole thing for you.
yes the output should be like you have summarized.
regards
Seán
Sorry i’m new to bonitasoft and i know a little on dev. was only able to add one row. here is my code in a test process , could you tell me what the problem is in the script ?
StringBuilder inserts = new StringBuilder();
for (String n : name1){
inserts.append(“INSERT INTO test.demo1 (name1) VALUES ('”+n+“');”);
}
return inserts.toString();
this isn’t actually Bonitasoft, this is basic Java code…you need to learn that too.
Includes debug code so you can see the stuff on the log…
The code you want is:
import java.lang.StringBuilder;
import org.bonitasoft.engine.api.ProcessRuntimeAPI;
import java.util.logging.Logger;
int dI = 0;
boolean debug = true; //set to false to stop logging
ProcessRuntimeAPI processRuntimeAPI = apiAccessor.getProcessAPI();
String processName = processRuntimeAPI.getProcessInstance(processInstanceId).getName();
//set the name of the routine
String thisTrace = " "+processName+ " thisModuleName: "; //change thisModuleName to your name so you can search the log for it
Logger logger= Logger.getLogger(“org.bonitasoft”);
if(debug){dI++; logger.severe(dI+thisTrace+“Trace Start”);}
//TODO - Code goes in here - START
//set example array
String names = [“john”, “steve”, “gerard”];
StringBuilder inserts = new StringBuilder();
for (String n : names){
if(debug){dI++; logger.severe(dI+thisTrace+“n: “+n);};
inserts.append(“INSERT INTO test.demo1 (name1) VALUES ('”+n+”');”);
}
//TODO - Code goes in here - END
if(debug){dI++; logger.severe(dI+thisTrace+"full Insert: "+inserts.toString());}
if(debug){dI++; logger.severe(dI+thisTrace+“Trace End”);}
return inserts.toString();
try this and see what happens, the log can be found at Help->Bonita BPM Engine Log
regards
Seán