FWHMYSQL/MARIADB: Stored Procedures and Functions

[nageswaragunupudi]

A common practice to call stored procedures and functions is something like this:

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 ...

[nageswaragunupudi]

RowSet from Stored Procedure:

We have a table `states` imported from \fwh\samples

Code: Select all  Expand view

oCn:ImportFromDBF( "c:\fwh\samples\states.dbf" )
 

Now we create a stored procedure to return a rowset containing states matching a search criterion givein in first param and retrieve the number of rows found in the second parameter:

Code: Select all  Expand view

   oCn:Execute( "DROP PROCEDURE IF EXISTS read_states" )

TEXT INTO cSql
   CREATE PROCEDURE read_states( IN seek VARCHAR(20), OUT rows_found INT )

      READS SQL DATA

   BEGIN
      IF seek IS NULL OR seek = '' THEN
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM `states`;
      ELSE
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM `states`
         WHERE `code` LIKE seek OR `name` LIKE seek;
      END IF;

      SELECT FOUND_ROWS() INTO rows_found;

   END;
ENDTEXT

   oCn:Execute( cSql )
 

Usage:

Code: Select all  Expand view

   oRs   := oCn:Call( "read_states", "%Y%", @nRows )
   ? nRows
/*
   other possible syntaxes
   oRs   := oCn:Call( "read_states", aParams )
   oRs   := oCn:read_states( aParams )
*/
   XBROWSER oRs FASTEDIT SHOW SLNUM
 

The rowset can now be edited, fields modified and rows deleted or inserted like any other rowset. Though the result set is generated within a stored procedure and names of fields are changed by aliasing, the RowSet object is able to figure out that the original table is `states` and the orginal names of fields `StateID`, `StateCode` and `StateName` are in fact `id`, `code`, `name`. That is how the RowSet is able to offer editing, deleting and inserting.

Now let us raise a question ourselves and answer it. When it is possible to create a rowset from the Sql statement directly, why should anybody take the trouble of creating a stored procedure and then read the data through the stored procedure.

There can be no doubt that in any organization, security, intergrity and consistency of corporate data is of paramount importance. In large organizations, several programmers would be working on different applications on different platforms (web-based, desktop) and in different languages all of them interecting with the same corporate database. Even simple bugs in any of these programs can play havoc to the entire database. Large organisations which adopt good practices isolate UI software from DataBase software, generally handled by different teams. UI programmer does not have to know anything about the backend database except the documented interface to read and write data. Entire business logic is kept on the server with server-side scripting. The interface is through the Stored Procedures and functions.

This is a typical structure of IT department in a large organization.



Now, we are clear of the relevance as well as the necessity of reading rowsets through Stored Procedures. The purpose is to totally hide the tables, table structures, etc from the UI programmer.

Obviously the above stored procedure might have been authored by a member of the Database Proramming team. It is of interest to examine how far the author is successful in hiding the details of the table and structure in question from the UI programmer.

Sadly, the DB programmer has utterly failed in achieving his objective. He thought he was smart but the RowSet object is smarter. This UI program knows all about the table, structure, joins, primary keys, unique keys and everything, which he did not want to reveal.

So what should the DB programmer do?
Continued ........

[nageswaragunupudi]

Secure Serverside Scripting

More advanced Examples

An example of a safer stored procedure:

Code: Select all  Expand view

   db():Execute( "DROP PROCEDURE IF EXISTS states_read" )

TEXT INTO cSql
   CREATE PROCEDURE states_read( IN seek VARCHAR(20), OUT rows_found INT )
      READS SQL DATA
   BEGIN
      IF seek IS NULL OR seek = '' THEN
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM ( SELECT `id`,`code`,`name` FROM `states` ) S;
      ELSE
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM (
           SELECT `id`, `code`, `name` FROM `states`
           WHERE `code` LIKE seek OR `name` LIKE seek
         ) S;
      END IF;

      SELECT FOUND_ROWS() INTO rows_found;

   END;
ENDTEXT

   db():Execute( cSql )
 

Now let us open the result set in our UI program.

Code: Select all  Expand view

   oRs   := oCn:Call( "states_read", "%Y%", @nRows )
   XBROWSER oRs FASTEDIT SHOW SLNUM
   ? oRs:lReadOnly // .T.
 

Now the RowSet object does not know the basetable information and can not automatically provide editing capabilities. So it marked the rowset as Readonly. We can only view the data but can not modify.

But that is not the intention. UI program should be able to provide all editing features but without knowing anything about the base tables. To enable this, again the DB programmer has to provide another Stored Procedure to "write data". Here is a sample:

Procedure "states_write"

Code: Select all  Expand view

   db():Execute( "DROP PROCEDURE IF EXISTS states_write" )

