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.

It is also a very flexible database, which makes it highly suitable for use in the development of prototypes for applications that might eventually be implemented in other database systems. Finally, it's the one database that is provided with PowerBuilder (a single-user license that can be upgraded).

Connecting via PowerBuilder
Many of the other databases that PowerBuilder supports have a native client layer that the PowerBuilder driver interacts with. ASA does not provide such a native client layer. Instead, the interfaces it supports (e.g., ODBC, OLE DB, embedded SQL, and JDBC) interact directly with the database.

Configuring the PowerBuilder Connection Profile
PowerBuilder developers normally use the ASA ODBC driver to interact with ASA. The discussion of configuring an ODBC connection through DBParm parameters or editing the PBODB90.INI file are applicable to the use of ASA. Therefore, we will only be covering those same parameters here on a limited basis.

Data Types
ASA provides a number of common data types. Table 1 presents a description of the ASA data types and their analogous PowerBuilder native data types.

Development Time Considerations
When building client/server applications that target ASA databases, there are several development time aspects that should be considered. Choices that should be made early in the development cycle include how to best deal with the case sensitivity, whether or not to use qualified SQL in your PowerScript code and DataWindow objects, and whether or not to employ the PowerBuilder extended attribute tables. We'll explore these and other topics in the section that follows.

Identifier Case
ASA is a case-insensitive database in terms of identifiers such as table and column names. For example, a table could be created in mixed case as Employee, and referred to as Employee, EMPLOYEE, or employee. Note that unlike other databases, ASA remains case-insensitive even when the SQL statement is delimited.

SELECT LNAME, FNAME FROM EMPLOYEE
SELECT lname, fname FROM employee
SELECT Lname, FnAmE FROM EMPloyEE

SELECT "LNAME", "FNAME" FROM "EMPLOYEE"
SELECT "lname", "fname" FROM "employee"
SELECT "Lname", "FnAmE" FROM "EMPloyEE"

Other databases are case-sensitive, particularly if the SQL statements are delimited. Therefore, if your intent is to write cross-database applications, care should be taken with regard to the use of SQL delimiters and mixed case. The safest approach to use in this regard is to use upper case for database object names and avoid the use of embedded reserved words or characters that would require the use of delimiters.

Qualifying SQL with Owner Names
Like many other databases, ASA requires a database object reference to be qualified with the owner name. That is, there could be several EMPLOYEE tables in the database, each one under a different owner. When an unqualified SQL statement is submitted to the database, it assumes that the user is attempting to access a table that they own, essentially prefixing the user name to the object reference.

For a number of reasons the requirement for such prefixes may not be desired, particular for cross-database support. An easy way to accomplish this with ASA is to use ASA's group feature. Instead of creating the database objects under a specific user ID, create a group that is allowed to connect to the database (see Figure 1) and has Resource authority (the right to create tables and other database objects). Then use that group ID to create the database objects.

The developers can then be added to that group (see Figure 2), which allows them to reference the tables in code without requiring an owner prefix.

Note that adding a member to a group that has table creation rights doesn't mean that the users themselves will inherit such rights. They only inherit the object permissions, not the authorities, of the groups they are members of. Similarly, you would want to create a group with more limited rights that you would then make the end users of the applications members of.

Since the group is creating the tables in the database, the PowerBuilder catalog tables will also get created under that group ID. You'll need to have the developers ensure that their connection profiles to the database reference the group ID as the owner of those tables (see Figure 3).

Even though membership in the group means that the owner prefix is no longer required, PowerBuilder by default includes it anyway in all of the painters. This behavior can be changed through the PBTableOwner parameter in the PBODB090.INI file (see Figure 4).

Note that this parameter affects the display of all tables in the painters, not just those for which the user is a member of the group that owns them. The TableOwner setting of the TableCriteria DBParm parameter can be used to restrict these object lists to a particular group.

Interacting with the Database
There are three primary means of interacting with the ASA database from PowerBuilder: the DataWindow, stored procedures in the database, and embedded SQL. This division is somewhat artificial, as these methods can actually be combined. In particular, stored procedures can be used to select, update, insert, and delete methods for a DataWindow and stored functions can be used within embedded SQL statements. Such advanced techniques are covered in the following as well.

DataWindows
The simplest method of defining the source for a DataWindow's data is a simple select statement such as:

SELECT emp_id,
    manager_id,
    dept_id,
    emp_fname,
    emp_lname
FROM employee

If the intent is to show the employee's full name in such a case, a computed field could be added to the DataWindow that concatenates the emp_fname and emp_lname columns. Alternatively, the SQL statement could be modified to perform the concatenation through a computed column as the data is being retrieved:

SELECT emp_id,
    manager_id,
    dept_id,
    emp_fname || ' ' || emp_lname as emp_fullname
FROM employee