⚙️ Populate Joget Dropdown Options from SQL Server and Oracle ERP

Overview

In this post, we’ll explore how to connect Joget to external databases — specifically Microsoft SQL Server and Oracle ERP — using a BeanShell script.

This example dynamically loads dropdown options based on user-selected values (like Organization and PO Number).

---

Full Script (with explanations)

Source Code

import com.microsoft.sqlserver.jdbc.SQLServerDriver;
import com.sun.mail.imap.IMAPFolder;
import com.sun.mail.imap.IMAPStore;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Calendar;
package com.journaldev.mail;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
package com.journaldev.mail;
import java.util.Properties;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
package org.learn.uuid;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Address;
import javax.mail.Authenticator;
import javax.mail.BodyPart;
import javax.mail.Flags;
import javax.mail.Flags.Flag;
import javax.mail.Folder;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Store;
import javax.mail.Transport;
import javax.mail.event.MessageCountAdapter;
import javax.mail.event.MessageCountEvent;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.search.FlagTerm;
import javax.sql.DataSource;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.app.model.PackageActivityForm;
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.service.FormUtil;
import org.joget.commons.util.LogUtil;
import org.joget.commons.util.StringUtil;
import org.joget.commons.util.UuidGenerator;
import org.joget.directory.model.User;
import org.joget.directory.model.service.DirectoryManager;
import org.joget.plugin.base.ApplicationPlugin;
import org.joget.plugin.base.Plugin;
import org.joget.plugin.base.PluginManager;
import org.joget.plugin.property.model.PropertyEditable;
import org.joget.workflow.model.WorkflowAssignment;
import org.joget.workflow.model.service.WorkflowManager;
import org.joget.workflow.model.service.WorkflowUserManager;

LogUtil.info("", "WOrking");
public FormRowSet load(String[] values) {
  FormRowSet f = new FormRowSet();
  // f.setMultiRow(true);

  if (values.length < 2 || values[0] == "" || values[1] == "") {
    LogUtil.info("", "value length is 0");
    FormRowSet rows = new FormRowSet();
    FormRow option = new FormRow();
    option.setProperty(FormUtil.PROPERTY_VALUE, "");
    option.setProperty(
        FormUtil.PROPERTY_LABEL, "Please Select Organization and PO");
    rows.add(option);
    return rows;
  }

  DataSource ds =
      (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
  Connection conn1 = ds.getConnection();
  String SQL_SERVER_URL = "jdbc:sqlserver://<sql-server-host>";
  String SQL_SERVER_USER = "<username>";
  String SQL_SERVER_PASSWORD = "<password>";

  String orgId =
      values[0]; // Assuming values is an array or list containing the org_id
  String PO = values[1];
  String selectQuery =
      "select  from openquery(ORA_ORACLEERP, 'select  from APPS.XXMSCRM_PO_HEADERS where PO_NUMBER=''"
      + PO + "'' and org_id=''" + orgId + "''')";

  try {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection(
        SQL_SERVER_URL, SQL_SERVER_USER, SQL_SERVER_PASSWORD);
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(selectQuery);

    if (!rs.next()) {
      LogUtil.info("", "Not Found");
      FormRow option = new FormRow();
      option.setProperty(FormUtil.PROPERTY_VALUE, "");
      option.setProperty(FormUtil.PROPERTY_LABEL, "Not Found");
      f.add(option);
    } else {
      LogUtil.info("",
          "Else Working" + rs.getString("VENDOR_SITE_ID") + "  "
              + rs.getString("SUPPLIER_SITE_VAT_REG"));
      FormRow option = new FormRow();
      option.setProperty(
          FormUtil.PROPERTY_VALUE, rs.getString("VENDOR_SITE_ID"));
      option.setProperty(
          FormUtil.PROPERTY_LABEL, rs.getString("SUPPLIER_SITE_VAT_REG"));
      f.add(option);
      while (rs.next()) {
        LogUtil.info("", "While Loop Working");
        FormRow option = new FormRow();
        option.setProperty(
            FormUtil.PROPERTY_VALUE, rs.getString("VENDOR_SITE_ID"));
        option.setProperty(
            FormUtil.PROPERTY_LABEL, rs.getString("SUPPLIER_SITE_VAT_REG"));
        f.add(option);
      }
    }

    conn.close();

    LogUtil.info("",
        "Data insertion/update completed successfully." + values[0] + "  "
            + values[1] + rs.getRow());
  } catch (SQLException e) {
    LogUtil.info("", e.getMessage());
  }

  return f;
}

return load(values);