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:
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
( |
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.
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:
- Set the
disable procs
option off. - Choose .
- Edit the script to remove the
create table, create index
andgrant
statements, or ignore the error messages when the script is run. - 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 | Description |
---|---|
@update1 ,
@update2 , ..., @update n |
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 , ..., @insert n |
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 , ..., @where n |
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 |
Other important stored procedure parameters and their descriptions are shown in the table:
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.