You can store binary files in BLOB's in the database. Most database designers, though, do not put large files in a database since they tend to outweigh the structured data. For example, if you have a 100 tables that are under 10 megs, and a table with gigabytes of data, backing of the entire database means you need to be able to backup both the 100 tables and the giant table. Most designers put large files on disk somewhere which is why you unlikely to find much discussion of putting them in a database.
The most efficient way to create a BLOB might depend on your database. For Oracle, I've posted a way to insert a blob here on JavaRanch.
Athough putting binary data into the database has some overhead, it has some benefits as well. The most important factor is whether you need to be able to backup and recover your data reliably. Youtube doesn't have to worry much if it loses a video or two (or a thousand), but if you store medical video recordings, you may have to be able to restore all your data in a consistent fashion, to the same point in time. If that is the case, database storage is your way.
On our current project we generate some reports (PDF files and such) and were discussing the storage with client's IT department. They wanted to have it all in the database, so as to reduce the complexity of the administration of the application. This way they don't have to train their employees in backing up and restoring the file system, and, more importantly, they know that if the database is properly backed up, nothing would be missed. Also, when they decided to replicate the data for further protection, no special consideration was necessary for the file system of our application -- they have everything in the database.
I came to like this scenario. No need to hassle with the files, no need to search and remove files whose master records were deleted in the DB, no need to delete files created in a transaction that was rolled back. Much easier this way.
The example I've posted was tested with Oracle 10.2 and corresponding driver. It is based on Oracle documentation. It will not work with non-Oracle DB or driver and might not work with earlier versions.
To understand the example, you need to know that Blobs (and Clobs) are handled differently from other kinds of data. When you select a access Blob and read it from a ResultSet, you'll actually get just some kind of a "handle" (or "pointer") to the actual data. When you then actually access Blob data, the drivers goes again to the database using the provided handle and retrieves the actual data.
When you write data to the Blob, the mechanism is similar - the Blob instance only keeps a handle and when you write data into it, it sends them to the database.
Now, there are fundamentally two ways how to insert a new Blob into Oracle database:
1) Create the Blob using Connection.createBlob(), and insert it into the target table using ResultSet or insert command. This uses standard JDBC calls, but in Oracle data for this blob are stored in user's temporary data area. When the blob is inserted into the target table, its data are copied from user area into its final destination corresponding to the target table.
2) Insert into the table a new, empty blob. Oracle provides the empty_blob() SQL function for this. Then obtain the handle of the newly created blob and fill it up with your data. This way the data are written directly to the target table, avoiding the step that copies them from user's temp space in the process. (If you're going to store video files, you'll probably benefit from this, as video files tend to be rather large.)
This is achievable using standard JDBC calls, if you re-read newly created record into a ResultSet to obtain the Blob handle. However, Oracle database and Oracle JDBC drivers allow you to retrieve the handle of the Blob at the time it is inserted into the target table, using the RETURNING clause (I don't actually know whether this clause is an SQL standard or ORACLE specific dialect). Oracle JDBC driver has specific methods for this (more on this later).
So now let's see the code:
1) At line 3, the SQL statement with RETURNING clause is specified. The "returning data into ?" part of the query specifies that the value of column data resulting from the insert operation should be stored into a bind variable (in PL/SQL you would have actual variable name instead of "?" here).
2) Lines 14-15 first casts the statement into OraclePreparedStatement. We need to do this to be able to use the Oracle specific methods, and we can use this because we know we're using Oracle JDBC driver. Then the specific Oracle method is used to inform the driver that the second bind parameter will actually obtain the value from the RETURNING clause whose type will be BLOB.
3) Line 18 obtains the data returned by the RETURNING clause. Oracle JDBC driver provides the data conveniently as a ResultSet.
4) Lines 22 and 23 then obtain the returned Blob handle and write data to the Blob. Nothing special here actually.
Everything other is a standard JDBC code. If you don't understand those, you need to read some JDBC tutorials.
I still maintain putting large video files in a database should be avoided if possible. The performance overhead of pulling a binary file from a database versus disk is extremely high and should not be overlooked. I'm sure YouTube does not store videos in a relational database. Storing and caching videos requires special consideration in design to account for performance.
Please let somebody help with an explanation on how to insert into and retrieve from database table (column) whose type is either clob or blob.
I have a table that holds information about my registered users including the User;s passport.
I want these passport to be into the database instead of the other way round.
Am using MySQL 5.1.28.
I also understands that some GUI Tool allows you to add images to the database and also view such images. But the question is WHAT IS THE SQL STATEMENT FOR THIS TYPE OF OPERATION?