Database Connections Action Types

 Previous  Home topic  Next  Print this topic Send Feedback

In the case of database and LDAP connection actions, PerfectForms™ passes the action details to the connection agent. This then assembles the parameters into a SQL statement (or LDAP query). This means that there is no risk of SQL injection attacks.

 

Database connection actions can both send and receive data using SQL statements or by running a stored procedure. Database connection action types include:

 

•Select
•Update
•Insert
•Delete
•Stored Procedure

 

 

Using Stored Procedures to Return a Result Set

 

Returning a result set is necessary in situations where a form has several objects running connect and search commands to return different parameters. For example, a form has two separate tables and each presents a different set of data based on the search command. A DBA should already know how to set up and use a stored procedure in his own database, the only thing that remains is to integrate it into PerfectForms™.

 

The following example is based on a sample stored procedure for Microsoft’s AdventureWorks database.

 

USE [AdventureWorks]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Get_Managed_Employees]

(

@ManagerID int,

@Title varchar(50) = NULL out,

@Name varchar(100)= NULL out

)

AS

SELECT @Title=he.Title,@Name = pc.FirstName +' '+ pc.LastName

 from HumanResources.Employee he 

         inner join Person.Contact pc

 on he.ContactID = pc.ContactID

 where he.EmployeeID = @ManagerID;

 

select EmployeeID,LoginID from HumanResources.Employee where ManagerID = @ManagerID;

return 0

GO

 

 

The screenshot below shows the results of the stored procedure in MS-SQL.

 

Stored Procedure

 

 

Document Icon
1.In PerfectForms™, navigate to the Connections Dashboard. Next, expand the Databases node from the tree menu and select the desired database connection. Click the Add Action button.
2.On the New Action page, assign a name to the action and select Stored Procedure as the Type.
3.In the Details area, enter the Procedure Name as it was named when it was created on the database server. For example, using the  EXECUTE statement shown above the procedure name would be: dbo.Get_Managed_Employees.
4.Parameters will use the procedure parameters as specified by their SQL names. The example shows one input, or Send parameter (ManagerID), and three output, or Return parameters (Title, Name, LoginID).
5.The result set columns are specified as Return parameters and should follow this naming convention: RS[index].ColumnName
•RS specifies that this is a result set.
•[index] is the result set index. Note that there can be more than one result set returned by a stored procedure.
•ColumnName is the name of the column inside the result set.
6.Click Test to verify the action was configured properly. On the Test Action screen, click Connect.

Test Action

7.Now, open the form and add a Connect command to an event on the form or object properties.

Connect Command

8.To see how the Connect command functions on the example form, enter the ID and click Search.

Form Example

9.The image below shows the result of running the stored procedure.

Result of Running Procedure

 

Updated: 06/12/2011  Page url: http://www.perfectforms.com/Documentation/manual/html/?admin_database_connections_actions.htm
PerfectForms™ -- forms software, workflow software, and business process management