Hi,
I can insert data in my database but I can’t update the value!!!
Can someone have an idea?
I join the .bos file
Link file
Database : demo
fields : Id, titre, description, date
Regards
BLE
Hi,
I can insert data in my database but I can’t update the value!!!
Can someone have an idea?
I join the .bos file
Link file
Database : demo
fields : Id, titre, description, date
Regards
BLE
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 org.bonitasoft.engine.api.ProcessRuntimeAPI;
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:
updateString.append("titre='"+titre+"', ");
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:
updateString.append("titre=\'"+titre+"\', ");
this will now generate the required string for updating the database.
titre='thisismytitre',
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…
Thank’s for this advise, I try that tomorrow…
BLE
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
Hi,
I’m new and learn by myself and I don’t understand all clearly…
Regards
BLE
updateString.append("UPDATE demo SET ");
updateString.append("titre="+titre+", ");
updateString.append("description="+descriptionup+", ");
updateString.append("dateajout="+dateup+" ");
updateString.append("WHERE Id="+idup+";");
to be
updateString.append("UPDATE demo SET ");
updateString.append("titre='"+titre+"', ");
updateString.append("description='"+descriptionup+"', ");
updateString.append("dateajout='"+dateup+"' ");
updateString.append("WHERE Id="+idup+";");
?
What does it say, if anything?
Sorry - but learning can be an experience.
regards
Seán
Hi,
Yes i have change it
4.1) It change titre and description but problem with the format date!!!
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
OK,
do you know what the date format should be for mySQL? Here is a link that might help…
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
so what have you done to learn about changing the date format? There are loads of sites to help you with this…yes I could do it for you but that’s not learning.
Show us where you are in doing it and then we will help, but this is something you can do…
regards
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.
<div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> online backup storage
regards
BLE