The following fields should be completed on this screen:
Name:
Enter a name for this database query here.
Database Query:
Enter the actual SQL query, stored procedure, or function call in the
text area provided. When executed, the query should return a single
user's profile. The following are examples of SQL statements and stored procedures:
| | |
|
select * from users where username=${authentication.subject.id}
{ call load_user (${authentication.subject.id}, ${out.param}) }
{ call ${out.param.cursor} := p_test.f_load_user(${authentication.subject.id}) }
| |
| | |
|
These examples show that properties in the form of message attributes can be
used in the query. The property that is most commonly used in this context is
the authentication.subject.id , which holds the identity
of the authenticated user. For a complete list of message attributes, see the
Message Attribute Reference.
Statement Type:
The database can take the form of an SQL query, stored procedure, or
function call, as shown in the above examples. Select the appropriate
radio button depending on whether the database query is an SQL
Query or a Stored procedure/function call
Table Structure:
To process the result set that is returned by the database query, the Enterprise Gateway
needs to know whether the user's attributes are structured as rows or columns in
the database table.
The following example of a database table shows the user's attributes
(Role, Dept, and Email) structured as table columns:
Username |
Role |
Dept |
Email |
Admin |
Administrator |
Engineering |
admin@org.com |
Tester |
Testing |
QA |
tester@org.com |
Dev |
Developer |
Engineering |
dev@org.com |
In the following table, the user's attributes have been structured as
name-value pairs in table rows:
Username |
Attribute Name |
Attribute Value |
Admin |
Role |
Administrator |
Admin |
Dept |
Engineering |
Admin |
Email |
admin@org.com |
Tester |
Role |
Testing |
Tester |
Dept |
QA |
Tester |
Email |
tester@org.com |
Dev |
Role |
Developer |
Dev |
Dept |
Engineering |
Dev |
Email |
dev@org.com |
If the user's attributes are structured as column names in the database
table, select the attributes as column names radio button.
If the attributes are structured as name-value pair in table rows,
select the attribute name-value pairs in rows option.
|