Areas > Languages > VBScript
Areas > ASP / ASP.Net > Functions > Conversion
Areas > ASP / ASP.Net > Database
Areas > WSH
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.
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
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.
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.
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.
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.
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
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.
for 'Read and write SQL image data, store binary file to sql table.' articleWork 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.
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>
© 1996 - 2014 Antonin Foller, Motobit Software | About, Contacts | (Find us on Google+)