Monthly Archive for April, 2008

I’m currently migrating some data from an old Access database to a new MySQL database. So I have to write a converter application due to the incompatible data types in these RDBMS’s. More precisely the BLOB fields (OLE objects) within the Access DB should be transformed into string fields (varchar) in the MySQL DB. But how can you transform that data if the database connections are established by ODBC?

Here I’d like to give you a conversion method that takes an instance of an OdbcDataReader and the zero-based index of the field according to the current fetched row. At first I’m calling GetBytes with zero’s and NULL’s to determine the field-size of the Long Binary. By the way this purpose is really scarcely described in the MSDN documentation! After this I’m recalling GetBytes to retrieve the actual bytes. Finally the byte array is converted into a real string object. And of course, usually you have to check if index >= 0 and so on. But I omit that issue for reason of clarity.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

string GetLongBinary(OdbcDataReader reader, int index)
{
    // is field NULL?
    if (reader.IsDBNull(index))
        return string.Empty;
 
    // is field empty?
    long fieldSize = reader.GetBytes(index, 0, null, 0, 0);
    if (0 == fieldSize)
        return string.Empty;
 
    // read field content and convert to string            
    byte[] buffer = new byte[fieldSize];
    reader.GetBytes(index, 0, buffer, 0, buffer.Length);
    return Encoding.Default.GetString(buffer);
}