Show SP return value in a form

 
1
0
-1

I have a form with a Bean Shell Form Store Binder code that executes a Sql Server external database stored procedure and returns a value (the SP runs an INSERT with the form fields as parameters and returns the newly created id). I want to show the returned value in some way in the UI

    CommentAdd your comment...

    1 answer

    1.  
      1
      0
      -1

      Hi, you could store the returned value as form data like in Bean Shell Programming Guide#UseasFormStoreBinder, or as a workflow variable Setting Workflow Variable Value in Process Tool Bean Shell. You can then display the value in a UI form element.

      1. Nik

        What you mean about "store the returned value as form data". Do you mean to use "row.setProperty" and set the sp returned value (id) in the FormRow object (row) to the "id" field? Like:

        row.setProperty("id",id);

        What is the purpose of the returned FormRowSet (with one FormRow) at the end of the code. Is this what will appear in the fields after saving the form? I tried it and it didn't work. I put a read-only ID field on the form expecting it to populate after saving the form with the returned ID from within the previous code but nothing!

      2. Nik


        I have the code I use to store a form to Sql Server database through stored procedure. The fields are (id,phone,from,to). I don't use the id as sp parameter to save it with the other fields. I take it back as sp returned value. Then add this id to form's row (row.setProperty("id",id)). At the end I return the FormRowSet: (return storeData(element, rows, formData)) hoping that it will appear in the form fields but finally while the insert is being made in the Sql Server, joget it returns an alert "failed to fetch" message and then closes the window!!! And I don't see an error in the logs!!!

        import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.joget.apps.form.model.*;
import org.joget.commons.util.LogUtil;
  
public FormRowSet storeData(Element element, FormRowSet rows, FormData formData) {

    FormRow row = rows.get(0);
    Connection con = null;
    String id="";
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=myDatabase", "username", "password");
        String sp="EXEC SPInsertPhoneAvailability ?,?,?";
        PreparedStatement istmt = con.prepareStatement(sp);
        istmt.setString(1, row.getProperty("phone"));
        istmt.setString(2, row.getProperty("from"));
        istmt.setString(3, row.getProperty("to"));
        ResultSet rs = istmt.executeQuery();
        if (rs.next()){
            id=rs.getString(1);
            LogUtil.info("Sample app","The id is:"+id);    
        }
    } catch (Exception e) {
        LogUtil.error("Sample app", e, "Error storing using jdbc");
    }
    row.setProperty("id",id);
    return rows;    
}

return storeData(element, rows, formData);


      CommentAdd your comment...