Getting image into field (SQL 7) and getting it back out

Getting image into field (SQL 7) and getting it back out

Post by Frank Eersel » Thu, 16 Nov 2000 04:00:00

Hi folks,

I'm currently working on a project which has a back office application
(Visual Basic)  to maintain a SQL7 based database, and a front office
app, written in ASP and HTML to read the data out of the database and
to display it in Explorer or Netscape.

Some of the tables contain on or more Image fields. I had no trouble
of getting the image in and out of the field using the back office
application. For this job I wrote a customized ocx to put an image in
and out of an Image field using GetChunk and AppendChunk.

The problem of getting the images into a blob field object isn't a
problem. However, we didn't manage to read the field object in the
front office app, which is written in ASP and HTML.

We use Visual Basic 6.0 SP3, Windows 2000, ADO 2.1 (2.5 isn't an
option because we use other self made ocx's and dll's who only support
ADO 2.1).

The following code is used in the customized ocx control to place an
image into a blob field in MS SQL 7:

Const BlockSize = 32768

Public Sub PlaceImageIntoField(aoFld as ADODB.Field, aoFldWidth as _
                ADODB.Field, aoFldHeight as ADODB.Field)
        Dim iNumBlocks As Integer
        Dim lLeftOver As Long
        Dim i As Integer
        Dim lFile as Long
        Dim s As String
        Dim strFolder As String  255    'string containing the tempdir
                                        'of Windows
        'get the temporary path of Windows
        iTempPathLength = GetTempPath(255, strFolder)
        sTempPath = Left(strFolder, iTempPathLength)

        If Not moImage Is Nothing then
                'moImage is a method to export a picturebox's contents
                'to a jpgfile
                moImage.SaveImageToJPG sTempPath & "cattemp.jpg", _
                        typeJPG, 16, optionNoCompression
                'get the filesize
                L = FileLen(sTempPath & "cattemp.jpg")                    
                lFile = FreeFile
                Open sTempPath & "\cattemp.jpg" For Binary Access _
                        Read As lFile
                If L > 32768 'default blocksize
                        iNumBlocks = L \ BlockSize
                        lLeftOver = L Mod BlockSize
                        S = String(lLeftOver, chr(0))
                        Get #lFile, , S
                        S = String(BlockSize, Chr(0))
                        For i = 1 To iNumBlocks
                                Get #lFile, , S
                                aoFld.AppendChunk S
                        Next i
                        Close lFile
                        S = String(L, Chr(0))
                        Get #lFile, ,S
                        aoFld.AppenChunk S
                        Close lFile
                End If
                If msImageH <> 0 Then aoFldHeight.Value = msImageH
                If msImageW <> 0 Then aoFldWidth.Value = msImageW
                'user cleared the control, so insert Null into Fld
                aoFld.Value = Null
                aoFldHeight.Value = 0
                aoFldWidth.Value = 0
        End If
End Sub

This is how I place an image into a blob field into SQL 7. There are
some functions which I don't discuss here but I think you'll get the
general idea if you read the code.

Now, to get the image back out again in ASP:

'Clear out the existing HTTP  header information
        Response.Expires = 0
        Response.Buffer = True

'Declare variables
        Dim RecordSetTempFotos
        Dim SQLStatementFotos
        Dim TrackNumber
        Dim Field
        Dim PictureField

'Catch the input
        TrackNumber = Request.QueryString("id")
        Table = Request.QueryString("t")
        Field = Request.QueryString("f")
        PictureField = Request.QueryString("pf")

'Create DataBaseConnection

'Create SQLStatements
        SQLStatementFotos = "SELECT * " & _
                        " FROM " & Table & _
                        " WHERE " & Field " = " & _

'Create RecordSets
        Set RecordSetTempFotos = _

'Change the header to reflect that an image is being passed
        Response.ContentType = "image/jpeg"

'Display the image
        Response.BinaryWrite RecordSetTempFotos.Fields( _

'Close the recordsets...

Inserting images and getting them out of the database isn't a problem
in the back office application. If the user chooses detailed
information of a product, an image of the product is shown next to the
textual product information. This image is read from the database with
a function GetImageFromField, which is like PlaceImageIntoField, a
method in the same ocx. We know the image is valid because the ocx
creates a temporary file on disk containing an image, which was read
from the database.

However, the front office app can't read any of the images we inserted
with the back office app before. We presume that there's something
wrong with the ASP code but we don't know what we possible may have
overlooked here.

Any suggestions or assistance are welcome, and if possible, please

the news server at work.


Frank Eersels


1. Getting back an identity field value after an insert SQL statement

Got some code doing an insert for a new record in a table, first field in
the table is ID which is the primary key, an int data type and is set to be
an identity  field with identity seed 1 and increment = 1.

After the insert statement has correctly completed, how do I get back the Id
for the record that my code has just created?
I know I could do a select statement getting the top 1 value of the ID back
but this does not guarantee that in a multi-user environment someone else
has not just added a record practically at same time as me and that I might
be getting back his record Id instead of my own newly created one. Chances
of this happening are slim but I want  to be sure to use a method that
prevents this from ever happening.

Thanks for any help.


2. ADO Connection

3. getting distinct recordset back in a record which includes a text field

4. Why does my VB App stay in memory

5. Getting Image Data into a Memo Field

6. Disaster Recovery

7. Getting data back to SQL

8. Getting PL/SQL back-end errors into ADODB.Errors collection

9. Getting back @@INDETITY from SQL DB from a Recordset

10. Getting SQL Errors Back From Stored Procedures

11. OCI problem in getting back results from SQL query

12. Getting IMages out of SQL SERVER