BuildSQL is a handy SQL creation interface for your users. Most users should be able to create complex boolean expressions using BuildSQL with "AND"s, "OR"s and brackets. But before we go any further...
First the legal stuff: BuildSQL is freeware. You are under no obligation to send me anything even if you make money using it for a single app (though if it saved your butt, you might want to send a little present my way<g>). If you want to use it in anything sold commercially, I'm open to negotiation, but you need my permission first. BuildSQL may not be sold, rented or leased as is without written consent from me (the author). In other words, this ain't the next FoxFire!, and if you try to sell it as a 3rd party tool of your own creation you'd better know Bill Gates' lawyer.
Disclaimer: As far as I know, this class *won't* format your hard drive <g>. It works fine on my machine, so there's no reason for it not to work on yours. Even if you think it screwed something up for you, I will take no responsibility.
Feedback: If you have the time, send me a note on BuildSQL's strengths and/or weaknesses, especially the latter. Things can only be improved with dialog...
If you need to reach me for comments or help, here's the data:
Jon Wiest
CIS: 74131,2752
MSN: Jon_Wiest@msn.com
There, 'nuff said. Now back to the fun stuff...
BuildSQL is stored as a class in the file BASEFORM.VCX. It is based on frmBase, also stored in BASEFORM.VCX. The idea for frmBase came from Whil Hentzen's book "Programming Visual FoxPro 3.0", so I can't take credit for that. The controls used to create BuildSQL are stored in BASECTRL.VCX. On my system, BASEFORM.VCX and BASECTRL.VCX are in the directory C:\FoxDev. You may need to modify this.
BuildSQL is an SQL generator which searches the field captions and comments of tables in a DBC to create SQL expressions. It's simple to use, but effective. Currently it is limited to working with one DBC, but since you're getting the source code it would be pretty easy to modify it to handle more than one DBC. It does require some background work: the field captions and comments must be modified, and there are a few spots in the code which must be custom tailored for your situation (these spots can be searched for by looking for the string "CUSTOM MODIFY"). However, you can probably read through this document and set the whole thing up in under an hour.
Once you've set up your tables and fields you can call BuildSQL as a class like so:
SET DEFA TO MyAppDir && Whatever that is. All your tables and the DBC should
&& reside in a single directory.
SET CLASSLIB TO baseform
oSQL = CREATEOBJECT("frmBuildSQL", "MyDBC")
IF TYPE("oSQL") = "O"
oSQL.Show()
ENDIF
If you prefer, you can create a BuildSQL form, and then you can call the form like so:
SET DEFA TO MyAppDir
DO FORM BuildSQL WITH "MyDBC" TO cSQL
***************************************
GETTING STARTED -- Modify your field captions and comments
I've included a sample database from my most recent project. The database is called MSIMAIN.DBC. After you read this, take a gander to be sure I've explained it right. Look especially at MUSICIAN.DBF. Note that not all the fields are used.
Your DBC and all it's tables should reside in a single directory. Open the database, and modify the tables which contain fields you want to present to the user. Give each field you want displayed a caption. That's the easy part. It's the comments where things get tricky. To understand it we'll have to go behind the scenes:
There are 3 arrays/comboboxes and 1 text control for frmBuildSQL which are used to create the SQL expressions: aCategory/cboCategory - a picklist of fields; aOperator/cboOperator - six operators such as "=", "<", etc; aValue/cboValue - presents a list of items for the "right" side of the equation if the category requires a picklist; and, txValue - a text box for those categories which require a straight value.
The form property "aCategory" contains a picklist of fields which can be used to build an SQL statement. It is an array with 3 columns. Column 1 contains a "user friendly" list of field names, column 2 contains the actual table and field names, and column 3 contains the contents of the field comment.
The text in column 1 is derived from the field captions stored in the database. If the caption is empty, that field is ignored. If the caption contains text, and the comment contains one of 5 keywords, it is added to the list. As well, the comment for that field is analysed to determine how to fill the form property "aValue" (to provide a picklist of possible values), or whether to offer a text box instead. This code is stored in column 3 of the array "aCategory".
There are five types of value input which use two different controls: txValue is a text box control, and cboValue is a combo box control. The five types are:
TEXT: Offers a text box with a possible inputmask specified, ie: "TEXT:999999" offers a maximum 6 digit number, "TEXT:999-999-9999" allows for a phone number, and "TEXT:D" allows for dates (by using the Format property). These are stored as is in column 3 of aCategory.
SELECT: Stores the SQL code used to populate the aValue array, ie: "SELECT:instname, instname FROM instrmnt INTO ARRAY THISFORM.aValue ORDER BY instname". This text is stored in column 3 of aCategory.
VALUE: Fills THISFORM.aValue with the contents of a comma delimited list, ie for gender: "VALUE:M,F". This is stored as is in column 3 of aCategory.
NUM: Is similar to "TEXT:" (above) but allows only numeric responses. This is needed to distinguish from numeric "text" in the generated SQL code, ie: "phone = '2049998765'" vs "sales > 25000". If a "NUM:" is encountered no quotes are put around the "value" part of the equation.
BOOL: Fills THISFORM.aValue with "True" or "False". If "BOOL:" is encountered the SQL string will not contain quotes around the value: "PanelMembr = .T.".
Comments may still be added to the field comment box by placing a "&*" after the code.
*******************************************
Customizing BuildSQL:
Once you have modified your captions and comments, you will need to modify the code in a few places. Search for the string "CUSTOM MODIFY" to find these. BuildSQL stores the SQL code in the form property cSQLCode. This can be accessed from the class variable:
cSQL = oSQL.cSQLCode
or it will be passed back to the variable you specify if you run it as a form:
DO FORM BuildSQL WITH MyDBC TO cSQL
How you deal with this is up to you.
You may notice a couple of errors relating to the non-existence of "oApp.ToolBar" or "oApp.nFormInstanceCount". This class is designed to be called from within the app object oApp. If you aren't doing this, check out Hentzen's book (mentioned above). He'll give you good reason to try it. Besides, the lines are easily deleted.
*******************************************
A COMPREHENSIVE USER'S GUIDE
Your computer illiterate users may wonder how to use this, and what the bracket and OR buttons are for. Basically, any two items which are adjacent to each other are assumed to be connected by an "AND". You can pop an "OR" between two items, add brackets to group items, etc. You can add items above or below the current line by toggling the "Above/Below" option button. The length of the string is limited only by FoxPro. If you can't figure it out, call me.
*******************************************
Done! I hope you enjoy this class. I've already used it in two subsequent and unrelated projects, and I know I'll use it again. Have fun!
