Accessing informix blobs from Visual Basic

Hi, Can someone show me an example on how to store and retrieve images with informix 7.2 running on Unix from Visual Basic with an ODBC connection (Informix CLI 2.5 32 bit) ? Thank You Camilo Eid cameid@hotmail.com Sent via Deja.com http://www.deja.com/ Before you buy.

cameid@hotmail.com wrote in message <7v7u6b$log$1@nnrp1.deja.com>... >Hi, > >Can someone show me an example on how to store and retrieve images with >informix 7.2 running on Unix from Visual Basic with an ODBC connection >(Informix CLI 2.5 32 bit) ? > >Thank You > >Camilo Eid >cameid@hotmail.com > > >Sent via Deja.com http://www.deja.com/ >Before you buy. Hope this helps you. For extracting the image from the blob, you must use a temporary file on the disk Heres the idea for reading the image (this function returns a string containing the name of the temporary file on the disk) Public Function GetImage() As String On Error GoTo ErrorHandler Dim DBConn As ADODB.Connection Dim Rs As ADODB.Recordset Dim sqlclause As String Dim picture_size As Variant Dim file_handle As Integer Dim data() As Byte Dim bytesread As Long CheckState "GetImage" If HasImage Then file_ext = "gif" Set DBConn = Env.OpenConnection ' get the image type from systkernel..permitval where cdvalue = typict sqlclause = "SELECT snvalue FROM " & Env.GetPhysicalName("systkernel") & _ ":permitval WHERE nmfield = " & Env.SQLEncode("typict") & _ " AND cdvalue = " & Env.SQLEncode(typict) Set Rs = DBConn.Execute(sqlclause) If Rs.EOF Then file_saved = False GetImage = "" Exit Function End If file_ext = Env.SQLDecode(Rs.Fields("snvalue").Value) ' get the image field from the database Set DBConn = New ADODB.Connection DBConn.Open Env.GetConnectionString("searchnat") DBConn.Execute "SET ISOLATION TO DIRTY READ" Set Rs = New ADODB.Recordset sqlclause = "SELECT picture FROM picture WHERE idgroup = " & Env.SQLEncode(idgroup) & _ " AND odpicture = 0 AND dtuntil IS NULL" Rs.Open sqlclause, DBConn, adOpenStatic, adLockReadOnly If Rs.EOF Then ' there is no record file_saved = False GetImage = "" DBConn.Close Set DBConn = Nothing Exit Function End If picture_size = Env.SQLDecode(Rs.Fields("picture").ActualSize) ' this is the real size of the image in the DB temp_file_name = Env.GetUserName & "_" & idmark & Format(Now, "hhmmss") & "." & file_ext ' now save the image to the picturedir directory file_handle = FreeFile If picture_size <> "" And picture_size <> 0 Then Open picturedir & temp_file_name For Binary As #file_handle Do While picture_size <> bytesread If picture_size - bytesread < CHUNK_SIZE Then ' CHUNK_SIZE is a constant declared outside the function If picture_size <= CHUNK_SIZE Then data = Rs.Fields("picture").GetChunk(picture_size) Else data = Rs.Fields("picture").GetChunk(picture_size - CHUNK_SIZE) End If bytesread = picture_size Else data = Rs.Fields("picture").GetChunk(CHUNK_SIZE) bytesread = bytesread + CHUNK_SIZE End If Put #file_handle, , data Loop Close file_handle file_saved = True GetImage = temp_file_name Rs.Close DBConn.Close Set DBConn = Nothing Exit Function Else file_saved = False GetImage = "" DBConn.Close Set DBConn = Nothing Exit Function End If End If file_saved = False GetImage = "" Exit Function ErrorHandler: GetImage = "" Abort "GetImage" Err.Raise Err.Number, Err.Source, Env.RTErrDescription(TypeName(Me), "GetImage", Err.Description) End Function To insert an image is more or less the same, except you use AppendChunk instead of GetChunk Public Sub PutPicture(ByVal idpicture As Long, ByVal image_filename As String) On Error GoTo ErrorHandler Dim DBConn As New ADODB.Connection Dim Rs As ADODB.Recordset Dim sqlclause As String Dim filehandle As Long, i As Integer Dim data() As Byte, doclength As Long Dim bytesread As Long CheckState "PutPicture" filehandle = FreeFile DBConn.Open Env.GetConnectionString("searchnat") DBConn.Execute "SET ISOLATION TO DIRTY READ" sqlclause = "SELECT idpicture, picture FROM picture WHERE idpicture = " & Env.SQLEncode(idpicture) Set Rs = New ADODB.Recordset Rs.Open sqlclause, DBConn, adOpenKeyset, adLockOptimistic Open image_filename For Binary As #filehandle doclength = LOF(filehandle) If doclength = 0 Then DBConn.Close Set DBConn = Nothing Exit Sub End If Do While doclength <> bytesread If doclength - bytesread < CHUNK_SIZE Then data = InputB(doclength - bytesread, filehandle) bytesread = doclength Else data = InputB(CHUNK_SIZE, filehandle) bytesread = bytesread + CHUNK_SIZE End If Rs!Picture.AppendChunk data Loop Rs.Update DBConn.Close Set DBConn = Nothing Close filehandle Exit Sub ErrorHandler: Abort "PutPicture" Err.Raise Err.Number, Err.Source, Env.RTErrDescription(TypeName(Me), "PutPicture", Err.Description) End Sub