Saving my workflow data to a table in my Mysql database.

OpenKM a de nombreuses fonctionnalités interessantes, mais demande un minimum de configuration pour être pleinement exploité.

Moderator: dedisoft

Forum rules
Avant de poser une question, merci de regarder la documentation du wiki ou d'utiliser la fonction recherche du forum. Et rappelez vous que nous n'avons ni boule de cristal ni possibilité de lire dans les pensées, aussi pensez à spécifier quelle version d'OpenKM vous utilisez ainsi que la version du navigateur web et du système d'exploitation. Pour de plus amples informations lisez Comment reporter un bug efficacement (anglais).
Post Reply
kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Saving my workflow data to a table in my Mysql database.

Post by kouadio »

Please, I need assistance saving my workflow data to a table in mydatabase.You will find in the attached file the maximum information on my form and my database.Thanks already for your help.
Attachments
OpenKM Workflow Mysql.pdf
(347.29 KiB) Downloaded 25 times

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

In the ActionHandler - transaction - you must collect all the data from the context variables and then with OpenKM legacyDAO method you can insert data in your table:

In this documentation section you can see how to capture variable https://docs.openkm.com/kcenter/view/wf ... -node.html, in your case you should cast to (Input) or (Select) based on the type of the object you set in the XML.

Once you have all the data you can insert into your table with LegacyDAO method: https://docs.openkm.com/kcenter/view/ok ... cydao.html

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

Thank you for your prompt response. But as a beginner, I would like to know what for example the line option.setValue (row.get (0)) corresponds to? Thank you for everything.

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

The executeSQL method return a Object of type List<List<String>>, where each row in the results is a list of values. In the sample row.get(0) is the value of DMT_VIRTUAL_COLUMN column for each row in the list of results.

I suggest try some known query from scripting to check the returned code ( Administration > Scripting )

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

Having no knowledge in java, I have trouble understanding the proposed example.
I worked a little in the tutorials and I managed to create this program as an attached file.
I integrated it into my workflow which runs without error. By no record is made in my database. I really don't know which one I faulted.
Thanks for your help.
Attachments
Insert_Mysql.pdf
(3.44 KiB) Downloaded 27 times

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

All the code should go into the execute method.

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

I don't understand what you are saying.
Attached my three code files: xml file, forms and java. In fact, I just wanted to do a test inserting this information in my database.
INSERT INTO WORKFLOW_ARCHIVAGE VALUES (1, 1, 'Informatique', 'Usertest', 'Test')
Attachments
Insert_Mysql.pdf
(3.44 KiB) Downloaded 23 times
Pret documentaire.pdf
(2.73 KiB) Downloaded 22 times
forms.pdf
(7.85 KiB) Downloaded 23 times

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

AS I explained, you are implementing an Interface of ActionHandler. The code into the execute method is what will be executed but your execute method is always empty.

Wrong

Code: Select all

@Overridepublic void execute(ExecutionContext executionContext) throwsException {// TODO Auto-generated method stub}
Should have the code in this manner:

Code: Select all

@Overridepublic void execute(ExecutionContext executionContext) throwsException {
Connection conn = null; 
Statement stmt = null;   
try{      
  //STEP 2: Register JDBC driver
  Class.forName("com.mysql.jdbc.Driver");
  //STEP 3: Open a connection
  conn = DriverManager.getConnection(DB_URL, USER, PASS) 
  }
  //etc...
}

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

Thank you for everything. Finally, here is my program which in my opinion works well.

Code: Select all

package com.openkm.workflow.archivage;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import org.jbpm.graph.def.ActionHandler;
import org.jbpm.graph.exe.ExecutionContext;

public class InsertMysql_db implements ActionHandler {

  /**
	 * 
	 */
	private static final long serialVersionUID = 1L;

public void execute(ExecutionContext executionContext) throws Exception {
 
	String myDriver = "com.mysql.jdbc.Driver";
      String myUrl = "jdbc:mysql://localhost:3306/okmdb";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "kouadio", "1234");
      
      Statement st = conn.createStatement();

         st.executeUpdate("INSERT INTO WORKFLOW_ARCHIVAGE (Ordre, Date, Direction_Service, Ordonnateur, Nom_document, Justificatif_sortie, Description_document, Duree_probable, Observations) VALUES (3, 1, 'Informatique', 'User Test', 'Test', 'Un test', 'Essai', '2j', 'RAS')");
 
      conn.close();
  
  }

}

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

I have updated documentation with your sample https://docs.openkm.com/kcenter/view/wf ... ransaction I think might be useful for other users, thanks

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

OK thank you very much

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

I wanted to improve my programme by retrieving the data from my form and inserting them directly into the database. The java code is as follows:

Code: Select all

package com.openkm.workflow.archivage;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import org.jbpm.graph.def.ActionHandler;
import org.jbpm.graph.exe.ExecutionContext;

import com.openkm.bean.form.*;

public class InsertionMysql implements ActionHandler {

  /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	//int date = 10;
	//String direction = "informatique";
	//String ordonnateur = "KOUADIO";
	//String document = "stage";
	//String justificatif = "Rappoart de stage";
	//String description = "Stage pratique";
	//String duree = "2 mois";
	//String observations = "RAS";
	
 
@Override
public void execute(ExecutionContext executionContext) throws Exception {
	
	String date = (String)executionContext.getContextInstance().getVariable("date");
	Select direction = (Select) executionContext.getContextInstance().getVariable("direction");
	String ordonnateur = (String)executionContext.getContextInstance().getVariable("ordonnateur");
	String document = (String)executionContext.getContextInstance().getVariable("document");
	String justificatif = (String)executionContext.getContextInstance().getVariable("justificatif");
	String description = (String)executionContext.getContextInstance().getVariable("description");
	String duree = (String)executionContext.getContextInstance().getVariable("duree");
	String observations = (String)executionContext.getContextInstance().getVariable("observations");
	
	String myDriver = "com.mysql.jdbc.Driver";
      String myUrl = "jdbc:mysql://localhost:3306/okmdb";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "kouadio", "1234");
      
      Statement st = conn.createStatement();
      String sql = "INSERT INTO WORKFLOW_ARCHIVAGE (Ordre, Date, Direction, Ordonnateur, document, Justificatif, Description, Duree, Observations) VALUES (Null, " + date+ ", '" + direction + "', '" + ordonnateur+ "', '" + document+ "', '" + justificatif+ "', '" + description+ "', '" + duree +"', '" + observations+"')";

      st.executeUpdate(sql);
 
      conn.close();
  
  }

}
However, when I run it, I get the following error:


Thank you for helping me.
Attachments
Erreur insertion mysql.PNG
Erreur insertion mysql.PNG (9.57 KiB) Viewed 262 times

jllort
Moderator
Moderator
Posts: 11240
Joined: Fri Dec 21, 2007 11:23 am
Location: Sineu - ( Illes Balears ) - Spain
Contact:

Re: Saving my workflow data to a table in my Mysql database.

Post by jllort »

The error is clear you are trying to convert a Object of type Input into a String, that will not working. You must cast as (Input) and then the value with getValue() method.

kouadio
Senior Boarder
Senior Boarder
Posts: 76
Joined: Wed Jan 09, 2019 9:26 am

Re: Saving my workflow data to a table in my Mysql database.

Post by kouadio »

OK, thank you very much, it works.

Post Reply