[RESOLVED] Problem with update mysql
This has nothing to do with bonitasoft, as such...
however I will answer it...and you need to learn some SQL! :)
but first a handy tip, never just write your SQL like that. Always switch the editor and return the string from a script. this will allow you to do some debugging etc. using the logs.
So your code should go something like this...
import java.util.logging.Logger;
import java.lang.StringBuilder;
int dI = 0;
boolean debug = true; //set false when not debugging.
ProcessRuntimeAPI processRuntimeAPI = apiAccessor.getProcessAPI();
String processName = processRuntimeAPI.getProcessInstance(processInstanceId).getName();
//set the name of the routine
String thisTrace = " "+processName+ " Uppdate DB::updateScript: "; // change Uppdate DB::updateScript to the name of the step and script
Logger logger= Logger.getLogger("org.bonitasoft");
if(debug){dI++; logger.severe(dI+thisTrace+"Trace Start");}
//TODO - Code goes in here - START
StringBuilder updateString = new StringBuilder();
updateString.append("UPDATE demo SET ");
updateString.append("titre="+titre+", ");
updateString.append("description="+descriptionup+", ");
updateString.append("dateajout="+dateup+" ");
updateString.append("WHERE Id="+idup+";");
//TODO - Code goes in here - END
if(debug){dI++; logger.severe(dI+thisTrace+"updateString: "+updateString.toString());}
if(debug){dI++; logger.severe(dI+thisTrace+"Trace End");}
return updateString.toString();
The String builder will build a string from the data in the appends and this will be returned to form the actual statement.
HOWEVER this will only return what you've already got and it will still fail....
The thing about SQL is it needs quotes on strings when updating...
so your string needs to be built up as follows:
However this will also not work as single and double quotes are part of the java/groovy specification and need special handling in this case.
You need to escape the ones you want to keep. So the actual statement will become:
this will now generate the required string for updating the database.
NOTE: I have given you all the code and the solution to fix the update string...now you have to do it.
Another Hint...
When using code like the above this will print out the information on the Log, this can be found in Help-> Bonita BPM Engine Log.
You will then be able to search for Uppdate DB::updateScript which will allow you to see what the code is generating so you can fix it.
Hope this helps and welcome to the crowd,
regards
Seán
PS: If this reply answers your question, please mark a resolved.
PPS: Third hint. Not all fields require the single quotes :) I'll leave that to you...
Comments
Hi,
I'm new and learn by myself and I don't understand all clearly...
1) I have open the Update Db connection out and put the script you give me
2) I have change that: String thisTrace = " "+processName+ "Update DB"; The name of the connexion and the name of the step
3) I run and it don't work, may I have some more hint or an update of my example.
Regards
BLE
Hi,
4) Yes i have change it
4.1) It change titre and description but problem with the format date!!!
5) the Log :
Caused by: org.bonitasoft.engine.connector.exception.SConnectorException: java.util.concurrent.ExecutionException: org.bonitasoft.engine.connector.exception.SConnectorException: org.bonitasoft.engine.connector.ConnectorException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 01 12:00:00 CET 2016' for column 'dateajout' at row 1
at org.bonitasoft.engine.connector.impl.ConnectorExecutorImpl.execute(ConnectorExecutorImpl.java:125)
at org.bonitasoft.engine.core.connector.impl.ConnectorServiceImpl.executeConnectorInClassloader(ConnectorServiceImpl.java:268)
... 14 more
Caused by: java.util.concurrent.ExecutionException: org.bonitasoft.engine.connector.exception.SConnectorException: org.bonitasoft.engine.connector.ConnectorException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 01 12:00:00 CET 2016' for column 'dateajout' at row 1
at java.util.concurrent.FutureTask.report(Unknown Source)
at java.util.concurrent.FutureTask.get(Unknown Source)
at org.bonitasoft.engine.connector.impl.ConnectorExecutorImpl.getValue(ConnectorExecutorImpl.java:149)
at org.bonitasoft.engine.connector.impl.ConnectorExecutorImpl.execute(ConnectorExecutorImpl.java:122)
... 15 more
Caused by: org.bonitasoft.engine.connector.exception.SConnectorException: org.bonitasoft.engine.connector.ConnectorException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 01 12:00:00 CET 2016' for column 'dateajout' at row 1
at org.bonitasoft.engine.core.connector.impl.SConnectorAdapter.execute(SConnectorAdapter.java:75)
at org.bonitasoft.engine.connector.impl.ConnectorExecutorImpl$ExecuteConnectorCallable.call(ConnectorExecutorImpl.java:205)
at org.bonitasoft.engine.connector.impl.ConnectorExecutorImpl$ExecuteConnectorCallable.call(ConnectorExecutorImpl.java:176)
at java.util.concurrent.FutureTask.run(Unknown Source)
... 3 more
Caused by: org.bonitasoft.engine.connector.ConnectorException: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 01 12:00:00 CET 2016' for column 'dateajout' at row 1
at org.bonitasoft.connectors.database.Database.executeCommand(Database.java:77)
at org.bonitasoft.connectors.database.jdbc.JdbcConnector.executeSingleQuery(JdbcConnector.java:188)
at org.bonitasoft.connectors.database.jdbc.JdbcConnector.execute(JdbcConnector.java:94)
at org.bonitasoft.engine.core.connector.impl.SConnectorAdapter.execute(SConnectorAdapter.java:73)
... 6 more
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: 'Mon Feb 01 12:00:00 CET 2016' for column 'dateajout' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4118)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:899)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:737)
at org.bonitasoft.connectors.database.Database.executeCommand(Database.java:75)
... 9 more
Regards
BLE
Hi,
I come back for some hint! I don't know a lot in Java but I try to change the format date.
I have look at : http://www.mkyong.com/java/how-to-convert-string-to-date-java/
If I show the result in a textfield or if I look in the log engine the date is like that : Tue Feb 16 12:27:00 CET 2016 and I what that's 2016-02-16 12:27:00
So I try with this code :
//On the top I add this
import java.text.SimpleDateFormat
//TODO - Code goes in here - START
SimpleDateFormat newDateFormat = new SimpleDateFormat("EEE MMM dd hh:mm:ss yyyy");
Date myDate = newDateFormat.parse(dateup);
newDateFormat.applyPattern("yyyy-MM-dd hh:mm:ss")
String myDateString = newDateFormat.format(myDate);
StringBuilder updateString = new StringBuilder();
updateString.append("UPDATE demo SET ");
updateString.append("titre='"+titreup+"', ");
updateString.append("description='"+descriptionup+"', ");
updateString.append("dateajout='"+myDatestring+"' ");
updateString.append("WHERE Id='"+idup+"';");
//TODO - Code goes in here - END
It doesn't work!
But if in the component date I don't put a display format it works ;) but I need the hours at the end of the date.
I just try this:
Date thedate = dateup;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String newDate = format.format(thedate);
The date update correctly but the hour is set to 12:00:00 and not the time now.
Regards
BLE
Hi,
I join you the file with the format date ok and the operation for the database Select from ID => Read information and Update informations.
regards
BLE
Comments
Can you give me your code for the insert ? i'm stuck on this , i cant insert my java.list
Hi,
See my last post...
Regards
BLE