Native Sybase Connector: Output value of a stored procedure run on the server

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Version:

Home | Sign in | English

Help

Overview

How to proceed?

Example

See also

Native Sybase Connector: Output value of a stored procedure run on the server

Available only with this kind of connection

Overview

The Native Sybase Connector (also called Native Sybase Access) is used to:

specify the input values during the call to a stored procedure.
retrieve the output values after a call to a stored procedure.

This feature is available from ASE 12.5.

How to proceed?

The steps

To specify and retrieve values when running a "Stored procedure" query, you must:

Declare a data source. This data source will contain the result of stored SQL procedure.
Declare the variables of the stored procedure (input variables and output variables)
The variables are retrieved in the type specified in WLanguage. The variables are text variables by default.
Therefore, conversions may occur between the native type of the database and the WLanguage type. These conversions may cause unexpected rounding or formatting errors (a Date changed into a string for example).
Therefore, we advise you to specify the WLanguage types of variables with great care before running the stored procedure. To do so:
- initialize the value of the variable (simple types: integer, string, float)
- specify the expected type with ..Type (not supported in this version).
  The name of these variables must correspond to the name of the parameters of this procedure on Sybase.
Note: In some cases, the server does not accept to perform an implicit conversion. A conversion error is displayed. In this case, you must initialize the type by defining any ordinary value in the selected type.
For example, to define a numeric type, use MyQuery.Parameter=0.
Run the stored procedure with HExecuteSQLQuery.

Notes

HExecuteSQLQuery must be used with:
- the connection name,
- the hQueryWithoutCorrection constant.
The declared variables must be identical to the ones used. Otherwise, a WLanguage error occurs.
In the call to the stored procedure, you must use the syntax specific to the database used, including for the syntax of parameters. For Sybase, the parameters are specified with the following syntax: @ParamName. Caution: the "@" character must be followed by at least one letter (the syntax @1 is not allowed).
The same parameter can be used several times. In this case, the corresponding variable will be re-used.
The return value of the stored procedure is returned in a variable automatically defined by the Native Sybase Connector. This variable is named RETURN_VALUE.
To run a query without enabling the bind, use the hNoBind constant in HExecuteSQLQuery.

Example

The stored procedure named MyProc returns the sum of @p& and @p21 in @res. The return value is 2*@res.

The code used to create this procedure on the server is as follows:

CREATE PROCEDURE MyProc(@p1 int, @p2 int, @res int output)
AS select @res=@p1+@p2
return 2*@res

1. Declaring the data source

The data source is used to handle the variables of the procedure that must be run.

MyProc is Data Source

2. Declaring the variables used by the procedure

The variables handled by the procedure are declared from the data source.

MyProc.p1 = 10 // Automatically declares an integer initialized to 3
MyProc.p2 = 20

Note: There is no need to declare all the variables used. In this example, the variable used to retrieve the result of the procedure is not declared.

3. Running the stored procedure and retrieving the result

To run the stored procedure, all you have to do is use HExecuteSQLQuery:

IF HExecuteSQLQuery(MyProc, "MyConnection", hQueryWithoutCorrection, "MyProc @p1,@p2,@res") THEN
Trace("res=" + MyProc.res)
Trace("p1=" + MyProc.p1)
Trace("p2=" + MyProc.p2)
Trace("return_value=" + MyProc.return_value)
ELSE
Error(HErrorInfo())
END