• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to store & retrieve a video file from database?

 
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a requirement to store video files and retrieve them from database. I searched older threads, but couldn't find any help.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
#

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No need to use the specific Oracle implementation. Look for a good explanation on how to use BLOBS with jdbc.
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am still searching a good explanation on how to use BLOBS & CLOBS with jdbc.

Can any one provide a link for that?
 
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Paul Okeke
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please Any person the has solution to this should assist
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 child proofed my house but they still get in. Distract them with this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic