- Code: Select all Expand view
oCn:Execute( "CALL sp_myprocedure( 9, 'sometext', '2010-10-10' )" )
oRs := oRs:Open( "SELECT fn_myfunction( 9, 'sometext', '2010-10-10' )" )
? oRs:Field(0):Value
The above code is for ADO, but the same approach may be used for TMySql/TDolphin also. TDolphin provides two method CALL(...) and RETURNCALL(...). Using these methods is a better option.
FWHMYSQL/MARIADB:
We strongly recommend using CALL method to call functions and stored procedures instead of the above usage.
Syntax:
- Code: Select all Expand view
// Functions
[uRet := ] oCn:Call( "<function>", [p1], ... [pN] )
[uRet := ] oCn:Call( "<function>", aParams )
[uRet := ] oCn:<functioname>( paramslist )
// Stored Procedures
[oRs := ] oCn:Call( "<procedure>", [[@]p1],...[[@]pN] )
[oRs := ] oCn:Call( "<procedure>", aParams )
[oRs := ] oCn:<procedurename>( [p1], ... [pN] )
[oRs := ] oCn:<procedurename>( aParams )
[oRs := ] oCn:RowSet( "<procedurename>", aParams )
Simple examples of Usage:
We create a simple function
fn_Interest( nAmount, dDate, nRate ) --> nInterest.
- Code: Select all Expand view
oCn:Execute( "DROP FUNCTION IF EXISTS fn_Interest" )
TEXT INTO cSql
CREATE FUNCTION fn_Interest( nAmount DOUBLE, dDate DATE, nRate DOUBLE )
RETURNS DOUBLE
NO SQL
NOT DETERMINISTIC
BEGIN
RETURN ( nAmount * DATEDIFF( CURDATE(), dDate ) * nRate / 365 );
END;
ENDTEXT
oCn:Execute( cSql )
Three different ways of calling the function:
- Code: Select all Expand view
nAmount := 1000
dDate := {^ 2016/08/01}
nRate := 0.12
? nInterest := oCn:call( "fn_Interest", nAmount, dDate, nRate )
aParams := { nAmount, dDate, nRate }
? nInterest := oCn:Call( "fn_Interest", aParams )
? nInterest := oCn:fn_Interest( nAmount, dDate, nRate )
aParams := { nAmount, dDate, nRate }
? nInterest := oCn:fn_Interest( aParams )
A function returns only one result. If we want more than one result to be returned, we need to use a stored procedure with OUT params. The following procedure returns both number of days lapsed and interest.
- Code: Select all Expand view
oCn:Execute( "DROP PROCEDURE IF EXISTS sp_Interest" )
TEXT INTO cSql
CREATE PROCEDURE sp_Interest( IN nAmount DOUBLE, IN dDate DATE, IN nRate DOUBLE,
OUT nDays INT, OUT nInterest DOUBLE )
BEGIN
SET nDays = DATEDIFF( CURDATE(), dDate );
SET nInterest = ( nAmount * nDays * nRate / 365 );
END;
ENDTEXT
oCn:Execute( cSql )
Usage:
- Code: Select all Expand view
nAmount := 1000
dDate := {^ 2016/08/01}
nRate := 0.12
oCn:call( "sp_Interest", nAmount, dDate, nRate, @nDays, @nInterest )
? nDays, nInterest
aParams := { nAmount, dDate, nRate, nDays, nInterest }
oCn:Call( "sp_Interest", aParams )
? nDays := aParams[ 4 ], nInterest := aParams[ 5 ]
// Following does not work
// oCn:sp_Interest( nAmount, dDate, nRate, @nDays, @nInterest )
aParams := { nAmount, dDate, nRate, 0, 0 }
oCn:sp_Interest( aParams )
? aParams[ 4 ], aParams[ 5 ]
It is possible to retrieve the values of INOUT and OUT parameters of a Stored Procedure using @<var> or by using aParams syntax.
This is possible only with FWH implementation of MYSQL/MARIADB and not possible with either ADO or any other 3rd party libs.
Next, we shall consider more complex examples like RowSets from Stored Procedures.
To be continued ...