aspose file tools*
The moose likes JDBC and the fly likes How to store & retrieve a video file from database? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to store & retrieve a video file from database?" Watch "How to store & retrieve a video file from database?" New topic
Author

How to store & retrieve a video file from database?

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I have a requirement to store video files and retrieve them from database. I searched older threads, but couldn't find any help.


kunal
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
#

THESE THINGS ARE NOT CLEAR TO ME. . .

try {
# ps = conn.prepareStatement(sql);
#
# ps.setInt(1, id);
# OraclePreparedStatement oraps = (OraclePreparedStatement) insert;
# oraps.registerReturnParameter(2, OracleTypes.BLOB);
# ps.executeUpdate();
#
# rs = oraps.getReturnResultSet();
# if (!rs.next())
# throw new RuntimeException("Could not locate clob");
#
# Blob blob = rs.getBlob(1);
# blob.setBytes(1, data);
# }
# catch (SQLException ex) {
# ... Handle the exception as you like
# }
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

No need to use the specific Oracle implementation. Look for a good explanation on how to use BLOBS with jdbc.


OCUP UML fundamental and ITIL foundation
youtube channel
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I am still searching a good explanation on how to use BLOBS & CLOBS with jdbc.

Can any one provide a link for that?
Paul Okeke
Ranch Hand

Joined: May 16, 2009
Posts: 58
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?


OCJP 1.6
Paul Okeke
Ranch Hand

Joined: May 16, 2009
Posts: 58
Please Any person the has solution to this should assist
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18874
    
    8

Paul Okeke wrote: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'm confused. There's a long and detailed answer by Martin Vajsar earlier in this thread which is exactly and precisely that. So why do you ask for another one? What's wrong with Martin's answer?
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

As Paul said, your question has been answered in this post.
Also listen to Scott's advise in this thread.

If it helps: You can follow JGuru's JDBC trail. Exercise 21 and 22 focus on BLOBS.

http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html#JDBC2018
Exercises:
21: Storing an Image in a Blob
22: Retrieving and Displaying an Image from a Blob
Simbu chak
Greenhorn

Joined: Nov 11, 2011
Posts: 1
Connection conn = getConnection();
Statement stmt = conn.createStatement();
File file = new File("Your Video File Location and file name");
FileInputStream fileInputStream = new FileInputStream(file);
ByteArrayOutputStream out = new ByteArrayOutputStream();
while (fileInputStream.available()>0) {
out.write(fileInputStream.read());
}
byte[] jpgByteArray = out.toByteArray();
String driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
String INSERT_PICTURE = "insert into MyPictures(name, video) values (?, ?)";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(INSERT_PICTURE);
ps.setString(1, "Test1");
ps.setBytes(2,jpgByteArray);
ps.executeUpdate();
ResultSet rs = stmt.executeQuery("SELECT video FROM MyPictures");
while (rs.next()) {
File file1 = new File("Mention the destination File Location and FileName");
FileOutputStream foStream = new FileOutputStream(file1);
ByteArrayOutputStream oStream = new ByteArrayOutputStream();
if(rs.getString("photo") != null){
InputStream is2 = rs.getBinaryStream(1);
byte[] bytes=new byte[is2.available()];
is2.read(bytes);
oStream.write(bytes);
oStream.writeTo(foStream);
}
conn.commit();
}finally {
ps.close();
}

NOTE :
If you are using MySQL DB then include code in the my.ini file as follows,This is for increase the storeage file size

wait_timeout=28800
interactive_timeout = 28800
max_allowed_packet=64M
socket = /tmp/mysql.sock
skip-locking
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to store & retrieve a video file from database?