Hack 59. Define Personalized Parameters

If you need every user to have a different set of global variables to use as query parameters and the like, you can create a view of your parameter table such that each user sees a different row of the underlying table.

It is easy to create query parameters that are persistent when there's only one user. But if there are several users you have a problem: Janet may not want to see John's parameters. Worse still, there is a danger that John will change the parameters after Janet has set them but before she gets to run her query.

You can deal with this by creating a table that provides one row per user, and replacing the param table shown in "Store Parameters in the Database" [Hack #58] with a view that returns only the row from the param table that is associated with the current user. What follows is the standard SQL syntax needed to create the table and the view. Oracle, SQL Server, MySQL, and Access require a slight variation to the pattern, but the differences are trivial:

CREATE TABLE userparam ( who VARCHAR(20) DEFAULT CURRENT_USER , prmStart DATE , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userparam WHERE who=CURRENT_USER;

You can use precisely the same code for the query and for updating the parameter, as shown in "Store Parameters in the Database" [Hack #58] (the param VIEW can be updated):

UPDATE param SET prmStart = DATE '2006-05-27';

If john issues this UPDATE, only his row will change.

Similarly, if john issues the SELECT statement involving the param table, only his row will be accessed.

8.2.1. Adding New Users

Each user on the system must have an entry in the userParam table. If you have set up the primary key correctly you can issue the INSERT command at the start of every session. When an existing user initiates subsequent sessions, this will generate an error that can be safely ignored:

INSERT INTO userParam(who) VALUES (CURRENT_USER)

You can modify this to avoid the error messagethis will not generate an error for existing users:

INSERT INTO userParam(who) SELECT (CURRENT_USER) FROM (SELECT 1) x WHERE NOT EXISTS (SELECT * FROM param);

The outer SELECT statement gives zero rows if the user has already been added to the userParam table and one row otherwise.

8.2.2. Platform-Specific Variations

Some minor variations are required for each platform. With the changes, the hack works well in MySQL, SQL Server, Access, and Oracle. The changed portions appear in bold text:

MySQL

MySQL does not allow DEFAULT CURRENT_USER (as of version 5.0.18). Here's the code to create the table and the view:

CREATE TABLE userParam ( who VARCHAR(20) , prmStart DATE , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userParam WHERE who=CURRENT_USER;

Here's the INSERT code you need to run at the start of each session to add the current user; because MySQL will not allow CURRENT_USER as the default for the who column, you must give it explicitly:

INSERT INTO userParam(who) SELECT CURRENT_USER FROM dual WHERE NOT EXISTS (SELECT * FROM param);

SQL Server

You'll need to replace CURRENT_USER with SYSTEM_USER:

CREATE TABLE userparam ( who VARCHAR(20) DEFAULT SYSTEM_USER , prmStart DATETIME , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userparam WHERE who=SYSTEM_USER;

Here's the INSERT code you need to run at the start of each session to add the current user:

INSERT INTO userParam(prmStart) SELECT NULL WHERE NOT EXISTS (SELECT * FROM param);

Oracle

You must replace CURRENT_USER with USER:

CREATE TABLE userparam ( who VARCHAR(20) DEFAULT USER , prmStart DATE , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userparam WHERE who=USER;

Here's the INSERT code you need to run at the start of each session to add the current user:

INSERT INTO param(prmStart) SELECT NULL FROM dual WHERE NOT EXISTS (SELECT * FROM param);

PostgreSQL

You cannot update or insert into a view in PostgreSQL, so you must change the underlying table instead. The customized view still works:

CREATE TABLE userParam ( who VARCHAR(20) DEFAULT CURRENT_USER , prmStart DATE , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userParam WHERE who=CURRENT_USER; INSERT INTO userParam(prmStart) SELECT NULL WHERE NOT EXISTS (SELECT * FROM param); UPDATE userParam SET prmStart = DATE '2007-01-01' WHERE who = CURRENT_USER;

Access

The Windows username comes from the function call ENVIRON('username').

You can create the userParam table from the Query Editor, start a new query, and then select Viewimages/U2192.jpg border=0>SQL from the menu and type in the text:

CREATE TABLE userParam (who VARCHAR(20) ,prmStart DATETIME ,PRIMARY KEY (who) )

You can create the param query with the following SQL:

SELECT prmStart FROM userparam WHERE who = ENVIRON('username');

The following Visual Basic code needs to run every time the application starts up. The OnLoad event of your splash screen is a good place to put it:

If DCount("*", "param") = 0 Then DoCmd.SetWarnings False DoCmd.RunSQL "INSERT INTO userParam(who, prmStart) " & _ "VALUES (ENVIRON('username'),Date( ))" End If

Apart from PostgreSQL (where you need to update the underlying table directly as shown above), the following code can be used to update param:

UPDATE param SET prmStart='2007-01-01';