TEXT INTO cSql
   CREATE PROCEDURE states_write (
      INOUT id_inout    INT,
      INOUT code_inout  VARCHAR(2),
      INOUT name_inout  VARCHAR(20),
      OUT   errorno_out INT,
      OUT   error_msg   VARCHAR(255) )

      MODIFIES SQL DATA
   BEGIN

      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
         GET DIAGNOSTICS CONDITION 1
         errorno_out = MYSQL_ERRNO,
         error_msg   = MESSAGE_TEXT;
      END;

      IF id_inout = 0 THEN
         INSERT INTO states
            ( code, name )
         VALUES
            ( UCASE( code_inout ), name_inout );
         SET id_inout = LAST_INSERT_ID();
      ELSE
         UPDATE states
         SET    code = UCASE( code_inout ),
                name = name_inout
         WHERE  id = id_inout;
      END IF;

      SELECT code,name
      INTO code_inout,name_inout
      FROM states
      WHERE id = id_inout;

      SET errorno_out = 0;

   END;
ENDTEXT

   db():Execute( cSql )
 

Now all that is necessary is to tell the RowSet object is to use "states_write" procedure to save data.

Code: Select all  Expand view

oRs:cSaveSP := "states_write" // SP is for Stored Procedure
 

This one line of code is all that is needed to enable full editing capabilities.

Consistent with the philosophy of FiveWin, MYSQL/MARIADB functionality supports simplest to the most advanced and sophisticated MYSQL implementations with the same ease and simplicity.

Also, what all is dicussed in this post is possible only with FWH implementation and not even with ADO, not to mention other libs.

[nageswaragunupudi]

Putting things together here is a full test program:

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

//----------------------------------------------------------------------------//

function Main()

   local cLog  := cFileSetExt( ExeName(), "log" )
   local oRs, cPattern, nRows, aParams
   local oCn

   FErase( cLog )
   RDDSETDEFAULT( "DBFCDX" )
   SET DATE ITALIAN
   SET CENTURY ON
   SET DELETED ON

   if db() == nil
      ? "Connect fail"
      return nil
   endif

   db():lLogErr      := .t.  // like to see log file
   db():lShowErrors  := .t.

   if db():TableExists( "states" )
      db():DropTable( "states" )
   endif

   if db():ImportFromDBF( "c:\fwh\samples\states.dbf" )
      db():Execute( "CREATE UNIQUE INDEX states_code_idx ON states(code)" )
   endif

   CreateStoredProcedures()

   aParams  := { "%d%", 0 }

   oRs   := db():RowSet( "states_read", aParams )

   ? aParams[ 2 ]
   ? oRs:lReadOnly  // -> .T.
   oRs:cSaveSP := "states_write"
   ? oRs:lReadOnly  // -> .F.

   XBROWSER oRs FASTEDIT SHOW SLNUM TITLE "STATES"

   if File( cLog )
      WinExec( "notepad.exe " + cLog )
   endif

return nil

//----------------------------------------------------------------------------//

function db()

   static oCn

   if oCn == nil
      FWCONNECT oCn HOST "localhost" USER "gnrao" PASSWORD "secret" DATABASE "fwh"
   endif

return oCn

//----------------------------------------------------------------------------//

static function CreateStoredProcedures()

   local cSql

   db():Execute( "DROP PROCEDURE IF EXISTS states_read" )

TEXT INTO cSql
   CREATE PROCEDURE states_read( IN seek VARCHAR(20), OUT rows_found INT )
      READS SQL DATA
   BEGIN
      IF seek IS NULL OR seek = '' THEN
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM ( SELECT `id`,`code`,`name` FROM `states` ) S;
      ELSE
         SELECT `id` AS StateID, `code` AS StateCode, `name` AS StateName
         FROM (
           SELECT `id`, `code`, `name` FROM `states`
           WHERE `code` LIKE seek OR `name` LIKE seek
         ) S;
      END IF;

      SELECT FOUND_ROWS() INTO rows_found;

   END;
ENDTEXT

   db():Execute( cSql )

   db():Execute( "DROP PROCEDURE IF EXISTS states_write" )

TEXT INTO cSql
   CREATE PROCEDURE states_write (
      INOUT id_inout    INT,
      INOUT code_inout  VARCHAR(2),
      INOUT name_inout  VARCHAR(20),
      OUT   errorno_out INT,
      OUT   error_msg   VARCHAR(255) )

      MODIFIES SQL DATA
   BEGIN

      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
         GET DIAGNOSTICS CONDITION 1
         errorno_out = MYSQL_ERRNO,
         error_msg   = MESSAGE_TEXT;
      END;

      IF id_inout = 0 THEN
         INSERT INTO states
            ( code, name )
         VALUES
            ( UCASE( code_inout ), name_inout );
         SET id_inout = LAST_INSERT_ID();
      ELSE
         UPDATE states
         SET    code = UCASE( code_inout ),
                name = name_inout
         WHERE  id = id_inout;
      END IF;

      SELECT code,name
      INTO code_inout,name_inout
      FROM states
      WHERE id = id_inout;

      SET errorno_out = 0;

   END;
ENDTEXT

   db():Execute( cSql )

return nil

//----------------------------------------------------------------------------//
 

[goosfancito]

hola.
cuando defino un procedimiento que va a devolver (OUT) un valor de siete cifras con dos decimales, lo defino como DOUBLE? o FLOAT?

gracias.

[Armando]

Mr. Rao:

Excellent explanations

Regards