Using ODBC to Access Image Data (Visual Basic)

This lesson describes how to use the LEADTOOLS ODBC features to maintain images in a table with other data. In this lesson, you accomplish the following:

Create a database with one table containing images and names for the images.

Create an ODBC data source that references the table.

Use a data control to manage the table and to access the names.

Use the LEADTOOLS methods to access the images through the ODBC data source.

Use SQL SELECT statements to maintain a sorted view of the records.

Synchronize the record pointers so that the two views of the same table are always correct.

Note: In Visual Basic, a simpler alternative is to use the LEAD control as a data-aware control. Refer to Implementing a Database with a Data Control.

Note: The LEADTOOLS ActiveX requires ODBC drivers that can properly handle long binary fields. Examples have been tested and will work with version 3.0 of the Microsoft ODBC Desktop Driver Pack. (This driver pack ships with Access 95 and works on 32-bit systems.) You can download drivers from the LEAD BBS, the CompuServe forum, or the Worldwide Web home page. The CompuServe forum is at GO LEADTECH. The web page is ftp://ftp.leadtools.com/pub/utils/ODBC32.zip. The file to download is ODBC32.ZIP.

1. Start Visual Basic.

2. On the Add Ins menu, select the Data Manager option (Visual Data Manager in VB5).

3. Create a new database.

For VB4: On the Select File menu, select the New Database option.

For VB5: On the File menu, select New Database, and specify Microsoft Access 7.

4. Specify a file name of leadpic.mdb and click the Save button.

5. Add a table.

For VB4: Click the New button.

For VB5: Use the right mouse button, and select the New Table menu option.

6. In the Add Table window, specify a table named: people, with the following two fields:

a. Field name: who. Data Type: text. Size: 255

b. Field name: photo. Data Type: Long Binary (or Binary). Size: N/A.

7. Close the Database Manager.

8. Add a data control to the bottom of your form and change its properties as follows:

a. Set the DatabaseName property to leadpic.mdb. (Use the browser to get the full path name.)

b. Set the RecordSource property to the following:

SELECT who FROM people ORDER BY who

c. Set the Name property to Dpeople.

9. On the Tools pull-down menu, use the Custom Controls option to add the LEAD OLE Custom Control module to your project.

10. $image\btnlead.gif$ Select the LEAD control; then add the control to your main form. Stretch and position the control as you want it to appear at run time.

11. In the Properties box for the LEAD control, make the following changes:

a. Set the BorderStyle property to 1. This adds a 1-pixel border to the control.

b. Set the Name property to Lead1.

12. Add a TextBox control to your form and change its properties as follows:

a. Set the DataSource property to Dpeople.

b. Set the DataField property to who.

c. Set the Name property to Cwho.

13. From the Windows Control Panel, create the ODBC data source as follows:

a. Open the ODBC administrator and click the Add button.

b. Select Microsoft Access driver and click the OK button.

c. Enter the name LEADODBC.

d. Click the Select directory button and select the path to the database that you created.

e. Click the OK button; click the next OK button; then click the Close button.

14. In Visual Basic, add the following code to your form's general declarations. In online help, you can use the Edit pull-down menu to copy the block of code.

' The Windows Sleep function lets us synchronize requeries on adds and deletes
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

' These are variables for implementing the dbMove method
Public OldPosition, NewPosition As Long

' This variable lets us make the dbMove method conditional
Public NormalMove As Boolean

15. Add the following code to the form's Load procedure.

On Error GoTo ERRORHANDLER
    ' Turn off automatic scrolling and repainting
    Lead1.AutoScroll = False
    Lead1.AutoRepaint = False

    ' Open the database.
    ' Use the same SELECT statement that is used for the data control.
    ' Note that the image field must be listed first in the SELECT statement.
    DString = "ODBC;DSN=LEADODBC"
    SelString = "SELECT photo,* FROM people ORDER BY who"
    Lead1.dbOpen DString, SelString, "photo", DB_OPENOPTIONS_NONE

    ' Set the LEAD control's database properties
    Lead1.dbLoadBits = 24
    Lead1.dbLockingMode = DB_LOCKINGMODE_OPTIMISTIC

    ' Enable use of the dbMove method in the data control's Reposition event
    If (Lead1.dbIsBOF And Lead1.dbIsEOF) = False Then
      NormalMove = True
    End If

Exit Sub

ERRORHANDLER:
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

16. Add the following code to the form's Unload procedure.

Lead1.dbClose

17. Add a CommonDialog control to your form.

18. Add a CommandButton control to your form, change its Caption property to Add Photo, and add the following code to its Click event:

On Error GoTo ERRORHANDLER
    ' Get an image file
    ' This filter list is complete, except for GIF.
    CommonDialog1.Filter = "Grapics|*.cmp; *.jpg; *.jff; *.jtf; *.bmp; *.tif; *.tga; *.pcx; *.cal; *.mac; *.mac; *.img; *.msp; *.wpg; *.wpg; *.ras; *.pct; *.pcd; *.eps; *.wmf"
    CommonDialog1.Flags = cdlOFNFileMustExist
    CommonDialog1.DialogTitle = "Open File"
    CommonDialog1.CancelError = True
    CommonDialog1.ShowOpen
    Myfile = CommonDialog1.filename

' Suspend use of the dbMove method in the data control's Reposition event
NormalMove = False

'Set the pointer to an hourglass
Screen.MousePointer = 11

' Hide the LEAD control to avoid unnecessary repaints
Lead1.Visible = False

    ' Add and update the record, without inserting the image
    Dpeople.Recordset.AddNew
    Cwho = Myfile
    Dpeople.Recordset.Update

