Calling database stored procedures
You can call stored procedures using an eFORMz database connection. First, you configure the database connection, then create a variable to pass to the stored procedure, and then create a variable to store the result that the stored procedure returns. The advantage of using a stored procedure over SQL is that the stored procedure is precompiled, and the execution plan is already determined.
Complete the following steps to call a stored procedure your database server:
Define your database connection
- Start the eFORMz Composer and open your project.
- Click Projects > Properties and go to the Databases tab.
- Click New and define your database connection:
Database connection name The local name to identify the database connection. Driver class The identity of the file that tells Java how to connect. More information is at Supported URL and Driver Classes. URL The connection string for the database. The URL includes the IP address or host name and port to connect to along with properties such as a user ID, password, and default schema. Properties You can add properties to include in the URL here.
- Click Test to test the connection.
- Optional: Click Save to file to save the connection definition in a separate file so you can reuse the connection with other projects.
- Click OK to close the Configure database connection window.
- In the Project Properties window, select the database connection name, and then click Activate.
- Click OK.
Create the variable to pass to the stored procedure
You can create this variable several ways. For details, see “Creating an eFORMz Variable” in the Minisoft eFORMz Manual. You can skip this step if your stored procedure does not require a parameter.
Create a second variable to store the value that the stored procedure returns
- Add a variable to your form by right clicking Variables > Add Variable > By position…
- Type a name for the variable and click OK.
Create a SQLLoad procedure to call the stored procedure
- Right click the form > Add Pre-condition Procedure > SQL Load.
- Type a name for the procedure and select the variable to store the results to. Click OK.
- In the SQL Load window, select your database connection.
- Click Constant and in the text field, type the text that calls the stored procedure, using a question mark (?) to indicate a parameter to pass, if you pass one. This example calls the stored procedure, sp_MyStoredProc:
exec sp_MyStoredProc ?
- In the Parameters area, click Add and select the variable to pass to the stored procedure.