Calling database stored procedures

Using Microsoft SQL Server TSQL Language, a wide variety of things can be created under the title ‘stored procedure’.

From eFORMz’s point-of-view, we are expecting a process that falls into one of two categories.

1) A process that accepts zero or more parameters and returns a result set. (SQL Load)
2) A process that accepts zero or more parameters and does not return a result set. (SQL Update)

When a result set is handled by “SQL Load”, the columns are loaded in the order defined in the ‘columns assigned to’ list. The names of the column are not used, only the relative column number.

eFORMz does not support ‘output’ parameters, only input.

Process

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

1. Start the eFORMz Composer and open your project.
2. Click Projects > Properties and go to the Databases tab.

1. 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. 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

1. Add a variable to your form by right clicking Variables > Add Variable > By position…
2. Type a name for the variable and click OK.

Create a SQLLoad procedure to call the stored procedure

1. Right click the form > Add Pre-condition Procedure > SQL Load.
2. Type a name for the procedure and select the variable to store the results to. Click OK.
3. In the SQL Load window, select your database connection.
4. 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 ?
5. In the Parameters area, click Add and select the variable to pass to the stored procedure.

NOTE: Calling an Oracle stored procedure

To call an Oracle stored procedure where anything might be returned (e.g. UPDATE, INSERT), use the SQL Update action. If you want to retrieve the data returned from the stored procedure (e.g. SELECT), use the SQL Load action. The syntax should be as follows:

call libname.storedprocedure( ?, ?, ? )

call storedprocedure( ?, ?, ? )

The question marks are placeholders for parameters to the stored procedure. The “libname.” is an optional library name, in case the library is not defined in the database connection string and must be specified in each call to the stored procedure.