Create New/Edit SQL Data Source

To create or edit a SQL data source:

1. In the wizard, enter a Name and optional Description for the data source.
2. Under Configuration:

   • In the JDBC Data Source field, enter a JNDI name of JDBC data source.

   • In the Password field, enter the JDBC data source password.

3. Enter a SQL Statement to query the SQL data source. For example:

   SELECT * FROM Persons WHERE City LIKE 'sa%'

   You can use :bind-name to refer to bind variables in the SQL statement. Bind variables are useful in controlling the data displayed, such as in the following example:

   SELECT ename, empno, mgr, deptno FROM emp WHERE job IN (:job) ORDER BY empno ASC

   This query returns details of all employees with a particular job role, for example, sales managers. In this query, :job defines the parameter job, which maps to the job column in the emp database table. The query returns data based on the job value. You can add as many bind variables as required. You can also use the same variable multiple times in the query.

   Notes:

   • Avoid using SQL reserved words and keywords for parameter names in the query. The use of reserved words will cause the query to fail. For more information, see the topic titled "Oracle SQL Reserved Words and Keywords" in the Oracle Database SQL Language Reference in the Oracle Database documentation library.

   • If you are using a SQL Server database, then you may get an error if the query contains a column with the NCHAR or NVARCHAR data type. If the SQL data control fails to execute a query containing NCHAR and NVARCHAR, it will not be added to the Data Controls folder in the resource catalog. To work around this limitation, you must modify the query using the CONVERT function. For example:

     SELECT CONVERT(VARCHAR(20), col1) col1, CONVERT(VARCHAR(20), col2) col2 FROM table1

4. To allow this SQL data source to be updated, select Updatable. The underlying table must have a primary key defined on the table for this feature to work and updateable query should be based on the single table.
5. Click Test to confirm whether required entries are complete and valid.
6. Click Save.