Problem insert data into mysql
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
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+"')";
Comments
so it works...?
Is there more to the problem?
regards
Seán
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.
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.
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):
//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
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 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
i made a video which may or may not help....
Comments
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
Comments
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.