How to execute multiple dml commands in oncloud joget

 
1
0
-1

Hi guys,

i´m want to execute multiple dml commands (updates, inserts) in oncloud joget.


Im trying with "Configure Database Update Tool", but always return an error.

This queries sequency  run on mysql workbanch local normally:

update app_fd_ordemservico set c_cboStatus = 'Paga' WHERE id = '#form.app_fd_pagamento_contrato.cboOs#';

update app_fd_ordemservico set c_cboStatus = 'Paga' WHERE id = '#form.app_fd_pagamento_contrato.cboOs#';


You have any sample ?


Thanks,

Paulo Nascimento

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      Hi Anders.

      when i attempt to use sample bean shell adopted to my tables, i´m receive error message:


      "ERROR 05 Sep 2022 16:24:57 org.joget.apps.app.lib.BeanShellTool - jwc_paulomnasc : Error executing script

      2224 bsh.EvalError: Sourced file: inline evaluation of:  ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.R . . . '' : Undefined argument: element "


      Code .java:


      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import javax.sql.DataSource;
      import org.joget.apps.app.model.AppDefinition;
      import org.joget.apps.app.service.AppService;
      import org.joget.apps.app.service.AppUtil;
      import org.joget.apps.form.model.Element;
      import org.joget.apps.form.model.FormData;
      import org.joget.apps.form.model.FormRow;
      import org.joget.apps.form.model.FormRowSet;
      import org.joget.apps.form.model.FormStoreBinder;
      import org.joget.apps.form.service.FormUtil;
      import org.joget.plugin.base.PluginManager;
      import org.joget.commons.util.LogUtil;

      /*Comando legado usado no Database Update Tool 
      update app_fd_conta_contabil set c_saldo_conta = c_saldo_conta - FORMAT(#form.app_fd_transacao.id_valor_transacao#, 2)
      where ID = '#form.app_fd_transacao.cboCtContabil#'
      */


      //LÓGICA: Irá gravar app_fd_conta_contabil ao inserir novo registro na tabela app_fd_transacao  
      public FormRowSet storeData(Element element, FormRowSet rows, FormData formData) {
          //check for empty data
          if (rows == null || rows.isEmpty()) {
              return rows;
          }
          normalStoring(element, rows, formData);
        
          //store only needed field by create new Form Row Set
          FormRow originalRow = rows.get(0);
          FormRowSet newRows = new FormRowSet();
          FormRow newRow = new FormRow();
        
          newRow.put("cboTipoConta", originalRow.getProperty("cboTipoConta"));
          newRow.put("cboCtContabil", originalRow.getProperty("cboCtContabil"));
          newRow.put("id_valor_transacao", originalRow.getProperty("id_valor_transacao"));
          newRow.put("cboTipoTransacao", originalRow.getProperty("cboTipoTransacao"));
          newRows.add(newRow);
        
          //String id = "#currentUser.username#";
        
          //store
          storeToOtherFormDataTable(element, newRows, formData, id);
          //storeUsingJDBC(element, newRows, formData, id);
        
          return rows;
      }
        
      //this function will reuse workflow form binder to store data
      public void normalStoring(Element element, FormRowSet rows, FormData formData) {
          PluginManager pluginManager = (PluginManager) AppUtil.getApplicationContext().getBean("pluginManager");
          FormStoreBinder binder = (FormStoreBinder) pluginManager.getPlugin("org.joget.apps.form.lib.WorkflowFormBinder");
          binder.store(element, rows, formData);
      }

        
      //this function will store rows data to a form's data table
      public void storeToOtherFormDataTable(Element element, FormRowSet rows, FormData formData, String id) {
          AppService appService = (AppService) AppUtil.getApplicationContext().getBean("appService");
        
          String formId = "app_fd_transacao"; // the table of database is configured in the form with id "user"
          AppDefinition appDef = AppUtil.getCurrentAppDefinition();
        
          appService.storeFormData(appDef.getId(), appDef.getVersion().toString(), formId, rows, id);
      }
        
      //call storeData method with injected variables
      return storeData(element, rows, formData);

      1. Anders

        The error says "Undefined argument: element" so it appears you are using a variable "element" that does not exist. 

      2. Paulo

        Hi Anders,

        Why the element variable is missing? 

        See import package in my code below:

        "import org.joget.apps.form.model.Element;"


        The element is an injected variable in Form class ok? Then i´m not understand because this not working...


        Message:  "Undefined argument: element"

      CommentAdd your comment...
    2.  
      1
      0
      -1

      Hi, if the database update tool does not support multiple statements, you could try executing them as separate tools. Alternatively, you could try running custom JDBC calls using BeanShell.

      1. Paulo

        Hi Anders, thanks for your answer.


        In custom JDBC calls using BeanShell i´m not found a mode to debug this scripts.

        This is very important in cenarios that involve programming developing activities ok ?

        How joget supply this necessity ?


        Thanks

      2. Anders

        BeanShell is essentially Java code, so typically I write and test using a Java IDE first before using it in BeanShell. Having said that, BeanShell is for convenient scripting, and if you are a developer then it might be better to develop an actual plugin like in Developing Plugins.

      CommentAdd your comment...