Sybase Stored Procedures

The SYB connector supports the use of Sybase stored procedures. These can also be activated as service stored procedures.

Service Store Procedures

The SYB connector supports the use of Uniface service stored procedures to activate Sybase stored procedures.

To enable the use of Unicode in service stored procedures when Unicode support is enabled in the connector (using the unicode option), set the $DEF_CHARSET to UTF8.

Stored Procedures

The SYB connector uses Sybase stored procedures to enhance the performance of the most commonly executed commands. Performance is enhanced because the procedure is parsed at creation time, and compiled the first time it is executed, providing the DBMS is configured with a large enough procedure cache. Procedures are executed with the Sybase execute procedure SQL statement.

To disable the use of stored procedures, set the disable procs connector option. The SYB connector then uses plain SQL for all access Sybase tables.

The SYB connector never uses stored procedures to access the Sybase system tables sysobjects, syscolumns and so on.

The SYB connector uses two types of stored procedures:

SYB stored procedure types
Type Description
Per database Used for all Uniface entities. One such procedure must exist in each database.

Per database stored procedures are created during the installation or upgrade procedure by running the SQL script syb4xext.sql in all databases where Uniface is used.

Note:  When stored procedures are enabled, the per database procedures must be created for Uniface to access Uniface entities that are stored in Sybase.

Per table Designed for a particular Uniface entity. Used to access the Sybase table which corresponds to the Uniface entity.

Per table stored procedures are created for each table at the same time as the table is created, provided stored procedures are enabled (disable procs is set off). The procedures can be created either on the fly or with scripts generated by the Create Table facility.

Stored Procedures Created for Each Entity

When stored procedures are enabled, several stored procedures are created for each Uniface entity.

Stored procedure names are created by adding a suffix to the table name as shown in the table. If the name of the table is longer than 21 characters, only the first 21 characters are used to generate the procedure name. The 21st position truncation point is used on all platforms.

SYB Stored Procedure Names
Stored Procedure I/O operation carried out
TableName_FETCH Fetch a row using the primary key.
TableName_INSERT Insert a new row.
TableName_UPDATE Update a row using primary key information. If the table contains a U_VERSION field, that field is also included with the search conditions.
TableName_DELETE Delete a row using primary key information. If the table contains a U_VERSION field, that field is also included with the search conditions.
TableName_LOCK Lock a row which matches a specific primary key.
TableName_SELECT Retrieve an entire table.
TableName_SEL#I Select all of the rows in a table which match a particular value for index number #I.
TableName_OV_FET Only overflow tables have this procedure. This selects all rows in the overflow table which match a specified row in the normal table.

If the table was created with stored procedures disabled, and stored procedures are required at a later date, create the procedures as follows:

  1. Set the disable procs option off.
  2. Choose GoTo Deployment SQL Table .
  3. Edit the script to remove the create table, create index and grant statements, or ignore the error messages when the script is run.
  4. Execute the command file.

Parameters for Stored Procedures

Parameters are used to pass data values to stored procedures, so that the data values in the SQL statements in the procedures can vary from one execution to another.

Parameters for Stored Procedures
Parameters Description
@update1, @update2, ..., @updaten Parameters for the update list, where n is the number of fields in the table.

Text and Image fields are an exception to this rule because they are not updated in the stored procedure.

@insert1, @insert2, ..., @insertn Parameters for the insert list, where n is the number of fields in the table.

Text and Image fields are an exception to this rule. The insert procedure always sets non-mandatory Text and Image fields to NULL, and mandatory Text and Image fields are set to a dummy value. No value is inserted for identity fields since the DBMS provides the identity value.

The SYB connector uses the ‘write segmented field’ functionality of the Sybase to write the correct Text or Image values after the insert procedure has been executed.

@where1, @where2, ..., @wheren Parameters for the where list, where n is the number of fields in the where clause. These fields correspond to the fields in one of the indexes for the table.

If the table contains a U_VERSION field, an extra where parameter (@wheren+1) is created which includes that field in the where clause. This is only used for update and delete procedures on such tables.

Other important stored procedure parameters and their descriptions are shown in the table:

Other SYB stored procedure parameters
Parameter Description
@tablename Table name for the table existence check.
@indexname Name of an index.
@username User name for the table existence check.
@uid User identification.
@procname Procedure name for the procedure check.
@objname The complete name for a table.
@use_uversion Whether or not a delete or update should use U_VERSION in the where list for this particular execution of the procedure.

By default, the maximum number of parameters defined for a stored procedure is 255. The actual number of parameters defined for a stored procedure depends on the number of database fields in the entity. The maximum number of database fields you can define for an entity that needs to use stored procedures is 238.

Procedure Check

The SYB connector does not require tables to have stored procedures. It performs a check when a Uniface entity is first opened to determine if the table has stored procedures. The result of the check determines whether the connector can use stored procedures or SQL for tables.

If you want to disable this check (for example, when you know that all tables have stored procedures defined for them), you can do so by setting the disable proc check option.

When this option is on and procedures are enabled (the disable procs option has not been set), the connector automatically uses stored procedures when handling all tables other than Sybase system tables. In this case, procedures must exist for all Uniface entities.

The procedure check is performed by querying the Sybase system catalogs to determine if the table's write procedure, TableName_INSERT, exists. If the procedure does not exist, SQL is used to access the table.

Restrictions on Fetch and Lock Procedures

Fetch and lock procedures cannot be used on a view that contains Text or Image fields. SQL is used instead.

Related Topics