SQL-002: Retrieve single preference blob

This SELECT retrieves the stored PREFERENCE_TEXT for a single application/user combination, optionally scoped to a specific page name. It backs PreferenceData.GetPreferences.

Classification

Field	Value
SQL ID	SQL-002
Operation type	Read
Execution method	Inline SQL (SqlHelper.ResultSetQuery.Execute)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	PreferenceData
Criteria property	n/a (no IBusinessCriteria)
Lifecycle method	GetPreferences

Parameters

Parameter	Direction	Type	Max length	Description
:userid	Input	string (SqlHelper.NewParm)	40	Lower-cased user ID (userId.ToLower()); scopes the lookup to the requesting user.
:appname	Input	string (SqlHelper.NewParm)	40	Application name that owns the preference.
:pagename	Input	string (SqlHelper.NewParm)	40	Optional page name; only bound when pageName is non-empty (otherwise the whereNull clause AND PAGE_NAME IS NULL is used instead, with no bind variable).

Tables

Table	Access	Columns used
USER_PREFERENCE	SELECT	USER_ID, APP_NAME, PAGE_NAME, PREFERENCE_TEXT

Sample SQL

-- Default form (no page name supplied)
Select USER_ID, APP_NAME, PAGE_NAME, PREFERENCE_TEXT
from USER_PREFERENCE
where USER_ID = :userid
  and APP_NAME = :appname
  and PAGE_NAME is NULL

-- When pageName is supplied, the final predicate is replaced with:
Select USER_ID, APP_NAME, PAGE_NAME, PREFERENCE_TEXT
from USER_PREFERENCE
where USER_ID = :userid
  and APP_NAME = :appname
  and PAGE_NAME = :pagename

Operations and processes

• Retrieve single preference blob

Business rules

• BR-001: User IDs are normalized to lower case
• BR-003: Preference identity is scoped by app, user, and optional page

Sources

• SAIF.Framework.Preferences\PreferenceData.cs