SQL Lookup
The purpose of an SQL Lookup is to return additional data to an eFORMz project based upon a value in a data file. Normally this value is an indexed item to a table in a database.
Step One: Setting up the Connection
The Configure database connection window
1. If you haven’t done so already, from the Project Properties > Database tab add a new database reference by selecting New.
2. The Configure Database Connection dialog box displays. Enter the database name, driver class, and url.
3. Once the database has been configured select Test to confirm the connection. Once complete, click OK.
For more information, view the following tutorial: Where to set up your database connection
A the list of supported databases and their URLs is available here: Supported URL and Driver Classes
To troubleshoot a connection, view the following reference: Troubleshooting a Database Connection
Step Two: Implement SQL Lookup
4. The database name will display on the Project Properties > Database tab. Select Activate so that the information from the database becomes “real-time” data. Once complete, click OK.
5. Create a variable containing the value you want to find in your database.
6. Add the Right Trim and Left Trim function to the variable. This removes any possible leading and trailing spaces that would cause your lookup to fail. TIP: To confirm the value, view the results in the Variables Viewer window.
7. Create a second variable to be populated by the first item in your SELECT statement. If more than one column of data will be returned create those variables now. From the Variables placeholder in the Project window, right click and select Variables > By Position.
8. Give your new variable a name and then select OK.
9. Select your newly created variable, right-click, and select Function > SQL Lookup.
10. The SQL Lookup dialog box appears. In the database dropdown box, select the database you want to access.
11. Enter the SQL Statement you want executed. Use “?” as replacement characters for parameters you want to pass to the statement.
12. Add parameters in the order you want the “?” replaced in your statement.
13. If you are returning more than one column of data, add the additional columns, selecting the variable you want the returned value stored in the ‘Additional columns assigned to’ field. NOTE: For complex queries write a stored procedure and execute it. The stored procedure must return a result set, not just a value.
Additional Resources on Databases
eFORMz Table Lookup
Loading Multiple Rows with SQL Load
Calling Database Stored Procedures
Calling Database Functions
Troubleshooting: SQL Error – The Statement Didn’t Return a Result Set