This article describes how to work with BLOB data in dotConnect Universal.

• LOB basics
• Working with BLOB data
• Working with CLOB data
• Working with NCLOB data
• Additional information

LOB basics

LOB stands for Large OBject, a stream of data stored in a database. LOBs are represented as UniBlob objects. There three two kinds of LOB data type:

1. BLOB datatype stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. Corresponding UniDbType is Blob.
2. The CLOB datatype stores single-byte and multibyte character data using database character set. Corresponding UniDbType is Clob.
3. The NCLOB datatype stores unicode or national character set data. Corresponding UniDbType is NClob.

You can use any of the data types with dotConnect Universal.

Working with BLOB data

Binary data is generally represented as stream of bytes, or buffers. dotConnect Universal allows to manipulate BLOB data in most convenient ways. You can work with UniBlob.Value property or use UniBlob.Read() and UniBlob.Write() methods to handle BLOB data. Both ways are presented in the samples below. Note that when UniBlob.LobType property is UniDbType.Blob, the UniBlob.Value is treated as array of bytes (byte[]), whereas UniDbType.Clob represents UniBlob.Value as string.

The next sample routines show how to upload a file from hard disk to server and download it back. To execute these routines you have to create a table that is described as follows:

CREATE TABLE pictures (
  id INT,
  PicName VARCHAR(20),
  Picture BLOB
)

static void UploadBlob(UniConnection myConnection) 
{ 
  FileStream fs = new FileStream("D:\\Tmp\\test.bmp", FileMode.Open, FileAccess.Read); 
  BinaryReader r = new BinaryReader(fs); 
  myConnection.Open(); 
  UniBlob myLob = new UniBlob(myConnection,UniDbType.Blob); 
  int streamLength = (int)fs.Length; 
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength); 
  UniCommand myCommand = new UniCommand(
    "INSERT INTO pictures (id, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection); 
  UniParameter myParam = myCommand.Parameters.Add("Pictures", UniDbType.Blob); 
  myParam.Value = myLob; 
  try 
  { 
    Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected."); 
  } 
  finally 
  { 
    myConnection.Close(); 
    r.Close(); 
    fs.Close(); 
  } 
}


static void DownloadBlob(UniConnection myConnection) 
{
  UniCommand myCommand = new UniCommand("SELECT * FROM pictures", myConnection);
  myConnection.Open();
  UniDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  try {
    while (myReader.Read()) {
      int colind = myReader.GetOrdinal("Picture");
      if (!myReader.IsDBNull(colind)) {
        UniBlob myLob = myReader.GetUniBlob(colind);
        string FN = myReader.GetString(myReader.GetOrdinal("PicName"));
        FileStream fs = new FileStream("D:\\Tmp\\" + FN + ".bmp", FileMode.Create);
        byte[] b = new byte[myLob.Length];
        myLob.Read(b, 0, (int)myLob.Length);
        fs.Write(b, 0, (int)myLob.Length);
        fs.Close();
        Console.WriteLine(FN + " downloaded.");
      }
    }
  }
  finally {
    myReader.Close();
    myConnection.Close();
  }
}

Public Sub UploadBlob(ByVal myConnection As UniConnection)
  Dim fs As FileStream = New FileStream("D:\Tmp\test.bmp", FileMode.Open, FileAccess.Read)
  Dim r As BinaryReader = New BinaryReader(fs)
  myConnection.Open()
  Dim myLob As UniBlob = New UniBlob(myConnection, UniDbType.Blob)
  Dim streamLength As Int32 = fs.Length
  myLob.Write(r.ReadBytes(streamLength), 0, streamLength)
  Dim myCommand As UniCommand = New UniCommand("INSERT INTO Pictures (id, PicName, Picture) VALUES(1,'pict1',:Pictures)", myConnection)
  Dim myParam As UniParameter = myCommand.Parameters.Add("Pictures", UniDbType.Blob)
  myParam.Value = myLob
  Try
    Console.WriteLine(myCommand.ExecuteNonQuery() & " rows affected.")
  Finally
    myConnection.Close()
    r.Close()
    fs.Close()
  End Try
