Hot answers tagged blob
22
Store in the database with a blob
A disadvantage is that it makes your database files quite large and possibly too large to back up with your existing set up. An advantage is integrity and atomicity.
Store on the filesystem with a link in the database
I've come across such horrible disasters doing this, and it scares me that people keep suggesting it. ...
11
If going for oracle, take a look at dbfs and Secure Files.
Secure Files says it all, keep ALL your data safe in the database. It is organized in lobs. Secure Files is a modernized version of lobs, that should be activated.
dbfs is a filesystem in the database. You can mount it similar like a network filesystem, on a Linux host. It is real powerful. See ...
8
You could do that in Oracle 11gR2 with expdp and the REMAP_DATA option.
Create a function in a package that takes a blob as argument, and returns null (or an empty blob perhaps).
Call expdp as usual, adding:
REMAP_DATA=SCHEMA.TAB.BLOB_COLUM:SCHEMA.PACKAGE.YOUR_FUNCTION
Short example (schema: mat):
create or replace
package remap as
function ...
5
Although it partly depends on the application/environment (people included), I'd go for the blob.
Keeping everything in the database means replication works for file data. You'd need a separate mechanism to synchronise FS files.
In some applications, the filesystem shouldn't be modified anyway. For example, on a production website, I'd avoid ever using the ...
5
BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB
As for TINY/MEDIUM/LONG; how big is your data?
TINYBLOB : max 255 bytes
BLOB : max 65,535 bytes
MEDIUMBLOB : max 16,777,215 bytes
LONGBLOB : max 4,294,967,295 bytes
In some small minority of cases TINYBLOB may suffice, ...
4
Back in the day, Microsoft hyped up the ability to store images (and similar blob data types) in the database. The was a cool new feature of SQL Server 2000 (I am pretty sure it was 2000, not 7.0) and many people jumped on the bandwagon.
Storing BLOBS in the database has advantages and disadvantages:
On one hand, all your data and related images or ...
4
For postgres:
It's actually straight foreward. There is a BYTEA type that can be used for storing binary strings. Per default, there are no build in utilites like the ones mentioned for MS or Oracle. So storing lots of big files and retrieving them can get tedious. You also need to do the conversion of the files within the application (like with a ...
4
I think the right answer here depends a lot on your application, and how important those documents are.
For a document management system, or a system where recoverability of the stored documents is critical (so most things financial, HR or CRM related), storing documents inline or using your favourite DB vendor's proprietary document tech seems like the ...
3
I want to add my experience here as to the tradeoffs. In PostgreSQL, at least, the performance impacts are quite minimal in terms of the db server. Large blobs are stored in separate files, not in the main heap tables so as to move them out of the way of operations that may count large numbers of records. Other dbs may do something similar.
The major ...
3
Just do
SELECT ItemId
FROM MyTable
WHERE Value = 0xAAFF
You are converting the varchar representation to varbinary(max) which is not correct.
SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.
3
Which db to choose? I would choose PostgreSQL having worked with MySQL and PostgreSQL but it is worth noting how different the databases are. I will say I have frequently been impressed (and only rarely disappointed) by what sort of abuse I can throw at PostgreSQL only to watch things be handled gracefully.
In your specific case, however, there may be ...
3
Consider using de-duplication if you have lots of identical XML - documentation link.
I wrote a huge unit test, but it turns out that SecureFile LOBs are always stored in a Lob Segment outside the row!
1
Is it possible to use a stored procedure to read the file inside the blob column, loop on each line and insert the data in a table?
Definitely.
However, I would consider rethinking this plan.
A SQL Server database generally isn't a great place to be storing BLOBs, particularly given that you're just going to turn around and process them into row data ...
1
I wonder why any kind of DB is needed. It sounds like you'd like it if these were just files, but that the number of them and their small size are problematic.
There are two strategies that can help here, one is to use a few levels of directories to help partition the files into smaller clusters.
Now, some file systems may have inode issues or become very ...
1
There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor.
If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this ...
1
I have faced similar situation for a procedure call with input Blob parameter. Same piece of code was working just fine on 11.2.0.2, but began to throw
"java.io.IOException: I/O Exception: Connection reset by peer: socket write error"
on 11.2.0.3. I have downgraded from ojdbc6.jar to ojdbc14.jar+ocrs12.jar now it is working. I don't know why but it seems ok ...
1
The max text repl size options exists to limit how much LOB data can be added to a replicated column. If you turn off the LOB size limit and a user inserts a large amount of LOB data, latency will increase and replication will come to a crawl. The max text repl size option can be used to prevent this.
1
If you use mysqldump, you can specify the hexadecimal format for BLOB data
mysqldump -u... -p... --hex-blob --all-databases ...
Here is the help on hex-blob in mysqldump
$ mysqldump --help | grep -A 1 "blob" | head -2
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
1
Here is a general way of getting a table with bytea data from postgres into a similar Oracle table with a blob.
It should be trivial to adapt my test to your real tables, and you should probably increase the size of the 'chunks' that I used to keep the output readable, from 20 to something more like the 2000 limit for string literals (4000/2 as each byte ...
Only top voted, non community-wiki answers of a minimum length are eligible
