Example: A Single-Row Connection-Level Table

Question: How do I create a table that can contain only a single row for each connection, and that row exists only as long as that connection exists, and is visible only on that connection?

Answer: Start with a non-shared CREATE GLOBAL TEMPORARY TABLE statement to ensure that rows inserted by each connection are only visible on that connection and are automatically deleted when the connection finishes.

Then, use a CHECK constraint to ensure that each connection can insert only one row.

Here's an example of a connection settings table; "non-shared" is the default when the SHARE BY ALL clause is omitted:

CREATE GLOBAL TEMPORARY TABLE connection_settings (
   one_row_per_connection    INTEGER NOT NULL DEFAULT 1 CHECK ( one_row_per_connection = 1 ),
   setting1                  INTEGER NOT NULL DEFAULT 0,
   setting2                  VARCHAR ( 100 ) NOT NULL DEFAULT '',
   setting3                  VARCHAR ( 1 ) NOT NULL DEFAULT 'Y',
   PRIMARY KEY ( one_row_per_connection ) ) 
   NOT TRANSACTIONAL;

The schema for a GLOBAL TEMPORARY TABLE is permanent and visible to all connections; in other words, you CREATE it once and then use it on each connection.

Each row of data, however, is temporary in the sense that it lasts no longer than the connection that inserted it, and each row is only visible to code running on the connection that inserted it.

Note: The rules that "data is temporary" and "only visible" do not apply when the SHARE BY ALL clause is used; see the Help for more information about non-shared versus shared global temporary tables.

The CHECK ( one_row_per_connection = 1 ) constraint enforces the "single row for each connection" requirement by limiting the primary key to a single value... since primary keys must be unique, that implies there can only be one row.

Tip: Don't forget to code NOT TRANSACTIONAL or ON COMMIT PRESERVE ROWS on all your GLOBAL TEMPORARY TABLE statements... unless you want your data in those tables to disappear every time you run a COMMIT. The default is ON COMMIT DELETE ROWS, one of those rare decisions that violates the rule "Watcom does things the way they should be done."

The resulting table is somewhat similar to connection-level variables created with the CREATE VARIABLE statement:

	Table	Variable
Create schema when:	Ahead of time	On connection
Create schema how:	CREATE GLOBAL TEMPORARY TABLE t ...	CREATE VARIABLE v...
Scope of schema:	All code on all connections	All code on connection
Data values:	Multiple columns	Single value
Create data when:	On connection	On connection
Create data how:	INSERT t ...	CREATE VARIABLE v ...
Scope of data:	All code on connection	All code on connection
Reference values how:	SELECT first, then reference	Direct references to variable names
Update data how:	UPDATE t SET c = ...	SET v = ...
Explicitly delete data:	DELETE t	DROP VARIABLE v
Data implicitly deleted:	End of connection	End of connection

Tip: Use the login_procedure option to ensure the row exists before the connection is used for anything else:

CREATE PROCEDURE DBA.connection_start() 
BEGIN
   INSERT connection_settings DEFAULT VALUES;
END; 

SET OPTION PUBLIC.login_procedure = 'DBA.connection_start';

Warning: Don't use a Connect EVENT: a row inserted inside an event will only be visible inside that event because it runs on its own connection.

Oh, and if you're worried about overriding the default login_procedure value sp_login_environment, go read the Help to see if any of the crap stuff in there means anything to you. It probably won't, but if it does, be sure to clutter up add the code to your procedure.

Here's an example of how data in the table can be used and updated:

BEGIN
DECLARE @setting1 INTEGER;
SELECT setting1 INTO @setting1 FROM connection_settings;
END;

UPDATE connection_settings SET setting2 = 'Hello, World!';

Neither the SELECT nor the UPDATE needs a WHERE clause because there's only one row.

Also, the UPDATE doesn't need a COMMIT, and it isn't affected by a ROLLBACK, because of the NOT TRANSACTIONAL clause. If you want your data to be involved in transaction processing, use ON COMMIT PRESERVE ROWS instead (or the default, ON COMMIT DELETE ROWS, if you're in that 0.01% who needs that kind of strangeness functionality :)

...and if you do use ON COMMIT PRESERVE ROWS instead of NOT TRANSACTIONAL, you going to need a COMMIT when you update your table... just sayin'.

---

Hey, that INSERT DEFAULT VALUES clause is pretty cool, isn't it? Betcha didn't know you could do that...

---