⚙️ Dynamic Dropdown Integration Between SQL Server and Oracle ERP in Joget Using BeanShell

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)

// Import required packages
import java.sql.*;
import javax.sql.DataSource;
import org.joget.apps.form.model.*;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

// Log entry to confirm script execution
LogUtil.info("", "Script Execution Started");

// Function to dynamically load dropdown data
public FormRowSet load(String[] values) {
    FormRowSet rows = new FormRowSet();

    // Validation: Check if both required parameters are present
    if (values.length < 2 || values[0] == "" || values[1] == "") {
        LogUtil.info("", "Insufficient input values");

        FormRow option = new FormRow();
        option.setProperty(FormUtil.PROPERTY_VALUE, "");
        option.setProperty(FormUtil.PROPERTY_LABEL, "Please Select Organization and PO");
        rows.add(option);
        return rows;
    }

    try {
        // Retrieve Joget data source
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        Connection conn1 = ds.getConnection();

        // External database connection details (example placeholders)
        String SQL_SERVER_URL = "jdbc:sqlserver://<server-host>";
        String SQL_SERVER_USER = "<username>";
        String SQL_SERVER_PASSWORD = "<password>";

        // Dynamic parameters
        String orgId = values[0];
        String PO = values[1];

        // Oracle ERP data access through OpenQuery
        String selectQuery =
            "SELECT VENDOR_SITE_ID, SUPPLIER_SITE_VAT_REG " +
            "FROM OPENQUERY(ORA_ORACLEERP, " +
            "'SELECT VENDOR_SITE_ID, SUPPLIER_SITE_VAT_REG " +
            "FROM APPS.XXMSCRM_PO_HEADERS " +
            "WHERE PO_NUMBER = '''" + PO + "''' " +
            "AND ORG_ID = '''" + orgId + "'''')";

        // Load SQL Server driver and execute query
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection(SQL_SERVER_URL, SQL_SERVER_USER, SQL_SERVER_PASSWORD);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(selectQuery);

        // Handle data
        if (!rs.next()) {
            LogUtil.info("", "No records found");
            FormRow option = new FormRow();
            option.setProperty(FormUtil.PROPERTY_VALUE, "");
            option.setProperty(FormUtil.PROPERTY_LABEL, "Not Found");
            rows.add(option);
        } else {
            do {
                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"));
                rows.add(option);
            } while (rs.next());
        }

        conn.close();
        LogUtil.info("", "Dropdown data loaded successfully for Org: " + orgId + " PO: " + PO);

    } catch (Exception e) {
        LogUtil.error("BeanShell", e, "Error loading data");
    }

    return rows;
}

// Execute the load function
return load(values);