There is a limit to what can be accomplished through an inline-computed column. Fortunately, ASA not only allows SQL functions to be used in a SQL statement, but stored functions as well. Therefore, more complicated processing can also be performed. The concatenation of the employee's name, though trivial, will continue to be used in the following examples simply to demonstrate the technique. What can actually be accomplished through stored functions is significantly more powerful than simple concatenation.

You could create the following function in ASA:

CREATE FUNCTION fullname (
   firstname CHAR(30),
   lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
   DECLARE name CHAR(61);
   SET name = firstname || ' ' || lastname;
   RETURN (name);
END ;

And revise the select statement as follows:

SELECT emp_id,
    manager_id,
    dept_id,
    fullname ( emp_fname, emp_lname ) as emp_fullname
FROM employee

One rather unique feature of ASA is that computed columns can actually be created in the database. That is, the following statement will make the full employee name a computed column on the table itself:

ALTER TABLE employee
ADD emp_fullname char(61)
   COMPUTE ( emp_fullname ( emp_fname, emp_lname ) ) ;

And the select statement is then further simplified to:

SELECT emp_id,
    manager_id,
    dept_id,
    emp_fullname
FROM employee ;

The database automatically maintains the value of the computed column in the database. There are two particular advantages of this feature. The first is that the work involved in executing the function is handled by the database before the query is run. In the case of a rather complex function that can significantly improve performance. The second is that an index can be created on computed columns in the database as if they were regular columns. For this particular example, the index could be created as follows:

CREATE INDEX idx_fullname ON employee ( emp_fullname) ;

The result is that the computed column can be used in the WHERE, HAVING, and ON sections of the select statement and the index can be used (a condition referred to as sargable), which can dramatically improved performance in cases where the computed column is used in such sections.

Such stored functions can include error checking that returns information to the user through the RAISERROR function. The stored function could be rewritten as:

CREATE FUNCTION fullname (
   firstname CHAR(30),
   lastname CHAR(30) )
RETURNS CHAR(61)
BEGIN
   DECLARE name CHAR(61);
   if firstname is null then
    RAISERROR 23000 'First name is null!'
   end if ;
   if lastname is null then
    RAISERROR 23000 'Last name is null!'
   end if ;
   SET name = firstname || ' ' || lastname;
   RETURN (name);
END ;

And if the following statement was issued using it:

select fullname ( null, 'last_name' ) from dummy ;

The result is shown in Figure 5.

When a SQL select statement is used as the source for a DataWindow's data, the DataWindow is usually allowed to generate the database insert, update, and delete statements automatically based on the DataWindow update properties. One of those properties is the selection of an identity column that is usually used as the primary key for the table being updated.

An identity column is a column whose value is maintained by the database and is automatically incremented for new rows. ASA supports this through the autoincrement default property on a numeric column. PowerBuilder actually leaves the value of this column null in the insert statement and then must retrieve the value that the database assigned to the column after the insert is complete.

The statement that ASA uses to retrieve the value in the column is based on the value of the GetIdentity parameter of the [WATCOM50_SYNTAX] section of the PBODB90.INI file (or whichever syntax is pointed to in the PBSyntax value of the [Adaptive Server Anywhere] section of the PBODB90.INI file). By default, the value of the GetIdentity is:

GetIdentity='Select @@identity from dummy'

@@identity is a session global variable that holds the last value assigned to an autoincrementing column.

For some other databases that also support identity columns, this approach can run into problems if there is a trigger on the column that in turn updates another table that also contains an autoincrementing column. In those other databases, the @@identity variable will end up holding the last value assigned to any autoincrementing column and as a result will not hold the value actually assigned to the original table. In the case of ASA, though, this is not an issue. ASA treats the @@identity variable as a stack, so that it will hold the value assigned to the original table outside of any triggers and the value assigned to tables updated within the trigger. Therefore, other approaches for getting the value of the autoincrementing column that are used with other databases, such as selecting the max value of the column for the table, should not be used with ASA.