Add Data from SQL by Prepared Statement

Version: 1.1

Category: Input

Extended Category: SQL

Action Package: Standard Actions

Description

Add data 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).	;

Use Cases

Example 1: Add new attributes from table PIP_TABLE

This action will add 2 new session attributes (Name, Description) with values from the SQL table “PIP_TABLE”. The prepared statement uses a STRING parameter.

Parameter	Value
SQL Data Source	MS SQL
Prepared Statement	SELECT Name, Description FROM PIP_TABLE WHERE Id = ?
Parameters	userid\|STRING
Separator	[BLANK]

Example 2: Add new attributes from procedure PIP_PROCEDURE

This action will add new session attributes with values from the SQL procedure “PIP_PROCEDURE”. No parameters are required to run the procedure.

Parameter	Value
SQL Data Source	MS SQL
Prepared Statement	EXEC PIP_PROCEDURE
Parameters	[BLANK]
Separator	[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