Chapter 7

Procedures and Triggers

This chapter describes procedures and provides you with the information necessary to create procedures of your own. It covers the following topics:

What is a procedure?

Format of a procedure

Data types supported in procedures

System constants supported in procedures

How to generate, store, and execute procedures

Using SAL functions in procedures

Error handling

Procedure examples (contained in the \Gupta\sp.sql directory)

Triggers

What is a procedure?

A SQLBase procedure is a set of Scalable Application Language (SAL) and SQL statements that is assigned a name, compiled, and optionally stored in a SQLBase database.

SQLBase procedures can be static or dynamic. Static procedures must be stored (at which time they are parsed and precompiled) before they are executed. Dynamic procedures contain dynamic embedded SQL statements, which are parsed and compiled at execution time. For this reason, they do not have to be stored before they are executed.

There are several different types of procedure implementations:

Stored procedures: compiled and stored in the database for later execution. They can be static or dynamic. You can define triggers on stored procedures.

Non-stored procedures: compiled for immediate execution.

Inline procedures: used optionally in triggers. You may want to specify the INLINE clause of the CREATE TRIGGER command to call inline procedure text. When you create the trigger, SQLBase stores these inline procedures in the system catalog.

SQLBases implementation of procedures will be familiar to anyone already using Guptas Team Developer, a graphical application development system. That product implements SAL (SQLWindows Application Language). SQLBase provides a set of SAL functions that you can embed in procedures, and the flow control language of procedures is the same as Team Developer programs. However, you do not need the Team Developer product to use these functions; they are provided by SQLBase.

SQLBase also provides preconstructed procedures as useful tools to help you maintain your database. See Appendix B of the Database Administrators Guide for a description of SQLBase-supplied procedures.

Why use procedures?

Procedures offer a number of benefits:

They simplify applications by transferring processing to the server.

They reduce network traffic by storing the SQL statements to be executed on the backend where the procedures are processed. The frontend need only call the procedure and wait for results.

They provide more flexible security, giving end-users privileges on data which they might not otherwise be allowed to access.

Storing procedures provide these additional benefits:

They improve runtime performance because the procedural logic is precompiled. In the case of static stored procedures, the SQL statements are also precompiled; as a result, the SQL execution plans are predetermined.

You have a centralized location of precompiled programs, which different sites can then access for their own customized applications. This facilitates control and administration of database applications.

You can store a procedure and then retrieve and execute this procedure from a variety of front-ends, such as SQLTalk, Team Developer, or a SQL/API application.

You can invoke an external function within a stored procedure, providing you with the flexibility to extend the functionality of your stored procedures, or add functionality to your existing applications by creating plug and play external components. Read Chapter 8, External Functions for details.

When used in conjunction with triggers, procedures also can implement business rules that are not possible from the database server through SQL declarative referential integrity. For examples and more information on triggers, read Triggers.

Stored procedures versus stored commands

SQLBase already allows you to store often-used SQL statements in stored commands for future execution. However, a stored command can only contain a single SQL statement. Procedures, on the other hand, allow you to create a program using procedural logic, data typing, and variables using multiple SQL statements.

Unlike stored commands, stored procedures themselves never become invalid, although the stored commands within procedures may become invalid. This means you do not need to automatically recompile the procedure with EXECUTE RECOMPILE, or flag it to be recompiled with ALTER COMMAND.

Note: When using procedures with Team Developer programs, be aware that there are some implementation issues you must address. These issues are discussed in the section Using procedures with Gupta applications.

Format of a procedure

SQLBase procedures follow a format and syntax similar to a Team Developer program. A SQLBase procedure has the following elements:

Name. This is the name of the procedure, which can be different from the name under which you store the procedure.

Parameters. You can define parameters for input and output to the procedure.

Local Variables. You can define local variables for temporary storage.

Action section. Use this section to control both the conditions under which the statements are executed and the order in which they are executed.

Unlike Team Developer, the elements of SQLBase procedures are case insensitive.

This example shows a sample procedure and its format.

Name

Every procedure has a name. For example:

PROCEDURE: WithDraw

The procedure name is a long identifier, and can contain up to 36 characters.

Note: Even though the colon is optional, you must supply it if your procedures are to be compatible with Team Developer.

When you store a procedure, you give it an additional name that lets you refer to the procedure as well as access it once it is stored (this parallels the syntax for stored commands). You can assign a stored name that differs from the procedure name. For example:

STORE WDPROC

PROCEDURE: WithDraw

Parameters

...

Note that you cannot replace an existing procedure with one that uses the same stored name. As in the example, assume you have stored procedure WithDraw under WDPROC. You cannot replace WDPROC with a procedure that uses the same stored name unless you have erased WDPROC first using the SQLTalk ERASE command.

Parameters

Parameters enable you to provide input to and receive output from a procedure. This section is optional; you do not have to define parameters for a procedure. You supply the values for all the parameters when you execute the procedure.

Declare a parameter using this syntax:

[Receive] DataType [:] ParameterName

Note: Even though the colon is optional, you must supply it if your procedures are to be compatible with Team Developer.

For example:

Parameters

Boolean: bDone

Date/Time: dtBirthDate

Number: nCount

Receive Number: nTotal

String: strLastName

See the Data types supported in procedures for information on valid data types for parameters.

Output parameters in procedures must be preceded with the keyword Receive:

Receive Number: nTotal

Note: Some vendors databases define both an "OUT" parameter type and an "IN/OUT" parameter type. SQLBase does not support OUT parameters explicitly, but any need for such a parameter can be met by using the SQLBase equivalent of "IN/OUT", which is "Receive" (Receive Number, Receive String, etc.).

SQLTalk accepts values for binding for input parameters. For output receive parameters, you must supply a place holder, with or without a value, for all binds which map to those parameters. If the receive parameter is used strictly as output, you can use a comma (,) with no leading space as a placeholder.

On the other hand, a SQL/API application uses bind values for input, and sets buffers to receive output values. In the SQL/API, an output parameters value (generated by an executing function such as sqlexe) can be retrieved with the sqlssb function (Set Buffer) before the procedure starts executing, and then by the sqlfet function (Fetch) after the procedure passes control back to the invoker.

Note: In SQLTalk, output strings default to 80. This means you should resize the column(s) generated from the procedure with the COLUMN command.

You cannot pass an array as a parameter. All parameters passed into a procedure keep the values that were passed in, whether null or not null.

Local variables

Local variables perform several functions in SQL statements:

They store data.

They bind input data to a SQL statement. Variables used in this way are called bind variables.

They specify where to put the output of a SQL SELECT statement. The SELECT statements INTO clause specifies the variables where query data is placed. Variables in an INTO clause are called into variables.

This section is optional; you do not have to define local variables for a procedure.

The Receive keyword is not supported for local variables.

Declare a local variable using the same syntax as parameters:

DataType [:] LocalVariableName

Local variables are available to and accessible by only the procedure in which they are defined. They are also automatic, which means that they are created when the procedure executes and destroyed when the procedure ends.

Data you store in a variable are active across all stages of a procedure; their initial values persist across multiple fetch and execute statements, and are destroyed only when the procedure closes. Once the procedure closes, however, these values are not retained for future invocations. See the section on the ON directive for information on procedure states.

Variable buffers are allocated dynamically.

In addition to those data types supported for parameters, the Local Variables section also supports Sql Handles and File Handles. For example:

Sql Handle: hSqlCur1
File Handle: hFileActive

Note: Even though the colon is optional, you must supply it if your procedures are to be compatible with Team Developer.

If you do not initialize a local variable, SQLBase assigns it a default value based on its data type when the procedure is invoked and before it takes control. For default value information, read the section Data types supported in procedures.

Like parameters, you cannot pass an array as a local variable in a stored procedure.

Actions

This section contains statements to be executed depending upon the state of the procedure. It also contains logic flow language that controls the order in which SQLBase executes the statements.

Read Appendix A for a detailed description of the SAL functions you can include in a procedure.

Unlike Team Builder, you cannot include user-defined functions in procedures. However, your procedure can invoke another procedure that performs the work of your desired function.

Statement blocks

A block in the Actions section contains a set of statements to be executed in successive order. All the statements in a block are either of the same indentation level or enclosed within Begin and End statements.

Indentation

Indentation is an important element of logic flow. Use it to control the order in which SQLBase executes blocks of statements in a procedure.

SQLBase is very strict about indentation, and a change in indentation is interpreted as a block change. For example, when defining parameters, make sure that all of them are indented by the same amount:

Parameters

Boolean: bDone

Date/Time: dtBirthDate

Number: nCount

Defining them according to the following example will produce an error:

Parameters

Boolean: bDone

Date/Time: dtBirthDate

Number: nCount

You can use spaces or tabs to implement indentation. If you are using spaces, one or more spaces defines a specific indentation.

Note: Do not mix spaces and tabs for indentation. For example, four spaces may appear to have the same indentation as a tab in your on-line editor, but the four spaces represent four levels of indentation, while a tab only represents one.

