3 Attachment(s)
VB6 - SQL Parameters Example
Background
I'll start with a quote:
Why Parameters are a Best Practice
Quote:
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:
Code:
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:
Code:
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
