Using the wwSQL class from WestWind

to make things
easier
WestWind
Web Connection is a great tool for all sorts of development efforts. I'm not a big fan of
views, or cursoradapters in VFP development. I prefer the rough and tumble SQL Pass Through (SPT)
method of manipulating data. The wwSQL class helps take the pain out of SPT for me. There are two
methods in particular that really help: SQLBuildInsertStatementFromObject and
SQLBuildUpdateStatementFrom Object. These methods accept an object as the first parameter, and the
name of the target table as the second parameter, and creates a string containing a valid INSERT or
UPDATE command matching the contents of the object.
There is a problem however. WestWind
targets these methods at either VFP tables or SQL Server
tables. There are some differences between SQL Server INSERT statements and MySQL INSERT
statements. I probably haven't found all of the differences, but the changes are minor.
The big difference is the representation of the empty date. In SQL Server, the empty date can be
represented by the empty string. In MySQL, the empty date is represented by a series of zeros in the
format of '00000000'.
WestWind
also wraps field names in square brackets to help avoid problems
with spaces in field names, and also to avoid problems with reserve words. In MySQL, fields wrapped
in square brackets throws an error.
So, I have subclassed the wwSQL class and created three new properties: cEmptyDate,
cFieldParamLeft, and cFieldParamRight. When in use against a MySQL table, I have populated
cEmptyDate with the zero formatted string as above, and set the cFieldParamLeft and
cFieldParamRight with the empty string as well.
I then overrode the SQLBuildInsertStatementFromObject and the
SQLBuildUpdateStatementFromObject to draw from these properties and it works like a charm. As a
side note, I have also created a function named DtoMySQLDate( toDate AS Date) function that returns
a date formatted as '20070620'
no matter what SET DATE is set to.
I hope that you have gotten some tips in how to get VFP to access a MySQL server. Once you try it, I
think you will find it a fast, robust, and inexpensive alternative to other solutions.
Kevin