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:

$image\sqrblit.gif$ Create a database with one table containing images and names for the images.

$image\sqrblit.gif$ Create an ODBC data source that references the table.

$image\sqrblit.gif$ Use a data control to manage the table and to access the names.

$image\sqrblit.gif$ Use the LEADTOOLS methods to access the images through the ODBC data source.

$image\sqrblit.gif$ Use SQL SELECT statements to maintain a sorted view of the records.

$image\sqrblit.gif$ Synchronize the record pointers so that the two views of the same table are always correct.

Note:

The ILEADRasterODBC Object 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.
	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.
	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 RasterView Control to your project.
10.	$image\btnlead.gif$ Select the LEADRasterView 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 LEADRasterView 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 LeadRasterView1.
12.	Add the LEAD RasterProcess Object Library to your project.
	On the Project pull-down menu, use the References option, and select the LEAD RasterProcess Object Library (14.5).
13.	Add the LEAD Raster Object Library and LEAD Raster Variant Object Library to your project.
	On the Project pull-down menu, use the References option, and select the LEAD Raster Object Library (14.5).
	On the Project pull-down menu, use the References option, and select the LEAD Raster Variant Object Library (14.5).
14.	Add the LEAD RasterIO Object Library to your project.
	On the Project pull-down menu, use the References option, and select the LEAD RasterIO Object Library (14.5).
15.	Add the LEAD Raster ODBC Object Library to your project.
	On the Project pull-down menu, use the References option, and select the LEAD Raster ODBC Object Library (14.5).
16.	Add the LEAD Raster Annotation ODBC Object Library to your project.
	On the Project pull-down menu, use the References option, and select the LEAD Raster Annotation Object Library (14.5).
17.	Add the following code to the general declarations section of your project.

Public RasterIO As LEADRasterIO
Public RasterProc As LEADRasterProcess
Public WithEvents LeadRasODBC As LEADRasterODBC
Public RasAnn As LEADRasterAnnotation

18.	Add the following code to the end of the Form_Load event handler.

'Create the RasterIO Object
Set RasterIO = CreateObject("LEADRasterIO.LEADRasterIO ")
'Create the RasterProcess Object
Set RasterProc = CreateObject("LEADRasterProcess.LEADRasterProcess ")
'Create the RasterODBC Object
Set LeadRasODBC = CreateObject("LEADRasterODBC.LEADRasterODBC ")

'Create the RasAnn Object
Set RasAnn = CreateObject("LEADRasterAnnotation.LEADRasterAnnotation")
LEADRasterView1.Raster.UnlockSupport L_SUPPORT_DOCUMENT, "TestKey"
RasAnn.AnnParentRasterView = LEADRasterView1
RasAnn.AnnUserMode = ANN_USERMODE_DESIGN
RasAnn.AnnTool = ANN_TOOL_SELECT
LeadRasODBC.AnnContainer = RasAnn.AnnContainer

19.	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.
20.	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.
21.	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

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

LeadRasODBC.EnableMethodErrors =  FALSE
On Error GoTo ERRORHANDLER
    ' Turn off automatic scrolling and repainting
    LeadRasterView1.AutoScroll = False
    LeadRasterView1.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"
    LeadRasODBC.Raster = LEADRasterView1.Raster
    LeadRasODBC.dbOpen DString, SelString, "photo", DB_OPENOPTIONS_NONE
    ' Set the LEAD control's database properties
    LeadRasODBC.dbLoadBits = 24
    LeadRasODBC.dbLockingMode = DB_LOCKINGMODE_OPTIMISTIC
    ' Enable use of the dbMove method in the data control's Reposition event
    If (LeadRasODBC.dbIsBOF And LeadRasODBC.dbIsEOF) = False Then
      NormalMove = True
    End If
Exit Sub
ERRORHANDLER:
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

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

LeadRasODBC.dbClose

