Surety is performing system maintenance this weekend. Electronic date stamps on new Prior Art Database disclosures may be delayed.
Browse Prior Art Database

An Efficient Method to handle variable SQL special register environments for SQL routines

IP.com Disclosure Number: IPCOM000010101D
Original Publication Date: 2002-Oct-23
Included in the Prior Art Database: 2002-Oct-23
Document File: 3 page(s) / 63K

Publishing Venue


This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 37% of the total text.

Page 1 of 3

  An Efficient Method to handle variable SQL special register environments for SQL routines

   This disclosure is to introduce an efficient method to handle variable SQL special register environments for SQL routines.

Many popular relational databases support the definition and use of SQL routines within a database. An SQL Routine is a set of logic defined and executed within the database, such as stored procedures (STP) or user-defined functions (UDF), which contain SQL statements. These SQL statements can reference known register names, referred to as SQL Special Registers, defined by the SQL language. The values in these registers can be used within the text of an SQL statement as a reference to the value currently contained in the register and, in some cases, these register values can also be updated by SQL statements. Those registers whose values can be updated by SQL statements are referred to as updatable SQL special registers.

Since SQL routines can themselves be invoked from within other SQL statements, the SQL language has incorporated the ability to define for an SQL Routine the desired behaviour for updatable SQL special registers with respect to the initial values and the special registers should be handled with respect to their initial values and the scope of impact if any changes to these values. There are three typical approaches to this issue which are called INHERIT, NORMAL or DEFAULT.

If the desired behaviour is defined as INHERIT, the SQL routine will inherit the values for all updatable special registers from its caller. If there are any updates inside this routine to the value of an updatable special register, they will not be reflected to the caller. If DEFAULT is selected, then the SQL routine will receive the set of values for the updatable special registers equivalent to the initial set of values that occurred at connection time. Similar to an INHERIT routine. if there is any update to a special register value inside this routine, it will not be reflected to the caller. If the chosen behaviour is NORMAL, the SQL routine will share the set of special register values with its caller. If there are any updates to the set of values by this routine, the new value will be reflected to the caller. Similarly, if the caller modifies a value for an updatable special register, the SQL Routine is aware of it.

Since every SQL routine can be defined to use a different behaviour, a relational database system must be able to support and respect the individual defined behaviour for each SQL routine in any number of combinations at one time. The simplest way to support this by a system is to make a separate and unique copy of the special register values each and every time an INHERIT or DEFAULT SQL routine is invoked. For a NORMAL SQL routine, it can use and share the special register with its caller. If an SQL routine is repeatedly visited, such as during the invocation of a UDF within an SQL statement, then the set of special...