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;