End Sub


Public Sub DownloadBlob(ByVal myConnection As UniConnection)
  Dim myCommand As New UniCommand("SELECT * FROM Pictures", myConnection)
  myConnection.Open()
  Dim myReader As UniDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default)
  Try
    While myReader.Read()
      Dim colind As int= myReader.GetOrdinal("Picture");
      If Not myReader.IsDBNull(colind) Then
        Dim myLob As UniBlob = myReader.GetUniBlob(colind)
        Dim FN As String = myReader.GetString(myReader.GetOrdinal("PicName"))
        Dim fs As FileStream = New FileStream("D:\Tmp\" + FN + ".bmp", FileMode.Create)
        Dim w As BinaryWriter = New BinaryWriter(fs)
        w.Write(myLob.Value)
        w.Close()
        fs.Close()
        Console.WriteLine(String.Concat(FN, " downloaded."))
      End If
    End While
  Finally
    myReader.Close()
    myConnection.Close()
  End Try
End Sub

Working with CLOB data

Working with CLOB data generally is same as working with BLOB data. The difference is in representation of Value property. For CLOB data type when you read from UniBlob.Value property, you get strings. The data is transparently decoded so you do not have to take care about its character set. If you need raw data however you can use streaming capabilities of UniBlob, that being UniBlob.Read and UniBlob.Write methods. Note that since UniBlob.Value is a read-only property, you can write data to LOB only with UniBlob.Write method, and no encoding is performed.

For example, consider this table definition.

CREATE TABLE clobtable (
  Id INT,
  Name VARCHAR(30),
  Value CLOB
)

If you need to perform simple tasks like fetch record set in most cases you do not need to know about UniBlob. When calling UniDataReader.GetValue on fetch you get array of bytes for BLOB column and string for CLOB one. In addition you can use UniDataReader.GetChars, UniDataReader.GetBytes to get pieces of LOB value.

For example,

...
UniCommand myCommand = new UniCommand("SELECT * FROM clobtable", myConnection);
UniDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
  Console.WriteLine(reader.GetValue(2));
...

...
Dim myCommand As UniCommand = new UniCommand("SELECT * FROM clobtable", myConnection)
Dim reader As UniDataReader = myCommand.ExecuteReader()
while (reader.Read())
  Console.WriteLine(reader.GetValue(2))
end while
...

You can do the same thing using UniBlob object directly.

...
UniCommand myCommand = new UniCommand("SELECT * FROM clobtable", myConnection);
UniDataReader reader = myCommand.ExecuteReader();
UniBlob clob;
while (reader.Read()) {
  clob = reader.GetUniBlob(2);
  Console.WriteLine(clob.Value);
}
...

...
Dim myCommand As UniCommand = new UniCommand("SELECT * FROM clobtable", myConnection)
Dim reader UniDataReader = myCommand.ExecuteReader()
Dim clob As UniBlob
while (reader.Read())
  clob = reader.GetUniBlob(2)
  Console.WriteLine(clob.Value)
end while
...

As we see first code cut was shorter. So, usually, you need to access UniBlob directly in more complicated cases (e.g using Oracle temporary LOBs, advanced streaming operations).

Working with NCLOB data

NCLOB type represents text data in national character set. You have to use this BLOB type when data requires some transformation. For Oracle and MS SQL Server, dotConnect Universal uses database encoding. The MySQL server does not have suitable BLOB type, so the data provider converts data to Unicode. For PostgreSQL, NCLOB is just a synonym for CLOB.

Additional information

Keep in mind that UniBlob class is not supported for OleDB, ODBC, DB2 and MS Access. Instead you can use GetBytes method of UniDataReader object.

The UniBlob functionality is limited for dotConnect for Oracle 3.50 and older versions. Use dotConnect for Oracle 4.00 and higher.

See Also

UniBlob Class  | Devart.Data.Universal Namespace  | dotConnect Universal articles