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

[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.

[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]

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]

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]

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.

[jllort]

All the code should go into the execute method.

[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')

[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]

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]

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]

OK thank you very much

[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.

[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]

OK, thank you very much, it works.

[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();
  
  }

}