This is an example of valid indentation:

Loop Outerloop

If I3 > 0

If NOT SqlExecute (hSqlCur1)

Return 201

Set I3 = I3 - 1

Else

Break Outerloop

Using Begin and End statements (block delimiters)

Another way to achieve the same level of control is to use block delimiters to surround a set of statements. To use block delimiters, begin a set of statements with Begin, and end with End. This allows you to reduce the number of indentation layers in your program.

Using Begin and End statements reduces the number of indentation levels in the previous example:

On Procedure Execute

Loop Outerloop

Begin

If I3 > 0

If NOT SqlExecute (hSqlCur1)

Return 201

Set I3 = I3 - 1

Else

Break Outerloop

End

Block delimiters are only allowed in a procedures Actions section.

Note: The If, Else, Else if, and Loop statements require either indentation or a Begin and End statement.

Data types supported in procedures

You must specify one of the following data types when defining parameters and local variables in procedures.

The following table lists valid data types supported in procedures and their default value. It also lists their SQL standard naming prefix. Although not required, using these prefixes in the names of variables will help make your procedure self-documenting.

Data type

Default Value

Suggested Name prefix

Example

Comments

Boolean

FALSE

b

bOk

 

Sql Handle

none

hSql

hSqlCur1

Supported only for local variables.

Date/Time

null

dt

dtStartDate

 

String

null string

s (or) str

strLastName

Use the Long String data types for strings longer than 254 bytes

Long String

null string

s (or) str

strLastName

Supports strings longer than 254 bytes

Number

0

n

nSalary

 

Window Handle

0

hWin

hWinActive

Bind to the variable using the program data type SQLNUM. The same holds for set select buffer. Cannot be used for any arithmetic operation.

File Handle

0

hFile

hFileActive

Supported only for local variables. Cannot be used for any arithmetic operation

Note the following restrictions:

In SQLBase, you cannot do anything with an array other than pass it to a procedure.

Unlike Team Developer, you cannot use user-defined constants in a procedure. However, you can use system constants. Read the section System constants supported in procedures for details.

All data types can be an alternate form called a receive data type, which identify output parameters. Receive data types allow you to pass data to a procedure by reference rather than value. This means that SQLBase passes the variables storage address and the procedure has access to the original value which it can then change. For example:

Parameters

Receive Boolean: bOrderFilled

...

Actions

Set bOrderFilled = TRUE

Note: All parameters passed into a procedure keep the values that were passed in, whether null or not null.

Unless otherwise noted, procedure data types conform to SQLBase data type formats. Note that these may be different from Team Developer data type formats.

Boolean

Use this data type for variables that can be TRUE (1) or FALSE (0). For example:

Local Variables

Boolean: bDone

...

Actions

Set bDone = FALSE

Date/Time

Use this data type for date and/or time variables. For example:

Parameters

Date/Time: dtBirthday

...

Actions

If dtBirthday > 07/01/1983

Number

Use this data type for numbers with up to 15 digits of precision. For example:

Parameters

Number: nMonth

...

Actions

If nMonth = 3

...

Sql Handle

Use this data type to identify an existing connection to a database. All access to a database requires a Sql Handle. For example:

Local Variables

Sql Handle: hSqlCur1

...

Actions

Call SqlConnect (hSqlCur1)

String

Use this data type for character data. Unlike Team Developer, the maximum length of a procedure string is 64 Kbytes; however, if a string is used as a receive parameter, its length cannot exceed 254 characters on return from the procedure. If its length exceeds 254 characters, SQLBase issues an error message. Use the Long String data type to return strings longer than 254 characters,

Enclose literal strings in single quotes. For example:

PROCEDURE: CLIENTPROC

Parameters

Receive Date/Time: dtAppt
Receive String: sSelect

Local Variables

Sql Handle: hSqlCur1

Number: nInd

Actions

Call SqlConnect(hSqlCur1)

Set sSelect = 'Select max(APPT) from CLIENT into :dtAppt '

Call SqlPrepare(hSqlCur1, sSelect)

......

Long String

Use this data type for character data to return strings greater than 254 bytes or to bind the string to a LONG VARCHAR column type. Note that the behavior of a Long String data type is identical to the String data type with the following exceptions:

When used to return data (Receive Long String), the data type is identical to Long Varchar. For example, if you use sqldes() to describe the parameter, the data type returned will be SQLDLON. You must use the read long primitives to fetch this data.

When used to bind data, SQLBase uses the write long primitives to bind to the string variable. SQLBase treats the target column as a Long Varchar.

Enclose literal strings in single quotes. For example:

Variables

Long String: sLong

...

Set sLong = 'Long String'

Window Handle

Use this data type to store window handles. A window handle identifies a single instance of a particular window. This data type supports the SAL and WINAPI functions that use and manipulate window handles. If this data type is used in the parameter section of the procedure (that is, input/output), bind to the variable using the program data type SQLPNUM. The same holds for set select buffer. For example:

PROCEDURE: CLIENTPROC
Parameters

Window Handle: hWind

Actions

Call SalSendMsg(hWind, ...)

....

File Handle

Use this data type to store file handles. A file window identifies an open file. This data type supports the SAL file manipulation functions. For example:

Local Variables

File Handle: hFileActive

...

Actions

Call SalFileOpen (hFileActive, ...)

System constants supported in procedures

You can use the following standard system constants:

The null constants: STRING_Null, NUMBER_Null, and DATETIME_Null

You can check for nulls within procedures using null constants. For example, you can create a boolean expression, such as:

IF (A = NUMBER_Null)
IF (S = STRING_Null)

If the variable is null, the expression evaluates to TRUE.

The TRUE and FALSE boolean constants.

The Fetch_Delete, Fetch_EOF, Fetch_Ok, and Fetch_Update constants.

The DBP parameters: DBP_AUTOCOMMIT, DBP_BRAND, DBP_PRESERVE, DBP_VERSION, DBP_LOCKWAITTIMEOUT, DBP_ROLLBACKTIMEOUT.

The DBV_BRAND database brands: DBV_BRAND_DB2, DBV_BRAND_ORACLE, and DBV_BRAND_SQL.

For details on these constants, read the constant descriptions in Appendix A.

Note: System constants in SQLBase are case insensitive. Case sensitivity that appears in the system constants listed in this section apply only to Team Developer.

Using SAL statements

Use Scalable Application Language (SAL) statements to control the logic flow of the statements in a procedure. SQLBase provides the following SAL statements:

Break

Call

If, Else, and Else If

Loop

On

Return

Set

When SqlError

While

Break

The Break statement terminates a Loop statement. If you specify a loop name, that particular loop terminates. This allows you to break out of more than one level of loop. If you do not specify a loop name, the BREAK statement breaks out of the most recently-entered loop.

Syntax

Break [loopname]

Example

Loop

Set nOutput2 = nOutput2 + nInput2

If nOutput2 > nInput2 + 10

Break

Call

The Call statement executes a SAL function. SAL functions are listed in the section SAL functionality in SQLBase.

Syntax

Call FunctionName (Parameters, ... )

Example

Call SqlImmediate ( 'DELETE FROM CUSTOMER WHERE \
CUSTNO = 1290' )

Be aware that using the Call statement means that the functions return value is lost. However, if an error is returned, SQLBase passes control to the closest error handle. Read Error handling.

If, Else, and Else If

The If, Else, and Else If statements execute other statements based on the outcome of an expression. The Else and Else If parts are optional. For each If statement, you can code as many Else If sections as you want, but there can be only one Else section.

Indentation determines the conditional flow of control.

Syntax

If Expression1

<statement(s)>

Else If Expression2

<statement(s)>

Else

<statement(s)>

If Expression1 evaluates to TRUE, the first set of statements executes. If Expression1 evaluates to FALSE, Expression2 is evaluated. If Expression2 evaluates to TRUE, the second set of statements executes. If Expression2 evaluates to FALSE, the third set of statements executes.

Example

If nMonthly_Salary < 1000

Set nTax_Rate = 10

Else If nMonthly_Salary < 2000

Set nTax_Rate = 20

Else

Set nTax_Rate = 25

Loop

The Loop statement repeats a set of statements until a Break or Return statement is executed.

Syntax

Loop [loopname]

The loopname is optional. Specifying a loopname lets you refer to that loop in a later Break statement.

Examples

Loop

If nCount = 100

Return 1

Set nCount = nCount + 1

and:

Loop Outer

If I3 > 0

If NOT SqlExecute ( hSqlCur )

Return 201

Set I3 = I3 - 1

Else

Break Outer

On <procedure state>

The ON directive identifies the procedures current state, such as startup or executing. When a procedure is at a specific state, the statements indented underneath it are processed. The state of a procedure changes as the procedure execution progresses. A procedure can be at any of the following states:

Procedure Startup

Procedure Execute

Procedure Fetch

Procedure Close

