Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Book Excerpt: Sybase Adaptive Server Anywhere

A powerful database

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.

More Stories By Bruce Armstrong

Bruce Armstrong is a development lead with Integrated Data Services (www.get-integrated.com). A charter member of TeamSybase, he has been using PowerBuilder since version 1.0.B. He was a contributing author to SYS-CON's PowerBuilder 4.0 Secrets of the Masters and the editor of SAMs' PowerBuilder 9: Advanced Client/Server Development.

Comments (3) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
mukul trivedi 12/28/07 02:47:36 AM EST

1. What is the exact diffrence between server and database?
2.Adaptive servers works as normal server or it is some diffrent from normal servers?

PBDJ News Desk 10/18/05 05:09:01 PM EDT

Book Excerpt: Sybase Adaptive Server Anywhere. Sybase's Adaptive Server Anywhere (ASA) is a small footprint, low maintenance database designed for mobile, embedded, personal and workgroup settings. In spite of its compact nature, it still provides all the 'big iron' features, such as replication, synchronization, and Java in the database.

PBDJ News Desk 10/12/05 03:50:42 PM EDT

Adaptive Server Anywhere. Sybase's Adaptive Server Anywhere (ASA) is a small footprint, low maintenance database designed for mobile, embedded, personal and workgroup settings. In spite of its compact nature, it still provides all the 'big iron' features, such as replication, synchronization, and Java in the database.