How to Convert 1000's of BLOBs to Image Files
Hello Experts,
I have been tasked with the challenge of exporting data contained in an existing medical practise managment database application (Lets call this application "A") into a form which can be imported into a new practice management application ("B"). There is no facility for exporting data out of "A", but by importing a backup file of "A's" database into a newly created MS SQL database, I have gained access to the raw data and I have worked out the mechanics of pulling the raw data and parsing it for import into"B".
BUT my problem is that "A" stored scans of images and documents (growth charts, lab studies, letters and coorespondence, etc) as BLOBS. There are several thousands of these BLOBS. I cannot import the blob data into "B" as it is designed to store them in flat file form (.tiff or .pdf preferred).
Does anyone know of a tool or application for accomplishing the conversion of blob data fields to image files? I have heard that Visual Fox Pro may be capable of this, but I don't know VFP so that doesn't help a heck of a lot. (Any volunteers? :-) )
Note: "A" is constructed with MS SQL 2000. "B" is built with MySQL.
PS. I am not a programmer by anyone's definition, so an off-the-shelf tool would be much preferred, but I'll take all the help I can get.
Thanks in advance.
I have been tasked with the challenge of exporting data contained in an existing medical practise managment database application (Lets call this application "A") into a form which can be imported into a new practice management application ("B"). There is no facility for exporting data out of "A", but by importing a backup file of "A's" database into a newly created MS SQL database, I have gained access to the raw data and I have worked out the mechanics of pulling the raw data and parsing it for import into"B".
BUT my problem is that "A" stored scans of images and documents (growth charts, lab studies, letters and coorespondence, etc) as BLOBS. There are several thousands of these BLOBS. I cannot import the blob data into "B" as it is designed to store them in flat file form (.tiff or .pdf preferred).
Does anyone know of a tool or application for accomplishing the conversion of blob data fields to image files? I have heard that Visual Fox Pro may be capable of this, but I don't know VFP so that doesn't help a heck of a lot. (Any volunteers? :-) )
Note: "A" is constructed with MS SQL 2000. "B" is built with MySQL.
PS. I am not a programmer by anyone's definition, so an off-the-shelf tool would be much preferred, but I'll take all the help I can get.
Thanks in advance.
ASKER
rw,
My problem limited only to the MS SQL side of the equation. Exporting is the issue. Once I can convert the MS SQL BLOB data to file, importing is a non-issue.
Thanks
My problem limited only to the MS SQL side of the equation. Exporting is the issue. Once I can convert the MS SQL BLOB data to file, importing is a non-issue.
Thanks
ok tell me is that field really BLOB?, cause SQL have TEXT and IMAGE datatypes, I think BLOB is ORACLE and MySQL standard....
btw, for an example I exported
from following query
Select logo from pub_info where pub_Id='1389' and I got this in txt file
474946383961C2001D00B30F00
from following query
Select logo from pub_info where pub_Id='1389' and I got this in txt file
474946383961C2001D00B30F00
actually I just realized I maybe waisting your time, I have knowledge of messing with image data type but I certainely am not any master...
ASKER
rw,
Your knowledge exceeds mine, so jump in. I am going to have to log into the client's server to verify the data type. My teminology of BLOB may be inacurate. All I know is that the image data must be in some sort of (binary?) form as there is no flat file structure for the images. (Believe me, I have searched and searched). I will log in this evening and try to determine the exact specs of the image table and fields within.
Thanks for you input so far.
Your knowledge exceeds mine, so jump in. I am going to have to log into the client's server to verify the data type. My teminology of BLOB may be inacurate. All I know is that the image data must be in some sort of (binary?) form as there is no flat file structure for the images. (Believe me, I have searched and searched). I will log in this evening and try to determine the exact specs of the image table and fields within.
Thanks for you input so far.
>>My teminology of BLOB may be inacurate.<<
It is correct. A BLOB (Binary Large Object) in MS SQL Server 2000 are all data types that are of data type text, ntext and image.
>>Does anyone know of a tool or application<<
MS SQL Server 2000 comes with a utility callled TextCopy.exe
It is correct. A BLOB (Binary Large Object) in MS SQL Server 2000 are all data types that are of data type text, ntext and image.
>>Does anyone know of a tool or application<<
MS SQL Server 2000 comes with a utility callled TextCopy.exe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony
>>It is correct. A BLOB (Binary Large Object) in MS SQL Server 2000 are all data types that are of data type text, ntext and image.<<
just a clarification.
There is no BLOB, CLOB or LOB in MSSQL, yes you are right in essense Text, nText and Image are Binary Large Objects, but the keyword BLOB, CLOB and LOB are used in Oracle, and thats what I was pointing.
rw3admin
>>It is correct. A BLOB (Binary Large Object) in MS SQL Server 2000 are all data types that are of data type text, ntext and image.<<
just a clarification.
There is no BLOB, CLOB or LOB in MSSQL, yes you are right in essense Text, nText and Image are Binary Large Objects, but the keyword BLOB, CLOB and LOB are used in Oracle, and thats what I was pointing.
rw3admin
ASKER
imran_fast,
Thanks for your in-depth response in the use of TextCopy.exe and for taking the time to suggest using a stored procedure complete with examples. (Experts-Exchange is a life-saver!)
I will have access to the client's database this evening and will give your solution a try. My email is skipb(at)t3-tech.com if you think of anything else useful, I would appreciate you forwarding your ideas.
I am still unclear on how I will step through the rows of the table.
Thanks again, experts
Thanks for your in-depth response in the use of TextCopy.exe and for taking the time to suggest using a stored procedure complete with examples. (Experts-Exchange is a life-saver!)
I will have access to the client's database this evening and will give your solution a try. My email is skipb(at)t3-tech.com if you think of anything else useful, I would appreciate you forwarding your ideas.
I am still unclear on how I will step through the rows of the table.
Thanks again, experts
rw3admin,
>>There is no BLOB, CLOB or LOB in MSSQL ...<<
BLOB is a generic DBMS term. I was not referring to a specific data type. If you look up BLOB in BOL it points to the section: "ntext, text, and image" and throughout BOL it refers to "BLOB (text, ntext, or image data)".
cbdrako99,
>>My email is skipb(at)t3-tech.com if you think of anything else useful, I would appreciate you forwarding your ideas. <<
Don't do this. This is against EE Guidelines.
>>There is no BLOB, CLOB or LOB in MSSQL ...<<
BLOB is a generic DBMS term. I was not referring to a specific data type. If you look up BLOB in BOL it points to the section: "ntext, text, and image" and throughout BOL it refers to "BLOB (text, ntext, or image data)".
cbdrako99,
>>My email is skipb(at)t3-tech.com if you think of anything else useful, I would appreciate you forwarding your ideas. <<
Don't do this. This is against EE Guidelines.
yes Anthony, but I was just pointing that Oracle have datatypes named as BLOB CLOB and LOB datatypes...
I just wanted to clear it so you dont think I was an oximoron... (whatever that means)... :)
I just wanted to clear it so you dont think I was an oximoron... (whatever that means)... :)
rw3admin