helpinghand
search
needassistance
 
How To
Work with BLOBs and Mimer SQL in Windows
Categories: Programming Examples, SQL
Introduction

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
  1. You must have Mimer SQL Engine 9.1 or later installed.
  2. You must have a Mimer SQL Engine database server running.
  3. You must have a database IDENT that you can connect as. Use the CREATE IDENT statement.
  4. The IDENT must have access to a databank where he/she can create a table. Use the GRANT TABLE statement.
Getting the Program

All you have to do is click here to download it. (File name demodist.exe)

Installing and Setting-up the BlobDemo
  1. Unpack demodist.exe and run setup.exe.
  2. Start BlobDemo and connect to your database.
  3. 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.
  4. 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!

Using BlobDemo

You can load BLOBs into your table in two ways:
  • 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.
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.

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.

BLOBTABLE

The SQL code for the BLOBTABLE that the program creates is:
create table  BLOBTABLE(
NAME CHARACTER VARYING(64),
TYPE CHARACTER(10),
CRE_DATE TIMESTAMP(0),
DATA BINARY LARGE OBJECT(2097152),
primary key(NAME));

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)
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
Exit Sub
End Sub
Extraction Code – Even Simpler!

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 getchunk method.
The pblob variable would be defined as a dynamic byte array.
dim pblob() as byte
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
rs.Open sql
file_type = Trim(rs(0))
filedate = rs(2)
plen = rs(3)
pblob = rs(1).GetChunk( plen)
rs.Close
You can access non BLOB columns simply by doing
file_type = rs(0)
that's the first column of the record set.

Restrictions
  • 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.
Good luck and have fun!

Links
How to Get the Most Out of BLOBs and JDBC

Last updated: 2002-08-09

 

Powered by Mimer SQL

Powered by Mimer SQL