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:
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.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[Get_Managed_Employees]
@Title varchar(50) = NULL out,
@Name varchar(100)= NULL out
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;
The screenshot below shows the results of the stored procedure in MS-SQL.
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.
7.Now, open the form and add a Connect command to an event on the form or object properties.
8.To see how the Connect command functions on the example form, enter the ID and click Search.
9.The image below shows the result of running the stored procedure.