insert/update image datatype column

  • Hi, someone can show me how can I insert/update a row containing an image datatype column ?

    Table: Product (ProdId int, Description varchar(50), ProdImage image)

    The image files (Prod1.jpg, Prod2.jpg,...) are located in a directory \Image.

    If possible, I'd like to use stored procedures for this purpose.

  • Hey Sergio,

    The hard part is getting the data into the variable. I like to just use ADO for the entire process rather than using a stored procedure. The "old" way of loading a blob in ADO required "chunking", but with streams its much easier.

    This loads a file from disk into a stream object:

    Set mStream = New ADODB.Stream

    With mStream

    .Type = adTypeBinary


    .LoadFromFile "filename"

    End With

    Then you open a recordset that is updatable and load it (content is whatever col you're loading it into):

    With rsFile


    .Fields("Content") = mstream.Read


    End With

    If you're using SQL2K you should also look at the full text indexing help if you'll have any type of blob containing text (Word docs for instance) - its possible to search against them natively. Doesnt matter if you're just using images though.

    Other options are to bcp it in (one file at a time) or there is a utility in the SQL2K resource kit that will handle multiple files though I have not tried it.


Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply