Loading multiple rows with SQL Load

SQL Load is an eFORMz procedure to load one or more rows of data from one or more columns of a relational database. Use the eFORMz function Table Lookup to load a single value from a row of a table. Table Lookup is simpler and more efficient for single-value queries.

Complete the following steps to set up an SQL Load procedure. This example lists the invoice date, invoice amount, and invoice status for one customer. You must have a variable set to the number identifying the customer. That value typically comes from a data file.

1. Open your project in the eFORMz Composer.
2. Create a database connection.
3. This example sets a variable, CustNum, to 12345. This is the customer number whose invoices we want to list.
4. Create a variable for each column you want to include in the result set: InvDate, InvAmt, InvState. To create a variable, right click Variable in the Project window > Add variable > By position. Name the variable, and click OK.
5. Right click the form > Add Pre-condition Procedure > SQL Load. If you have a condition on the form, use Add Post-condition Procedure instead. A post-condition procedure has additional performance costs, so avoid it when possible.
6. Name the procedure. In the Use variable field select N/A. Click OK.
   
7. In the SQL Load window, select which database connection to use.
8. In Parameters, click Add. Select the variable that contains the customer number.
9. Type your SQL statement into the SQL statement area. Type a question mark (?) where the customer number goes. If you have multiple parameters, they are evaluated in the order they are listed in the Parameters area.  For example, the following query requires the first parameter in Parameters to be the customer number, and the second to be the region:
   

   select InvDate, InvAmt, InvState from invoices where CustNum = ? and Region = ?

10. Click Add in the Columns area. Add the variables that you created in Step 4 in the order that your query returns them.
11. Click OK.
12. If your database connection is active, the query returns the result set as an array: