October 23, 2007 at 7:34 am
Jeff,
Just don't blindly state "no BLOBs in the database". At times your application needs to store binary content. Our entire application (inciteKnowledge)(http://www.kadient.com/) is a SaaS solution storing binary documents (Word docs, PowerPoint slides, etc.) For each of these we have to save images (preview images). Trying to save these in the file system with the synchronization, 2-phase commit, backup, recovery, etc. issues would be a nightmare. I know, I architected just such a system back in the mid 80s -- when databases couldn't really handle BLOBs.
October 24, 2007 at 12:11 pm
JohnG,
I used your sample and it worked to extract the BLOB, but I am unable to open it in the original program.
The way the BLOBs were inserted was via an embedded object in an access form. Is there something more that I need to do to strip out anything Access may have added to make the object visible in the form?
They are all PDFs, and if I open the object from in access and resave it with acrobat, it is able to be opened, but I have over 1,200 records done this way and I need a script to help me get them into the file system.
Your script looked so promising in that it actually saves the file and it looks to be the same file size that I am getting by using select DATALENGTH(COLUMN_NAME)
Thank you!
October 24, 2007 at 12:37 pm
I also tried it the BCP way with the same result - the BLOB being extracted, the same size as obtained with DATALENGTH, but unreadable.
I'm thinking this has to have something to do with the object having been embedded with Access, and there needs to have something stripped off before it is readable...
I hope someone has seen this before... thanks!
October 24, 2007 at 12:39 pm
Joseph,
1. Glad to see that the script logic works. Yes, the file size (exact bytes) should be equal to the DATALENGTH value of the VARBINARY or IMAGE column.
2. As for why the binary, once extracted, is not able to be opened by the binary's original application, all I can say is that it is probably some "wrapper" stuff that Access is putting around it. To confirm, compare the original file with an extracted one using some sort of binary/hex viewer. If different, then you'll have to get help from others who know more about Access and imbedded objects. Sorry, Microsoft Access is not in my skills list.
October 24, 2007 at 1:32 pm
I had an issue (as noted earlier) with the BCP solution. It worked fine with PDF binary store but not for any other file format - tif for example.
The fix I found was to remove the -n parameter (which applies the native data type) and, when prompted, choose an image type of size 0 as opposed to 4 which it defaults to.
Thia may help with your problem but it does sound like Access is trying to be too helpfull 🙁
October 24, 2007 at 8:11 pm
Thanks for the feedback... I've not had the same nightmare with the file system as you, though. Maybe I've just been lucky, so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2007 at 7:13 am
Jeff,
(clarification)
The "nightmare" is not with the file system per se, but with all of the logic that must be developed managing items (BLOBs, etc.) in the file system with the corresponding meta-data being stored in a "real" database management system (DBMS). Note than both items must be kept in sync as to the end-user it is all one "transaction". Issues such as 2-phase commit, some sort of [recovery] logging mechanism, a backup mechanism, a recovery mechanism, performing "rollback", "roll forward", etc., etc. In essence, you're [re-]desiging a database management system.
True, if you have [non-customer] static data, e.g., images to be shown on a static web page, then the file system is OK. But if the application is storing customer data, e.g., X-ray images, then 100% data integrity is paramount.
The choices made are all part of the overall system design. And that's what us professionals are paid to do correctly. As for "BLOBs" or "no BLOBs" -- one size doesn't fit all.
October 25, 2007 at 7:59 am
I found out how many bytes Access is adding to the beginning of the file (and to the end, but that doesn't matter with PDF, as it only reads up the the "%%EOF" tag).
The problem I'm having now is instructing SQL to strip off those bytes when exporting.
I tried datalength(substring(EMBEDDEDOBJECT,14953,datalength(embeddedobject)-14953)) but that only gives me 8000, since that is the max row size... apparently the substring function isn't following the out-of-row link for the image data, and only giving 8000 bytes.
Is there another function for image data that lets you specify an offset to start/end at?
Thanks!
October 26, 2007 at 7:14 am
SUBSTRING will return incorrect values from the older SQL Server TEXT and IMAGE data types as it will stop at 8,000 for TEXT (and probably 4,000 for NTEXT). However, SUBSTRING will work correctly against the new SQL Server 2005 data types of VARCHAR(MAX) and NVARCHAR(MAX). Having to perform a SUBSTRING against CLOBs in both SS2000 and SS2005, I love the new data types. There are numerous reasons to switch and it is painless. See the BOL for details.
As for returning a "chunk" of IMAGE data see the section titled Retrieving Parts of ntext, text, or image Values in the BOL.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply