Using the REFERENCES Clause to Point to a Simple Large Object

CREATE FUNCTION sel_text()
   RETURNING REFERENCES text;
   DEFINE blob_var REFERENCES text;
   SELECT blob_col INTO blob_var 
      FROM blob_table WHERE key_col = 10;
   RETURN blob_var;
END FUNCTION;

For simple large objects that are column values from the Projection list of a query, as in this example, the pointer in the returned descriptor references the sysblobs.spacename value from the system catalog, based on the BYTE or TEXT column definition.

For simple large objects that do not correspond to columns of permanent tables, however, the pointer references the dbspace of the database in which the UDR is defined. This is the default storage location for a BYTE or TEXT object that a UDR returns, when no location from the sysblobs table is known to the database server.

The DB-Access session in the following example creates two routines, udr1 and udr2, that each return the descriptor of a TEXT object:

CREATE DATABASE db WITH LOG;

CREATE TABLE t (c2 TEXT);
CREATE TABLE t1 (c2 TEXT);
LOAD FROM "t.unl" INSERT INTO t;

CREATE FUNCTION udr1 ( param_1 
   REFERENCES TEXT DEFAULT NULL )
   RETURNING REFERENCES TEXT 
      WITH (NOT VARIANT)
   DEFINE var1 REFERENCES TEXT;
   ON EXCEPTION
      RETURN param_1;
   END EXCEPTION;
   SELECT t.c2 udr1_col1 
      INTO var1 FROM t;
   RETURN var1;
END FUNCTION;

CREATE PROCEDURE udr2 ( OUT param_1 
   REFERENCES TEXT DEFAULT NULL )
      RETURNING INT;
   SELECT t.c2 udr1_col1 
      INTO param_1 FROM t; 
   RETURN 1; 
END PROCEDURE;

SELECT udr1(t.c2) query_1_col1 FROM t 
   INTO TEMP mytemp;

SELECT c2, slv1 FROM t1 
   WHERE udr2(slv1#TEXT) > 0 
   INTO TEMP mytemp;

In the SELECT statements that call these UDRs, the TEXT object that each query returns to the mytemp temporary table are stored in the dbspace of the db database.