This appendix contains a step-by—step explanation of the creation of the SQL-VB Interpreter project. This program is already on the CD-ROM included with your copy of the book. The SQL-VB program in this lesson allows you to use an ASCII editor to create SQL scripts that SQL-VB can read and process. SQL-VB can handle fully commented, multiline SQL scripts. You'll find that SQL-VB will become a very handy data management tool.
You do not need to construct this project from scratch. It is already shipped on the disc. However, you may want to go through this chapter as an added guide to constructing SQL-enabled applications in Visual Basic.
You may often need to quickly generate sample database layouts for a programming project. You may even need to build some test data to run through data editing or reporting routines. The SQL-VB program enables you to do all that. The SQL-VB program is able to read SQL scripts you create with the Windows Notepad application (or any other ASCII editor). Listing A.1 is an sample SQL script that can be processed by SQL-VB.
Listing A.1. Sample SQL script.
// // load and read data tables // // open a database dbOpen C:\TYSDBVB\SQLVB\BOOKS.MDB // open some tables to view SELECT * FROM Authors; SELECT * FROM Titles; SELECT * FROM Publishers; SELECT * FROM BookSales; SELECT * FROM Buyers; SELECT * FROM [Publisher Comments]; // // eof //
Listing A.1 opens a database and then displays several data tables on the screen. This same script could perform any valid SQL statement and show the results on the screen for the user to review or edit.
The advantage of generating database layouts using SQL-VB is that you have some documentation on the database structure that you can refer to in the future. You can also use SQL-VB to generate test SELECT queries and other SQL statements before you put them into your Visual Basic programs. Finally, SQL-VB is an excellent tool for exploring SQL and your databases.
Before you jump into code mode, lay out some general design parameters for the SQL-VB project. SQL-VB should be able to do the following:
That last item may be a surprise to some. Remember that Microsoft Access SQL has no keyword for opening, closing, or creating a database! You'll add your own script keywords to handle this.
To accomplish all this you need three forms and one code module:
The SQLVBMain form needs some menu items and a CommonDialog control to handle the Open File dialog that runs the SQL scripts. The SQLVBChild form needs a Data control and a DBGrid control to handle the result sets. The SQLVBAbout needs a couple of Label controls and a single OK command button.
The SQLVBMod code module needs three main routines and a host of supporting routines. The three main routines are:
The SQLVBMod needs an error routine; some special routines to handle the database OPEN, CLOSE, and CREATE commands; a routine to handle the SQL DML commands (SELECT...FROM ); and a routine to handle the SQL DDL commands (CREATE TABLE, for example). You can add these as you go along.
You'll need one other set of tools to meet the design criteria—the ability to edit scripts from within SQL-VB. Instead of trying to create your own editor, we'll show you how you can include the Windows program Notepad as part of your Visual Basic project. This can be done with minimal effort, and it is a great way to take advantage of the software already available on users' desktops. This is perfectly legal as long as you do not provide users with a copy of the NOTEPAD.EXE program. Because all Windows systems have this program already, you're all set.
NOTE: If you haven't already done so, start up Visual Basic and prepare it for a new project.
The first thing you'll do is define the MDI form for the project. This form provides the interface to the Notepad editor for managing script files. It also enables users to run existing scripts to see the results. Because it is a multidocument interface, you need to add some menu options to enable users to arrange the forms within the workspace. Finally, you add access to an About Box via the menu.
Add an MDI form to your project by selecting Insert | MDIForm from the Visual Basic main menu. This form contains a few controls that allow the user to open an ASCII file to edit or run, arrange the various child forms open within the SQLVBMain MDI form, and show the About Box upon request. Use Table A.1 and Figure A.1 as a guide as you build the form.
Creating the SQLVBMain MDI form.
NOTE:In Table A.1, two controls are listed with an extra space preceding them. These controls are child controls. They must be placed directly on the controls that precede them in the table listing. For example, the CommonDialog control must be placed on the Picture1 control. The Label controls must be placed on the Picture2 control. Visual Basic does not allow standard controls to be placed directly on an MDI Form. You can, however, place Picture controls on an MDI form, and then place your standard controls on the Picture controls.
Table A.1. Visual Basic controls for the SQLVBMain MDI form.
| Control | Property | Setting |
|---|---|---|
| MDIForm | Name Caption WindowState | SQLVBMain SQL-VB Interpreter 2—Maximized |
| PictureBox | Name Align Height Visible | Picture1 1—Align Top 450 0—False |
| —CommonDialog | Name | CommonDialog1 |
| PictureBox | Name Align Height | Picture2 2—Align Bottom 420 |
| —Label | Name BorderStyle Left Height Width | lblProgress 1—Fixed Single 60 300 9375 |
Now that you have created the form, you need to add the menu. Table A.2 shows the hierarchy tree of the menu items you need for the SQLVBMain form.
Table A.2. Menu tree for the SQLVBMain MDI form.
| Caption | Name |
|---|---|
| &File | mnuFile |
| —&New... | mnuFileNew |
| —&Edit... | mnuFileEdit |
| —— | mnuFileSp01 |
| —&Run... | mnuFileRun |
| —— | mnuFileSp02 |
| —E&xit | mnuFileExit |
| &Windows | mnuWindows |
| —&Cascade | mnuWindowsCascade |
| —&Tile | mnuWindowsTile |
| —&Arrange | mnuWindowsArrange |
| &Help | mnuHelp |
| —A&bout | mnuHelpAbout |
As you build the menu, you need to set two additional properties of the &Windows menu item. Set the Index property to zero and set the WindowList property to True. This forces Visual Basic to create a dynamic list of all the child forms open under the SQLVBMain MDI form window.
The final step in completing the SQLVBMain form is adding the Visual Basic code that activates the various menu options selected by the user. Because most of that code calls other routines you have not yet written, skip the Visual Basic code for now, and you'll get back to it at the end of the project.
Before continuing with the lesson, save this form as SQLVBMAI.FRM and save the project as SQLVB.VBP.
The SQLVBChild child form displays any result set created by SQL statements in the script being processed. You need two controls on this form—a data control and a data bound grid control. Add a new form to your project by selecting Insert | Form from the Visual Basic main menu. Use Table A.3 and Figure A.2 as a guideline for creating SQLVBChild.
Creating the SQLVBChild child form.
Table A.3. Visual Basic controls for the SQLVBChild child form.
| Control | Property | Setting |
|---|---|---|
| Form | Name Height Width MDIChild | SQLVBChild 3690 7485 -1—True |
| Data | Name Height Left Top Width | Data1 300 120 2820 7095 |
| DBGrid | Name Height Left DataSource Top Width | DBGrid1 2535 120 Data1 120 7095 |
You need to add code in three locations within the SQLVBChild form: 1) in the Form_Load event, 2) in the Form_Unload event, and 3) in the Form_Resize event.
TIP: To add code to one of the Form events, double-click on any empty location of the form to bring up the Visual Basic code window for SQLVBChild. The first event you should see is the Form_Load event. You can use the drop-down list box on the right to locate other events for the form object.
Open the Visual Basic code window for the Form_Load event and add Visual Basic program code in Listing A.2.
Listing A.2. Adding code to the Form_Load event.
Private Sub Form_Load() Me.Caption = Trim(cSQLLine) Data1.DatabaseName = Trim(cGlobaldbName) Data1.RecordSource = Trim(cSQLLine) Data1.Caption = Trim(cSQLLine) DBGrid1.Caption = Trim(cSQLLine) End Sub
Listing A.2 first sets the form's Caption property. Then, the code sets the Data1.DatabaseName, Data1.RecordSource, and Data1.Caption properties. Finally, it sets the DBGrid.Caption property. The variables used to set these properties (cSQLLine and cGlobaldbName) are global variables that are initialized prior to loading this form (see Listing A.4).
The Form_Unload event contains a single line of code. This code line decrements the count of all open child forms. This allows the MDI form (SQLVBMain) to keep track of all the result sets that are currently open. Enter the following code in the Form_Unload event.
Private Sub Form_Unload(Cancel As Integer) nForms = nForms - 1 End Sub
The last code piece needed for the SQLVBChild form is the code behind the Form_Resize event (see Listing A.3.). This code automatically resizes the DBGrid and Data controls whenever the user resizes the form. Note the if test that occurs at the start of the routine. Whenever a form is minimized, the Form_Resize event occurs. Attempts to resize a minimized form will result in Visual Basic errors, so you check to be sure the form is not minimized before you continue with the routine.
Listing A.3. Adding code to the Form_Resize event.
Private Sub Form_Resize()
'
' if the form was re-sized by user
' and was not minimized, then
' re-size controls, too
'
If Me.WindowState <> 1 Then
Data1.Width = Me.Width - 240
Data1.Left = 60
Data1.Height = 300
Data1.TOP = Me.Height - 480 - Data1.Height
'
DBGrid1.Width = Me.Width - 240
DBGrid1.Left = 60
DBGrid1.TOP = 60
DBGrid1.Height = Me.Height - 720 - Data1.Height
End If
'
End Sub
After you have entered these code pieces, save the form as SQLVBCHI.FRM. It's a good idea to save the project at this time, too.
The last form you need for this project is the SQLVBAbout form. This is a simple form that lists the name and version of the program and its authors. Use Table A.4 and Figure A.3 as guides as you create this form for your project.
Creating the SQLVBAbout form.
Table A.4. Visual Basic controls for the SQLVBAbout form.
| Control | Property | Setting |
|---|---|---|
| Form | Name Caption Height Left MaxButton MinButton Top Width | SQLVBAbout About SQL-VB 2040 2520 0—False 0—False 2490 4380 |
| CommandButton | Name Caption Height Left Top Width | Command1 &OK 300 1500 1200 1200 |
| Label | Name Alignment BorderStyle Caption Font—Name Font Height Left Top Width | Label1 2—Center 1—Fixed Single SQL-VB Interpreter Microsoft Sans Serif —Size 15 495 120 120 3975 |
| Label | Name Alignment BorderStyle Caption Height Left Top Width | Label2 2—Center 1—Fixed single Copyright 1995—MCA/CLS 300 120 720 3975 |
You need a single Visual Basic code line behind the Command1_Click event. Double-click on the OK button to bring up the code window and insert the following line of code.
Private Sub Command1_Click() Unload Me End Sub
This code line exits and unloads the form from memory when the user hits the command button. Save this form as SQLVBABO.FRM. Save the project at this time, too.
Now that you have created all three forms, you can go back to SQLVBMain and add the code behind the menu options. This is also the time when you add code that calls Windows' Notepad program from within SQLVB.
First, add code behind the Help menu option that shows off the SQLVBAbout form. To open the code window for the About menu option, select Help | About. When the code window pops up, insert the line below.
Private Sub mnuHelpAbout_Click()
SQLVBAbout.Show vbModal
End Sub
Now add code that gives the user the ability to control the multiple child forms within the SQLVBMain MDI form. Select Windows | Cascade and insert the following code.
Private Sub mnuWindowsCascade_Click() SQLVBMain.Arrange vbCascade End Sub
The Arrange method requires a single parameter. The vbCascade parameter tells Visual Basic to cascade all open child windows starting from the top left of the MDI form. The vbTileHorizontal parameter tells Visual Basic to tile the child forms. Select Windows | Tile from the menu and insert the following code.
Private Sub mnuWindowsTile_Click() SQLVBMain.Arrange vbTileHorizontal End Sub
The vbArrangeIcons parameter tells Visual Basic to arrange all minimized forms in an orderly fashion at the bottom of the MDI parent form. Select Windows | Arrange to open the code window and add the following code piece.
Private Sub mnuWindowsArrange_Click() SQLVBMain.Arrange vbArrangeIcons End Sub
Now add the code behind the File | Exit menu option. This code will safely closes down all open child windows before exiting to the operating system.
Private Sub mnuFileExit_Click() Unload Me End Sub
Select File | Run and add the following code line. Notice that the code line starts with the comment character. This tells VB to treat this line as a comment, not executable code. You have it "commented out" right now because you haven't created the SQLMain routine yet. You'll do that in the next section when you create the SQL-VB Main Code Module.
Private Sub mnuFileRun_Click() ' SQLMain End Sub
The following two segments of code should be added behind the File | New and File | Edit menu options. The code calls a routine you will build in the SQLVB Main Module so you have commented out the calls for now to prevent Visual Basic from reporting an error at compile time.
Private Sub mnuFileNew_Click() ' LoadNotePadFile "Create New SQLVB File" End Sub Private Sub mnuFileEdit_Click() ' LoadNotePadFile "Edit an Existing SQLVB File" End Sub
You need to add one more code routine to the form. As an added feature of the software, you want to be able to add a script file as a parameter when you run the program. This script would be loaded and executed automatically. You can use this feature to create associations in Windows that will automatically load and run the program when you click on the script file from the File Manager shell program.
To add command line processing to this application, you only need to add three lines of code to the Form_Activate event.
Private Sub MDIForm_Activate()
If Command$ <> "" Then
' SQLMain Trim(Command$) ' call main job w/ parm
End If
End Sub
The preceding code piece checks the Visual Basic system variable Command to see if it contains a value. If it does, you assume it is a valid script file, and then call the main processing routine just as if someone had used the File | Run menu option to select a file. Note that you have commented out the line that calls SQLMain because you have not yet created that routine.
Now that all the code is added, save this form and save the project. As a test, you can run the project. You can't do much except view the About box and exit, but you can check for compile errors.
The SQLVBMod Code Module contains the major portion of the system. It's here that you add the routines that can read and execute the SQL statements found in the ASCII file. You'll also add routines to handle any errors that occur along the way. Even though this module does a lot, you have only slightly more than ten routines to define before you complete the project.
First, you need to declare a set of variables to be used throughout all the project. These variables contain information about the script being processed, any forms that are open, and so forth. Add a module to the project by selecting Insert | Module from the Visual Basic main menu. Set its name property to SQLVBMod and enter the lines Listing A.4 into the declarations section. The meaning and use of these variables will become clearer as you build the various routines within the module.
Listing A.4. Adding the global variables.
' ' general declarations ' Global cSQLFile As String Global nGlobalErr As Integer Global nSQLFlag As Integer Global ndbFlag As Integer Global nSQlFileHandle As Integer Global cSQLLine As String Global nLine As Integer Global cLine As String Global cGlobalSelect As String Global cGlobaldbName As String Global db As DATABASE Global nForms As Integer Global TblForms() As Form
The topmost routine in this module is the SQLMain routine. This routine has only three tasks: 1) open the script file, 2) process the script commands, and 3) close the script file. So let's write a module that does just that. To add a new procedure to the module, select Insert | Procedure from the Visual Basic main menu. Enter SQLMain(cRunFile) as the name, select the Sub radio button, and select the Public radio button. Now enter the code in Listing A.5.
Listing A.5. Coding the SQLMain routine.
Sub SQLMain(cRunFile) ' ' main loop for interpreting SQL ASCII file ' InitApp ' clean up environment SQLFileOpen cRunFile ' open the script SQLFileProcess ' process the script SQLFileClose ' close the script ' cRunFile = "" ' clear passed parm MsgBox "Script Completed", vbInformation End Sub
The routine in Listing A.5 does all the things mentioned earlier and adds two more actions. You perform some application initialization. You set an error condition during the SQLFileOpen routine in case something goes wrong when you open the file. Then you can check that error condition before you try to run the SQLFileProcess routine. Also, once the script processing is completed, you show the user a friendly little message box.
Now let's start building the next level of routines. The first is the SQLFileOpen routine. Use the CommonDialog control to get the filename from the user. If a filename was selected, open that file for processing, and then return to SQLMain. Notice that you have built in an error trap to catch any problems that may occur during file selection and opening.
Select Insert | Procedure from the Visual Basic main menu and set the name to SQLFileOpen(cSQLFile). Make this a Public Sub procedure. Now enter the code Listing A.6 in the procedure window.
Listing A.6. Coding the SQLFileOpen routine.
Sub SQLFileOpen(cSQLFile)
On Error GoTo SQLFileOpenErr
'
If Len(Trim(cSQLFile)) = 0 Then
SQLVBMain.CommonDialog1.DialogTitle = "Load SQLVB File"
SQLVBMain.CommonDialog1.DefaultExt = "SQV"
SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV"
SQLVBMain.CommonDialog1.ShowOpen
cSQLFile = SQLVBMain.CommonDialog1.filename
End If
'
If Len(cSQLFile) = 0 Then
nGlobalErr = True
nSQLFlag = False
GoTo SQLFileOpenExit
End If
'
nSQlFileHandle = FreeFile(0)
Open cSQLFile For Input As nSQlFileHandle
nGlobalErr = False
nSQLFlag = True
GoTo SQLFileOpenExit
'
SQLFileOpenErr:
ErrMsg Err, Error$, nLine, cLine, "SQLFileOpen"
InitApp
'
SQLFileOpenExit:
End Sub
Let's skip over the SQLProcess routine and write the SQLFileClose routine next. The only task this routine has to complete is to safely close the script file upon completion. Create a Public Sub Procedure called SQLFileClose and enter Listing A.7.
Listing A.7. Coding the SQLFileClose routine.
Sub SQLFileClose()
On Error GoTo SQLFileCloseErr
'
If nGlobalErr = False Then
If nSQlFileHandle <> 0 Then
Close (nSQlFileHandle)
End If
nSQLFlag = False
End If
GoTo SQLFileCloseExit
'
SQLFileCloseErr:
ErrMsg Err, Error$, nLine, cLine, "SQLFileClose"
InitApp
'
SQLFileCloseExit:
'
End Sub
Now you get to the heart of the program—SQLFileProcess. This routine reads each line of the script file and performs whatever processing is necessary to build and execute the SQL statements in the script. You also add a few lines that show the user the script lines as they are processed. Also, you'll remember that the script file has regular SQL statements, special database OPEN, CREATE and CLOSE words, and comments. This processing routine has to handle each of these differently. Of course, you need an error handler, too.
Create a Public Sub Procedure called SQLFileProcess and enter the code in Listing A.8. Don't be discouraged by the length of this code piece. Although it's about 50 lines long, it won't take you long to enter it into the project.
Listing A.8. Coding the SQLProcess routine.
Sub SQLFileProcess()
'
' main loop for processing ASCII file lines
'
On Error GoTo SQLFileProcessErr
'
Dim cToken As String
'
If nSQLFlag = False Then
GoTo SQLFileProcessExit
End If
'
cSQLLine = ""
While Not EOF(nSQlFileHandle)
If nGlobalErr = True Then
GoTo SQLFileProcessExit
End If
'
Line Input #nSQlFileHandle, cLine
nLine = nLine + 1
cLine = Trim(cLine) + " "
If Len(cLine) <> 0 Then
cToken = GetToken(cLine)
If Right(cToken, 1) = ";" Then
cToken = Left(cToken, Len(cToken) - 1)
End If
'
SQLVBMain.lblProgress.Caption = cLine
DoEvents
Select Case UCase(cToken)
Case Is = "//"
' no action - comment line
Case Is = "DBOPEN"
SQLdbOpen
Case Is = "DBMAKE"
SQLdbMake
Case Is = "DBCLOSE"
SQLdbClose
Case Else
cSQLLine = cSQLLine + cLine
If Right(cLine, 2) = "; " Then
SQLDoCommand
cSQLLine = ""
End If
End Select
End If
Wend
GoTo SQLFileProcessExit
'
SQLFileProcessErr:
ErrMsg Err, Error$, nLine, cLine, "SQLFileProcess"
InitApp
'
SQLFileProcessExit:
'
End Sub
Despite containing less than 50 lines of code, several things are happening in Listing A.8. Let's review the routine more closely. After setting up the error trap and initializing variables, the main While...Wend loop starts. This loop reads a line from the script file opened by SQLFileOpen, updates a line counter, removes any trailing or leading spaces from the line, and then adds a single space at the end of the line. This single space is added to help the GetToken function do its work.
The SQLVB program processes each line of script word by word. The first word in each command line is used to determine how SQLVB will process the line. The GetToken function returns the first word in the line (you'll learn more about GetToken a bit later). Next, you show the current script line to the user by updating SQLVBMain.lblProgress. Notice that you added the DoEvents command right after updating the label. This forces your program to pause a moment, and that will allow Windows time to send the message that ultimately updates the SQLVBMain form.
Once the main form is updated, The program must handle the 'word' it pulled from the script line. Usually, the word is an SQL keyword and SQLVB can add it to the cSQLLine variable for eventual processing. However, there are four 'words' that require special handling. These four 'words' are listed in Table A.5 along with comments about how they are handled.
Table A.5. Script words that require special handling.
| Script Word | Handling Comments |
|---|---|
| // | This is the comment word. If a line begins with this keyword, ignore the rest of the line and get the next line in the script. You must leave at least one space between the // and the comment. For example, //comment would be rejected by SQL-VB, but // comment is just fine. |
| DBOPEN | This is the OpenDatabase word. If a line starts with this keyword, call a special routine (SQLdbOpen) that executes a Visual Basic OpenDatabase operation. |
| DBMAKE | This is the CreateDatabase word. If a line starts with this keyword, call a special routine (SQLdbMake) that executes a Visual Basic CreateDatabase operation. |
| DBCLOSE | This is the CloseDatabase word. If a line begins with this keyword, call a special routine (SQLdbClose) that executes a Visual Basic Close operation on a database object. |
If the word found at the start of the line is not one of the four above, the program assumes that it is a valid SQL word and adds the entire line to the variable cSQLLine. After doing this, the routine checks to see if the current line ends with a semicolon (;). If so, the program attempts to execute the SQL statement using the SQLDoCommand routine. After executing this routine, the cSQLLine variable is cleared in preparation for the next SQL statement.
This process is repeated until the program reaches the end of the script file. At that time, the routine exits SQLFileProcess and returns to the SQLMain routine.
Now would be a good time to save the SQLVBMod code module and save the project. You can't run the program at this point because you added references to several routines that do not yet exist. You'll add those final routines in the next section.
Now that you have entered all the main routines, you need to add several support routines. Almost all these support routines are called directly from SQLFileProcess. You'll concentrate on those first and add others as needed.
The first routine called from SQLFileProcess is GetToken. This routine takes a line of script and returns the first word in the list. You'll use this word (often referred to as a token) as a way to determine how SQLFileProcess handles each line of script. Because GetToken returns a value, it is a function. To create a Visual Basic function, select Insert | Procedure. Enter the function name as GetToken(cString As String) As String and select the Function radio button. Now enter Listing A.9 in the code window.
Listing A.9. Coding the GetToken routine.
Function GetToken(cString As String) As String
Dim nTemp As Integer ' holds string location
'
nTemp = InStr(cString, " ") ' search for first space
If nTemp > 0 Then ' if you found one,
GetToken = Left(cString, nTemp - 1) ' get the first 'word'
Else
GetToken = "" ' else return empty string
End If
End Function
The comments in the code explain things pretty well. You use the Visual Basic InStr function to locate the first occurrence of a space within the script line, then use that position to grab a copy of the first word in the line. If you can't find a word, you return an empty string.
The next three routines you add handle the DBOPEN, DBMAKE and DBCLOSE script words. These are all non-SQL commands that you need in order to open, create, and close Microsoft Access JET databases. The first one you add is the routine that handles opening a Microsoft Access JET database. Use the Visual Basic menu to create a Public Sub routine named SQLdbOpen and enter the code in Listing A.10.
Listing A.10.Coding the SQLdbOpen routine.
Sub SQLdbOpen()
On Error GoTo SQldbOpenErr
'
Dim cOpen As String
Dim nTemp As Integer
'
cLine = Trim(cLine) ' drop any spaces
nTemp = InStr(cLine, " ") ' locate first embedded space
cOpen = Mid(cLine, nTemp + 1, 255) ' get rest of line
'
' if line ends w/ ";", dump it!
If Right(cOpen, 1) = ";" Then
cOpen = Left(cOpen, Len(cOpen) - 1)
End If
'
' now try to open database
Set db = OpenDatabase(cOpen)
ndbFlag = True
GoTo SQldbOpenExit
'
SQldbOpenErr:
ErrMsg Err, Error$, nLine, cLine, "SQldbOpen"
InitApp
'
SQldbOpenExit:
'
End Sub
Listing A.10 performs three tasks. First, it strips the DBOPEN keyword off the script line. Second, if a semicolon (";") appears at the end of the line, the routine drops it off the line. What's left is the valid database filename in the variable cOpen. The routine then attempts to open this file as a Microsoft Access JET database. Once that's done, the routine returns to SQLFileProcess.
The next routine to add handles the DBCLOSE command. This is a simple routine. It's only job is to close the Microsoft Access JET database. This routine also classes out any open child forms and clears flags variables. Create a Public Sub called SQLdbClose and add Listing A.11.
Listing A.11. Coding the SQLdbClose routine.
Sub SQLdbClose()
On Error Resume Next ' ignore errors here
'
db.Close
'
For x = 0 To nForms
Unload TblForms(x)
Next x
'
nForms = 0
ndbFlag = False
End Sub
The final routine to handle special commands is the routine that processes the DBMAKE keyword to create new Microsoft Access JET databases. This one works much like the DBOPEN routine except that there are a few additional chores when creating a new file. Create a Public Sub called SQLdbMake and enter the code in Listing A.12.
Listing A.12. Coding the SQLdbMake routine.
Sub SQLdbMake()
On Error GoTo SQLdbMakeErr
'
Dim cMake As String
Dim nTemp As Integer
'
cLine = Trim(cLine) ' drop any spaces
nTemp = InStr(cLine, " ") ' locate first embedded space
cMake = Mid(cLine, nTemp + 1, 255) ' get rest of line
'
' if line ends w/ ";", dump it!
If Right(cMake, 1) = ";" Then
cMake = Left(cMake, Len(cMake) - 1)
End If
'
' try to open it (to see if it already exists)
nSQLMakeHandle = FreeFile(0)
Open cMake For Input As nSQLMakeHandle
Close nSQLMakeHandle
'
nResult = MsgBox("ERASE [" + cMake + "]", vbYesNo + vbQuestion,
"Database Already Exists!")
If nResult = vbYes Then
Kill cMake
Else
ErrMsg 0, "Script Cancel - Database Already Exists",
nLine, cLine, "SQLdbMake"
InitApp
End If
'
' now try to make a new database
SQLdbMake2:
Set db = CreateDatabase(cMake, dbLangGeneral, dbVersion25)
ndbFlag = True
GoTo SQLdbMakeExit
'
SQLdbMakeErr:
If Err = 53 Then
Resume SQLdbMake2
Else
ErrMsg Err, Error$, nLine, cLine, "SQLdbMake"
InitApp
End If
'
SQLdbMakeExit:
'
End Sub
A few things in this routine deserve attention. First, the routine drops the first word from the script line (the DBMAKE word). Then it strips the semicolon off the end of the line, if necessary. Then, instead of performing the create operation, the routine first tries to open the file. This is done to see if it already exists. If it does, you can issue a warning before you clobber that multimegabyte database that the user has been nursing for the last few months. If no error occurs when you try to open the file, the routine sends out a message warning the user and asking if it's okay to erase the existing file. If the answer is Yes, the file is erased. If the answer is No, a message is displayed, and the script processing is halted.
Now, if an error occurs during the attempt to open the file, you know that the file does not exist. The local error handler is invoked and the first thing checked by the local error handler is to see if the error was caused by an attempt to open a non-existent file. If so, the error handler sends the routine to the file creation point without comment. If it's another error, the global error handler is called and the program is halted.
Finally, after all the file creation stuff is sorted out, the routine executes the Visual Basic CreateDatabase operation and returns to the SQLFileProcess routine. Notice that you declared two parameters during the CreateDatabase operation. The first parameter (vbLangGeneral) tells Visual Basic to use the general rules for sorting and collating data. The second parameter (vbVersion25) tells Visual Basic to create a version 2.5 Microsoft JET database. This data format can be read by Visual Basic 3.0, Visual Basic 4.0, and Microsoft Access 2.0.
The last routine called from SQLFileProcess handles the execution of SQL statements. Create a Public Sub called SQLDoCommand and enter the code in Listing A.13.
Listing A.13. Coding the SQLDoCommand routine.
Sub SQLDoCommand()
On Error GoTo SQLDoCommandErr ' set error trap
'
Dim cTemp As String ' holds token
'
' skip errors if you're deleting objects
cTemp = GetToken(Trim(cSQLLine)) ' get first word
Select Case UCase(cTemp)
Case Is = "DELETE" ' don't report error
On Error Resume Next
Case Is = "DROP" ' don't report error
On Error Resume Next
Case Is = "ALTER" ' don't report error
On Error Resume Next
End Select
'
' check for queries that return a view
Select Case cTemp
Case Is = "TRANSFORM"
ShowTable cSQLLine ' show view form
Case Is = "SELECT"
If InStr(cSQLLine, " INTO ") <> 0 Then
db.Execute cSQLLine ' execute make-table SQL
Else
ShowTable cSQLLine ' show view form
End If
Case Else
db.Execute cSQLLine ' execute SQL
End Select
GoTo SQlDoCommandExit ' exit routine
'
' local error handler
SQLDoCommandErr:
ErrMsg Err, Error$, nLine, cLine, "SQLDoCommand"
InitApp
'
' routine exit
SQlDoCommandExit:
'
End Sub
Even though it looks as though several things take place in this routine, only three tasks are being handled by SQLDoCommand. First, you get the first word in the script line, and then you have to make a couple of decisions on how to properly execute the SQL statement.
If the first word is DELETE, DROP, or ALTER, you turn off the local error handler. This is done for convenience. You want to be able to create scripts that can use the SQL words DELETE, DROP, and ALTER to remove table objects from the database. Because the objects may not exist, you could get errors that can halt the script processing. To make life simple, SQL-VB ignores these errors. Once you write a few SQL-VB scripts, you'll appreciate this feature.
Next, you have to check for the SQL keywords that may return result sets. These are TRANSFORM and SELECT. These keywords should be handled differently from SQL statements that do not return result sets. If you see TRANSFORM, you call the ShowTable routine to load and display the SQLVBChild child form on the screen. If you see SELECT, you make one additional check. If the line contains the INTO keyword, you have an SQL statement that will create a new table. Using the INTO keyword means that the SELECT statement will not return a result set. If there is no INTO in the SQL statement, you hand the statement off to the ShowTable routine. If the line starts with any other SQL keyword, you simply execute the command using the Visual Basic Execute method on the database.
The SQLDoCommand routine calls the ShowTable routine, so you need to add that routine to the project. This is a simple routine that updates some variables, creates a new instance of the SQLVBChild child form, and shows the new form. Create a Public Sub called ShowTable and enter the code in Listing A.14.
Listing A.14. Coding the ShowTable routine.
Sub ShowTable(cSQL As String) ' cGlobalSelect = cSQLLine cGlobaldbName = db.Name ' nForms = nForms + 1 ReDim Preserve TblForms(nForms) As Form Set TblForms(nForms) = New SQLVBChild TblForms(nForms).Show End Sub
The only real fancy stuff in this module is the creation of new Form objects. Remember that you created a global array called TblForms in the declaration section of the module? This routine increases the size of the array by one each time it is invoked. Also, this routine uses the Visual Basic SET command to create a new instance of the SQLVBChild child form. This new instance is a copy of SQLVBChild that will have its one "life," once it is loaded and displayed with the Visual Basic SHOW method. You may also remember that you added code in the SQLVBChild.Form_Unload event that destroys the current instance of the form and decrements the array counter to keep everything in order.
You need to add another support routine. This one handles the loading of the scripts into the Windows Notepad for editing. This is called from the SQLVBMain MDI form. Create a Public Sub called LoadNotePadFile(cLoadMsg As String) and enter the code in Listing A.15.
Listing A.15. Coding the LoadNotPadFile routine.
Sub LoadNotePadFile(cLoadMsg As String)
On Error GoTo LoadNotePadFileErr:
'
Dim cEditFile As String
Dim nAppID As Long
'
SQLVBMain.CommonDialog1.DialogTitle = cLoadMsg
SQLVBMain.CommonDialog1.DefaultExt = "SQV"
SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV"
SQLVBMain.CommonDialog1.ShowOpen
cEditFile = SQLVBMain.CommonDialog1.filename
'
If Len(cEditFile) <> 0 Then
nAppID = Shell("NotePad " + cEditFile, 1)
AppActivate (nAppID)
End If
GoTo LoadNotePadFileExit
'
LoadNotePadFileErr:
ErrMsg Err, Error$, 0, cEditFile, "LoadNotePadFile"
InitApp
'
LoadNotePadFileExit:
'
End Sub
Most of this code should look familiar. The first part of the routine in Listing A.15 sets up and activates the CommonDialog object to allow the user to select an existing file or create a new file. Once this is done, the routine forces Windows to load a new instance of the Notepad application, then gives that application the focus. Now the user sees the Notepad application (with the selected file loaded, too!). The SQL-VB application resumes processing once it gains the focus again.
The next routine you need to add to SQLVBMod is the global error handler. This routine (see Listing A.16) simply displays the error messages and waits for the user the click the OK button before it returns to the calling routine. Create a Public Sub called ErrMsg and enter the following code.
Listing A.16. Coding the ErrMsg routine.
Sub ErrMsg(nErr As Integer, cError As String, nLine As Integer, cLine As String, cModule As String) Dim cMsg As String ' cMsg = "ErrNo:" + Chr(9) + Str(nErr) + Chr(13) cMsg = cMsg + "ErrMsg: " + Chr(9) + cError + Chr(13) cMsg = cMsg + "LineNo:" + Chr(9) + Str(nLine) + Chr(13) cMsg = cMsg + "Text: " + Chr(9) + cLine ' MsgBox cMsg, vbCritical, cModule End Sub
No real magic in this routine. Listing A.15 is passed the Visual Basic error number and error message, the script line number and script line text, and the name of the SQL-VB routine that experienced the error. All this is formatted into a readable (if not entirely welcome) message that is displayed to the user. Notice that you used the tabs (Chr(9)) and carriage returns (Chr(13)) to make the information easier to read.
You need to add one more routine to SQLVBMod. The routine in Listing A.17 handles all the initialization chores for the start of a script. It is also called whenever an error is reported and when the program is exited. Create a Public Sub Procedure called InitApp and enter the following code.
Listing A.17. Coding the InitApp routine.
Sub InitApp()
On Error Resume Next ' ignore any errors here
'
' close all child forms
For x = 0 To nForms
Unload TblForms(x)
Next x
'
' close open database
If ndbFlag = True Then
db.Close
End If
'
' close open script file
If nSQLFlag = True Then
Close (nSQlFileHandle)
End If
'
' reset flags & stuff
nSQLFile = ""
nSQLFlag = False
ndbFlag = False
nGlobalErr = False
nLine = 0
'
End Sub
That's the last routine in the SQLVBMod code module. Save this module and save the project before you continue. Before you run the program, you need to go back and remove comment marks from three locations in the SQLVBMain MDI Form. Remove the comment mark from the following routines: mnuFileNew_Click, mnuFileRun_Click, and mnuFileEdit_Click. Also remove the comment mark from the Form_Activate event in SQLVBMain. Then save the project again. Now you are ready to compile and test the SQL-VB project.
All you need to do now is compile the program as an executable and you're done. But first, let's run a test script through the system to make sure all is working properly. You'll run this test by starting SQL-VB from within Visual Basic. If all goes well, you'll create a final compiled version that will run faster.
Begin the testing by selecting Run | Start with Full Compile. This starts the SQL-VB project with a clean compilation. Once SQL-VB is up and running, you load two SQL-VB scripts and give them a test run.
The test script is called SQLVB01.SQV. This script contains a set of lines that open a database and then create several result sets to display. Before you run the first script, you should load it for edit and make sure the drive letter and path are correct for your desktop setup. To load the SQL script file, select File | Edit. This brings up the Open File dialog. Locate the SQLVB01.SQV script file in the TYSDBVB\SQLVB directory on your machine (see Figure A.4).
Loading an SQL-VB script for testing.
Once the file is loaded into Notepad, inspect the script line that opens the database file. Make sure the path and drive letters match your desktop setup (see Figure A.5).
Editing an SQL-VB script in Notepad.
Make any changes needed and exit Notepad. Be sure you save the script if you made any updates. Now you are ready to run the script.
To run the script, select File | Run and use the File Open dialog box to locate the SQLVB01.SQV script file. Once you select the file, the program will automatically begin processing the script. The line at the bottom of the screen shows the script lines as they are processed. The SQLVB01.SQV script opens a database and creates six result set forms before it issues a "Script Completed" message. Your screen should look like the one in Figure A.6.
Result sets created by SQLVB01.SQV script.
If you had problems with the script, review the SQLVB01.SQV file for errors. You may also have to review the Visual Basic code to check for program errors, too. If the script ran without errors, you can check out other aspects of the program, including the Windows menu and re-sizing the grid forms.
Once you are sure that the program is working properly, you should compile it into an .EXE file. To do this, select File | Make EXE File. When prompted for the filename, enter SQLVB if you are running the 32-bit version of Visual Basic. If you are running the 16-bit version of Visual Basic, enter SQLVB16. Once the compiler has completed its work, test the file by running the .EXE version using the same SQL-VB script you used earlier.
You now have a very valuable tool to add to your database programming tool kit. You can use SQL-VB to generate database layouts for all your projects in the future. You can also use SQL-VB to test data integrity options, load test data into existing tables, even create simple data backup and replication scripts.
You could even add more options to the project. Some additional features that you might want to consider are the following:
Make SQL-VB an Add-In application. This involves adding a class module to the project and declaring an SQL-VB class with at least one method that loads and processes a script. Check the Visual Basic documentation on creating OLE-enabled applications for more information on how to create a Visual Basic Add-In.