' Wait for the update to take effect
Call Sleep(5000)

' Requery the data control's recordset
Dpeople.Recordset.Requery

    ' Requery the LEAD control's recordset and make sure it is
    ' synchronized with the data control.
    Lead1.dbRequery
    NormalMove = True ' Do normal record synchronization
    Dpeople.Recordset.MoveLast
    If Lead1.dbCurrentRecord <> Dpeople.Recordset.AbsolutePosition Then
        MsgBox "Synchronization error!" + Chr(13) + "Delete the record and exit"
        Dpeople.Recordset.FindFirst "who = " + "'" + Myfile + "'"
        Screen.MousePointer = 0
        Exit Sub
    End If

    ' Go to the new record, and show the LEAD control
    Dpeople.Recordset.FindFirst "who = " + "'" + Myfile + "'"
    Lead1.Visible = True

    ' Load the image file and update the recordset
    Lead1.dbEdit
    Lead1.Load Myfile, 0, 0, 1

    ' Update the recordset using the appropriate format
    If Lead1.BitmapBits = 1 Then
        Lead1.dbUpdate FILE_LEAD1BIT, 1, 0
    ElseIf Lead1.BitmapBits = 4 Then
        Lead1.dbUpdate FILE_PCX, 4, 0
    ElseIf Lead1.IsGrayscale = GRAY_NO Then
        Lead1.dbUpdate FILE_CMP, 24, QFACTOR_QMS
    Else 'save as grayscale
        Lead1.dbUpdate FILE_CMP, 8, QFACTOR_QMS
    End If

'Set the mouse pointer back to the default
Screen.MousePointer = 0

Exit Sub

ERRORHANDLER:
'Set the mouse pointer back to the default
Screen.MousePointer = 0

If Dpeople.Recordset.EditMode = dbEditAdd Then
Dpeople.Recordset.CancelUpdate
End If
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

19. Add another CommandButton control to your form, change its Caption property to Delete Photo, and add the following code to its Click event:

On Error GoTo ERRORHANDLER
    ' Disable the normal record synchronization and hide the LEAD control.
    NormalMove = False
    Lead1.Visible = False

'Set the pointer to an hourglass
Screen.MousePointer = 11

' Save the current position
RecMarker = Dpeople.Recordset.AbsolutePosition

   ' Delete the record and wait for the deletion to take effect
    Dpeople.Recordset.Delete
    Call Sleep(5000)

    ' Requery the recordsets, move to the last record,
    ' and initialize the NewPosition global variable.
    Dpeople.Recordset.Requery
    Lead1.dbRequery
    Dpeople.Recordset.MoveLast
    NewPosition = Dpeople.Recordset.AbsolutePosition
    Lead1.dbMove NewPosition

    ' Make sure the LEAD control is on the last record.
    Lead1.dbMoveNext
    If Lead1.dbIsEOF Then
        Lead1.dbMovePrev
    Else
        MsgBox "Synchronization error!" + Chr(13) + "Restart the application"
        Exit Sub
    End If

    ' Return to the old record position, if possible.
    NormalMove = True
    Lead1.Visible = True
    RelativePosition = RecMarker - NewPosition
    If RelativePosition < 0 Then
         Dpeople.Recordset.Move RelativePosition
    End If

'Set the mouse pointer back to the default
Screen.MousePointer = 0

Exit Sub

ERRORHANDLER:
'Set the mouse pointer back to the default
Screen.MousePointer = 0
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

20. Add another CommandButton control to your form, change its Caption property to Flip Photo, and add the following code to its Click event:

On Error GoTo ERRORHANDLER
    ' Flip the image and update the record
    Lead1.Flip
    Lead1.dbEdit

Exit Sub

ERRORHANDLER:
If Dpeople.Recordset.EditMode = dbEditInProgress Then
Dpeople.Recordset.CancelUpdate
End If
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

21. Add the following code to the Lead1 control's Change event:

' Avoid processing events that occur before the bitmap is fully loaded
If Lead1.Bitmap = 0 Then Exit Sub

' Calculate the display rectangle to fit the image inside the control
HeightAllowed = Lead1.ScaleHeight
WidthAllowed = Lead1.ScaleWidth

If (HeightAllowed * Lead1.BitmapWidth / Lead1.BitmapHeight) <= WidthAllowed Then
    DisplayTop = 0
    DisplayHeight = HeightAllowed
    DisplayWidth = DisplayHeight * Lead1.BitmapWidth / Lead1.BitmapHeight
    DisplayLeft = (Lead1.ScaleWidth - DisplayWidth) / 2
Else
    DisplayLeft = 0
    DisplayWidth = WidthAllowed
    DisplayHeight = DisplayWidth * Lead1.BitmapHeight / Lead1.BitmapWidth
    DisplayTop = (Lead1.ScaleHeight - DisplayHeight) / 2
End If

'Set the image display size
Lead1.SetDstRect DisplayLeft, DisplayTop, DisplayWidth, DisplayHeight
Lead1.SetDstClipRect DisplayLeft, DisplayTop, DisplayWidth, DisplayHeight
Lead1.ForceRepaint

22. Add the following code to the Dpeople control's Validate event:

' Set the OldPosition, which is used in a calculation in the Reposition event
OldPosition = Dpeople.Recordset.AbsolutePosition

23. Add the following code to the Dpeople control's Reposition event:

' Use the dbMove method to move the LEAD control to the current record
If NormalMove Then
    ' The current position is the NewPosition
    ' The OldPosition is set in the Validate event
    NewPosition = Dpeople.Recordset.AbsolutePosition
    Lead1.dbMove NewPosition - OldPosition
End If

24. Run your program to test it.