Read and write SQL image data, store binary file to sql table.

I wrote previous article about read/write binary files from a disk or read a file from remote server (URL - using http/ftp). Sometimes you will need to read or write binary data within an SQL connection. This article contains sample code to create a table with a binary column, get a binary data from a file (image) and store them to a database table, read the binary data from the database and sent the data to a client using ASP Response.BinaryWrite.
See also: Online Base16 (hex string) decoder and encoder - sample for hexstring conversion. The sample allows you to create SQL command to insert binary data to a database.

1. Database connection

All of my samples are using GetSQLConn function. Next is an SQL connection to MDB database (jet engine), you can simply use another connection to MS SQL, ORACLE, MYSQL or FoxPro DBF files.

a) Function to open database connection - GetSQLConn

Function GetSQLConn()
  Const MDBFile = "F:\work\Basic\testy.bas\binarywork\binarywork.mdb"
  'Create Connection object
  Dim GlobalADOConn
  Set GlobalADOConn = CreateObject("ADODB.Connection")
  
  'Open OLEDB connection To MDB/Jet engine
  GlobalADOConn.Provider = "Microsoft.Jet.OLEDB.4.0"
  GlobalADOConn.Open "Data Source=" & MDBFile
  Set GetSQLConn = GlobalADOConn
End Function

2. Create a table with binary (image) data column.

CreateTableWithBinary function creates simple database table with primary key column, one image (binary) field and text (description) field.

Function CreateTableWithBinary()
  Dim SQL, Conn
  'SQL statement with 'Create Table' command
  'the table has three columns - primary ID
  ' one text And one binary column
  SQL = "Create Table WebData ("
  SQL = SQL & "ID int identity primary key Not null,"
  SQL = SQL & "Description char(100),"
  SQL = SQL & "BinaryColumn image"
  SQL = SQL & ")"
  
  'Get connection To database
  Set Conn = GetSQLConn()
  
  'Create the table
  Conn.Execute SQL
End Function

3. Add a row with binary data to the table using recordset.

AddBinaryDataRow opens a recordset and appends one row to WebData table. You can use ReadBinaryFile function from the previous article or Form("FieldName").ByteArray from Pure-ASP file upload as a data source for BinaryData parameter.

Function AddBinaryDataRow(BinaryData, Description)
  Const adCmdText = 1
  Const adOpenDynamic = 2
  Const adLockOptimistic = 3
  Const adOpenKeyset = 1
  
  Dim SQL, Conn, RS As ADODB.Recordset
  'Create SQL command To retrieve data
  SQL = "Select ID, Description, BinaryColumn from WebData Where 1=0"
  
  'Get connection To SQL database
  Set Conn = GetSQLConn()
  Set RS = CreateObject("ADODB.Recordset")
  RS.Open SQL, Conn, adOpenKeyset, adLockOptimistic, adCmdText
  
  'AddNew - new row To the recordset
  RS.AddNew
  
  'Set source BinaryData To BinaryColumn
  RS("BinaryColumn") = BinaryData
  RS("Description") = Description
  
  'Use this code instead of previous line For ORACLE.
  'RS("BinaryColumn").AppendChunk BinaryData
  'Store data To database
  RS.Update

  'Or using one-row AddNew only - instead of AddNew + Update
  'RS.AddNew Array("BinaryColumn", "Description"), _
  '   Array(BinaryData, Description)
  
  'Get an ID of currently added row.
  AddBinaryDataRow = RS("ID")
End Function

'store some binary files In a database
AddBinaryDataRow ReadBinaryFile("f:\logo.gif"), "Company logo"
AddBinaryDataRow ReadBinaryFile("f:\next.gif"), "Next page"

'store result of an upload form To the database
If Form.State = 0 Then
  AddBinaryDataRow _
    Form("File1").ByteArray, _
    Form("Description").String
End If

4. Retrieve binary data from the table.

GetImageData returns a binary data of the ID from the WebData table. You can use output of the function for Response.BinaryWrite or for SaveBinaryData function.

Function GetImageData(ID)
  Dim SQL, Conn, RS
  'Create SQL command To retrieve data
  SQL = "Select BinaryColumn from WebData"
  SQL = SQL & " Where ID=" & ID
  
  'Get connection To SQL database
  Set Conn = GetSQLConn()
  Set RS = Conn.Execute(SQL)
  
  'GET binary data from recordset
  GetImageData = RS("BinaryColumn")
  
  'Use this code instead of previous line For ORACLE.
'  GetImageData = RS("BinaryColumn").GetChunk( _
'      RS("BinaryColumn").ActualSize)
End Function


'send image To the client from ASP
Dim ID
ID = Request.QueryString("ID")
If IsNumeric(ID) Then
  Response.BinaryWrite GetImageData(ID)
End If

'write image data To a disk
SaveBinaryData "C:\inetpub\root7\next.gif", GetImageData(2)

5. Very large/huge files.

Previous functions are suitable only for small files. Remember that ADODB.Recordset takes at about 4-5 bytes per one source byte from binary data. So if you have 1MB file, AddBinaryDataRow takes about 5MB of memory. Some time also takes SQL engine or Jet engine - See more about it at 'Upload - to database, or not to database' article.

You must use HexString functionality (see UPDATETEXT in the article) or AppendChunk ADO method if you want to work with huge files (10th and 100th of megabytes).

6. Insert binary (or image/text) data using hexstring.

SQL standard let's you use also hexstring values to store binary data. The SQL command looks like

Insert Into Tablename
 (BinaryColumn, OtherColumns)
Values
 (0xAB1FCD...., "some other data")

We need function to convert binary data to a hex string - BinaryToHex. Remember that BinaryToHex is byte-to-byte VBS function and it is suitable only for small amount of binary data - up to 100kB. If you want to work with bigger size, please see HexString property of ByteArray object.

Function AddBinaryDataRowUsingHex(BinaryData, Description)
  Dim SQL, Conn, RS As ADODB.Recordset
  
  'Create SQL command To insert data
  SQL = "Insert Into WebData (Description, BinaryColumn) Values("
  SQL = SQL & " '" & Replace(Description, "'", "''") & "'"
  SQL = SQL & ",0x" & BinaryToHex(BinaryData)
  SQL = SQL & ")" & vbCrLf
  
  'Get connection To SQL database
  Set Conn = GetSQLConn()
  
  'Insert data To the table
  Conn.Execute SQL
  
  'get latest ID
  SQL = " select @@Identity As ID"
  Set RS = Conn.Execute(SQL)
  
  'Get an ID of currently added row.
  AddBinaryDataRowUsingHex = RS("ID")
End Function

'Simple binary-to-hex Function
'2003 Antonin Foller, Motobit Software
Function BinaryToHex(Binary)
  Dim c1, Out, OneByte
  
  'For each source byte
  For c1 = 1 To LenB(Binary)
    'Get the byte As hex
    OneByte = Hex(AscB(MidB(Binary, c1, 1)))
    
    'append zero For bytes < 0x10
    If Len(OneByte) = 1 Then OneByte = "0" & OneByte
    
    'join the byte To OutPut stream
    Out = Out & OneByte
  Next
  
  'Set OutPut value
  BinaryToHex = Out
End Function

Read and write SQL image data, store binary file to sql table.MOTOBIT.COM