Open Source Document Management System | OpenKM

Because information matters
https://forum.openkm.com/

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

https://forum.openkm.com/viewtopic.php?t=23535

Page 1 of 1

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

PostPosted:Wed Aug 12, 2020 1:13 pm
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.

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

PostPosted:Fri Aug 14, 2020 8:18 am
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

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

PostPosted:Wed Aug 19, 2020 6:59 pm
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.

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

PostPosted:Fri Aug 21, 2020 8:09 am
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 )

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

PostPosted:Fri Aug 21, 2020 4:40 pm
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.

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

PostPosted:Sun Aug 23, 2020 6:01 am
by jllort
All the code should go into the execute method.

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

PostPosted:Tue Aug 25, 2020 2:08 pm
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')

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

PostPosted:Sat Aug 29, 2020 7:51 am
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...
}

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

PostPosted:Thu Sep 24, 2020 3:58 pm
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();
  
  }

}

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

PostPosted:Fri Sep 25, 2020 6:38 pm
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

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

PostPosted:Wed Sep 30, 2020 9:52 pm
by kouadio
OK thank you very much

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

PostPosted:Fri Oct 16, 2020 12:30 pm
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.

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

PostPosted:Sat Oct 17, 2020 7:40 am
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.

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

PostPosted:Mon Oct 19, 2020 1:14 pm
by kouadio
OK, thank you very much, it works.

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

PostPosted:Wed Dec 02, 2020 3:17 pm
by kouadio
I have improved my program which I am posting here in case it could be useful to others.
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 {
	
	Input date = (Input)executionContext.getContextInstance().getVariable("date");
	Select direction = (Select) executionContext.getContextInstance().getVariable("direction");
	Select requerant = (Select)executionContext.getContextInstance().getVariable("requerant");
	Input document = (Input)executionContext.getContextInstance().getVariable("document");
	Input justificatif = (Input)executionContext.getContextInstance().getVariable("justificatif");
	TextArea description = (TextArea)executionContext.getContextInstance().getVariable("description");
	Input duree = (Input)executionContext.getContextInstance().getVariable("duree");
	TextArea observations = (TextArea)executionContext.getContextInstance().getVariable("observations");
	Input avisarchiviste = (Input)executionContext.getContextInstance().getVariable("avisarchiviste");
	Input dateretour = (Input)executionContext.getContextInstance().getVariable("dateretour");
	// Input relance = (Input)executionContext.getContextInstance().getVariable("relance");
	Input noteimportante = (Input)executionContext.getContextInstance().getVariable("noteimportante");
	Input dateeffectiveretour = (Input)executionContext.getContextInstance().getVariable("dateeffectiveretour");
	Input retard = (Input)executionContext.getContextInstance().getVariable("retard");
	Input commentaire = (Input)executionContext.getContextInstance().getVariable("commentaire");

	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 sql1 = "INSERT INTO WORKFLOW_ARCHIVAGE (Ordre, Date, Direction, Requerant, document, Justificatif, Description, Duree, Observations) VALUES (Null, CURRENT_DATE(), '" + direction.getValue() + "', '" + requerant.getValue()+ "', '" + document.getValue()+ "', '" + justificatif.getValue()+ "', '" + description.getValue()+ "', '" + duree.getValue() +"', '" + observations.getValue()+"')";
      String sql2 = "INSERT INTO WORKFLOW_ARCHIVAGE (Ordre, Date, Direction, Requerant, document, Justificatif, Description, Duree, Observations, avisarchiviste, dateretour, noteimportante, dateeffectiveretour, retard, commentaire) VALUES (Null, CURRENT_DATE(), '" + direction.getValue() + "', '" + requerant.getValue()+ "', '" + document.getValue()+ "', '" + justificatif.getValue()+ "', '" + description.getValue()+ "', '" + duree.getValue() +"', '" + observations.getValue()+"', '"+ avisarchiviste.getValue()+"', '"+ dateretour.getValue()+"', '"+ noteimportante.getValue()+"', '"+ dateeffectiveretour.getValue()+"', '"+ retard.getValue()+"', '"+ commentaire.getValue()+"')";
      
  //    if (executionContext.getContextInstance().getVariable("dateeffectiveretour") == null) {
    	  
   // 	  st.executeUpdate(sql1);
   //   }
   //   else {
    	  st.executeUpdate(sql2);  
   //   }
      
 
      conn.close();
  
  }

}
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited