...

SELECT
   * 
FROM
   app_fd_table 
WHERE
   id = ?

SELECT `myAppName.myColumn` FROM app_fd_myTable;

Configure Database SQL Query Data Store

...

Figure 3: Configure Store Database SQL Query BinderData Store

Note
If a column name contains reserved keywords, do ensure it is encapsulated properly. For example for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this: Code Block language sql SELECT `myAppName.myColumn` FROM app_fd_myTable;

Name	Description
Datasource	Custom Datasource - setup to connect to an external database, has additional configuration. Default Datasource - connect to the Joget database. By selecting Default Datasource, the database your Joget is currently using will be selected. (See Figure 3).
Custom JDBC Driver	Custom JDBC Driver. This field is required when Custom Datasource is selected in Datasource above. Example: com.mysql.jdbc.Driver
Custom JDBC URL	Custom JDBC URL. This field is required when Custom Datasource is selected in Datasource above. Example: jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF8&useSSL=false
Custom JDBC Username	Custom JDBC Username. This field is required when Custom Datasource is selected in Datasource above.
Custom JDBC Password	Custom JDBC Password. This field is required when Custom Datasource is selected in Datasource above. Info title Test the connection parameters Click on the "Test Connection" button at the bottom of the page to quickly test out your configurations.
SQL SELECT Query for store binderData Store	The SQL query specific to your database type (MySQL, MSSQL, Oracle, etc.). Use syntax like {field_id} in query to inject submitted form data. Code Block language sql title Example linenumbers true SELECT * FROM app_fd_table WHERE id = {id} Note: If you use JDBC in a form grid or spreadsheet for load or save binderdata store, be advised the SQL syntax is different from form load or save binderdata 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 query to inject submitted form data.  Code Block language sql title Example linenumbers true INSERT app_fd_table (id, c_name, c_emailaddress) VALUES ({id}, {name}, {emailAddress}) You can use {uuid} to tell Joget to create a random unique id or a "Universally unique identifier". Example: Code Block language sql title Example linenumbers true INSERT app_fd_table (id, c_name, c_emailAddress VALUES ({uuid}, {name}, {emailAddress}) For JDBC SQL INSERT in a grid, use "{foreignKey}" syntax to populate the parent id into the child table foreign key value, as follows: Code Block INSERT app_fd_table_child ( id, c_name, c_fk) VALUES ( {uuid}, {name}, {foreignKey})
SQL UPDATE Query	Use syntax like curly brackets {field_id} in query to inject submitted form data. Code Block language sql title Example linenumbers true UPDATE app_fd_table SET id = {id}, c_name = {name}, c_emailaddress = {emailAddress}, c_contacttel = {contactTel} WHERE id = {id}
SQL DELETE Query	SQL statement to delete form data records in Datalist/Grid element. Use syntax like curly brackets {id} in query to inject form data primary key value. Code Block language sql title Example linenumbers true DELETE FROM app_fd_table WHERE id = {id}
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.

...

Error Messages	Causes And Solutions
Message on screen: Record cannot be found	There is a JDBC configuration error. Check the joget.log file for more information.
The rest of the error messages below are found in the \apache-tomcat-x.x.xx\logs\joget.log file.
ERROR org.joget.plugin.enterprise.JdbcLoadBinder - org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class	The property  Custom JDBC Driver is incorrect
ERROR org.joget.plugin.enterprise.JdbcLoadBinder - org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not connect to address=(host=localhost)(port=3306)(type=master) : Socket fail to connect to host:localhost, port:3306. Connection refused: connect)	The property  Custom JDBC URL  is incorrect
ERROR org.joget.plugin.enterprise.JdbcLoadBinder - org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Could not connect to address=(host=localhost)(port=3307)(type=master) : Access denied for user 'xxxx'@'localhost' (using password: YES)	The property Custom JDBC Username  or  Custom JDBC Password is incorrect.
ERROR org.joget.plugin.enterprise.JdbcLoadBinder - java.sql.SQLSyntaxErrorException: .....	The property SQL SELECT Query is incorrect. Do also check the SQL lower or upper case spelling on table and column names, especially if you are running a database on a Linux OS which is case sensitive.
ERROR org.joget.plugin.enterprise.JdbcLoadBinder - java.sql.SQLException: Could not set parameter at position 1 (values was '?') Query - conn:506(M) - SELECT * FROM app_fd_table WHERE id = "?"	Do not enclosed the ? in single or double quotation marks. Correct syntax is: SELECT * FROM app_fd_table WHERE id = ?
ERROR org.joget.tutorial.JdbcOptionsBinder - com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 2	DB error on JDBC Options with "Field ID to control available options based on Grouping". You can only pass one ? parameter in the SQL.

Related Database SQL Query

...

Data Store

• Database SQL Query Options
• Database SQL Query List Action
• List Database SQL Query

...