Thursday, September 14, 2017

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

2 comments:

  1. Thank you Matthew. These new features will speed up further our xBox.

    ReplyDelete