Calling database functions

You can call built-in and user-defined functions (UDFs) through an eFORMz database connection. First, you configure the database connection, then create a variable to pass to the function as a parameter, and then create a variable to pass to the database with the first variable as a parameter. Complete the following steps to call a function on 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.
3. 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.

   

   The Configure database connection window

4. Click Test to test the connection.
5. Optional: Click Save to file to save the connection definition in a separate file so you can reuse the connection with other projects.
6. Click OK to close the Configure database connection window.
7. In the Project Properties window, select the database connection name, and then click Activate.
8. Click OK.

Create the variable to pass to the function

You can create variables in several ways. For details, see “Creating an eFORMz Variable” in the Minisoft eFORMz Manual. You can skip this step if your function does not require a parameter.

Create a second variable that passes the previously defined variable as a parameter

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.
3. Right click the variable you just created and click Add Function > SQL Lookup.
4. Verify that the Database field has the correct database connection name.
5. Select the Constant radio button. In the Constant field, type SELECT followed by the function this way if you created a UDF named fn_MyFunction in the dbo schema:
   SELECT dbo.fn_MyFunction(?)
6. Click the Add button in the Parameters area and select the variable that you want to pass to the function.
7. Click OK to save your changes. Save your project.

Check that your variables are in order

The variables are processed sequentially in the order they are listed in the Project window. The variable to pass to the function must come before the variable that passes it. You can drag and drop the variable icons to change their order.