Using ON directives is optional. If you do not specify an ON directive in a procedure, SQLBase processes the entire procedure when the calling program issues an execute command. In other words, not specifying any ON directive in a procedures Actions section is equivalent to including only an On Procedure Execute section under Actions (see the following paragraphs).

The default state (On Procedure Execute) is often adequate for many procedures. However, there are two situations in particular which do require one or more specific ON <procedure states>:

If you wish to repeatedly execute a procedure, such as when supplying different parameter values, it can be more efficient to code an On Procedure Startup state that contains commands requiring only a single execution (for example, database connections and variable assignments.) This avoids unnecessary multiple executions of these commands.

When you are fetching multiple rows, an On Procedure Fetch state is required.

SQLBase processes the Procedure Startup and Procedure Close sections only once. The Procedure Execute and Procedure Fetch sections can be processed as many times as you want. Local variables values are retained through multiple execute and fetch operations; the values are only destroyed at the close section.

SQLBase only allows you to specify On directives at the topmost level of the Actions section. In other words, you cannot nest an On directive within a statement block or between Begin and End statements.

To retrieve all the output data generated by the procedures, you must declare as many output variables as the number of items you want returned.

The following paragraphs describe the different procedure states.

Procedure Startup. A procedure is in procedure startup state after the following two steps are completed:

1. The calling program compiles the procedure (for example, with the SQL/API sqlcom function).

2. The calling program executes the procedure for the first time (for example, with the first SQL/API sqlexe function).

After processing the commands in the Procedure Startup stage, the first execute command from the calling program also processes the commands in the Procedure Execute stage. In other words, the calling programs first execute command processes both the Procedure Startup and Procedure Execute sections.

However, subsequent execute commands from the calling program only process the Procedure Execute stage; they do not process the Procedure Startup section again.

Procedure Execute. A procedure is in procedure execute state after the following two steps are completed:

1. The calling program first executes the procedure.

2. The Procedure Startup section is processed.

The Procedure Execute section is processed and reprocessed each time the calling program issues subsequent execute commands.

Procedure Fetch. If the calling program issues a FETCH command (for example, with the SQL/API sqlfet function) and you have a Procedure Fetch section, the statements in the Procedure Fetch section are processed. The Procedure Fetch section is processed and reprocessed each time you issue a FETCH command.

You must include a Procedure Fetch section to fetch multiple rows in your procedure. It is recommended that you also include a Return statement (see the following section on Return) to first return 0 while fetching is in progress, and then return 1 when the fetch is finished.

To retrieve all the output data generated by the procedures, you must declare as many output variables as the number of items you want returned.

Note that for each row returned by a procedure, the On Procedure Fetch section is executed. With multi-row buffering, therefore, a FETCH command from the client can cause the On Procedure Fetch section to be executed several times (as many times as the number of rows that fit into the buffer, or until end of the fetch). See the following Examples section which contains a procedure that demonstrates multi-row buffering behavior. Although multi-row buffering is a performance feature, it can result in unexpected behavior.

For example, you may expect that a single fetch command from the client causes the Procedure Fetch section to issue a COMMIT each time it returns a row. But instead, you find with multi-row buffering that the On Procedure Fetch section issues several COMMITs for the first row returned to the client.

If needed, you can have the On Procedure Fetch section generate exactly one row for each fetch call from the client, by setting the FETCHTHROUGH mode ON at the client. The default is OFF.

There are two ways to set FETCHTHROUGH mode:

From SQLTalk, use the SET FETCHTHOUGH ON command

From SQL/API, use sqlset function with the SQLPFT parameter

Procedure Close. Finally, when the calling program either issues a disconnect command (for example, with the SQL/API sqldis function) or you compile another command on the same cursor that the calling program was using to execute the procedure, the Procedure Close section is processed.

Note: Return values are not allowed in the Close section. You can call functions, but not specify returns.

Syntax

On <procedure state>

<statement(s)>

Examples

This section shows examples of various procedure states using the ON directive. You can find most of the examples shown in this section in the directory \Gupta\SP.SQL. These examples use the following PRODUCT_INVENTORY table:

create table PRODUDCT_INVENTORY (NAME varchar(25),
INVENTORY decimal (3,0), WHEN date);
insert into PRODUDCT_INVENTORY values (:1,:2,:3)

\

JF 12R,132,13-OCT-1992
DJ Y5Y,165,11-OCT-1992
DJ Y5Y,159,12-OCT-1992

/

Example with ON PROCEDURE states. This example prepares, executes, and fetches results from a procedure called PRODUDCT_INPROC.

PREPARE
PROCEDURE: PRODUDCT_INPROC
Parameters
String: sName
Receive Number: nINVENTORY
Local Variables
Sql Handle: hSqlCur1
String: sSelect
Number: nInd
Actions

On Procedure Startup
Call SqlConnect(hSqlCur1)
Set sSelect = 'Select INVENTORY from PRODUDCT_INVENTORY \
where NAME = :sName into :nINVENTORY'
Call SqlPrepare(hSqlCur1, sSelect)

On Procedure Execute
Call SqlExecute(hSqlCur1)

On Procedure Fetch
If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0

On Procedure Close
Call SqlDisconnect(hSqlCur1)

perform PRODUDCT_INPROC

\

JF 12R,,

/

FETCH 1;

perform PRODUDCT_INPROC

\

DJ Y5Y,,

/

FETCH 2;

SELECT * from PRODUDCT_INVENTORY;

1. This state is processed only once on the first EXECUTE by the calling program. If the calling program re-executes the procedure, the commands in this section are not processed again. This reduces procedure performance overhead.

2. This state is processed every time the calling program issues an EXECUTE command. If there are no ON <procedure states> coded, the procedure defaults to this state.

3. This state is processed every time the calling program issues a FETCH command, and is essential to fetching multiple rows.

4. This state is processed only 1) after the procedure has finished all processing, or 2) if another command is compiled or executed on the calling programs current cursor, or that cursor becomes disconnected.

5. The calling program executes the procedure for the first time. The On Procedure Startup and On Procedure Execute states are processed. Note that the second comma used in the SQLTalk PERFORM command for the binding of the Procedure provides the required placeholder for the procedures Receive parameter nINVENTORY. You must provide either a placeholder comma or an argument value for all procedure parameters.

6. This the first fetch issued by the calling applications. The On Procedure Fetch state is processed multiple times until end-of-fetch or until the buffer is full.

7. The calling program executes the procedure for the second time with a different bind value. Only the On Procedure Execute state is processed.

8. The calling program issues another fetch, this time with a different bind value. Two rows are returned to the client.

9. The On Procedure Close state is processed for the previous procedure.

Example with no On Procedure states. The next example compiles, executes and fetches a single row from a procedure which defaults to the On Procedure Execute state for all code under Actions.

PROCEDURE: PRODUDCT_INPROC
Parameters
Receive Number: nSumINVENTORY
Local Variables
Sql Handle: hSqlCur1
String: sSelect
Number: nInd
Actions

Call SqlConnect(hSqlCur1)
Set sSelect = 'Select max(INVENTORY) from
PRODUDCT_INVENTORY into :nSumINVENTORY'
Call SqlPrepare(hSqlCur1, sSelect)
Call SqlExecute(hSqlCur1)

If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0
Call SqlDisconnect(hSqlCur1)
\

,

/

1. Since there are no On Procedure statements, the entire procedure defaults to the On Procedure Execute state.

2. There is no On Procedure Fetch state in this procedure. This means that the calling program can FETCH from the ON Procedure Execute state by embedding SAL fetch calls like SqlFetchNext. However, in this instance you can only fetch and return to the caller a single row (even if within the procedure the fetch is in a loop). In this case, the caller's FETCH will only return the receive parameter values and perform no other processing.

Example with single row fetch and multiple row result. This example generates a single row fetch and then manipulates that data in order to produce a multiple row result. In this case the output is only indirectly tied to the database. This is a good method to produce "what-if" scenarios. In general, any fetches from the calling application do not necessarily have to have database sources within the procedure.

PROCEDURE: PRODUDCT_INPROC
Parameters
String: sName
Receive Number: nCurrentIN
Receive Number: nDays
Local Variables
Sql Handle: hSqlCur1
String: sSelect
Number: nMaxINVENTORY
Number: nInd
Actions
On Procedure Startup
Call SqlConnect(hSqlCur1)
Set sSelect = 'select max(INVENTORY) \
from PRODUDCT_INVENTORY
where NAME = :sName into :nMaxINVENTORY'
Call SqlPrepare(hSqlCur1, sSelect)
On Procedure Execute
Call SqlExecute(hSqlCur1)

Call SqlFetchNext(hSqlCur1, nInd)
Set nCurrentIN = nMaxINVENTORY
On Procedure Fetch

If nCurrentIN < 200
Set nCurrentIN = nCurrentIN + 10
Set nDays = nDays + 1
Return 0
Else
Return 1
On Procedure Close
Call SqlDisconnect(hSqlCur1)
\
DJ Y5Y,,,

/

1. Because an On Procedure Fetch state is also coded, this single row fetch is not returned to the caller and is only used internally by the procedure for subsequent processing.

