- Vytvoril D2000 Dev Team na okt 13, 2017
SQL_EXEC_PROC action
Function
The action executes given SQL command (stored procedure or function) along with parameters.
Declaration
SQL_EXEC_PROC handleIdent_Int, retCodeIdent_Int, stringExpr BIND _locVar1, _locVar2, ... SQL_EXEC_PROC handleIdent_Int, retCodeIdent_Int, stringExpr BIND _locVarRowIdent SQL_EXEC_PROC handleIdent_Int, retCodeIdent_Int, stringExpr BIND _locVarRecordIdent
Parameters
| handleIdent_Int | in | Identifier of Int type - unique number (handle) of a connection to database. |
| retCodeIdent_Int | out | Identifier of Int type - return code. |
| strExpr | in | Expression of Text type - SQL procedure or function. |
| _locVar1, _locVar2, ... | in/out | Local variables. |
| _locVarRowIdent | in/out | Reference to a row of local variable of Record type. |
| _locVarRecordIdent | in/out | Identifier of local variable of Record type. |
Return code
Value of the parameter retCodeIdent_Int - see the table of
error codes. It is also possible to get extended error information.
Description
The action executes specified SQL command over the database opened by the action SQL_CONNECT.
The SQL command is represented by the expression stringExpr. The command contains input, output or input/output parameters and their values are
specified after the keyword BIND.
If values of the parameters are changed after the command was executed, the process D2000 DBManager sets relevant local variables to values changed (with actual timestamps).
If the parameter _locVarRecordIdent is defined, the SQL command is to be called for each row of the structure. If calling returns an error, the action will not continue with another rows.
If values of the parameters are changed after the command was executed, the process D2000 DBManager sets relevant local variables to values changed (with actual timestamps).
If the parameter _locVarRecordIdent is defined, the SQL command is to be called for each row of the structure. If calling returns an error, the action will not continue with another rows.
Note
SQL command syntax is differ for ODBC and OCI versions of the process D2000 DBManager:
Example:
The use of modifiers is optional (default value is INOUT). The modifiers are not case sensitive.
-
ODBC version of the process D2000 DBManager:
According to the ODBC convention, a parameter is specified by question mark, the syntax of procedure call is
"{ call PROCEDURE_NAME(?,?,..) }"
and the syntax of function call is
"{ ? = call FUNCTION_NAME(?,?,..) }"
Examples:
Function with two parameters: "{ ? = call TEST_FUNC(?,?) }"
Procedure with three parameters "{ call TEST_PROC(?,?,?) }"
Procedure with three parameters, while the second parameter is a constant: "{ call TEST_PROC(?,5,?) }"
This example works just for Sybase SQL Anywhere, not for Oracle (for ODBC, all specified parameters musty be bound as variables, not constants). To lift this restriction use the OCI syntax also in the ODBC version: "BEGIN TEST_PROC(:par1,5,:par3); END;".
On the other hand, Sybase SQL Anywhere (unlike Oracle) supports output parameters just for procedures, not for functions.
-
OCI version of the process D2000 DBManager:
Parameter is marked by colon and its name, the syntax to call a procedure is "BEGIN PROCEDURE_NAME(:par1,:par2,..); END;" and the syntax to call a function is "BEGIN :result := FUNCTION_NAME(:par1,:par2,..); END;"
Examples:
Function with two parameters: "BEGIN :res := TEST_FUNC(:par1,:par2); END;"
Procedure with three parameters: "BEGIN TEST_PROC(:par1,:par2,:par3); END;"
Procedure with three parameters, while the second parameter is a constant: "BEGIN TEST_PROC(:par1,5,:par3); END;"
Sequence of the parameters is defined by the sequence in the string (in the previous example :res, :par1 a :par2). If the parameters are declared with the same name, they will be taken as one parameter, i.e. the SQL command "BEGIN :res := TEST_FUNC(:parX,:parX); END;" contains these two parameters :res (function result) and :parX (two output parameters with the same value).
In the OCI version of the process D2000 DBManager, SQL command may be an entire sequence, e.g.
"BEGIN :res := TEST_FUNC(:par1,:par2); IF :res=0 THEN :res := TEST_FUNC2(:par1,:par2); END IF; END;""
Example:
"
{ call TEST_PROC(IN ?, INOUT?, OUT?) }
"
"BEGIN TEST_PROC(IN :par1, INOUT :par2, OUT :par3); END;"
The use of modifiers is optional (default value is INOUT). The modifiers are not case sensitive.
Example
- 1. ODBC version of the process D2000 DBManager
Example: Creating stored procedures in SQL Anywhere:/* par1 is input/output parameter, par2 is input one and par3 is output parameter */
create procedure TEST_PROC(@par1 varchar(10) output, @par2 integer,@par3 integer output)
as
declare @vysl integer
begin
select @par=@par+'XYZ'
select @par3=2*@par2
end
/* example of function with two parameters (Sybase supports just input function parameters) */
create function TEST_FUNC(in @par1 real,in @par2 integer)
returns real as
begin
return(@par1*@par2)
end
Calls from script:
BEGIN INT _myInt INT _iRetCode INT _iHandle TEXT _myText REAL _myReal INT _myInt1 INT _myInt2 _myText := "ABC" _myInt1 := 10 SQL_CONNECT MyDB, _iHandle, _iRetCode ; procedure call SQL_EXEC_PROC _iHandle, _iRetCode, "{ call TEST_PROC(?,?,?) }" BIND _myText, _myInt1, _myInt2 ;_value of myText is "ABCXYZ" and value of _myInt2 is 20 (2 * 10) ; call of procedure with a constant SQL_EXEC_PROC _iHandle, _iRetCode, "{ call TEST_PROC(?,3,?) }" BIND _myText, _myInt1 ; value of _myText is "ABCXYZXYZ" and value of _myInt2 is 6 (2 * 3) ; function call SQL_EXEC_PROC _iHandle, _iRetCode, "{ ? = call TEST_FUNC(?,?) }" BIND _myReal, _myInt1, _myInt2 ; value of _myReal is 60 (10 * 6) ; call of function with a constant SQL_EXEC_PROC _iHandle, _iRetCode, "{ ? = call TEST_FUNC(?,3.3) }" BIND _myReal, _myInt1 ; value of _myReal is 33 (10 * 3.3)
- 2. OCI version of the process D2000 DBManagera
Example: creating stored procedures in Oracle 9i:/* par1 is input/output parameter, par2 is input and par3 is output parameter */
Calls from script:
CREATE OR REPLACE PROCEDURE "MYUSER"."TEST_PROC" (
par1 in out varchar,par2 integer, par3 out integer
)
as
begin
par1 := par1 || 'XYZ';
par3 := 2 * par2;
end;
/* par1,par2 are input parameters, succ is output one */
CREATE OR REPLACE FUNCTION "MYUSER"."TEST_FUNC" (
par1 in float, par2 in float, succ out integer
)
return float
as
begin
if par2 = 0.0 then
succ := 0;
return 0;
else
succ := 1;
return par1/par2;
end if;
end;
BEGIN INT _myInt INT _iRetCode INT _iHandle TEXT _myText REAL _myReal INT _myInt1 INT _myInt2 INT _Succ _myText := "ABC" _myInt1 := 10 SQL_CONNECT MyDB, _iHandle, _iRetCode ; procedure call SQL_EXEC_PROC _iHandle, _iRetCode, "BEGIN TEST_PROC(:p1,:p2,:p3); END;" BIND _myText, _myInt1, _myInt2 ;value of _myText is "ABCXYZ" and value of _myInt2 is 20 (2 * 10) ; function call SQL_EXEC_PROC _iHandle, _iRetCode, "BEGIN :ret := TEST_FUNC(:par1,:par2,:par3); END;" BIND _myReal, _myInt1, _myInt2, _Succ ; value of _myReal is 0.5 (10 / 20) and value of _Succ is 1
Related topics
Related pages:
0 komentárov