|
|
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
|
See also for 'Read and write SQL image data, store binary file to sql table.' article Work with binary files in VBSscript - read and write local and remote filesReading and writting binary and text files is a first task you will need to solve in server-side ASP. This article contains several VBS functions which let's you store data to local disk and read local or remote (http) files. Convert a binary data (BinaryRead) to a string by VBSThis article, demonstrates several versions of source VBS code you can use to work with binary data in ASP and convert the data to a String format. Create and work with binary data in ASP/VBScriptLets you convert/create binary data in ASP to use the data for BinaryWrite/BinaryRead. Copyright and use this code
The source code on this page and other samples at http://www.motobit.com/tips/
are a free code, you can use it as you want: copy it, modify it, use it in your products, ...
If you use this code, please:
1. Leave the author note in the source.
or
2. Link this sample from you page.
<A
Href="http://www.motobit.com/tips/detpg_read-write-sql-image-file/"
Title="Store and read SQL image/binary data
using functions in this article.
You can store local or
remote files in an sql
table along with a description
and other fields."
>Read and write SQL image data, store binary file to sql table.</A>
|