2. This statement lists the inventory by day (10 daily increase) until the inventory is greater than 200, starting from the historical maximum inventory. In this case, the caller is not directly fetching from the database.

Example of fetch with default multi-row buffering behavior. This example generates a multi-row buffer when a single fetch has been issued against the procedure. This example is only intended to show the affect of multi-row buffering.

create table X (COL1 int);


TABLE CREATED


insert into X values(:1)

\
1
2
3
/

 

PROCESSING DATA


1

2

3


3 ROWS INSERTED


create table Y (COL1 int);

 

TABLE CREATED

 

-- Set FETCHTHROUGH ON at client before executing

-- this procedure if you want to maintain 6.0.0 procedure
-- fetch semantics:

 

prepare

procedure: MROWBUF1

Parameters

Receive Number: nCol1

Local Variables

Sql Handle: hSqlCur1

Number: nInd

Actions

On Procedure Execute

Call SqlConnect( hSqlCur1 )

Call SqlPrepareAndExecute( hSqlCur1, 'select\

COL1 from X into :nCol1')

! 1 fetch from client causes On Procedure Fetch

! to be executed multiple times

On Procedure Fetch

If NOT SqlFetchNext( hSqlCur1, nInd )

Return 1

Else

Call SqlImmediate('insert into Y values \

(:nCol1)')

Return 0

On Procedure Close

Call SqlDisconnect( hSqlCur1 )

;

 

STATEMENT PREPARED

 

perform;

 

PROCESSING DATA

 

STATEMENT PERFORMED

 

fetch 1;

 

NCOL1

=====

1

 

1 ROW RETRIEVED FROM PROCEDURE

 

-- 3 rows should be inserted into Y because 1

-- fetch from client causes On Procedure Fetch

-- to be executed 3 times in this case.

 

select * from Y;

 

COL1

====

1

2

3

 

3 ROWS SELECTED

Example of data manipulation at the server if no data needs to be fetched at the client. This example is the recommended method for achieving the same results in the previous example. This example omits the On Procedure Fetch section.

drop table y;
create table y;
procedure: MOVE_DATA

Local Variables

Sql Handle: hSqlCur1

Number: nInd

Number: nCol1

Actions

! Omission of On Procedure section defaults

! to On Procedure Execute

Call SqlConnect( hSqlCur1 )

Call SqlPrepareAndExecute( hSqlCur1, 'select \

COL1 from X into :nCol1')

While SqlFetchNext( hSqlCur1, nInd )

 

Call SqlImmediate('insert into Y values (:nCol1)');

 

0 ROWS RETRIEVED FROM PROCEDURE

 

-- Same result as earlier example without the

-- need for a client fetch:

 

select * from Y;

 

COL1

====

1

2

3

 

3 ROWS SELECTED

Returrn

The Return statement breaks the flow of control and returns control to the calling program.

The exception is when a Return is executed from the When SqlError section. In this situation, control is returned back to the procedure with the boolean return (TRUE/FALSE). This becomes the return value for the failed SAL Sql* function. The procedure then resumes execution according to the Boolean return.

Note: Return values are not allowed in the Close section of a stored procedure. You can call functions, but not specify returns.

If you do not specify a Return statement in a procedure, one of the following codes is returned to the calling program:

If a SQL error occurs and there is no When SQLError block, the procedure returns the error code. If there is a When SQLError block and a return statement within the block, the procedure does not return the error code.

If no error occurs, the procedure returns 0.

Note: If the calling program performs fetches in a loop and expects an end-of- fetch return from the procedure, the On Procedure Fetch section must be coded with an appropriate return (usually Return 1) or the or the calling program will go into an endless loop

Syntax

Return <expression>

The expression is mandatory, and can be anything that evaluates to a number.

If you code a Return statement in a When SqlError block (see the section on When SqlError), you can only return a boolean such as TRUE or FALSE.

If you code a Return statement outside of a When SqlError block, you can only return integer values. You can code these as either constants or variables. You cannot return a string, date/time, or SQL Handle local variable type.

Example

On Procedure Startup

When SqlError

Set nRcd = SqlError(hSqlCur1)

If nRcd = 601

Return FALSE

Else

Return TRUE

.....

....

On Procedure Fetch

If NOT SqlFetchNext(hSqlCur1, nInd)

Return 1

Else

Return 0

....

Set

The Set statement assigns a value to a variable. You can set a variable to the value of another variable.

Syntax

Set VariableName = Expression

Example

!Declare two variables for End-of-File and Return Code

Local Variables

Boolean: bEOF

Number: nRCD

...

Actions

Set bEOF = FALSE

Set nRCD = 0

Trace

The Trace statement prints the value of one or more variables. Use it when debugging a procedure to check the values of variables. For example, code a Trace statement immediately before and after a command that you expect will change the value of a variable.

This statement is different from the SQLTalk SET TRACE command, which is issued independently of the procedure and traces every statement the procedure executes. You do not need to run SET TRACE ON to use the Trace statement.

By default, output from the Trace function is sent to the Process Activity screen for a multi-user server, and is not displayed for a single-user engine. Generally, you will want to direct the output to a file on the server with the SQLTalk SET TRACEFILE command.

Syntax

Trace Variable1, Variable2, ..., VariableN

Example

This example shows a procedure using the Trace statement to trace the values of two variables nCount and nRcd. It traces the values at different points in the procedure.

PROCEDURE: TRPROC

Local Variables

Number: nCount

Actions

Trace nCount

Loop

Set nCount = nCount + 1

Trace nCount

If nCount > 10

Trace nCount

Return 0

;

When SqlError

The When SqlError statement declares a local error handler. To learn more about local error handling, see the Error Handling section later in this chapter.

Syntax

When SqlError

<statement(s)>

Example

This example demonstrates local error handling with SqlError. It uses the following tables JF and PRODUDCT_INVENTORY:

CREATE TABLE JF 12R,132,13-OCT-1992 (NAME varchar(25),
INVENTORY decimal (3,0), WHEN date);

INSERT INTO PRODUDCT_INVENTORY values
(JF 12R,132,13-OCT-1992);

COMMIT;

This examples also uses the following stored command INVENTORY_QUERY:

STORE INVENTORY_QUERY
SELECT INVENTORY from PRODUDCT_INVENTORY
where NAME = :1;

To create the error condition, the stored command is dropped prior to procedure execution. The procedures When SqlError section traps error #207 (Command not found for retrieval) and fixes the problem of the missing stored command.

ERASE INVENTORY_QUERY;
PROCEDURE: ILPROC
Parameters
String: sName
Receive Number: nINVENTORY
Local Variables
Sql Handle: hSqlCur1
Number: nInd
Number: nRcd
Actions
On Procedure Startup

When SqlError
Set nRcd = SqlError(hSqlCur1)
If nRcd = 207
Call SqlStore(hSqlCur1, 'INVENTORY_QUERY', \
'select INVENTORY \
from PRODUDCT_INVENTORY \
where NAME = :1 into :2')
Call SqlCommit(hSqlCur1)

Call SqlRetrieve(hSqlCur1, 'INVENTORY_QUERY', \
':sName', ':nINVENTORY')
Return TRUE

Call SqlConnect(hSqlCur1)

Call SqlRetrieve(hSqlCur1, 'INVENTORY_QUERY', \
':sName', ':nINVENTORY')
On Procedure Execute
Call SqlExecute(hSqlCur1)
On Procedure Fetch
If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0
On Procedure Close
Call SqlDisconnect(hSqlCur1)

\

DJ Y5Y,,

/

 

1. This exception handling routine can detect the SQL error generated by the SqlRetrieve call, and handle this error by restoring the non-existing stored command. In order to continue processing the procedure, the error handler returns TRUE back to the procedure, and executes the stored command. If other SQL errors are encountered, no Return is executed; control (along with the SQL error code) is immediately returned to the calling program.

2. This call will fail due to the non-existing stored command. In this example, When SqlError forces SqlRetrieve to return TRUE, and the procedure continues to execute successfully.

While

The While statement repeats until the expression being evaluated becomes FALSE.

Syntax

While Expression

<statement(s)>

Example

...

While nInputVar3 > 0

If NOT SqlExecute ( hSqlCur1 )

Return 201

Set nInputVar3 = nInputVar3 - 1

...

Comments

Comment lines allow you to include explanations in a procedure. A comment starts at the beginning of a line with an exclamation point (!) and ends with a carriage return or line feed character. Comments and code are not allowed on the same line.

You do not need to follow indentation rules for comments.

Syntax

! Comment line

Example

! These are comment lines; SQLBase does not attempt to

! execute them.

Operators

These operators are supported in procedures and, excluding string concatenation, are listed according to precedence:

Operator

Description

( )

Parentheses

unary -

Unary

*, /

Numeric: multiply, divide

+, -

Numeric: add, subtract

>, <, >=, <=

Relational: greater than, less than, greater than or equal to, less than or equal to

=, !=

Relational: equal to, not equal to

&

Bitwise AND

|

Bitwise OR

NOT

Boolean NOT

AND

Boolean AND

OR

Boolean OR

||

Concatenate string

Continuation lines and concatenation

Use a backslash (\) at the end of a line to continue a statement on the next line. For example:

Actions

Set sUpdate = 'UPDATE Checking \

Set Balance = Balance - :nAmount \

WHERE AccountNum = :nAccount'

You can also use the double line symbol (||) to concatenate strings. For example:

Set sWhere = 'where INVENTORY <200'

Set sSelect = 'SELECT name, inventory \
from PRODUDCT_INVENTORY ||sWhere || 'into :sName,
:nINVENTORY'

Generate, store, execute or drop a procedure

This section describes how to generate, store, execute, and drop procedures through SQLTalk. It also describes debugging a procedure and security issues.

You can also perform these functions through the SQL/API (see the section Using SQL/API functions with procedures later in the chapter for a list of associated functions), through SQLConsole with the Procedures Editor, and through Team Builder with the following functions:

SqlStore

SqlRetrieve

SqlExecute

SqlDropStoredCmd

See the documentation for these products for more detail. In addition, SQLBase provides an online sample application called sp.app that demonstrates calling procedures in a Team Developer application.

Like all other SQL commands, procedures can be stored, retrieved, compiled, and executed through applications such as the SQL/API.

Generating a procedure

To generate a procedure, use the SQL PROCEDURE command. Read the SQL Language Reference for detailed information on this command. When you generate the procedure, specify the SAL statements and any parameters and local variables in the Actions section.

If you turn on result sets on the client, you can scroll forwards and backwards through the result set returned by a procedure. The result set for procedures is preserved across COMMIT and ROLLBACK operations, even if preserve context mode is off. In SQLTalk, result set mode is OFF by default.

Note: To avoid necessary performance degradation, keep result set mode OFF if you are not using scrollable cursors. When set to ON, SQLBase builds the result set for a procedure.

Following are restrictions to note when generating procedures:

If you are using a network version of SQLBase, you cannot create procedures that perform SQL commands that use a SET SERVER command. These commands are:

CREATE DATABASE
DROP DATABASE
CREATE STOGROUP
DELETE
INSTALL DATABASE
DEINSTALL DATA

Recursion and nesting limits of procedures are determined by various settings in your system, such as available memory.

You cannot include DDL commands in static procedures. The only exception is if you are specifying the LOAD...ON SERVER.. command in the procedure and the file you are loading contains DDL commands.

Restriction mode to filter a result set is not supported for procedures.

Procedure Validation

Typically, a procedure performs some action on a table, or contains stored commands that reference tables in a single SQL statement. Note that SQLBase allows users to drop or alter a table even if it is referenced in a procedure or in a stored command that is contained in a procedure.

If the object a procedure references is changed or no longer exists, the procedure remains valid. However, SQLBase issues a runtime error about the missing objects when the procedure is executed. In addition, if you attempt to load a static procedure that references a dropped or altered object, SQLBase also issues errors when it cannot locate the missing or altered objects.

To execute and load procedures successfully, be sure to recreate any referenced object that is dropped, or restore any referenced object that is altered to its original state (as known by the procedure).

Procedure Example

The following procedure updates and returns bank account balances. In this example, the procedure is executed through SQLTalk. This example uses the following table:

CREATE TABLE CHECKING (ACCOUNTNUM number, BALANCE
number);

PROCEDURE: WITHDRAW

Parameters

Number: nAccount

Number: nAmount

Receive Number: nNewBalance

Local Variables

String: sUpdate

String: sSelect

Actions

Set sUpdate = 'UPDATE CHECKING \

set BALANCE = BALANCE - :nAmount \

where ACCOUNTNUM = :nAccount'

Call SqlImmediate(sUpdate)

Set sSelect = 'SELECT BALANCE from CHECKING \

where ACCOUNTNUM = :nAccount \

into :nNewBalance'

Call SqlImmediate(sSelect)

\

1,50,,

/

Remember to follow the indentation guidelines when creating your procedure. Read the section Indentation for more information. Also, if you are breaking a long line to span multiple lines, you must use a backslash (\) at the end of the line as a continuation marker.

Of course, typing a long procedure directly into the SQLTalk interface is time-consuming, especially if you make typing errors. Generally, you will want instead to create a script that contains the PROCEDURE command. You can then use the SQLTalk RUN command to run this script in SQLTalk.

Static versus dynamic procedures

A procedure is either dynamic or static. Dynamic is the default.

The following table lists important differences between static and dynamic procedures.

Feature

Dynamic

Static

Require storing to execute?

No

Yes

Parse/precompile procedural logic?

Yes

Yes

Parse SQL at store time?

No

Yes

Precompile SQL at store time?

No

Yes

Dynamic SQL support?

Yes

No

*SQL performance

Slower

Faster

Use for Triggers?

No

Yes

* Performance of Dynamic procedures can be enhanced by retrieving previously stored SQL commands (SqlRetrieve as opposed to SqlPrepare).

Static procedures. SQLBase compiles and optimizes (determines the query plan) the SQL statements embedded in a static stored procedure. These statements and their associated query execution plans are kept in the database. Static procedures must be stored before they can be executed.

You must be sure that a static procedures embedded SQL commands meet the following criteria:

They are not data definition language (DDL) statements.

They are string literals and contain no variables other than bind or INTO variables.

The first requirement means that you cannot include a CREATE, ALTER, or DROP command in a static procedure. However, the procedure can contain a LOAD.. ON SERVER command that has DDL statements.

The second requirement means that SQLBase must know what the command string is at compilation time. For example, you cannot include the following excerpt in a static stored procedure:

Set sCmd = 'select * from employee'

...

SqlPrepare (cur, sCmd)

You must specify the actual command string itself:

SqlPrepare (cur, 'select * from employee')

As another example, this statement meets the static requirements:

Select Col1, Col2 from sysadm.Table1 into :Out1, :Out2

but these do not:

Set sColumns = 'Col1, Col2'

...

Set SELECT = 'Select' || sColumns || 'from sysadm.Table1
into :Out1, :Out2 '

Note that as with any statement that contains bind variables, SQLBase must determine the optimal access method without all the necessary information.

While static procedures do not provide the flexibility of dynamic procedures, they do optimize and parse SQL statements before storing and hence yield higher performance at runtime.

The following example shows a static stored procedure:

STORE STATICS
PROCEDURE: STATIC_SQL static
Parameters
Receive String: sName
Receive Number: nINVENTORY
Local Variables
Sql Handle: hSqlCur1
Number: nInd
Actions
On Procedure Startup
Call SqlConnect(hSqlCur1)

Call SqlPrepare(hSqlCur1, 'select NAME, INVENTORY from \
PRODUDCT_INVENTORY into :sName, :nINVENTORY')
On Procedure Execute
Call SqlExecute(hSqlCur1)
On Procedure Fetch
If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0
On Procedure Close
Call SqlDisconnect(hSqlCur1)

/
execute STATICS

\

,,

/

 

1. When static procedures are executed, the SqlPrepare statement is not reprocessed since all SQL statements within a static procedure are precompiled. If you have already stored the SQL statement either using the SQLTalk STORE command or within the procedure using the SqlStore() function, SqlRetrieve() can be substituted.

Dynamic procedures. A dynamic procedure can contain dynamic embedded SQL statements. Because the dynamic SQL string components can change, the SQL statements cannot be precompiled.

Unlike static procedures, dynamic procedures do not have to be stored before they are executed.

Note that since SQL statements in a dynamic stored procedure are not parsed until execution, SQLBase does not catch any SQL errors in the procedure when you store it.

The previous examples of invalid embedded SQL statements for static procedures are acceptable for dynamic procedures:

Set sCmd = 'select * from employee'

...

SqlPrepare (cur, sCmd)

and:

Set sColumns = 'Col1, Col2'

...

Set SELECT = 'Select' || sColumns || 'from sysadm.Table1
into :Out1, :Out2 '

To improve dynamic procedure performance and avoid the possibility of runtime errors, you can store SQL commands outside of the procedure (as opposed to using SqlPrepare within the procedure) and then retrieve and execute them within the procedure. However, this prevents you from using dynamic SQL for that particular SQL statement.

The following example shows a dynamic SQL procedure. This procedure cannot be static because of the symbolic string substitution of the SQL statement found in the SqlPrepare() call. The SQL statements in a dynamic procedure are not precompiled and so are not optimized or parsed when stored.

store DYNAMITE
procedure: DYNAMIC_SQL
Parameters
Number: nOver200
Receive String: sName
Receive Number: nINVENTORY
Local Variables
Sql Handle: hSqlCur1
String: sWhere
String: sSelect

Number: nInd
Actions
On Procedure Startup
Call SqlConnect(hSqlCur1)
If nOver200 = 1
Set sWhere = 'where INVENTORY > 200'
Else
Set sWhere = 'where INVENTORY < 200'
Set sSelect = 'select NAME, INVENTORY \
from PRODUDCT_INVENTORY ' || sWhere || ' into
:sName, :nINVENTORY'
Call SqlPrepare(hSqlCur1, sSelect)
On Procedure Execute
Call SqlExecute(hSqlCur1)
On Procedure Fetch
If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0
On Procedure Close
Call SqlDisconnect(hSqlCur1)

/
execute DYNAMITE

\

1,,,

/

The advantage to dynamic procedures is that they are more flexible than static procedures. You can run and rerun a dynamic stored procedure with embedded dynamic SQL by using string substitution to produce different SQL commands at run time.

Determining whether to store a procedure as dynamic or static. If you have a stored procedure that contains SQL statements, some of which would benefit from static storage and others which would benefit from dynamic storage, consider breaking the procedure into several smaller static and dynamic procedures. For example, you might have a main static stored procedure that calls several dynamic stored procedures.

Storing a procedure

Storing a procedure stores it in the system catalog for future execution. You can then later retrieve and execute it.

When you create the procedure with the PROCEDURE command, you specify whether it is a dynamic or static stored procedure; dynamic is the default. When you actually store the procedure, SQLBase also stores the procedures execution plan.

You can store a procedure under a different name than the one it is created with. For details, read the section Name.

Note: You cannot replace an existing procedure with a procedure that uses the same stored name. You must first use the SQLTalk ERASE command to erase the existing procedure before storing the new one.

You must store a procedure as static if you plan to use it in a trigger.

Use the SQLTalk STORE command to store a procedure. You issue this command at the same time you generate the procedure text with PROCEDURE. For example:

STORE WD_PROC

PROCEDURE: WITHDRAW

Parameters

Number: nAccount

Number: nAmount

Receive Number: nNewBalance

Local Variables

String: sUpdate

String: sSelect

Actions

Set sUpdate = 'UPDATE CHECKING set \

BALANCE = BALANCE - :nAmount where \

ACCOUNTNUM =:nAccount'

Call SqlImmediate(sUpdate)

Set sSelect = 'SELECT BALANCE from CHECKING \

where ACCOUNTNUM = :nAccount \

into :nNewBalance'

Call SqlImmediate(sSelect)

;

Generally, you will want to include the STORE command in a script file and then run the script file.

Executing a procedure

Issuing a PROCEDURE command by itself automatically compiles and executes a procedure. You can also run the SQLTalk PREPARE or RETRIEVE commands in conjunction with the PERFORM command to compile/execute or retrieve/execute the procedure in two separate steps.

To retrieve and execute a stored procedure in one step, use the EXECUTE command. This command accepts input values and retrieves data as well as executes the stored procedure. For example:

EXECUTE WD_PROC

\

1,50,,

/

Note: Stored commands embedded in procedures can become invalid if their underlying database object changes. However, a stored procedure itself never becomes invalidated.

Runtime Errors

Stored commands embedded in procedures can become invalid if the stored command, or its underlying objects are dropped or altered. In this case, SQLBase still executes the procedure, but issues a runtime error about any missing or altered objects.

Similarly, SQLBase also issues a runtime error if is it is unable to find tables that are referenced in the stored procedure. Note that SQLBase allows users to delete or alter tables that are referenced in existing stored procedures.

Dropping a procedure

To drop a procedure from the database, use the SQLTalk ERASE command. For example:

ERASE WD_PROC;

Debugging a procedure

Within the procedure, you can use the SAL Trace statement to check the values of individual variables. See the Trace statement documentation for more information on this statement.

The SQLTalk SET and SHOW commands also have TRACE and TRACEFILE options to help trace procedure statements. These are run independently of the PROCEDURE command:

SQLTalk command

Description

SET TRACE ON/OFF

Enables or disables statement tracing.

SET TRACEFILE
<filename>/OFF

If this is set to a file name, SQLBase directs statement trace output to a file on the server; an Off value directs the output to the servers Process Activity screen.

SHOW TRACE

Determines whether statement tracing is enabled or disabled.

SHOW TRACEFILE

Determines whether statement trace output is being directed to a file on the server or to the servers Process Activity screen.

For example:

SET TRACE ON;

RUN example.sql;

PROCEDURE: WithDraw

Parameters

Number: nAccount

Number : nAmount

...

SET TRACE OFF;

Security

To grant privileges to other users for stored procedures, use the SQLTalk GRANT EXECUTE command. You can grant either your own privileges to other users, or grant them privileges of their own. To revoke users privileges, use the REVOKE EXECUTE command.

Read the SQLTalk Language Reference for information on these commands.

SAL functionality in SQLBase

You can embed any of the following functions in a procedure. User-defined functions are not supported. Note that while these functions are similar to Team Developer functions, they are SQLBase-specific. You do not need Guptas Team Developer program to use these functions. See the Appendix for a complete description and syntax for these functions.

Team Developer system variables (such as SqlDatabase) are not supported. Also, unlike Team Developer, SQLBase procedures are not case sensitive.

Team Developer Function

Description

SqlClearImmediate

Disconnects the Sql Handle used by SqlImmediate.

SqlClose

Closes a named cursor.

SqlCommit

Commits the current SQL transaction.

SqlConnect

Connects a Sql Handle to a database.

SqlDisconnect

Disconnects a Sql Handle from a database.

SqlDropStoredCmd

Deletes a stored command or stored procedure.

SqlError

Gets the most recent error code for the specified Sql Handle.

SqlExecute

Executes a SQL statement, stored command, or stored procedure.

SqlExists

Checks if a specified row or rows exist.

SqlFetchNext

Fetches the next row in a result set.

SqlFetchPrevious

Fetches the previous row in a result set.

SqlFetchRow

Fetches a specific row from a result set.

SqlGetErrorPosition

Gets the offset of an error within a SQL statement.

SqlGetErrorText

Gets the message text for a SQL error number.

SqlGetModifiedRows

Returns the number of rows changed by an INSERT, UPDATE, or DELETE statement.

SqlGetParameter

Returns a database parameter.

SqlGetParameterAll

Returns a database parameter.

SqlGetResultSetCount

Returns the number of rows in a result set.

SqlGetRollbackFlag

Returns the database rollback flag.

SqlImmediate

Compiles and executes a SQL statement.

SqlOpen

Names a cursor and executes a SQL statement.

SqlPrepare

Compiles a SQL statement or non-stored procedure for execution.

SqlPrepareAndExecute

Compiles and executes a SQL statement or non-stored procedure.

SqlRetrieve

Retrieves a stored command or stored procedure.

SqlSetIsolationLevel

Sets the isolation level.

SqlSetLockTimeout

Sets the timeout period on waiting for a lock.

SqlSetParameter

Sets a database parameter.

SqlSetParameterAll

Sets a database parameter.

SqlSetResultSet

Turns results set mode on and off.

SqlStore

Compiles and stores a command or procedure.

Related SQLTalk commands

Use the following SQLTalk commands to compile, prepare, and execute procedures. For information on these commands, read the SQLTalk Language Reference.

Command

Description

ERASE

Erases a stored command/stored procedure.

EXECUTE

Executes a stored command or stored procedure.

PERFORM

Executes either a prepared SQL command/non-stored procedure, or retrieved stored command/stored procedure.

PREPARE

Compiles a SQL command or non-stored procedure.

SET TRACE

Enables or disables statement tracing.

SET TRACEFILE

Directs statement trace output to a file on the server or to the servers Process Activity screen.

SHOW TRACE

Determines whether statement tracing is enabled or disabled.

SHOW TRACEFILE

Determines whether statement trace output is being directed to a file on the server or to the servers Process Activity screen.

STORE

Compiles and stores a command or procedure (and its execution plan) for later execution.

Using procedures with Gupta applications

This section discusses implementation issues to consider when using procedures in Team Developer applications.

Default for Result Sets in Stored Procedures

To emulate scrollable results sets in Team Developer, the default for result sets in stored procedures is turned ON when you issue a Call SqlConnect (hsql). This cursor has results sets turned ON so that scrollable result sets are available when you issue a SqlFetchPrevious. Note that normally in SQLTalk, result sets in stored procedures is turned OFF by default.

Note: If you are NOT using SqlFetchPrevious in your procedures, you can improve performance by explicitly turning results sets OFF in procedures with SqlSetResultSet.

Calling a SQLBase Procedure

To call a SQLBase procedure from Team Developer, use the SAL SqlRetrieve call. You must follow these rules:

All SQLBase procedure parameters must have a representative Team Developer variable/visual object in the bind list (third parameter) of SqlRetrieve().

All Receive parameters of a SQLBase procedure that are used as output for the calling Team Developer application must be represented by a Team Developer variable/visual object in the into list (fourth parameter) of SqlRetrieve.

Note: There is an exception to these SqlRetrieve() rules when using Team Developer List and Combo boxes. These are discussed in the following paragraphs.

For example, assume you are populating two Team Developer data fields, df1 and df2, with the following procedure which returns rows from a SELECT:

Procedure: PRODUCTS
Parameters
Number: nInventory
Receive String: sName
Receive Date/Time: dtWhen
Actions
...

Call SqlPrepareAndExecute(hSqlCur1, 'select NAME, WHEN
from PRODUDCT_INVENTORY \
where INVENTORY = :nInventory into :sName, \
:dtWhen')
...

Within Team Developer, code the following lines. Notice the bind list.

...
Set nInvent = 200
Call SqlRetrieve( hCur1, 'PRODUCTS', ':nInvent, :df1,
:df2', ':df1, :df2')
Call SqlExecute( hCur1 )
Call SqlFetchNext( hCur1, nInd )
...

For table windows, the third parameter for SalTblPopulate is passed as a null. This is the same method used when a stored command is the data source for a table window.

...
Set nInvent = 200
Call SqlRetrieve( hCur1, 'PRODUCTS', ':nInvent, :df1,
:df2', ':df1, :df2')
Call SalTblPopulate( tbProducts, hCur1, '',
TBL_InventNormal

For list boxes and combo boxes, both the fourth parameter (into list string) for SqlRetrieve and the third parameter for SalListPopulate is are passed as nulls. This is the same method used when a stored command is the data source. Secondly, since Team Developer has no method of referencing individual database columns in a list or combo box, you must create dummy variables to represent the procedure Receive parameters within the bind list. Backend result sets do not need to be turned off.

   ...

Window Variables:
String: sDummy1
String: sDummy2
Number: nInvent
...

Message Actions
...

set nInvent = 200
Call SqlRetrive( hCur1, 'PRODUCTS', ':nInvent, :sDummy1,
:sDummy2', '')
Call SaListPopulate( hWndItem, hCur1, '')

To learn more about using SQLBase procedures with Team Developer, run the Team Developer application sp.app shipped with SQLBase.

Error handling

By default, SQLBase handles a SQL error by terminating execution of the procedure, and returning an error code to you.

You can override this default SQL error processing using the When SqlError statement. This enables you to specify a local error handler as you can in Team Developer. A local error handler is only effective for statements in the same statement block as that in which the error handler is declared.

However, if the When SqlError returns control back to the procedure, it is the procedures responsibility to check the return from the failed SQL statement and process accordingly. If there is no return from the When SqlError construct, both the control and the SQL error code are immediately returned to the caller.

Unlike Team Developer, procedures do not allow you to specify a global error handler.

Put a When SqlError statement in a procedures Actions section:

Before a procedure SAL function.

At the same indent level as the procedure SAL function.

The following flowchart shows the steps that SQLBase follows when a SQL error occurs during the execution of a procedure.

1. SQLBase looks for When SqlError in the procedures Actions section.

2. If there is no When SqlError statement, SQLBase breaks the procedure execution, and returns control to the calling program. The error code is returned to the calling program.

3. If there is a When SqlError, SQLBase performs the statements in the When SqlError section.

4. You can use a Return statement to specify that either a TRUE or FALSE value be returned by the procedure SAL function on which the error occurred. If you do not specify a Return statement, the procedure breaks execution, and both control and the error code are returned to the calling program.

If the Return statement returns FALSE, FALSE becomes the return value of the failed Sql* function. Procedure execution continues as if the failed SQL statement returned FALSE.

If the Return statement returns TRUE, TRUE becomes the return value of the failed Sql* function. Procedure execution continues as if the failed SQL statement returned TRUE.

This example uses the tables JR and PRODUDCT_INVENTORY:

CREATE TABLE JF 12R,132,13-OCT-1992 (NAME varchar(25),
INVENTORY decimal (3,0), WHEN date);

INSERT INTO PRODUDCT_INVENTORY values
(JF 12R,132,13-OCT-1992);

COMMIT;

This examples also uses the following stored command INVENTORY_QUERY:

STORE INVENTORY_QUERY
SELECT INVENTORY from PRODUDCT_INVENTORY
where NAME = :1;

In this example, the When SqlError construct tests for two error conditions:

If the stored command does not exist, error code 207 is returned.

If the table used in the stored command does not exist, error code 601 is returned.

In this example, error code 601 is returned because the table required for the stored command is dropped prior to procedure execution.

DROP TABLE PRODUDCT_INVENTORY
PROCEDURE: ILPROC
Parameters
String: sName
Receive Number: nINVENTORY
Local Variables
Sql Handle: hSqlCur1
Number: nInd
Number: nRcd
Boolean: bCond
Actions
On Procedure Startup

When SqlError
Set nRcd = SqlError(hSqlCur1)
If nRcd = 207
Call SqlStore(hSqlCur1, 'INVENTORY_QUERY', \
'select INVENTORY from PRODUDCT_INVENTORY \
where NAME = :1 into :2')
Call SqlCommit(hSqlCur1)
Call SqlRetrieve(hSqlCur1, 'INVENTORY_QUERY', \
':sName', ':nINVENTORY')
Return TRUE
Else If nRcd = 601
Return FALSE
Call SqlConnect(hSqlCur1)

 

Set bCond = SqlRetrieve(hSqlCur1,\
'INVENTORY_QUERY',':sName', ':nINVENTORY')

If NOT bCond
Return 6302
On Procedure Execute
Call SqlExecute(hSqlCur1)
On Procedure Fetch
If NOT SqlFetchNext(hSqlCur1, nInd)
Return 1
Else
Return 0
On Procedure Close
Call SqlDisconnect(hSqlCur1)

\

JF 12R,,

/

 

1. This exception handling routine Returns FALSE because the required table for the stored command was dropped prior to the execution of the procedure. Returning FALSE (as opposed to executing no Return from When SqlError) allows the procedure to provide additional processing, such as returning a user defined error.

2. Since the When SqlError construct returned FALSE, the return value for bCond is set to FALSE.

3. The When SqlError construct sets bCond to FALSE. This returns control back to the calling application with the user defined error 6302 "PRODUDCT_INVENTORY table is missing - see DBA".

Procedure examples

This section is a series of examples that demonstrate the different elements of procedures. They use a table called CHECKING. You can run these and other examples online using the sp.sql SQLTalk script, which is provided in the Gupta directory with your SQLBase software. In addition, the sp.app sample application provided in the Gupta directory demonstrates using procedures in Team Developer.

Example 1 - Procedure IF/Else statement

This next example adds an IF/Else statement to the procedure; this checks to see if the balance is negative.

STORE WITHDRAW

PROCEDURE: WITHDRAW

Parameters

Number: nAccount

Number: nAmount

Receive Number: nNewBalance

Receive Boolean: bOverDrawn

Local Variables

String: sSelect

String: sUpdate

Actions

Set sSelect = 'SELECT BALANCE from CHECKING \

where ACCOUNTNUM = :nAccount \

into :nNewBalance'

Call SqlImmediate(sSelect)

Set nNewBalance = nNewBalance - nAmount

If (nNewBalance < 0)

Set bOverDrawn = TRUE

Else

Set bOverDrawn = FALSE

Set sUpdate = 'UPDATE CHECKING \

set BALANCE = BALANCE - :nAmount \

where ACCOUNTNUM = :nAccount'

Call SqlImmediate(sUpdate)

;

 

EXECUTE WITHDRAW

\

1,100,,,

/

Example 2 - SQL handles and ON statements

The next example adds SQL handles and ON statements to the procedure.

STORE WITHDRAW

PROCEDURE: WITHDRAW

Parameters

Number: nAccount

Number: nAmount

Receive Number: nNewBalance

Receive Boolean: bOverDrawn

Local Variables

Sql Handle: hSqlSelect

Sql Handle: hSqlUpdate

String: sSelect

String: sUpdate

Number: nStatus

Actions

On Procedure Startup

Set sSelect = 'SELECT BALANCE from CHECKING \

where ACCOUNTNUM = :nAccount \

into :nNewBalance'

Set sUpdate = 'UPDATE CHECKING \

set BALANCE = BALANCE - :nAmount \

where ACCOUNTNUM = :nAccount'

Call SqlConnect(hSqlSelect)

Call SqlPrepare(hSqlSelect, sSelect)

Call SqlConnect(hSqlUpdate)

Call SqlPrepare(hSqlUpdate, sUpdate)

On Procedure Execute

Call SqlExecute(hSqlSelect)

Call SqlFetchNext(hSqlSelect, nStatus)

Set nNewBalance = nNewBalance - nAmount

If (nNewBalance < 0)

Set bOverDrawn = TRUE

Else

Set bOverDrawn = FALSE

Call SqlExecute(hSqlUpdate)

On Procedure Close

Call SqlDisconnect(hSqlSelect)

Call SqlDisconnect(hSqlUpdate)

;

 

EXECUTE WITHDRAW

\

1,100,,,

/

Example 3 - Doing a fetch

This example adds a fetch operation to the procedure.

STORE WITHDRAW

PROCEDURE: WITHDRAW

Parameters

Number: nAccount

Number: nAmount

Receive Number: nNewBalance

Local Variables

Sql Handle: hSqlSelect

String: sSelect

Number: nStatus

Boolean: bEOF

Actions

On Procedure Startup

Set sSelect = 'SELECT BALANCE from CHECKING \

where ACCOUNTNUM = :nAccount \

into :nNewBalance'

Call SqlConnect(hSqlSelect)

Call SqlPrepare(hSqlSelect, sSelect)

On Procedure Execute

Call SqlExecute(hSqlSelect)

! Internal fetch - column is not returned to the calling

! program since there is an On Procedure Fetch state

! which does return values to the calling program.

 

Call SqlFetchNext(hSqlSelect, nStatus)

On Procedure Fetch

If (nNewBalance > 100)

Set nNewBalance = nNewBalance * 1.005

Set nNewBalance = nNewBalance - 100

Set bEOF = FALSE

Else

Set bEOF = TRUE

Return bEOF

On Procedure Close

Call SqlDisconnect(hSqlSelect);

 

EXECUTE WITHDRAW

\

1,100,,

/

Example 4 - Procedure calling a procedure

This example shows how one stored procedure can call another stored procedure. The calling stored procedure is DYNAMIC and the called stored procedure is STATIC. Nesting procedures can enhance the modularity of code by creating common routines that perform specialized tasks. These tasks can then be called by any number of different procedures or calling programs.

This example uses the following two tables PRODUCTIVE and RATE:

 

create table PRODUCTIVE
(
NAME varchar(25),
DEPT varchar(2),
BUILD varchar(1),
PRICE integer
);

insert into PRODUCTIVE values('BM J18','TT','M',66);

create table RATE
(
RATE varchar(12),
PER_DAY double precision
);

insert into RATE values(:1, :2)
\
"LEVEL H",300,
"LEVEL B",190,
"LEVEL T",150,
"LEVEL I",25,
/

This is the syntax of the static stored procedure PRODUCT_COUNT, which determines the current population of the PRODUCT table. It is called by the dynamic procedure ADJUST_RATE.

 

STORE PRODUCT_COUNT

PROCEDURE: PRODUCT_COUNT static

Parameters

Receive Number: nCount

Local Variables

Sql Handle: hSqlCur1

Number: nInd

Actions

Call SqlConnect( hSqlCur1 )

Call SqlPrepareAndExecute( hSqlCur1, \
'select count(*) from PRODUCTIVE into :nCount' )

Call SqlFetchNext(hSqlCur1, nInd)

;

The following dynamic stored procedure ADJUST_RATE calls the PRODUCT_COUNT stored procedure. Based on the current PRODUCT population, the DAILY rates are determined.

store ADJUST_RATE
procedure: ADJUST_RATE dynamic
Parameters
Receive String: sRate
Receive Number: nPerDay
Local Variables
Sql Handle: hSqlCur1
Number: nPop
Number: nInd
String: sAdjust
String: sUpdate
String: sSelect
Actions
On Procedure Startup
Call SqlConnect( hSqlCur1 )
Set nPop = 0

Call SqlRetrieve( hSqlCur1, \
'PRODUCT_COUNT', ':nPop', ':nPop' )
Set sSelect = 'Select RATE, PER_DAY from RATE \
into :sRate, :nPerDay'
On Procedure Execute
Call SqlExecute( hSqlCur1 )

Call SQLFetchNext(hSQlCurl, nInd)

If nPop > 1
Set sAdjust = 'set PER_DAY = PER_DAY * 1.15'
Else
Set sAdjust = 'set PER_DAY = PER_DAY * 1.05'
Set sUpdate = 'Update RATE ' || sAdjust

Call SqlPrepareAndExecute( hSqlCur1, sUpdate )

Call SqlPrepareAndExecute( hSqlCur1, sSelect )
On Procedure Fetch
If NOT SqlFetchNext( hSqlCur1, nInd )
Return 1
Else
Return 0
On Procedure Close
Call SqlDisconnect( hSqlCur1 )

;

column 1 width 15;
execute ADJUST_RATE

\
,,
/

1. Retrieve the stored procedure to get the current PRODUCT count. Notice the bind list must include (in proper order) variables which represent all parameters declared in the called stored procedure PRODUCT_COUNT. Secondly, the into list must include variables which map to those Receive parameters of the called procedure that return output to procedure ADJUST_RATE.

2. Fetch a single row value into the nPop local variable.

3. Use dynamic SQL to build the update statement based on the PRODUCT population.

4. Update the RATE table.

5. Now select the new rates from the RATE table.

6. SQLTalk requires string columns to be resized.

Triggers

This section provides an overview of triggers which use stored procedures. For detailed information about triggers, see the documentation on the CREATE TRIGGER command in this manual.

What is a trigger?

A trigger activates a stored or inline procedure that SQLBase automatically executes when a user attempts to change the data in a table. You create one or more triggers on a table, with each trigger defined to activate on a specific command (an INSERT, UPDATE, or DELETE). Attempting to modify data within the table activates the trigger that corresponds to the command. For details on the trigger execution order before a single data manipulation statement is executed, read the Section DML Execution Model in Chapter 1.

Triggers enable you to:

Implement referential integrity constraints, such as ensuring that a foreign key value matches an existing primary key value.

Prevent users from making incorrect or inconsistent data changes by ensuring that intended data modifications do not compromise a databases integrity.

Take action based on the value of a row before or after modification.

Transfer much of the logic processing to the backend, reducing the amount of work that your application needs to do as well as reducing network traffic.

Creating Triggers

You can only use inline or static stored procedures with triggers. In addition, you must first store the static procedure with the STORE command; a trigger cannot call a non-stored procedure.

Use the SQL CREATE TRIGGER command to create a trigger. You can disable an existing trigger by using the ALTER TRIGGER command. This command causes SQLBase to ignore the trigger when an activating DML statement is issued. With this command, you can also enable a trigger that is currently inactive.

You can easily disable all triggers defined on a table by using the stored procedure \Gupta\rep_trig.sql included with SQLBase.

To access the stored procedure, you must have SYSADM authority and run the file REP_TRIG.SQL against the database that contains the triggers you want to enable or disable. This file creates the stored procedure SYSADM.SYSPROC_ALTTABTRIG.

To use the stored procedure, provide the owner and name of the table that contains the trigger and specify whether to enable or disable the triggers in the table. When you execute the procedure, it retrieves the names of all triggers belonging to the table and enables or disables each trigger one by one. Through the receive parameter, the procedure returns the number of triggers that it processed.

For example, to disable all triggers on table T1 created by USER1, run:

EXECUTE SYSPROC_ALTTABTRIG
\
USER1, T1, DISABLE, 0
/

To delete a trigger from the system catalog, use DROP TRIGGER.

Note: To see an online triggers tutorial, run the triggers.sql script that is installed with SQLBase.

Trigger example

The following example shows how an insert statement can invoke a trigger to insert data into a history table. The trigger calls an inline procedure called proc_newpres.

This trigger uses the following PRESIDENT and ELECTION tables:

CREATE TABLE PRESIDENT
(PRES_NAME varchar(20) not null, BIRTH_DATE date,
YRS_SERV integer, DEATH_AGE integer,
PARTY varchar (20),STATE_BORN varchar(20));

CREATE TABLE ELECTION (ELECTION_YEAR smallint,
CANDIDATE varchar(20),VOTES float,
WINNER_LOSER_INDIC char(1));

CREATE TRIGGER TRG_NEWPRES
after insert on SYSADM.PRESIDENT
(execute inline (1792,'Jefferson T',4,'L')

PROCEDURE: PROC_NEWPRES static

Parameters

Number: nElecYear

String: sCandidate

Number: nVotes

String: sWinLose

Local Variables

Sql Handle: hSqlCur

Actions

On Procedure Startup

Call SqlConnect(hSqlCur)

Call SqlPrepare(hSqlCur, 'Insert into \

sysadm.election values \

(:nElecYear, :sCandidate, :nVotes, :sWinLose)')

On Procedure Execute

Call SqlExecute(hSqlCur)

On Procedure Close

Call SqlDisconnect(hSqlCur)

)

for each statement;

This trigger is invoked when you INSERT into the PRESIDENT table, as in the following example:

INSERT into PRESIDENT values ('Jefferson T',
13-Apr-1743,8,83,'Demo-Rep','Virginia');

Security

When a user invokes a trigger, he/she assumes the privileges of the owner of the table on which the trigger is defined. The user invoking the trigger must have privileges to do the DML command that causes the trigger to be activated.

You can only create a trigger which uses a stored procedure under one of the following conditions:

You have either DBA or SYSADM privileges.

You are the owner of the stored procedure.

You have been granted EXECUTE privileges for that stored procedure.

Error handling in triggers

If a trigger calls a stored procedure and the procedure performs validation logic which returns an error code, the trigger returns the error code to the calling SQL statement, which displays it. A procedures error will "bubble" all the way to the trigger. This means that the error appears no matter how the trigger is invoked.

 

 

SQLBase Language Reference