24.	Add a Microsoft CommonDialog control to your form.
25.	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 = "Graphics|*.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
    LeadRasterView1.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 RasterODBC object's recordset and make sure it is
    ' synchronized with the data control.
    LeadRasODBC.dbRequery
    NormalMove = True ' Do normal record synchronization
    Dpeople.Recordset.MoveLast
    If LeadRasODBC.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 + "'"
    LeadRasterView1.Visible = True
    ' Load the image file and update the recordset
    LeadRasODBC.dbEdit
    RasterIO.Load LeadRasterView1.Raster, Myfile, 0, 0, 1
    LeadRasODBC.Raster = LeadRasterView1.Raster
    ' Update the recordset using the appropriate format
    If LeadRasterView1.Raster.BitmapBits = 1 Then
        LeadRasODBC.dbUpdate FILE_LEAD1BIT, 1, 0
    ElseIf LeadRasterView1.Raster.BitmapBits = 4 Then
        LeadRasODBC.dbUpdate FILE_PCX, 4, 0
    ElseIf LeadRasterView1.Raster.IsGrayscale = GRAY_NO Then
        LeadRasODBC.dbUpdate FILE_CMP, 24, QFACTOR_QMS
    Else 'save as grayscales
        LeadRasODBC.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

26.	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 ODBC control.
    NormalMove = False
    LeadRasterView1.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
    LeadRasODBC.dbRequery
    Dpeople.Recordset.MoveLast
    NewPosition = Dpeople.Recordset.AbsolutePosition
    LeadRasODBC.dbMove NewPosition
    ' Make sure the LEADODBC control is on the last record.
    LeadRasODBC.dbMoveNext
    If LeadRasODBC.dbIsEOF Then
        LeadRasODBC.dbMovePrev
    Else
        MsgBox "Synchronization error!" + Chr(13) + "Restart the application"
        Exit Sub
    End If
    ' Return to the old record position, if possible.
    NormalMove = True
    LeadRasterView1.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

27.	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
    RasterProc.Flip LeadRasterView1.Raster
    LeadRasODBC.dbEdit
    ' Update the recordset using the appropriate format
    If LeadRasterView1.Raster.BitmapBits = 1 Then
        LeadRasODBC.dbUpdate FILE_LEAD1BIT, 1, 0
    ElseIf LeadRasterView1.Raster.BitmapBits = 4 Then
        LeadRasODBC.dbUpdate FILE_PCX, 4, 0
    ElseIf LeadRasterView1.Raster.IsGrayscale = GRAY_NO Then
        LeadRasODBC.dbUpdate FILE_CMP, 24, QFACTOR_QMS
    Else 'save as grayscale
        LeadRasODBC.dbUpdate FILE_CMP, 8, QFACTOR_QMS
    End If
Exit Sub
ERRORHANDLER:
If Dpeople.Recordset.EditMode = dbEditInProgress Then
    Dpeople.Recordset.CancelUpdate
End If
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

28.	Add the following code to the LEAD RasterView control'sChange event:

' Avoid processing events that occur before the bitmap is fully loaded
If LEADRasterView1.Raster.Bitmap = 0 Then
LEADRasterView1.ForceRepaint
Exit Sub
End If
RasAnn.AnnContainer = LeadRasODBC.AnnContainer
LeadRasterView1.PaintSizeMode = PAINTSIZEMODE_FIT
LeadRasterView1.ForceRepaint

29.	Add the following code to the LEAD RasterODBC object's Change event:

' Avoid processing events that occur before the bitmap is fully loaded
If LeadRasterView1.Raster.Bitmap = 0 Then
LeadRasterView1.ForceRepaint
Exit Sub
End If
RasAnn.AnnContainer = LeadRasODBC.AnnContainer
LeadRasterView1.PaintSizeMode = PAINTSIZEMODE_FIT
LeadRasterView1.ForceRepaint

30.	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

31.	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
    LeadRasODBC.dbMove NewPosition - OldPosition
End If

32.	Run your program to test it.