Working with Binary Large OBjects (BLOBs) has never been easier. With Mimer SQL Engine's support for ActiveX Data Objects (ADO) and some simple Visual Basic, you can store and retrieve BLOBs at the drop of a hat. Our excellent support department has put together a nifty program, BlobDemo, that will get you up and running in no time.
Prerequirements for Running BlobDemo
Getting the Program
- You must have Mimer SQL Engine 9.1 or later installed.
- You must have a Mimer SQL Engine database server running.
- You must have a database IDENT that you can connect as. Use the
CREATE IDENT statement.
- The IDENT must have access to a databank where he/she can create a table. Use the
GRANT TABLE statement.
All you have to do is click here to download it. (File name demodist.exe)
Installing and Setting-up the BlobDemo
- Unpack demodist.exe and run setup.exe.
- Start BlobDemo and connect to your database.
- Make sure that you have the right to create tables in the data source name (DSN) i.e. the database you want to use. If you are running as a the database IDENT sysadm, make sure that sysadm has created a databank. Use the
CREATE DATABANK statement.
- The first time you connect, the program asks if you want to create the BLOBTABLE, answer Yes and the program will create it for you.
That’s it – you now have a Mimer SQL Engine table to store your BLOBs in!
You can load BLOBs into your table in two ways:
To review a BLOB, double-click on the row that contains the BLOB. The BLOB opens in a viewer. By right-clicking on the viewer you can zoom the file contents.
- Click in the "drop-frame" to open the Get BLOB from File dialog box
- In Windows Explorer, drag the BLOB file(s) and drop them on the drop-frame.
Note: the demo only knows how to display BLOBs in .jpg .bmp .gif and .txt formats.
Right-click on a row to access the pop-up menu where you can display/delete/rename/save a BLOB.
Tip: You can start the program directly from a shortcut by supplying data source info. For example:
"C:\Documents and Settings\jaan\My Documents\adoblobdemo.exe" -dmimernio -usysadm -psysadm starts the program with the datasource = mimernio, the user = sysadm and the password = sysadm.
What’s behind this great little program?
Well, the program is written in Visual Basic and the source code is a complete VB project.
The SQL code for the BLOBTABLE that the program creates is:
create table BLOBTABLE(
NAME CHARACTER VARYING(64),
DATA BINARY LARGE OBJECT(2097152),
The Insertion Code
You need an ADO command object. You set the ActiveConnection to that object and, using parameter markers in your SQL statement, you set the CommandText using the following SQL statement:
"insert into BLOBTABLE values(?,?, current_timestamp, ? )"You use the Execute method of the command object supplying the 3 parameters as an array.
Private Sub blobinsert(b_name As String, blob() As Byte, b_type As String)Extraction Code – Even Simpler!
Dim com As ADODB.Command
Set com = New ADODB.Command
Set com.ActiveConnection = GconnObj
com.CommandText = "insert into BLOBTABLE values(?,?, current_timestamp, ? )"
com.Execute , Array(b_name, b_type, blob), adCmdText
You get yourself a record set. Set the ActiveConnection to the record set and set the SQL statement to select what you want. In the example below, we use the following SQL statement:
" select TYPE, data, cre_DATE ,octet_length(data)
from BLOBTABLE where NAME= 'somename' "
Note that for the column containing the BLOB, we find the length of it with
octet_length(data), we can then use that length in the call to the
The pblob variable would be defined as a dynamic byte array.
dim pblob() as byteYou can access non BLOB columns simply by doing
Private Sub blobread()
Dim sql As String, rs As ADODB.Recordset
sql = " select TYPE, data, cre_DATE ,octet_length(data)
from BLOBTABLE where NAME= 'somename' "
Set rs = New ADODB.Recordset
rs.ActiveConnection = GconnObj
file_type = Trim(rs(0))
filedate = rs(2)
plen = rs(3)
pblob = rs(1).GetChunk( plen)
file_type = rs(0)that's the first column of the record set.
Good luck and have fun!
- If this program creates the BLOB table, the maximum size of a BLOB file is 1900000 bytes (1.9 MB). If someone else creates the table, the BLOBS may be of any size.
- The drag and drop functionality is not complete.
- The grid that contains the BLOB rows is a VBlistview control. When making more complicated selections/dragging, it sometimes gets confused.
How to Get the Most Out of BLOBs and JDBC