Forum Moderators: open
When I use the SQL Server Administrator Design tool I can select a VARCHAR but it is limited to 8000 bytes. There must be another way. Does anyone know how?
Not sure why you would want to store BLOB in a db, you should just store the path of the object in the db. You can set a field to be text which I think is unlimited.
Agreed - most DBA's wonder why this functionality was ever even included as most SQL professionals would NEVER use this feature. There is simply no advantage to it - it wastes disk space, RAM, makes the image loading slower, and generally slows down the entire database.
As macrost suggested, the ideal way to accomplish what you're ultimately trying to do is to use a component to upload the file to the server and write the path to the file into the database as a char or varchar field. Then whenever you need to pull down that file just retrieve the path.
If your reason for considering this was to protect the file so it cannot be directly linked to, you can always write a little ASP script that retrieves the file from a directory outside of your web root and does a binary write to output the file to the user's browser. Then you could use a session variable to determine if the user was "allowed" to see the picture. But seriously, stay away from SQL blobs.
Agreed - most DBA's wonder why this functionality was ever even included as most SQL professionals would NEVER use this feature. There is simply no advantage to it - it wastes disk space, RAM, makes the image loading slower, and generally slows down the entire database.
As a Sql Server Proffesional i may say you are NOT correct, you talk about wasting disk space, thats the biggest crap i ever heard, you think that an image outside your HD takes less space?
No Advantages? Think about:
-relational integrity on your image data
-security reasons
-version management
And if you think performance is an issue, it takes only 3 lines of code to write a simple caching mechanism.
As a Sql Server Proffesional i may say you are NOT correct, you talk about wasting disk space, thats the biggest crap i ever heard, you think that an image outside your HD takes less space?
As a SQL Server DBA and someone who knows how to spell "Professional" I wonder who declared you one :P You're the first person I've ever heard who would actually store images in the DB (aside from state/federal agencies who store scanned documents like articles of incorporation, but if you'll recall those sites tend to be notoriously slow). But hey, everyone has their own style I guess. Why you would want to waste your SQL server's space and resources to store pictures is beyond me. Something else important to keep in mind here is the fact that if the user is asking HOW to store data as a blob, they're not going to know much about database optimization to do it effectively anyway.
Combine that with the fact that he's using ASP, I made the logical assumption that he's using Microsoft SQL server 7 or 2000, which as you know doesn't like to let go of memory once it's been reserved for a query. I'm also assuming that this guy doesn't have the hardware required to make blob storage an effective method - if there is such a thing - since again, if you have to ask the "how do I do it" question you're probably not an expert on the hardware and software aspects of DB optimization in addition to learning how to optimize your queries. No insult intended to the poster for not knowing of course, we're all still learning :)
In any case, I didn't see you answer the poster's question.
I have apps that marshall very large documents which are greater then 8000 characters into a binary field. I have another that marshalls large audit trails into a binary field.
I've also had to work with many Site Server implementations and that application actually marshalls Dictionary Objects which can contain collections of other dictionary objects into an Image field as part of the Shopping Cart and Transaction mechanisms.
You'll also find the use of binary fields in enterprise applications that purposely do not want to expose certain bits of data in a plain text field. I've seen this with Accounting Systems that marshall General Ledger Codes into binary fields.
raja4 - I can post a c# example if that's what your looking for?
raja4 - I can post a c# example if that's what your looking for?
He was asking about ASP.Net - this is a web-based programming language. C# code won't help him, nor wil the useability of blobs in desktop applications. We're talking about web scripting here ... I still maintain that there isn't any valid reason to do this on the web.
-->He was asking about ASP.Net
Yes, I'm very much aware of what was asked for. I've already delivered this type of functionality in an asp.net web application; that is why I offered up the code. I used c#, sql server 2K and a stored procedure to marshall large xml documents into a binary field due to the fact that the varchar data type couldn't handle the size.
-->We're talking about web scripting here
Not really, this would be a compiled binary and C# is a perfectly acceptable language to use within the context of an asp.net web page. So is vb.net for that matter.
-->this is a web-based programming language
asp.net is a technology, not a language. C# can be used in .net desktop applications and asp.net web applications. In fact your objects should be designed with scalibility in mind so that they can be used in both environments.
raja4,
You can select binary when you design your table, that should do the trick.
