VB6 - SQL Parameters Example

[dilettante]

Background

I'll start with a quote:

Why Parameters are a Best Practice

Perhaps the single most important SQL Server application development Best Practice is the consistent use of parameters in application code. Parameterized database calls are more secure, easier to program and promote query plan reuse. Yet I continue to see code with SQL statements constructed by concatenating literal strings with variable values. I think this is largely out of ignorance rather than design so I decided to show how easy it is for .NET application developers to use parameters and peek into the internals to show why parameters are so important.

The same applies to VB6 just as well. However Dan's examples don't help us much since we're using ADO rather than any of the .Net data connector technologies.

So here is a simplified demo showing how to do similar things in VB6 with ADO.


The Demo

This is a simple demo showing the use of named ADO Command objects to perform parameter queries. It also shows how to store photos in the database as BLOB fields, retrieve them, display them, and update them.

While the demo uses Jet 4.0 to make it quick and easy to "unzip and play" the demo, these same concepts apply to other databases that you can use ADO with.


What It Does

When the program runs it begins by looking for an existing database. If found, it asks whether to delete it and create a new one or not.

If it creates a new database it then:

• Removes any existing database.
• Creates an empty database with one table [PicTable] with three fields:
  
  • [ID] an autonumber "identity" field set as the primary key.
  • [Description] a variable length (0-255 character) text field.
  • [Picture] a variable length (0-20000 byte)) long binary (BLOB) field.
• Closes the empty database.
• Reopens the database defining commands InsertPic and UpdatePic on the Connection.
• Populates the table with three sample records based on information in a provided text file and JPEG images in a subfolder.

Else it then:

• Opens the existing database defining the command UpdatePic (since it won't need InsertPic).

Finally, it:

• Displays the first record, showing all three fields.

The user interface has three buttons:

• "Back" and "Next" to step through records and display them.
• "Replace Photo" to replace the photo of the current record by a provided fixed replacement JPEG and redisplay the updated record.

The Command objects are used to do a SQL INSERT and a SQL UPDATE. They are invoked as dynamic methods of the open Connection object.


Running the Demo

Just unzip into a folder and open the Project in the VB6 IDE. Then go ahead and run it.

Step through the records. When you see the "wrong" picture you can click Replace Photo to update with a hard-coded replacement photo.

Close the program. Run it again and when prompted to create a new empty database click the "No" button.

Step through the records to see that the update was permanent.


Defining Named ADO Command Objects

By creating named Command objects you can use them dynamic methods of the Connection object until they are destroyed or disconnected. Here is what the demo does when connecting to the database after it has been created:

Public Sub OpenDbDefineCommands(ByVal NewDb As Boolean)
    Set conDB = New ADODB.Connection
    conDB.Open strConn

    If NewDb Then
        Set cmndInsert = New ADODB.Command
        With cmndInsert
            .Name = "InsertPic"
            .CommandType = adCmdText
            .CommandText = "INSERT INTO [PicTable] " _
                         & "([Description], [Picture]) " _
                         & "VALUES (?, ?)"
            .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, 255)
            .Parameters.Append .CreateParameter(, adLongVarBinary, adParamInput, MAX_PHOTO_BYTES)
            .Prepared = True
            Set .ActiveConnection = conDB
        End With
    End If

    Set cmndUpdate = New ADODB.Command
    With cmndUpdate
        .Name = "UpdatePic"
        .CommandType = adCmdText
        .CommandText = "UPDATE [PicTable] " _
                     & "SET [Picture] = ? " _
                     & "WHERE [ID] = ?"
        .Parameters.Append .CreateParameter(, adLongVarBinary, adParamInput, MAX_PHOTO_BYTES)
        .Parameters.Append .CreateParameter(, adInteger, adParamInput)
        .Prepared = True
        Set .ActiveConnection = conDB
    End With
End Sub

ADO will actually create entries in the Parameters collection itself on first use of a Command if you do not Create/Append them yourself. However it has to "guess" at things like the data type and length 9for variable length types).

In the cases here, those "guesses" are fine... until they aren't.

Let's say when you populate the new, empty database your first image is 4000 bytes. This will cause ADO to set the maximum length of the 2nd Parameter to 4000. And if you use the Command again passing an image larger than 4000 bytes you will get a runtime error!


Calling Named ADO Command Objects

You can call the Execute method on these Command objects, or you can also use them as dynamic methods of the Connection:

Public Function UpdatePic(ByVal PicFileName As String, ByVal ID As Long) As Boolean
    'Returns True if the operation fails.

    On Error Resume Next
    conDB.UpdatePic LoadPicBlob(PicFileName), ID
    If Err Then
        conDB.Errors.Clear
        Err.Clear
        UpdatePic = True
    End If
End Function

Private Function LoadPicBlob(ByVal PicFileName As String) As Byte()
    Dim PicFile As Integer
    Dim PicBlob() As Byte

    PicFile = FreeFile(0)
    Open PHOTOS_FOLDER & PicFileName For Binary Access Read As #PicFile
    ReDim PicBlob(LOF(PicFile) - 1)
    Get #PicFile, , PicBlob
    Close #PicFile
    LoadPicBlob = PicBlob
End Function