Add Session Objects from SQL by Prepared Statement

Version: 1.1

Category: Input

Extended Category: SQL

Action Package: Standard Actions

Description

Add Session Objects from an SQL data source using prepared statement. The action can handle multiple result sets.

Parameter	Description	Example
SQL Data Source	[Mandatory] The data source to read information from. Make sure it is an SQL data source.	MS SQL
Prepared Statement	[Mandatory] The prepared statement. Ex. SELECT ID FROM EMPLOYEES WHERE DEPARTMENT = ?	SELECT ID FROM EMPLOYEES WHERE DEPARTMENT = ?
Parameters	[Optional] The parameters to be used in the prepared statement. Syntax for each parameter: SessionAttributeName\|ParameterFormat. Syntax for Date and Timestamp parameters: SessionAttributeName\|ParameterFormat\|DateFormat. Multiple parameters are separated by comma, or separator entered below. Available parameter formats: STRING, DOUBLE, BYTEARRAY, FILE, DATE, TIMESTAMP. STRING – A text value DOUBLE – A numeric value BYTEARRAY – A binary value FILE – The file name, including path, of a file to insert in database DATE – A date value. Must include the parameter option DateFormat. TIMESTAMP – A timestamp value. Must include the parameter option DateFormat.DateFormat specifies the date and time pattern. Ex. yyyy-MM-dd. Date and Time Patterns may be found at: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html	department\|STRING
Separator	[Optional] The separator used between parameter pairs. Default: , (comma).	;
Keep existing Session Objects	[Optional] If any existing Session Objects should be kept (true) or removed (false). Default value = true	false

Use Cases

Example 1: Add new session objects from table PIP_TABLE

This action will add new session objects with the session attributes Id, Name, Description with values from the SQL table “PIP_TABLE”. The prepared statement uses a STRING parameter to only get the objects with the desired department. The initial session objects, with the departmentnumber attribute will be removed and replaced with the new objects from SQL.

Parameter	Value
SQL Data Source	MS SQL
Prepared Statement	SELECT Id, Name, Description FROM PIP_TABLE WHERE Department = ?
Parameters	departmentnumber\|STRING
Separator	[BLANK]
Keep existing Session Objects	false

Example 2: Add new session objects from procedure PIP_PROCEDURE

This action will add new session objects from the SQL procedure “PIP_PROCEDURE”. No parameters are required to run the procedure. Any existing session objects will be kept. This action configuration can also be used as an action data source.

Parameter	Value
SQL Data Source	MS SQL
Prepared Statement	EXEC PIP_PROCEDURE
Parameters	[BLANK]
Separator	[BLANK]
Keep existing Session Objects	[BLANK]

DISCLAIMER

Information provided in this document is for your information only. PhenixID makes no explicit or implied claims to the validity of this information. Any trademarks referenced in this document are the property of their respective owners.

The origin of this information may be internal or external to PhenixID. PhenixID makes all reasonable efforts to verify this information.

PhenixID - support.phenixid.se