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 - 2013 Antonin Foller, Motobit Software | About, Contacts | (Find us on Google+)
Partner sites: Search Czech Last minute Zajezdy Obsah na mobil Hry na mobil Java Hry Print-shop Affiliate programy
|IISTracer - IIS ISAPI real-time monitor IISTracer is a real-time monitoring tool for Microsoft IIS, which will show/log you what is happenning on IIS server right now. It let's you reveal problems with long-running scripts (.asp, .cgi, cfm...), hang-up states and low resource situations and lets you stop long-running requests (uploads/downloads).||ActiveX User account Manager - Set of simple objects for creating, deleting, and managing user accounts, groups, servers and domains in the Windows NT environment.|
|Active log file - Hi-performance text file logging for ASP/VBS/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.||ActiveX windows registry editor - Intuitive, easy to use COM interface to windows registry. Set of classes to read/enumerate/modify windows registry keys and values from ASP, VBS and T-SQL.|
|ActiveX/ASP Multi Dictionary object - Free-threaded hi-speed dictionary algorithm with unique/nonunique keys (map/multimap). Connect to another dictionary object in the same process. Lock and Unlock methods to synchronize tasks (application scope). Share ASP Application/Session objects.||Export DBF/MDB from ASP - Conversion from recordset to MDB/DBF. Direct binary output of MDB or DBF files from ASP pages with one row of code.|
|Pure-ASP upload - lets you upload files using Pure ASP VBS code (using multipart/form-data and input type=file).||ByteArray - Works with safearray binary data (VT_UI1 | VT_ARRAY) - save/restore binary data from disk, find, work with code pages, convert to string/hexstring(SQL).|
|WebChecker - Checks http, https, ftp and gopher internet connections in regular intervals. Lets you monitor web site functionality (uptime). Enables restart or notification on problems.||HTTPLog ISAPI filter - Lets you log incomming/outgoing http header and document data to separate files. Monitor of IIS service input/output.|