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.
spiral2007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick UpsonPrincipal Operations EngineerCommented:
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)
0
spiral2007Author 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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nick UpsonPrincipal Operations EngineerCommented:
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
0
jimyXCommented:
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.
0
spiral2007Author 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.
0
aikimarkCommented:
@spiral2007

How did this Firebird database come into your possession?
0
spiral2007Author 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.
0
jimyXCommented:
> 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?
0
aikimarkCommented:
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.
0
spiral2007Author 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
0
jimyXCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.