The use case i am trying to implement is :-

  1. There is an external oracle database containing archived documents, which Joget users might want to upload them during a workflow.
  2. this external database is not part of the Joget workflow database.
  3. what i need to do is during certain workflow activity to allow the users to retrieve all the archived documents from the database that have the "creator_name" column  = the login user. The sql statement should be something similar to "select data from document where creator_name = #requestor.username#."

But me question is how i can implement the above use case and where i should write the sql to retrieve the related documents?

Best Regards  

  • No labels

4 Comments

  1. Andrew Whalen

    Hello,

    There is some information about connecting to an external data source in the Bean Shell Programming article:

    ?Bean Shell Programming

    Look further down the page and you will find some sample code for connecting to an external database.

    About your use case however, I don't quite understand. You appear to say that you want the user to get a list of documents from an external archiving database so that they can then upload these to Joget? If this is the case, you will probably need a number of different things to make this work:

    1. A custom form binder plugin to retrieve the list of documents and their IDs.
    2. A custom process tool plugin that takes the custom IDs and then downloads these documents and uploads them to Joget as a file attachment to a form.
    3. (Optional) You could of course only store the ID of the archived document in Joget and then display a link in your application to have it downloaded directly from the archiving system whenever the user requests it.

    Regards,

    Andrew

    1. johnjohn123

      thanks for your help, i will explore more your reply as i did not perform such integration before ,, so i am not sure how technically i will handle it .

      But on the above link regarding Bean Shell Programming they include the following code (i modify the original sql statement to be Select instead of update) :-

      Class.forName("com.mysql.jdbc.Driver").newInstance();

      con = DriverManager.getConnection("jdbc:mysql://localhost/wflowdb?useUnicode=true&characterEncoding=UTF-8", "root", "root");

      if(!con.isClosed())

      Unknown macro: { System.out.println("Successfully connected to MySQL server using TCP/IP..."); PreparedStatement stmt = con.createStatement(); stmt.executeUpdate("Select documentid from documents where creator_name ='#assignment.userid#' ""); }

      } catch(Exception e)

      Unknown macro: { System.err.println("Exception}

      finally

      Unknown macro: { try{ if(con != null) con.close(); }

      catch(SQLException e) {}

      }

      But i am not sure how i can store the values of the docuemntid retrieved from the SQL statement ("Select documentid from documents where creator_name ='#assignment.userid#") into my Joget table.

      Best Regards

      1. Andrew Whalen

        Hi There,

        Well it depends on what you mean by 'my Joget table'. Do you mean a table that contains form data? These tables are prefixed with app_fd_ and if you take a look in that database that contains your Joget data (in MySQL, Oracle, whatever) you will see a single table for each form data table that you have specified in your forms.

        Because these are normal database tables you can use the same type of code shown in the Bean Shell Programming page to then make an update statement to update the correct form (row) in that table. Of course you will need to make sure that you have some kind row ID that you can uniquely identify and use to target the row you want to update. This could be taken from a workflow variable (#variable.variableName) or from a form variable (E.g. #form.formID#). Both of these hash variables are available to you and you can then use these in your SQL update statement.

        I hope this helps!

        Andrew

        1. johnjohn123

          thanks a lot for your appreciated help . i mean by 'my joget table' ; the tables that contain the forms data as u mentioned.