xBox via SQL Pass-Through and Stored Procedures

Since xBox was created while I am still working with pure VFP, the approach I did was designed to work only on VFP's native backend.  So to have something like this:

You have to put in its InteractiveChange Event codes like this:

Local lcSQL

TEXT TO lcSQL NOSHOW 

Select vesselname,vesselid from Vessels

      where [MySearch] $ vesselname 

      order By 1 

      into Cursor curVessel NOFILTER

ENDTEXT

This._searchgrid(m.lcSQL,'curVessel.VesselName')

Where [MySearch] will be automatically replaced by xBox class with whatever you have already typed.  And curvessel.VesselName is the value it will return, the one that will be held by its .Value property.  There is secondary value it can return after that via .ExtraValue property but that is another topic.  If you are already a subscriber or user of ssUltimate's xBox, then you already know about this.

Lately, I decided to use MariaDB and so to make xBox working there, I showed the way here: http://sandstorm36.blogspot.com/2017/04/xbox-on-mysql-backend.html

But that requires two jumps as follows:

* Get data from MariaDB/MySQL

SQLExec(gomyapp.dbHandle,'Select vesselname,vesselid from Vessels order by vesselname','vessels')

* Use generated cursor on the class

Local lcSQL

TEXT TO lcSQL NOSHOW  

Select vesselname,vesselid from Vessels

      where [MySearch] $ vesselname 

      order By 1 

      into Cursor curVessel NOFILTER

ENDTEXT

This._searchgrid(m.lcSQL,'curVessel.VesselName')

Or you can reverse the process limiting the number of records of the  cursor generated from the other backend using WHERE clause on the SPT query instead of the one needed by xBox class.  

Today, I added two new direct calls to the other backend which will not require a 2nd jump anymore.  Two new class methods, 1st is for SQL Pass-Through query approach and another for Stored Procedure approach.  Let us see how the call is made on those two:

via _SPT() Method.  This expects parameters as follows:

Lparameters ndbHandle, cSQL, cCursor, vValue, vExtraValue

Where:

ndbHandle - is the numeric handle for the database connection

cSQL - is your SQL SELECT statement for the SPT

cCursor - is the cursor that will be generated on VFP

vValue = is the primary value you can get from the class

vExtraValue - is the secondary value of the class

The standard way:

PRIVATE lcSearch

lcSearch = '%'+this._searchkeys+'%'

This._SPT(gomyapp.dbHandle,;

      'Select vesselname, vesselid from vessels WHERE inactive = 0 AND vesselname LIKE ?m.lcSearch',;

      'curVessel','curVessel.vesselname')

Alternative way (which I prefer):

This._SPT(gomyapp.dbHandle,;

      TEXTMERGE('Select vesselname, vesselid from vessels WHERE inactive = 0 AND vesselname LIKE "%<<ALLTRIM(This._SearchKeys)>>%"'),;

      'curVessel','curVessel.vesselname')

And that is how xBox can directly interact with your other backend via SPT.  Now, if you have a stored procedure, then the call is more simple.  

via _SPCALL() Method.  This expects parameters as follows:

Lparameters ndbHandle, cStoredProc, cCursor, vValue, vExtraValue

Where:

ndbHandle - is the numeric handle for the database connection

cStoredProc - is the name of your stored procedure

cCursor - is the cursor that will be generated on VFP

vValue = is the primary value you can get from the class

vExtraValue - is the secondary value of the class

Let us say you have a Stored Procedure named GetVessel with a parameter named SearchKeys like this:

CREATE PROCEDURE `getvessel`(

      IN `SearchKeys` VARCHAR(50)

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

Select vesselname, vesselid from vessels WHERE inactive = 0 AND vesselname LIKE SearchKeys;

END

Then you can take advantage of xBox' new _SPCALL() method instead of its _SPT() method.  The call is much simpler like this:

this._SPCALL(gomyapp.dbHandle,'getvessel','curvessel','curvessel.Vesselname')

Have you noticed? There is no mention of the value of the class or what has been typed so far?  Well, the class takes care already of that.  All you need to do is pass the name of the handle, the stored procedure name, the resultant cursor, and its return value (plus secondary value if you want to use that too).

And there you have it.  Working with other backend with xBox is now faster and simpler than ever.!!! Cheers!