SQL Update

SQL Update will return a single record from an SQL database. For returning arrays, use SQL Load.

How to implement

To implement a database SQL update within the eFORMz Composer

1. From the Project Properties > Database tab add a new database reference by selecting New.
2. The Configure Database Connection dialog box will display. Enter the database name, driver class, and url.
3. Once the database has been configured select Test to confirm the connection and then click OK.
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 in the eFORMz Viewer, Form and Data Viewer. 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

Create the SQL Update statement

1. From the Project window right click the form and select Add Procedure > SQL Update.
2. From the Procedure Properties dialog box enter a procedure name and select a variable or N/A for a variable option. Click OK. The SQL Update Action dialog box appears.
3. From within the SQL Update dialog box, enter the appropriate SQL statement to either UPDATE, DELETE or INSERT values within your database.

NOTE: New parameter-variable assignments can be made by clicking on the Add or Delete button. To delete a parameter-variable simply select the variable you would like to remove from the list and click Remove. There is also the option to rearrange the order by using the Move up or Move down button.

NOTE: Special caution is advised when specifying a select statement to update a database. Once the database is defined and information is entered in the statement field, real time updates will occur if a data file is processed. This will reflect in immediate changes to your database. Only persons familiar with database design should be permitted to enter this information into the statement field. Minisoft will not be held responsible for information entered resulting in database changes.

4. Once complete, click OK.

SQL End Transaction

The SQL End Transaction feature is used in conjunction with the SQL Update Procedure to set AutoCommit to off. There are two options: commit and rollback. Commit marks the end of a successful transaction by making all data modifications performed since the start of the transaction a permanent part of the database. Rollback rolls back a transaction to the beginning in order to erase all data modifications made from the start of the transaction.

Additional Resources on Databases

eFORMz Table Lookup
SQL 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