Database Statements

---

The sections below define the database statements needed for the built-in Database Adapter. Statements can be implemented as a query, stored procedure call, or anonymous SQL block.

a) Authentication / User Credential Validation: Evaluates the username and password and returns a single row with the Integrated Applications internal user id. If the username and password are not valid, then no row should be returned. Note: the Database adapter supports encrypting the password before setting the parameter in the SQL statement.

The following columns are expected to be returned from the statement if the username and password are valid:

Column	Required	Data Type	Description
xExtUserId	X	String	The internal id of the user record in the Integrated Application.

Example 1: Query

select xUserId as xExtUserId 
from axtapp_user WITH (NOLOCK) 
where xUsername = @username 
    and xPassword = @password
    and xIsActive = 1

Example 2: Stored Procedure

exec dbo.authenticate_user @username, @password;

Example 3: Anonymous Block

BEGIN
     set nocount on;
     declare @un nvarchar(100);
     declare @pw nvarchar(100);

    set @un = @username;
    set @pw = @password;

    if (some kind of test = true) begin
       select @un as xExtUserId;
    end else begin
        select null where 1=2;
    end;
END

b) User Information Lookup: Used to lookup relevant information about the user account. The XDOC internal database is updated with the user information every time the user logs on. This user information is also passed around in a secure session browser cookie and used through the various XDOC UIs as needed.

The following columns are available to be returned by the statement. Note: The columns that start with “xExt” (except xExtUserId) are not actually used by XDOC, but are stored and passed around in the XDOC User object and thus can be used by custom created user interface pages and services deployed under XDOC by customers.

Column	Required	Data Type	Description
xExtUserId	X	String	The internal id of the user record in the Integrated Application.
xFirstName		String	First Name
xLastName		String	Last Name
xDisplayName		String	Full Name
xEmail		String	Primary email
xProfileName		String	The security profile in XDOC to apply to the user.
xExtRef		String	Additional information field from the Integrated Application
xExtData		String	Additional data field from the Integrated Application.
xExtFlags		Long	Additional integer bits from the Integrated Application.

Example 1: Query

select 
    xUserId as xExtUserId
   ,xExtRef as xExtRef
   ,xExtData as xExtData
   ,xExtFlags as xExtFlags
   ,xFirstName as xFirstName
   ,xLastName as xLastName
   ,isNull(xFirstName,'') + ' ' + isNull(xLastName,'') as xDisplayName
   ,xEmail as xEmail
   ,xProfileName as xProfileName
from axtapp_user WITH (NOLOCK) 
   where xUsername = @username

Example 2: Stored Procedure

exec dbo.user_information @username;