Database SQL Query

The Database SQL Query (formerly JDBC Form Binder) allows you to use custom SQL statements to retrieve and load records into your form fields. Similarly, you can write SQL statements to save the records in your form fields into a database table.

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

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

Database SQL Query settings are in the form PROPERTIES tab under Advanced > Data > Load Data From & Save Data To. The Database SQL Query replaces the standard Workflow Form Binder.

You can configure the following options:

• Load Data From: You only need to configure the datasource and SELECT query.
• Store Data To: You must write SQL statements for SELECT, INSERT, UPDATE, and DELETE actions.

Exceptions

Database SQL Query cannot be used in the following cases (revert back to Default Data Store):

• Use of form element workflow variables: Database SQL Query will not populate or update the workflow variables.
• Use of file and image attachment field elements: Database SQL Query will not handle the retrieval or transfer into Joget file storage.

Configure data load for database SQL query

When configuring a data load for a database SQL query, you are presented with the form below.

Configure the following fields:

• Datasource:
  • Custom Datasource: Set up to connect to an external database with additional configuration.
  • Default Datasource: Connects to the Joget database.
    By selecting Default Datasource, the database your Joget is currently using will be selected.
• Custom JDBC Driver: Required when Custom Datasource is selected. For example, com.mysql.jdbc.Driver.

• Custom JDBC URL: Required when Custom Datasource is selected. For example, jdbc:mysql://localhost/jwdb?characterEncoding=UTF8&useSSL=false.
• Custom JDBC Username: Required when Custom Datasource is selected.
• Custom JDBC Password: Required when Custom Datasource is selected.
  
  Test the connection parameters: To test your configurations quickly, click the Test Connection button at the bottom of the page.
• SQL SELECT Query for Load Data: The SQL query specific to your database type (MySQL, MSSQL, Oracle, etc.). Use a question mark ? to represent the primary key or foreign key.
  
  

  SELECT *
  FROM app_fd_table
  WHERE id = ?

  SQL

  Copy

  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;

  SQL

  Copy

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") operator from being converted, i.e., disables XSS prevention checking. For more information, see Escaping the Resultant Hash Variable.

• Handling for Field Workflow Variable?: Click the checkbox if you are using workflow variables mapping in your form elements. This does not apply to grid elements.

• Handling for Uploaded Files?: Click the checkbox if you are using file or image attachments in your form elements. The JDBC binder will save your file or image attachment into the .wflow folder when you save the form.

• Form to store uploaded files: Select form to store uploaded files only if you are using form grid or spreadsheet.

Configure data store for database SQL query

When configuring a data store for a database SQL query, you are presented with the form below.

SELECT `myAppName.myColumn` FROM app_fd_myTable;

Configure the following fields:

• Datasource:
  • Custom Datasource: Set up to connect to an external database with additional configuration.
  • Default Datasource: Connects to the Joget database.
    By selecting Default Datasource, the database your Joget is currently using will be selected.
• Custom JDBC Driver: Required when Custom Datasource is selected. For example, com.mysql.jdbc.Driver.
• Custom JDBC URL: Required when Custom Datasource is selected. For example, jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF8&useSSL=false.
• Custom JDBC Username: Required when Custom Datasource is selected.
• Custom JDBC Password: Required when Custom Datasource is selected.
  
  Test the connection parameters: To quickly test your configurations, click the Test Connection button at the bottom of the page.
• SQL SELECT Query for Data Store: The SQL query specific to your database type. Use syntax like {field_id} in the query to inject submitted form data. See the example below.
  

  SELECT *
  FROM app_fd_table
  WHERE id = {id}

  SQL

  Copy
  Note: If you use JDBC in a form grid or spreadsheet to load or save data, be advised that the SQL syntax differs from that of a form load or save data store. Kindly download and view the sample app for JDBC in a form grid or spreadsheet. 

• SQL INSERT Query: Use syntax like curly brackets {field_id} in the query to inject submitted form data.  See the example below.
  

  INSERT INTO app_fd_table (id, c_name, c_emailaddress)
  VALUES ({id}, {name}, {emailAddress})

  SQL

  Copy

  You can use {uuid} to tell Joget to create a random unique id or a "Universally unique identifier". See the example below.

  INSERT INTO app_fd_table (id, c_name, c_emailaddress)
  VALUES ({uuid}, {name}, {emailAddress})

  SQL

  Copy

  For JDBC SQL INSERT in a grid, use {foreignKey} syntax to populate the parent ID into the child table foreign key value, as follows:

  INSERT app_fd_table_child (id, c_name, c_fk)
  VALUES ({uuid}, {name}, {foreignKey})

  SQL

  Copy

• SQL UPDATE Query: Use syntax like curly brackets {field_id} in the query to inject submitted form data. See the example below.

  UPDATE app_fd_table
  SET c_name = {name}, c_emailaddress = {emailAddress}
  WHERE id = {id}

  SQL

  Copy

   

• SQL DELETE Query: SQL statement to delete form data records. Use syntax like curly brackets {id} In the query, inject the primary key value of the form data. See the example below.

  DELETE
  FROM app_fd_table
  WHERE id = {id}

  SQL

  Copy
• Handling for field workflow variable?: Click the checkbox if you are using workflow variables mapping in your form elements. The JDBC binder will copy the field value to the workflow variable when you submit the form if the form is mapped to a  process. Not applicable to grid elements.
• Handling for uploaded files?: Click the checkbox if you are using file or image attachments in your form elements. The JDBC binder will save your file or image attachment into the ".\wflow" folder when you save the form. Select form to store uploaded files only if you are using a form grid or spreadsheet.
• Form to store uploaded files: Select form to store uploaded files only if you are using form grid or spreadsheet.

Understanding JDBC errors

The table below presents a list of common JDBC errors.

Additional resources

Explore more about SQL queries with Joget through these resources:

• Database SQL Query List Action
• Database SQL Query List Data Store