![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Appendix AThe SQL-VB5 ProjectThis appendix contains a step-by-step explanation of the creation of the SQL-VB5 Interpreter project. This program is already on the CD-ROM included with your copy of the book. The SQL-VB5 program in this lesson allows you to use an ASCII editor to create SQL scripts that SQL-VB5 can read and process. SQL-VB5 can handle fully commented, multiline SQL scripts. You'll find that SQL-VB5 is a very handy data management tool.
The Benefits of SQL-VB5You 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-VB5 program enables you to do all that. The SQL-VB5 program is able to read SQL scripts you create with the Windows Notepad application (or any other ASCII editor). Listing A.1 is a sample SQL script that can be processed by SQL-VB5. Listing A.1. A sample SQL script.// // load and read data tables // // open a database dbOpen C:\TYSDBVB5\SQLVB5\SCRIPTS\BOOKS5.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 //
The advantage of generating database layouts using SQL-VB5 is that you have some documentation on the database structure that you can refer to in the future. You can also use SQL-VB5 to generate test SELECT queries and other SQL statements before you put them into your Visual Basic programs. Finally, SQL-VB5 is an excellent tool for exploring SQL and your databases. Designing SQL-VB5Before jumping into code mode, let's lay out some general design parameters for the SQL-VB5 project. SQL-VB5 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 add your own script keywords to handle this. To accomplish all this, you need three forms, three standard code modules, and one class 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. The SQLVBGEN module needs routines to read the selected MDB and then write out the SQL-Visual Basic code that represents the tables, fields, indexes, and relationships defined in the MDB. It also has a handful of routines to handle script headers and footers, comment lines, and saving the finished script to the disk. The SQLVBCLASS module has a single Sub Main() used to start the application. The application class module has the properties and methods needed to
allow external VBA programs to access and run SQLVB methods. Other programs can create
their own instance of SQL-VB5 and then run all the primary commands. The
object model for SQL-VB5 is described in Figure A.1 and Table A.1.
You need one other set of tools to meet the design criteria--the ability to edit scripts from within SQL-VB5. Instead of trying to create your own editor, we show you how you can include the Windows Notepad program 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. Creating the SQL-VB5 Forms
The first thing you 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 through the menu. SQLVBMain FormAdd an MDI form to your project by selecting Project | Add MDI Form 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.2 and Figure A.2 as guides as you build the form.
Now that you have created the form, you need to add the menu. Table A.3 shows the hierarchy of the menu items you need for the SQLVBMain form. Table A.3. Menu tree for the SQLVBMain MDI form.
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; you 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 SQLVB5.VBP. Creating the SQLVBChild Child FormThe 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 Project |
Add Form from the Visual Basic main menu. Use Table A.4 and Figure A.3 as guides
for creating SQLVBChild.
You need to add code in four locations within the SQLVBChild form: the Form_Load event, the Form_Activate event, the Form_Unload event, and the Form_Resize event.
Open the Visual Basic code window for the Form_Load event and add the Visual Basic program code in Listing A.2. Listing A.2. Adding code to the Form_Load event.Private Sub Form_Load() ` Me.Data1.Refresh DoEvents ` ` set the captions Me.Caption = Trim(strGlobalSelect) Me.Data1.Caption = Trim(strGlobalSelect) Me.DBGrid1.Caption = Trim(strGlobalSelect) ` End Sub
The Form_Unload event contains a single line of code. This line clears up the main (SQLVBMain) form's menu display. Private Sub Form_Unload(Cancel As Integer) ` SQLVBMain.mnuWindows.Visible = False ` End Sub Next, add code to the Form_Activate event. This updates the MDI form menus and rebinds the data from the data control to the grid display. Private Sub Form_Activate() ` SQLVBMain.mnuWindows.Visible = True DBGrid1.ReBind ` 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 result in Visual Basic errors, so check to make 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 Me.WindowState <> 1 Then With DBGrid1 .Width = Me.ScaleWidth .Left = 1 .Top = 1 .Height = Me.ScaleHeight End With End If ` End Sub
Creating the SQLVBAbout FormThe last form you need for this project is the SQLVBAbout form. This is the form
that lists the name and version of the program and its authors, and so on. Use Table
A.5 and Figure A.4 as guides as you create this form for your project.
This version of the About box offers the user the ability to gather system information. This is done through a set of API calls that read and collate keys from the Registry. You need to add some APIs and code to perform the Registry lookups. First, add the code from Listing A.4 to the general declarations section of the form. Listing A.4. Coding the declarations for the SQLVBAbout form.Option Explicit ` Reg Key Security Options... Const READ_CONTROL = &H20000 Const KEY_QUERY_VALUE = &H1 Const KEY_SET_VALUE = &H2 Const KEY_CREATE_SUB_KEY = &H4 Const KEY_ENUMERATE_SUB_KEYS = &H8 Const KEY_NOTIFY = &H10 Const KEY_CREATE_LINK = &H20 Const KEY_ALL_ACCESS = KEY_QUERY_VALUE + KEY_SET_VALUE + _ KEY_CREATE_SUB_KEY + KEY_ENUMERATE_SUB_KEYS + _ KEY_NOTIFY + KEY_CREATE_LINK + READ_CONTROL ` Reg Key ROOT Types... Const HKEY_LOCAL_MACHINE = &H80000002 Const ERROR_SUCCESS = 0 Const REG_SZ = 1 ` Unicode nul terminated string Const REG_DWORD = 4 ` 32-bit number Const gREGKEYSYSINFOLOC = "SOFTWARE\Microsoft\Shared Tools Location" Const gREGVALSYSINFOLOC = "MSINFO" Const gREGKEYSYSINFO = "SOFTWARE\Microsoft\Shared Tools\MSINFO" Const gREGVALSYSINFO = "PATH" Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, ByRef phkResult As Long) As Long Private Declare Function RegQueryValueEx Lib "advapi32" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, ByRef lpType As Long, ByVal lpData As String, ByRef lpcbData As Long) As Long Private Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long
Next, create a new method called GetKeyValue to use these API calls and constants. Enter the code from Listing A.5 into the project. Listing A.5. Coding the GetKeyValue function.Public Function GetKeyValue(KeyRoot As Long, KeyName As String, SubKeyRef As String, ByRef KeyVal As String) As Boolean Dim i As Long ` Loop Counter Dim rc As Long ` Return Code Dim hKey As Long ` Handle To An Open ÂRegistry Key Dim hDepth As Long ` Dim KeyValType As Long ` Data Type Of A ÂRegistry Key Dim tmpVal As String ` Tempory Storage ÂFor A Registry Key Value Dim KeyValSize As Long ` Size Of Registry ÂKey Variable `------------------------------------------------------------ ` Open RegKey Under KeyRoot {HKEY_LOCAL_MACHINE...} `------------------------------------------------------------ rc = RegOpenKeyEx(KeyRoot, KeyName, 0, KEY_ALL_ACCESS, hKey) ` Open Registry ÂKey If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ` Handle Error... tmpVal = String$(1024, 0) ` Allocate Variable ÂSpace KeyValSize = 1024 ` Mark Variable Size `------------------------------------------------------------ ` Retrieve Registry Key Value... `------------------------------------------------------------ rc = RegQueryValueEx(hKey, SubKeyRef, 0, _ KeyValType, tmpVal, KeyValSize) ` Get/Create Key ÂValue If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ` Handle Errors If (Asc(Mid(tmpVal, KeyValSize, 1)) = 0) Then ` Win95 Adds Null ÂTerminated String... tmpVal = Left(tmpVal, KeyValSize - 1) ` Null Found, Extract From String Else ` WinNT Does NOT ÂNull Terminate String... tmpVal = Left(tmpVal, KeyValSize) ` Null Not Found, ÂExtract String Only End If `------------------------------------------------------------ ` Determine Key Value Type For Conversion... `------------------------------------------------------------ Select Case KeyValType ` Search Data ÂTypes... Case REG_SZ ` String Registry ÂKey Data Type KeyVal = tmpVal ` Copy String Value Case REG_DWORD ` Double Word ÂRegistry Key Data Type For i = Len(tmpVal) To 1 Step -1 ` Convert Each Bit KeyVal = KeyVal + Hex(Asc(Mid(tmpVal, i, 1))) ` Build Value Char. ÂBy Char. Next KeyVal = Format$("&h" + KeyVal) ` Convert Double ÂWord To String End Select GetKeyValue = True ` Return Success rc = RegCloseKey(hKey) ` Close Registry Key Exit Function ` Exit GetKeyError: ` Cleanup After An Error Has Occured... KeyVal = "" ` Set Return Val To ÂEmpty String GetKeyValue = False ` Return Failure rc = RegCloseKey(hKey) ` Close Registry Key End Function
Listing A.6. Coding the StartSysInfo subroutine.Public Sub StartSysInfo() On Error GoTo SysInfoErr Dim rc As Long Dim SysInfoPath As String ` Try To Get System Info Program Path\Name From Registry... If GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFO, gREGVALSYSINFO, SysInfoPath) Then ` Try To Get System Info Program Path Only From Registry... ElseIf GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFOLOC, gREGVALSYSINFOLOC, SysInfoPath) Then ` Validate Existance Of Known 32 Bit File Version If (Dir(SysInfoPath & "\MSINFO32.EXE") <> "") Then SysInfoPath = SysInfoPath & "\MSINFO32.EXE" ` Error - File Can Not Be Found... Else GoTo SysInfoErr End If ` Error - Registry Entry Can Not Be Found... Else GoTo SysInfoErr End If Call Shell(SysInfoPath, vbNormalFocus) Exit Sub SysInfoErr: MsgBox "System Information Is Unavailable At This Time", vbOKOnly End Sub
Listing A.7. Adding code to the cmdSysInfo_Click event.Private Sub cmdSysInfo_Click() Call StartSysInfo End Sub Private Sub cmdOK_Click() Unload Me End Sub Private Sub Form_Load() ` Me.Caption = "About " & App.Title lblVersion.Caption = "Version " & App.Major & "." & App.Minor & "." & ÂApp.Revision lblTitle.Caption = App.Title lblDescription = App.FileDescription Image1.Picture = SQLVBMain.Icon Me.Icon = SQLVBMain.Icon lblDisclaimer = "" ` End Sub
Adding the SQLVBMain CodeNow 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 the Windows Notepad program from within SQLVB. First, add the code from Listing A.8. This arranges the command button bar on the top of the form. Listing A.8. Coding the LoadCmdBtns subroutine.Public Sub LoadCmdBtns() ` Dim Top As Integer Dim Left As Integer Dim Width As Integer Dim Height As Integer ` Top = 45 Height = Picture1.Height * 0.75 Width = Height * 1.1 ` For x = 0 To 5 cmdBtn(x).Top = Top cmdBtn(x).Left = (x * Width) + 45 cmdBtn(x).Width = Width * 0.9 cmdBtn(x).Height = Height cmdBtn(x).BevelWidth = 2 cmdBtn(x).RoundedCorners = False Next ` cmdBtn(0).Picture = LoadPicture(App.Path & "\pics\new.bmp") cmdBtn(1).Picture = LoadPicture(App.Path & "\pics\open.bmp") cmdBtn(2).Picture = LoadPicture(App.Path & "\pics\save.bmp") cmdBtn(3).Picture = LoadPicture(App.Path & "\pics\sum.bmp") cmdBtn(4).Picture = LoadPicture(App.Path & "\pics\camera.bmp") cmdBtn(5).Picture = LoadPicture(App.Path & "\pics\undo.bmp") ` cmdBtn(0).ToolTipText = "New" cmdBtn(1).ToolTipText = "Edit" cmdBtn(2).ToolTipText = "Close" cmdBtn(3).ToolTipText = "Generate" cmdBtn(4).ToolTipText = "Run" cmdBtn(5).ToolTipText = "Exit" ` End Sub
Now add the following code to the Form_Load and Form_Unload events. Private Sub MDIForm_Load() LoadCmdBtns ` set up buttons End Sub Private Sub MDIForm_Resize() LoadCmdBtns lblProgress.Width = Me.ScaleWidth * 0.98 End Sub Next, add the code from Listing A.9 to the cmdBtns_Click event. This handles all the command button selections. Listing A.9. Adding code to the cmdBtn_Click event.Private Sub cmdBtn_Click(Index As Integer) ` Select Case Index Case Is = 0 mnuFileNew_Click Case Is = 1 mnuFileEdit_Click Case Is = 2 mnuFileClose_Click Case Is = 3 mnuFileAuto_Click Case Is = 4 mnuFileRun_Click Case Is = 5 mnuFileExit_Click End Select ` End Sub
Private Sub mnuFileClose_Click() ` On Error Resume Next SQLFileClose InitApp ` End Sub 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 following code: Private Sub mnuHelpAbout_Click() frmAbout.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 mnuWindowsItem_Click(Index As Integer) ` Me.Arrange Index ` End Sub The Arrange method requires a single parameter. This value determines whether the windows are cascaded, tiled, arranged as icons, and so on. Because you built the menu as a control array (with indexes), the Index parameter passed to this menu tells Visual Basic which operation was requested. All you need to do is call the method and pass the parameter. Now add the code behind the File | Exit menu option. This code 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 Visual Basic to treat this line as a comment, not as executable code. You have it "commented out" right now because you haven't created the SQLMain routine yet. You do that in the next section when you create the SQL-VB5 Main code module. Private Sub mnuFileRun_Click() SQLMain ` call main job w/o parm End Sub Now add the following code to the File | Generate menu item. This calls the routines that generate a new script from an existing MDB. Private Sub mnuFileAuto_Click() AutoGen ` call routine to read MDB and create SQV 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 that you build in the SQL-VB5 Main module, so you have commented out the calls for now to prevent Visual Basic from reporting an error at compile time. Private Sub mnuFileEdit_Click() LoadNotePadFile "Edit Existing SQLVB File" End Sub Private Sub mnuFileNew_Click() LoadNotePadFile "Create New SQLVB File" End Sub 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. Creating the SQL-VB5 Main ModuleThe 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 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 10 routines to define before you complete the project. Declaring Global VariablesFirst, you need to declare a set of variables to be used throughout the entire 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 Project | Add Module from the Visual Basic main menu. Set its Name property to SQLVBMOD and enter the lines in Listing A.10 into the declarations section. The meaning and use of these variables becomes clearer as you build the various routines within the module. Listing A.10. Adding the global variables.` ` general declarations ` Global strSQLFile As String Global intGlobalErr As Integer Global intSQLFlag As Integer Global intDBFlag As Integer Global intSQLFileHandle As Integer Global strSQLLine As String Global intLine As Integer Global strLine As String Global strGlobalSelect As String Global strGlobalDBName As String Global db As Database Global ws As Workspace Global intForms As Integer Global TblForms() As Form Global strConnect As String Global strVersion As String Global blnSQLQuiet As Boolean Creating SQLMainThe top-most routine in this module is the SQLMain routine. This routine has only three tasks: open the script file, process the script commands, and close the script file. Let's write a module that does all that. To add a new procedure to the module, select Tools | Add 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.11. Listing A.11. Coding the SQLMain routine.Sub SQLMain(Optional cRunFile As Variant) ` ` main loop for interpreting SQL ASCII file ` If IsMissing(cRunFile) = True Then cRunFile = "" End If ` InitApp ` clean up environment SQLFileOpen CStr(cRunFile) ` open the script If intGlobalErr = False Then SQLFileProcess ` process the script End If SQLFileClose ` close the script ` cRunFile = "" ` clear passed parm `If intGlobalErr = False Then ` MsgBox "Script Completed", vbInformation `End If End Sub
Creating SQLFileOpenLet'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 Tools | Add Procedure from the Visual Basic main menu and set the name to SQLFileOpen(cSQLFile). Make this a Public Sub procedure. Now enter the code in Listing A.12 in the procedure window. Listing A.12. Coding the SQLFileOpen routine.Sub SQLFileOpen(strSQLFile As String) ` ` open the SQV script file ` On Error GoTo SQLFileOpenErr ` If Len(Trim(strSQLFile)) = 0 Then SQLVBMain.CommonDialog1.DialogTitle = "Load SQLVB File" SQLVBMain.CommonDialog1.DefaultExt = "SQV" SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV" SQLVBMain.CommonDialog1.ShowOpen strSQLFile = SQLVBMain.CommonDialog1.filename End If ` If Len(Trim(strSQLFile)) = 0 Then intGlobalErr = True intSQLFlag = False GoTo SQLFileOpenExit End If ` intSQLFileHandle = FreeFile(0) Open strSQLFile For Input As intSQLFileHandle intGlobalErr = False intSQLFlag = True GoTo SQLFileOpenExit ` SQLFileOpenErr: If Err <> 32755 Then ErrMsg Err, Error$, intLine, strSQLFile, "SQLFileOpen" End If InitApp intGlobalErr = True ` SQLFileOpenExit: ` End Sub Creating SQLFileCloseLet'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 the code in Listing A.13. Listing A.13. Coding the SQLFileClose routine.Sub SQLFileClose() ` ` close the SQV text file ` On Error GoTo SQLFileCloseErr ` If intGlobalErr = False Then If intSQLFileHandle <> 0 Then Close (intSQLFileHandle) End If intSQLFlag = False End If GoTo SQLFileCloseExit ` SQLFileCloseErr: ErrMsg Err, Error$, intLine, strLine, "SQLFileClose" InitApp ` SQLFileCloseExit: ` End Sub Creating SQLFileProcessNow 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, remember that the script file has regular SQL statements, special database CONNECT, VERSION, QUIET, 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.14. Don't be discouraged by the length of this piece of code--it won't take you long to enter it into the project. Listing A.14. Coding the SQLFileProcess routine.Sub SQLFileProcess() ` ` main loop for processing ASCII file lines ` On Error GoTo SQLFileProcessErr ` Dim cToken As String ` If intSQLFlag = False Then GoTo SQLFileProcessExit End If ` strSQLLine = "" While Not EOF(intSQLFileHandle) If intGlobalErr = True Then GoTo SQLFileProcessExit End If ` Line Input #intSQLFileHandle, strLine intLine = intLine + 1 strLine = Trim(strLine) + " " If Len(strLine) <> 0 Then cToken = GetToken(strLine) If Right(cToken, 1) = ";" Then cToken = Left(cToken, Len(cToken) - 1) End If ` SQLVBMain.lblProgress.Caption = strLine DoEvents Select Case UCase(cToken) Case Is = "//" ` no action - comment line Case Is = "DBCONNECT" SQLdbConnect Case Is = "DBVERSION" SQLdbVersion Case Is = "DBOPEN" SQLdbOpen Case Is = "DBMAKE" SQLdbMake Case Is = "DBCLOSE" SQLdbClose Case Is = "DBQUIET" SQLQuietFlag Case Else strSQLLine = strSQLLine + strLine If Right(strLine, 2) = "; " Then SQLDoCommand strSQLLine = "" End If End Select End If Wend GoTo SQLFileProcessExit ` SQLFileProcessErr: ErrMsg Err, Error$, intLine, strLine, "SQLFileProcess" InitApp ` SQLFileProcessExit: ` End Sub
The SQL-VB5 program processes each line of script word by word. The first word in each command line is used to determine how SQL-VB5 processes the line. The GetToken function returns the first word in the line (you 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 allows 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 SQL-VB5 can add
it to the cSQLLine variable for eventual processing. However, there are
several words that require special handling. These words are listed in Table A.6
along with comments about how they are handled.
If the word found at the start of the line is not one of those listed in Table A.6, 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 whether 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 add those final routines in the next section. Creating the Support RoutinesNow 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 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 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 the code in Listing A.15 in the code window. Listing A.15. Coding the GetToken routine.Function GetToken(cString As String) As String ` ` get a token from the input line ` Dim intTemp As Integer ` intTemp = InStr(cString, " ") If intTemp > 0 Then GetToken = Left(cString, intTemp - 1) Else GetToken = "" End If ` End Function
Now let's add the three "setup" values you can use in your scripts: DBCONNECT, DBVERSION and DBQUIET. These three keywords do not actually execute any real actions, but they do set values used by the other action words DBOPEN and DBMAKE. First, add the code for the DBCONNECT keyword from Listing A.16. This code just accepts the Connect string from the script and saves it to an internal variable. Listing A.16. Coding the SQLDBConnect method.Public Sub SQLdbConnect() ` ` set global connect property ` strConnect = strSQLLine ` End Sub
Listing A.17. Coding the SQLdbVersion subroutine.Public Sub SQLdbVersion() ` ` set global version value ` Dim strTemp As String ` strTemp = GetToken(strSQLLine) ` Select Case UCase(strTemp) Case "1.0" strVersion = dbVersion10 Case "1.1" strVersion = dbVersion11 Case "2.0" strVersion = dbVersion20 Case "3.0" strVersion = dbVersion30 End Select ` End Sub
Listing A.18. Coding the SQLQuietFlag subroutine.Public Sub SQLQuietFlag() ` Dim strTemp As String ` strTemp = GetToken(strSQLLine) ` If UCase(strTemp) = "YES" Then blnSQLQuiet = True Else blnSQLQuiet = False End If ` End Sub
Listing A.19. Coding the SQLdbOpen routine.Sub SQLdbOpen() ` ` open an existing database ` On Error GoTo SQldbOpenErr ` Dim strOpen As String Dim intTemp As Integer ` strLine = Trim(strLine) ` drop any spaces intTemp = InStr(strLine, " ") ` locate first embedded space strOpen = Mid(strLine, intTemp + 1, 255) ` get rest of line ` ` if line ends w/ ";", dump it! If Right(strOpen, 1) = ";" Then strOpen = Left(strOpen, Len(strOpen) - 1) End If ` ` now try to open database Set ws = DBEngine.CreateWorkspace("wsSQLVB", "admin", "") Set db = ws.OpenDatabase(strOpen, False, False, strConnect) strGlobalDBName = strOpen intDBFlag = True GoTo SQldbOpenExit ` SQldbOpenErr: ErrMsg Err, Error$, intLine, strLine, "SQldbOpen" InitApp ` SQldbOpenExit: ` End Sub
The next routine to add handles the DBCLOSE command. This is a simple routine. Its only job is to close the Microsoft Access Jet database. This routine also closes any open child forms and clears flag variables. Create a Public Sub called SQLdbClose and add the code in Listing A.20. Listing A.20. Coding the SQLdbClose routine.Sub SQLdbClose() ` ` close open database ` On Error Resume Next ` ignore errors here ` db.Close ` For x = 0 To intForms Unload TblForms(x) Next x ` intForms = 0 intDBFlag = False ` End Sub
Listing A.21. Coding the SQLdbMake routine.Sub SQLdbMake() ` ` make a new database ` On Error GoTo SQLdbMakeErr ` Dim strMake As String Dim intTemp As Integer ` strLine = Trim(strLine) ` drop any spaces intTemp = InStr(strLine, " ") ` locate first embedded space strMake = Mid(strLine, intTemp + 1, 255) ` get rest of line ` ` if line ends w/ ";", dump it! If Right(strMake, 1) = ";" Then strMake = Left(strMake, Len(strMake) - 1) End If ` ` try to open it (to see if it already exists) nSQLMakeHandle = FreeFile(0) Open strMake For Input As nSQLMakeHandle Close nSQLMakeHandle ` nResult = MsgBox("ERASE [" + strMake + "]", vbYesNo + vbQuestion, "Database ÂAlready Exists!") If nResult = vbYes Then Kill strMake Else ErrMsg 0, "Script Cancel - database already Exists", intLine, strLine, Â"SQLdbMake" InitApp End If ` ` now try to make a new database SQLdbMake2: ` create a new db, close it, then open for use Set ws = DBEngine.CreateWorkspace("wsSQLVB2", "admin", "") Set db = ws.CreateDatabase(strMake, dbLangGeneral, strVersion) db.Close Set db = ws.OpenDatabase(strMake, False, False, strConnect) strGlobalDBName = strMake intDBFlag = True GoTo SQLdbMakeExit ` SQLdbMakeErr: If Err = 53 Then Resume SQLdbMake2 Else ErrMsg Err, Error$, intLine, strLine, "SQLdbMake" InitApp End If ` SQLdbMakeExit: ` End Sub
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 it checks is whether the error was caused by an attempt to open a nonexistent file. If so, the error handler sends the routine to the file creation point without comment. If the error has another cause, 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 (strVersion) can be set by the user with the DBVERSION keyword. 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.22. Listing A.22. Coding the SQLDoCommand routine.Sub SQLFileProcess() ` ` main loop for processing ASCII file lines ` On Error GoTo SQLFileProcessErr ` Dim cToken As String ` If intSQLFlag = False Then GoTo SQLFileProcessExit End If ` strSQLLine = "" While Not EOF(intSQLFileHandle) If intGlobalErr = True Then GoTo SQLFileProcessExit End If ` Line Input #intSQLFileHandle, strLine intLine = intLine + 1 strLine = Trim(strLine) + " " If Len(strLine) <> 0 Then cToken = GetToken(strLine) If Right(cToken, 1) = ";" Then cToken = Left(cToken, Len(cToken) - 1) End If ` SQLVBMain.lblProgress.Caption = strLine DoEvents Select Case UCase(cToken) Case Is = "//" ` no action - comment line Case Is = "DBCONNECT" SQLdbConnect Case Is = "DBVERSION" SQLdbVersion Case Is = "DBOPEN" SQLdbOpen Case Is = "DBMAKE" SQLdbMake Case Is = "DBCLOSE" SQLdbClose Case Is = "DBQUIET" SQLQuietFlag Case Else strSQLLine = strSQLLine + strLine If Right(strLine, 2) = "; " Then SQLDoCommand strSQLLine = "" End If End Select End If Wend GoTo SQLFileProcessExit ` SQLFileProcessErr: ErrMsg Err, Error$, intLine, strLine, "SQLFileProcess" InitApp ` SQLFileProcessExit: ` End Sub Sub SQLDoCommand() ` ` handle SQL Command ` On Error GoTo SQLDoCommandErr ` set error trap ` Dim cTemp As String ` holds token ` ` skip errors if you're deleting objects cTemp = GetToken(Trim(strSQLLine)) ` 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 UCase(cTemp) Case Is = "TRANSFORM" ShowTable strSQLLine, strGlobalDBName ` show view form Case Is = "SELECT" If InStr(UCase(strSQLLine), " INTO ") <> 0 Then ws.BeginTrans db.Execute strSQLLine, dbSeeChanges + dbFailOnError ` execute make-Âtable SQL ws.CommitTrans db.Close Set db = Nothing Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect) Else db.Close Set db = Nothing ShowTable strSQLLine, strGlobalDBName ` show view form Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect) End If Case Else ws.BeginTrans db.Execute strSQLLine, dbSeeChanges + dbFailOnError ` execute SQL ws.CommitTrans db.Close Set db = Nothing Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect) End Select GoTo SQlDoCommandExit ` exit routine ` ` local error handler SQLDoCommandErr: ErrMsg Err, Error$, intLine, strLine, "SQLDoCommand" On Error Resume Next ws.Rollback InitApp ` ` routine exit SQlDoCommandExit: ` End Sub
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-VB5 ignores these errors. Once you write a few SQL-VB5 scripts, you'll appreciate this feature. Next, you have to check for the SQL keywords that can 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 creates a new table. Using the INTO keyword means that the SELECT statement does 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.23. Listing A.23. Coding the ShowTable routine.Sub ShowTable(cSQL As String, strDB As String) ` ` show a selected table ` strGlobalSelect = strSQLLine strGlobalDBName = strDB ` intForms = intForms + 1 ReDim Preserve TblForms(intForms) As Form Set TblForms(intForms) = New SQLVBChild Load TblForms(intForms) TblForms(intForms).Caption = CStr(intForms) TblForms(intForms).Data1.DatabaseName = strGlobalDBName TblForms(intForms).Data1.RecordSource = strGlobalSelect TblForms(intForms).Data1.Refresh TblForms(intForms).Show TblForms(intForms).WindowState = vbMinimized TblForms(intForms).WindowState = vbNormal ` End Sub
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.24. Listing A.24. Coding the LoadNotePadFile routine.Sub LoadNotePadFile(cLoadMsg As String, Optional strScript As String) ` ` load notepad as an editor ` On Error GoTo LoadNotePadFileErr: ` Dim cEditFile As String Dim nAppID As Long ` If IsMissing(strScript) = False And strScript <> "" Then cEditFile = strScript Else SQLVBMain.CommonDialog1.DialogTitle = cLoadMsg SQLVBMain.CommonDialog1.DefaultExt = "SQV" SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV" SQLVBMain.CommonDialog1.ShowOpen cEditFile = SQLVBMain.CommonDialog1.filename End If ` 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
The next routine you need to add to SQLVBMOD is the global error handler. This routine (shown in Listing A.25) simply displays the error messages and waits for the user to click the OK button before it returns to the calling routine. Create a Public Sub called ErrMsg and enter the code in Listing A.25. Listing A.25. Coding the ErrMsg routine.Sub ErrMsg(nErr As Integer, cError As String, intLine As Integer, strLine As ÂString, cModule As String) ` ` report an error to user ` 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(intLine) + Chr(13) cMsg = cMsg + "Text: " + Chr(9) + strLine ` If blnSQLQuiet = False Then MsgBox cMsg, vbCritical, cModule End If ` End Sub
The routine in Listing A.26 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 code in Listing A.26. Listing A.26. Coding the InitApp routine.Sub InitApp() ` ` set up app values ` On Error Resume Next ` ignore any errors here ` ` close all child forms For x = 0 To intForms Unload TblForms(x) Set TblForms(x) = Nothing Next x ` ` close open database If intDBFlag = True Then db.Close Set db = Nothing Set ws = Nothing End If ` ` close open script file If intSQLFlag = True Then Close (intSQLFileHandle) End If ` ` reset flags & stuff nSQLFile = "" intSQLFlag = False intDBFlag = False nGlobalErr = False blnSQLQuiet = False intLine = 0 strConnect = "" ` "Access;" strVersion = dbVersion30 ` End Sub Adding the Sub Main RoutineThere is one final routine you need to add to the project to make SQL-VB5 start. It's the sub Main method. This starts the entire application, instead of the SQLVBMain form. Add another code module to your project and set its Name property to SQLVBClass. Now add the following code to the module: Public Sub Main() ` Dim strCmd As String strCmd = Command$ ` ` ignore system commands If Left(strCmd, 1) = "-" Then Exit Sub End If ` ` see if user passed a file If Trim(strCmd) <> "" Then SQLMain strCmd Else SQLVBMain.Show End If ` End Sub This routine allows you to send a script file on the command line when you start the program. First, SQL-VB5 checks to see if the first character of the command is a hyphen (-). This designates a system command and is ignored by SQL-VB5. For example, when you start SQL-VB5 from the OLE request, the -Embedded command is passed to SQL-VB5 by the operating system. This lets you write special code that executes the first time someone attempts to reference the application. Next, if there is a valid command on the line, it is passed to the script processor for immediate attention. Otherwise, the MDI form is shown to the user. Now you need to change the Startup property of the project. Select Project | Properties and set the Startup Form to Sub Main. Save the project before continuing.
That's the last routine in the SQLVBMOD code module. Save this module and save the project before you continue. In the next section, you add the code that reads an existing MDB and generates a valid SQL-Visual Basic script. Creating the SQLVBGEN ModuleBecause you have the entire Microsoft DAO at your disposal, it is possible to read an existing MDB and generate a valid SQL-Visual Basic script that can be used to re-create the database structure. This can be done by walking through all the collections of table, index, relationship, and field objects. In this section you create the code that does just that. First, add a new BAS module to your project. Set its Name property to SQLVBGEN. Then add the code from Listing A.27 to the general declarations section of the module. Listing A.27. Coding the general declaration section of SQLVBGEN.Option Explicit Dim cDBName As String Dim cWrLine As String Dim cWrFile As String Dim objDB As Database Dim nWrFile As Integer Dim EOL As String Dim INDENT As Integer
Coding the AutoGen MethodThe top-level routine (called AutoGen) is used to create the entire script. It accepts two parameters: the MDB name and the SQL script name. Add the code in Listing A.28 to your module. Listing A.28. Coding the AutoGen subroutine.Public Sub AutoGen(Optional cDB As Variant, Optional cSQV As Variant) ` ` ==================================================== ` inputs: ` cDB database name to read ` cSQV script name to write ` ` outputs: ` produces script file ` ` processing: ` Ask for database to load and file to save results. ` Enum all tables, fields, indexes and relations ` ==================================================== ` ` some local vars EOL = Chr(13) + Chr(10) INDENT = 3 intGlobalErr = False ` ` handle missing parms If IsMissing(cDB) = True Then cDB = "" End If If IsMissing(cSQV) = True Then cSQV = "" End If ` ` we can run silent If cDB <> "" And cSQV <> "" Then SQLVBMain.Visible = False End If ` AutoGenDBOpen cDB ` get mdb file ` If intGlobalErr = False Then AutoGenSQVOpen cSQV ` get sqv file End If ` If intGlobalErr = False Then CreateScript ` create script End If ` ` let'em know it's done If intGlobalErr = False Then If SQLVBMain.Visible = True Then MsgBox cWrFile + " Script Created", vbInformation End If End If ` ` clean up Close (nWrFile) nWrFile = 0 cWrFile = "" cDBName = "" cDB = "" cSQV = "" intGlobalErr = False ` reset ` End Sub
The AutoGenDBOpen MethodNext, add the AutoGenDBOpen routine from Listing A.29. This opens the MDB. Listing A.29. Coding the AutoGenDBOpen subroutine.Public Sub AutoGenDBOpen(Optional cDB As Variant) On Error GoTo AutoGenDBOpenErr ` If IsMissing(cDB) = True Or Len(cDB) = 0 Then SQLVBMain.CommonDialog1.DialogTitle = "Load MDB File" SQLVBMain.CommonDialog1.DefaultExt = "MDB" SQLVBMain.CommonDialog1.Filter = "MS Jet Database File|*.MDB" SQLVBMain.CommonDialog1.Flags = cdlOFNFileMustExist SQLVBMain.CommonDialog1.CancelError = True SQLVBMain.CommonDialog1.ShowOpen cDBName = SQLVBMain.CommonDialog1.filename Else cDBName = cDB End If ` If Len(cDBName) = 0 Then intGlobalErr = True Else cDBName = Trim(cDBName) Set objDB = OpenDatabase(cDBName) End If GoTo AutoGenDBOpenExit ` AutoGenDBOpenErr: If Err <> 32755 Then ErrMsg Err, Error$, intLine, strLine, "AutoGenDBOpen" End If intGlobalErr = True ` AutoGenDBOpenExit: End Sub The AutoGenSQVOpen methodNow add the AutoGenSQVOpen subroutine from Listing A.30. Notice that, if no name is supplied for the script, SQL-VB5 invents one based on the MDB name. Listing A.30. Coding the AutoGenSQVOpen subroutine.Public Sub AutoGenSQVOpen(Optional cSQV As Variant) On Error GoTo AutoGenSQVOpenErr ` Dim intTemp As Integer ` If IsMissing(cSQV) = True Or Len(cSQV) = 0 Then cWrFile = cDBName Else cWrFile = cSQV End If ` intTemp = InStr(cWrFile, ".") If intTemp > 0 Then cWrFile = Mid(cWrFile, 1, intTemp - 1) End If cWrFile = cWrFile + ".sqv" ` If IsMissing(cSQV) = True Or Len(cSQV) = 0 Then SQLVBMain.CommonDialog1.DialogTitle = "Create SQV File" SQLVBMain.CommonDialog1.DefaultExt = "SQV" SQLVBMain.CommonDialog1.Filter = "SQV Script File|*.SQV" SQLVBMain.CommonDialog1.filename = cWrFile SQLVBMain.CommonDialog1.Flags = cdlOFNCreatePrompt Or ÂcdlOFNOverwritePrompt SQLVBMain.CommonDialog1.CancelError = True SQLVBMain.CommonDialog1.ShowOpen cWrFile = SQLVBMain.CommonDialog1.filename End If ` If Len(cWrFile) = 0 Then intGlobalErr = True Else nWrFile = FreeFile Open cWrFile For Output As nWrFile End If GoTo AutoGenSQVOpenExit ` AutoGenSQVOpenErr: If Err <> 32755 Then ErrMsg Err, Error$, intLine, strLine, "AutoGenDBOpen" End If intGlobalErr = True ` AutoGenSQVOpenExit: ` End Sub The CreateScript methodNow you're ready for the fun stuff. The next method is the one that actually creates the script. Add the code from Listing A.31 to the module. Listing A.31. Coding the CreateScript subroutine.Public Sub CreateScript() ` ` walk through database objects and produce text file ` On Error Resume Next ` Dim objTableDef As TableDef Dim objIndex As Index Dim objRelation As Relation ` ` add script header cWrLine = WrScriptHeader(cDBName) Print #nWrFile, cWrLine ` ` add database create code cWrLine = WrCreateDB(cDBName) Print #nWrFile, cWrLine ` ` add table create code For Each objTableDef In objDB.TableDefs cWrLine = WrCreateTable(objTableDef) If Len(cWrLine) > 0 Then Print #nWrFile, cWrLine End If ` For Each objIndex In objTableDef.Indexes cWrLine = WrCreateIndex(objIndex, objTableDef.Name) If Len(cWrLine) > 0 Then Print #nWrFile, cWrLine End If Next Next ` ` add relation create code For Each objRelation In objDB.Relations cWrLine = wrCreateRelation(objRelation) If Len(cWrLine) > 0 Then Print #nWrFile, cWrLine End If Next ` ` add script footer cWrLine = WrScriptFooter Print #nWrFile, cWrLine ` SQLVBMain.lblProgress = "" End Sub
The WrScriptHeader and WrScriptFooter MethodsYou need to add some code comments at the start and end of all your scripts. Add the WrScriptHeader and WrScriptFooter routines from Listing A.32. Listing A.32. Coding the WrScriptHeader function.Public Function WrScriptHeader(cMDB) As String ` ` write header for text file ` Dim cTemp As String ` cTemp = "// " + String(60, "*") + EOL cTemp = cTemp + "// DATABASE CREATE SCRIPT FOR [" + cMDB + "]" + EOL cTemp = cTemp + "// " + String(60, "*") + EOL cTemp = cTemp + "// SQLGEN Version 5.0 - 03/97(MCA)" + EOL cTemp = cTemp + "// " + EOL cTemp = cTemp + "// For use with SQL-VB Interpreter" + EOL cTemp = cTemp + "//" + EOL cTemp = cTemp + "// CREATED: " + Format(Date, "General Date") + EOL cTemp = cTemp + "//" + EOL cTemp = cTemp + "// " + String(60, "*") + EOL ` WrScriptHeader = cTemp ` End Function Public Function WrScriptFooter() ` ` create end of script footer ` Dim cTemp As String ` cTemp = "// " + String(60, "*") + EOL cTemp = cTemp + "// " + "END OF FILE" + EOL cTemp = cTemp + "// " + String(60, "*") + EOL ` WrScriptFooter = cTemp ` End Function The WrCreateDB MethodNext, add the wrCreateDB method from Listing A.33. Listing A.33. Coding the WrCreateDB function.Public Function WrCreateDB(cMDB) As String ` ` write the create db line ` Dim cTemp As String ` SQLVBMain.lblProgress = "Creating Script Header..." DoEvents ` cTemp = EOL cTemp = cTemp + "// Create new database" + EOL cTemp = cTemp + "dbMake " + cDBName + ";" cTemp = cTemp + EOL ` WrCreateDB = cTemp ` End Function The WrCreateTable methodThe next step is to create each table definition. This is actually a two-step process. First, you need to create the table and its fields. Then you need to create the indexes that belong to the table. The code in Listing A.34 creates the table itself and calls the function to create the field definitions. Listing A.34. Coding the WrCreateTable function.Public Function WrCreateTable(tblObject As TableDef) As String ` ` create a write table SQL statement ` Dim cTemp As String Dim cTable As String Dim objField As Field ` ` ignore system tables cTable = tblObject.Name If UCase(Left(cTable, 4)) = "MSYS" Then WrCreateTable = "" Exit Function End If ` ` ignore non-native MDB tables If tblObject.Attributes <> 0 Then WrCreateTable = "" Exit Function End If ` SQLVBMain.lblProgress = "Creating Tables..." DoEvents ` ` if it contains spaces, enclose in braces If InStr(cTable, " ") <> 0 Then cTable = "[" + cTable + "]" End If ` ` start SQL line cTemp = "// Create " + cTable + EOL cTemp = cTemp + "CREATE TABLE " + cTable + EOL cTemp = cTemp + Space(INDENT) + "(" + EOL ` ` add each field For Each objField In tblObject.Fields cTemp = cTemp + Space(INDENT) + WrCreateField(objField) cTemp = cTemp + "," + EOL Next ` ` fix up end of line If Right(cTemp, Len(EOL) + 1) = "," + EOL Then cTemp = Left(cTemp, Len(cTemp) - (Len(EOL) + 1)) ` strip last EOL and Âcomma End If cTemp = cTemp + EOL cTemp = cTemp + Space(INDENT) + ");" + EOL ` add final paren and semi-colon ` WrCreateTable = cTemp ` return result to caller End Function
The WrCreateField and FieldTypeName MethodsNow build the WrCreateField routine from Listing A.35. Listing A.35. Coding the WrCreateField function.Public Function WrCreateField(fldObject As Field) As String ` ` create a field line ` Dim cTemp As String ` ` get field from collection cTemp = fldObject.Name ` ` if it has a space, enclose in braces If InStr(cTemp, " ") <> 0 Then cTemp = "[" + cTemp + "]" End If ` ` add a spacer cTemp = cTemp + " " ` ` add the field type cTemp = cTemp + FieldTypeName(fldObject) ` ` if it's a text field, add the length If fldObject.Type = dbText Then cTemp = cTemp + "(" + CStr(fldObject.Size) + ")" End If ` ` return results WrCreateField = cTemp ` End Function
Listing A.36. Coding the FieldTypeName function.Public Function FieldTypeName(fldObject As Field) As String ` ` get field type value ` return field string name ` Select Case fldObject.Type Case Is = dbDate FieldTypeName = "DATE" Case Is = dbText FieldTypeName = "TEXT" Case Is = dbMemo FieldTypeName = "MEMO" Case Is = dbBoolean FieldTypeName = "BOOLEAN" Case Is = dbInteger FieldTypeName = "INTEGER" Case Is = dbLong FieldTypeName = "LONG" Case Is = dbCurrency FieldTypeName = "CURRENCY" Case Is = dbSingle FieldTypeName = "SINGLE" Case Is = dbDouble FieldTypeName = "DOUBLE" Case Is = dbByte FieldTypeName = "BYTE" Case Is = dbLongBinary FieldTypeName = "LONGBINARY" Case Else FieldTypeName = "UNKNOWN" End Select ` If fldObject.Attributes And dbAutoIncrField Then FieldTypeName = "COUNTER" End If End Function The WrCreateIndex methodNow that all the field work is done, it's time to build the indexes. Listing A.37 shows the code for the WrCreateIndex method. Listing A.37. Coding the WrCreateIndex function.Public Function WrCreateIndex(idxObject As Index, cTable As String) As String ` ` create index code line ` Dim cTemp As String Dim cIndex As String Dim fldObject As Field ` ` ignore system table indexes If UCase(Left(cTable, 4)) = "MSYS" Then WrCreateIndex = "" Exit Function End If ` If InStr(cTable, " ") <> 0 And Left(cTable, 1) <> "[" Then cTable = "[" + cTable + "]" End If ` ` ignore indexes for foreign keys If idxObject.Foreign = True Then WrCreateIndex = "" Exit Function End If ` ` start SQL line cTemp = "// Index " + idxObject.Name + EOL If idxObject.Unique = True Then cTemp = cTemp + "CREATE UNIQUE INDEX " Else cTemp = cTemp + "CREATE INDEX " End If ` ` if name has spaces, add braces cIndex = idxObject.Name If InStr(cIndex, " ") <> 0 Then cIndex = "[" + cIndex + "]" End If cTemp = cTemp + cIndex ` ` prepare to add fields cTemp = cTemp + " ON " + cTable + EOL cTemp = cTemp + Space(INDENT) + "(" ` ` get each field in index For Each fldObject In idxObject.Fields cTemp = cTemp + fldObject.Name If fldObject.Attributes = dbDescending Then cTemp = cTemp + " DESC" Else cTemp = cTemp + " ASC" End If cTemp = cTemp + "," Next ` ` fix up end of field list If Right(cTemp, 1) = "," Then cTemp = Left(cTemp, Len(cTemp) - 1) End If cTemp = cTemp + ")" ` ` handle index attributes If idxObject.Primary = True Then cTemp = cTemp + " WITH PRIMARY" ` primary key End If ` If idxObject.IgnoreNulls = True Then cTemp = cTemp + " WITH IGNORE NULL" ` ignore nulls End If If idxObject.Required = True And idxObject.Primary = False Then cTemp = cTemp + " WITH DISALLOW NULL" ` disallow nulls End If ` ` last bit here! cTemp = cTemp + ";" + EOL ` WrCreateIndex = cTemp ` End Function
The wrCreateRelation MethodNow it's time to add the wrCreateRelation routine. This builds any relationship definitions into the script. Add the code from Listing A.38 to your module. Listing A.38. Coding the wrCreateRelation function.Public Function wrCreateRelation(relObject As Relation) As String ` ` create relation/constraint code ` Dim cTemp As String Dim fldObject As Field Dim cTable As String Dim cForgTable As String Dim cFields() As String Dim x As Integer Dim z As Integer ` cTable = relObject.Table cForgTable = relObject.ForeignTable ` ` ignore system tables If UCase(Left(cTable, 4)) = "MSYS" Then wrCreateRelation = "" Exit Function End If If UCase(Left(cForgTable, 4)) = "MSYS" Then wrCreateRelation = "" Exit Function End If ` SQLVBMain.lblProgress = "Creating Relations..." DoEvents ` ` if it has spaces, add braces If InStr(cTable, " ") <> 0 Then cTable = "[" + cTable + "]" End If If InStr(cForgTable, " ") <> 0 Then cForgTable = "[" + cForgTable + "]" End If ` ` create array of field name/foriegn names x = 0 For Each fldObject In relObject.Fields ReDim Preserve cFields(x + 1, 2) As String cFields(x, 1) = fldObject.Name cFields(x, 2) = fldObject.ForeignName x = x + 1 Next ` cTemp = "// create relation " + relObject.Name + EOL cTemp = cTemp + "ALTER TABLE " + cForgTable cTemp = cTemp + " ADD CONSTRAINT " + relObject.Name + EOL ` ` write out local table fields cTemp = cTemp + Space(INDENT) + "FOREIGN KEY (" For z = 0 To x - 1 cTemp = cTemp + cFields(z, 2) + "," Next cTemp = Left(cTemp, Len(cTemp) - 1) + ") " ` ` write out foreign table/fields cTemp = cTemp + "REFERENCES " + cTable + "(" For z = 0 To x - 1 cTemp = cTemp + cFields(z, 1) + "," Next cTemp = Left(cTemp, Len(cTemp) - 1) + ");" + EOL ` wrCreateRelation = cTemp ` End Function
That's the end of the generate portion of the script. Next you add the object interface for use as an OLE Server. Compiling and Testing SQL-VB5All 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 run this test by starting SQL-VB5 from within Visual Basic. If all goes well, you create a final compiled version that runs faster. Go ahead and run the application. The first test script is called SQLVB01.SQV.
You can find it in the ChapXA\Scripts folder on the CD. 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 editing 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.5). Make any changes needed and exit Notepad. Make 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 automatically
begins 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. Figure A.7 shows these six forms after they have been
rearranged on the screen. Figure
A.7. Viewing the results of SQLVB01.SQV.
When you are sure that the program is working properly, you can continue with the next section of the chapter. Creating the SQL-VB5 Object Model InterfaceNow you're ready to add an object model wrapper to the SQL-VB5 Interpreter. All you need to do is add a class module to the project, define some methods and properties, and you're all set. First, add a class module to the project (Project | Add Class Module) and set its Name to Application. Now you're ready to add properties and methods to the class. Adding Properties to the Object ModelYou need two properties: DBName and Script. Listing A.40 shows the Property Let and Property Get code for both items. Be sure to add the Private variables in the general declaration section, too. Listing A.40. Adding properties to the Application class.Option Explicit Private strScript As String Private strDBName As String Public Property Get Script() As Variant Script = strScript End Property Public Property Let Script(ByVal vNewValue As Variant) strScript = vNewValue End Property Public Property Get DBName() As Variant DBName = strDBName End Property Public Property Let DBName(ByVal vNewValue As Variant) strDBName = vNewValue End Property Adding Methods to the Object ModelNow that you have some properties built, you're ready to add some methods to the model. You can execute these methods from within other Visual Basic-compliant applications. First add the Start and CloseApp methods. You can use these to start an instance of SQL-VB5 and close it when you're done (see Listing A.41). Listing A.41. Coding the Start and CloseApp subroutines.Public Sub Start() ` ` starts interactive mode ` SQLVBMain.Show ` End Sub Public Sub CloseApp() ` ` end instance of application ` Dim frmTemp As Form ` For Each frmTemp In Forms Unload frmTemp Next End ` End Sub
Public Sub Run() ` ` calls Run routine ` If Trim(strScript) <> "" Then SQLMain strScript Else SQLMain End If ` End Sub The Generate method can read the MDB in the DBName property and create a script with the name in the Script property. Public Sub Generate() ` ` calls autogen routine ` If Trim(strDBName) <> "" And Trim(strScript) <> "" Then AutoGen strDBName, strScript Else AutoGen End If ` End Sub You can also add the Edit and Create methods to allow external programs to start the SQL-VB5 editor to edit or build new scripts. Public Sub Edit() ` ` call edit routine ` If Trim(strScript) <> "" Then LoadNotePadFile "", strScript Else LoadNotePadFile "Select Script to Edit" End If ` End Sub Public Sub Create() ` ` calls new file routine ` If Trim(strScript) <> "" Then LoadNotePadFile "", strScript Else LoadNotePadFile "Enter Name of New Script" End If ` End Sub Finally, what interface would be complete if it didn't allow external calls to the About box? Public Sub About() ` ` calls about box ` frmAbout.Show vbModal ` End Sub That's it for the object model. Save and compile the program. In the next step you build a quick Visual Basic application to test the object model. Testing the Object ModelStart a new Visual Basic 5.0 Standard EXE, and add a single control array of five
command buttons to the project. Refer to Figure A.8 for captions, size, and location
of the buttons. Option Explicit ` Dim objSQLVB5 As Object Next, you need to add the following code to the Form_Load event. This code makes the object variable refer to the application class of your compiled SQL-VB5. Private Sub Form_Load() ` Set objSQLVB5 = CreateObject("SQLVB5.Application") ` End Sub Now you need to add code to the command1_Click event of the form. This contains all the code you need to test the SQL-VB5 object model. Enter the code in Listing A.42 into the Command1_Click event. Listing A.42. Adding code to the Command1_Click event.Private Sub Command1_Click(Index As Integer) ` Select Case Index Case 0 ` start objSQLVB5.Start Case 1 ` run objSQLVB5.Run Case 2 ` about objSQLVB5.About Case 3 ` edit objSQLVB5.Script = "c:\tysdbvb5\source\chapxa\scripts\sqltest.sqv" objSQLVB5.Edit Case 4 ` generate objSQLVB5.DBName = "c:\tysdbvb5\source\data\books5.mdb" objSQLVB5.Script = "c:\tysdbvb5\source\chapxa\scripts\books5.sqv" objSQLVB5.Generate End Select ` End Sub
Modifying SQL-VB5You now have a very valuable tool to add to your database programming tool kit. You can use SQL-VB5 to generate database layouts for all your projects in the future. You can also use SQL-VB5 to test data integrity options, load test data into existing tables, and even create simple data backup and replication scripts. You could even add more options to the project. Here are some additional features that you might want to consider:
|
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |