API Guide

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:

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.

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;