Database SQL Query List Data Store

The Database SQL Query List Data Store in Joget allows the creation of custom lists using user-defined SQL queries and database connections. This feature provides flexibility in designing lists by leveraging SQL queries to fetch data from either the Joget or an external database.

SELECT * FROM app_fd_sample_table WHERE c_value = '#requestParam.id#'

SELECT * FROM app_fd_sample_table WHERE c_value = '#requestParam.id?sql#'

Order By ASC/DESC
ORDER BY using SQL Query will not work. You must use the Order By function inside the List Settings instead.

Configure database SQL Query

To set up an external directory, go to Data, then select Data Store. 

Fields to Configure:

• Select Source of Data (Data Store): Choose Database SQL Query List Data Store.
  Ensure your Joget environment is configured to use LDAP/AD in the Directory Manager Settings.

• Datasource: Target database to execute SQL statements on.
  • Custom Datasource: Requires JDBC Connection Parameters.
  • Default Datasource: Points to the current database your Joget instance connects to.
• Custom JDBC Driver: JDBC driver name 
  Example values:
  • com.mysql.jdbc.Driver (MySQL)
  • oracle.jdbc.driver.OracleDriver (Oracle)
  • com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft SQL Server)
    This is only applicable to the Custom Datasource option.
• Custom JDBC URL: Database connection URL (e.g., jdbc:mysql://localhost:3306/jwdb).
  This is only applicable to the Custom Datasource option.
• Custom JDBC Username:  Database username (e.g., root).
  This is only applicable to the Custom Datasource option.
• Custom JDBC Password: Specified database user's password.
  This is only applicable to the Custom Datasource option.
  Test the connection parameters
  Click the Test Connection button at the bottom of the page to test your configurations quickly.
• SQL SELECT Query: SQL Select query to populate the datalist. (e.g., SELECT * FROM app_fd_myTable).
  
  

  If a column name contains reserved keywords, ensure it is encapsulated properly.

  For example, for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this:

  SELECT `myAppName.myColumn` FROM app_fd_myTable;

  Table & Column Naming

  • • For database tables created by Joget Forms, Joget adds a c_ in front of table column names (or t_ if your column name starts with a number) and app_fd_ in front of database table names.
    • If you use environment hash variables to store SQL query strings, use ?noescape to escape SQL query strings in JDBC binders to prevent the <> (not equal) the operator from being converted, i.e., disables XSS prevention checking. For more information, see Escaping the Resultant Hash Variable.
• Primary Key: Define the primary key column. By default, it should be id.
• Optimize query for paging: When checked, the data store only fetches selected page's items to optimize performance for large dataset paging.
  It only works for MySQL and Microsoft SQL Server 2012 onwards.

Additional resources

Explore more about SQL queries with Joget through these resources:

• Database SQL Query (formerly JDBC Form Binder)
• Database SQL Query Options
• Database SQL Query List Action
• Understanding JDBC Errors
• How to Sort List Using Multiple Columns