We help IT Professionals succeed at work.

Decompression of BLOBS in Firebird and migrate to SQL server

I am looking for a suggestion how can I decompress Blob field in firebird in order to be able to open the file (pdf, JPG, doc etc). Then I would like to import the files in SQL server.
Comment
Watch Question

Top Expert 2011
Commented:
Blob fields under firebird (if stored as binary data) and MS SQL (varBinary) are stored in binary form which enables moving across different DBs or extract to the original form.

So it is a matter of connecting to both DBs and iterate and read from Firebird and write to MS Sql.

May be you need to clarify your current situation:

Is it Delphi you are using and which version?
Are both, Firebird (FDB) and MS Sql exist in the same workstation and you can connect to both of them?
Do you have any limitation in connecting to Firebird and MS Sql at the same time?
Can you connect freely to Firebird and MS Sql, or there are privileges limits?
Can you create identical table from Firebird in MS Sql to move your records or the data will be scattered over existing tables?
Nick UpsonPrincipal Operations Engineer

Commented:
if you actually want to access the ? file, that is stored as a blob currently you will need to use whatever method was used to place it into the table (e.g. delphi)

Author

Commented:
Hi,
I still looking how to export the blob data from Firebird.
I have a column with a context of data (in hexadecimal and  in text like "xœάΌX\[²?ڐ„ΰ @ξΑƒ'hπ...")  and I have an other column with name Is_compress and all rows have the value 'Y'. I want to retrieve all blobs to files(.doc,.pdf,.jpg etc). I tried several methods to convert blob to varbinary but I can't.
1)Can I export the blobs into a drive in regular type
2)If the file is compressed what can i do to decompress them
Nick UpsonPrincipal Operations Engineer

Commented:
1) you need to know how the blob was written, it could have been encrypted

2) one you have a file then run winzip or 7zip, they should be able to handle it
Top Expert 2011

Commented:
You need to know how the data got into that blob field, so you reverse the method of storing to make restore possible.

Many options are available to store BLOB data, combining of encode/compress/encrypt make it impossible to guess what is the original data.

Do you know the method used to store the data?
Also can you get the file type (.pdf/.jpg/.doc/.bin/.txt/...etc) from the other fields?
BLOB does not maintain the file attributes, most importantly, the file type and name. That will be the next challenge.

Would you give some info. for what I have asked in my last post please.

Author

Commented:
I don't know how the blob was written because I try to migrate this database and i have no idea, which is the method used to store the data.

Also I have the file type and name on other database for each blob data,so I can find them.

I can't create identical table from Firebird in SQL Server to move my records because the type which I can have in SQL Server is image or varbinary.
Top Expert 2014

Commented:
@spiral2007

How did this Firebird database come into your possession?

Author

Commented:
A client of ours gave the Firebird database to us which stores data of another application. Now our client wants us to migrate those data into another database (MSSQL) since he has change software vendors.
Top Expert 2011

Commented:
> I can't create identical table from Firebird in SQL Server to move my records because the type which I can have in SQL Server is image or varbinary.

AFAIK varbinary is the counterpart of Firebird's BLOB.
So, creating the table is not a problem, reading what you put in there will be the problem.
If the data is not in a binary form (the original form) then you need to figure out what operations applied on it to reverse and extract to the target files. We are assuming it is not the original form, but it could be in a binary form. Have you tried to extract any file from the Firebird DB and attach the file extension and test if it works?
Top Expert 2014

Commented:
What kind of application is it?
Do the non-BLOB data fields indicate the type/nature of the BLOB data?

Does your client have the right to do this?  You should make sure of this before you run afoul of digital rights laws.

Author

Commented:
Of course the client has the right to export his data. The data belongs to the client and not to the Software Vendor.  
Yes we know whether a blob is a pdf, jpeg or word document.
Can we use the Firebird ODBC Driver to export the blobs into the C drive? If yes how can we do that?

Best Regards
Top Expert 2011

Commented:
You could have got a solution by now, if you provided the details I requested before:

Is it Delphi you are using and which version?
Are both, Firebird (FDB) and MS Sql exist in the same workstation and you can connect to both of them?
Do you have any limitation in connecting to Firebird and MS Sql at the same time?
Can you connect freely to Firebird and MS Sql, or there are privileges limits?
Can you create identical table from Firebird in MS Sql to move your records or the data will be scattered over existing tables?