Monday, April 3, 2017

xBox on MySQL Backend

One of my subscribers is using MySQL as his backend so I requested him to provide a tutorial, as I do not use that backend myself, on how to use xBox on that one.  This guide can be useful to others too who has a different backend aside from that of VFP.  So here it is, it is quite simple actually:

Author:  Glenn Palic
Date: April 1, 2017

How to use xBox on MySQL Backend

1.  Make a connection with your mysql backend using ODBC in load event of your form

Example:

Public pnConnectionHandle,oConn

oConn= "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Yourdb;Port=3306;uid=username;pwd=password;Option=3;"

SQLSetprop(0,"DispLogin",3)
pnConnectionHandle = Sqlstringconnect(oConn)
If pnConnectionHandle > 0
      *   successs
Else
      Messagebox("Can't connect to the Server please inform network administrator.",0+32,"Error")
      Quit

Endif

2.  As xBox uses InteractiveChange event, then do it like this now:

Local lcSQL, lcName
lcName =[%]+Alltrim(This.Value)+[%]
TEXT TO lcSQL NOSHOW
  Select LastName, FirstName, CustomerID From customer WHERE LastName like ?lcName
ENDTEXT
SQLExec(pnconnectionhandle,m.lcstr,"junk")
If !Eof()
      TEXT TO lcSQL NOSHOW  
            Select * From junk ORDER By 1 into Cursor junkemp NOFILTER
      ENDTEXT
      This._searchgrid(m.lcSQL,'junkemp.LastName','junkemp.CustomerID')
Endif

And that is it!

The only difference between using VFP table is it does not need that first transfer to junk above.  The second transfer to junkemp is necessary for the _SearchGrid method to kick in.  I guess there won't be too much difference too on another backend such as MSSQL.  Cheers!




3 comments:

  1. A very Useful guide. thanks for this info and sharing your vfp knowledge. your ultimate class helps me a lot in giving new looks/life of our in-house system. More power and looking forward to more coming updates.

    ReplyDelete
  2. Hello Jun,
    every time user press a key query will bring records from server means lots of traffic with every key we can avoid this : suppose user typed fist key a then all matching records are in your cursor now for next key we can use that cursor for further filtration.
    only single letter will bring the records after it reuse that records locally.

    ReplyDelete
    Replies
    1. Hi Neeraj,

      That will only be good if you don't perform a backspace or anything that will change some later characters already typed. Though we can trap if its length() is more than one. Let me investigate this suggestion as it merits scrutiny. Thanks.

      BTW, I already made tons of improvements on xBox which is not found on xBoxFree.

      Delete