Handling Multimedia Content

SQL doesn’t provide mechanisms that are powerful enough to handle binary large objects, known as BLOBs. Fortunately, JDBC contains the necessary methods to insert and extract BLOBs.

Sending BLOBS

My next example is a simple command-line tool used to insert binary large objects in a table. You can use any kind of BLOB, including pictures, audio files, binary data, and texts. This program is quite simple to use. The tool prompts you for a database URL, a login, a password, the name of the table to be updated, the BLOB column name (which is the column that holds a BLOB), and the BLOB filename.

A row must exist in the table before trying to insert a BLOB. For example, a table of employees must contain a row for Jones before a picture can be inserted for this employee. To locate this row, the program also prompts for a column name and value, which represent search criteria. In the case of employee Jones, simply use name as the column name and Jones as the column value.

You can pass all parameters but the database URL, login, and password on the command line. In this case, the program uses the default URL, login, and password. Passing parameters on the command line is extremely convenient for inserting multiple BLOBs at once from a shell script.

Consider the following script, which contains batched commands. The script calls the BLOB program example several times in order to populate a database table with pictures:

java txblob -c employees pict name Jones /tmp/pictures/jones.jpg java txblob -c employees pict name Dupont /tmp/pictures/dupont.jpg java txblob -c employees pict name Duke /tmp/pictures/duke.jpg java txblob -c employees pict name Jack /tmp/pictures/jack.jpg ... ... 

The preceding batch command inserts the pictures of Jones, Dupont, Duke, and Jack in the table of employees. Note that the field that is updated with picture bytes should be of the SQL type LONGVARBINARY.

Listing 11-4 contains the source code for this example.

Listing 11-4: txblob.java

start example
import java.sql.*; import java.io.*; import java.util.*; /**  * Title:        txblob  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class txblob {   static DataInputStream kbd = new DataInputStream(System.in);   static String url = "jdbc:odbc:netbank";   static String driver = "sun.jdbc.odbc.JdbcOdbcDriver";   static String login = "dba";   static String passwd = "javabank";   static String filename = "";   static String tablename = "";   static String blobcolumnname = "";   static String selectcolumnname = "";   static String selectcolumnvalue = "";   static Connection curConn = null;   public static void main(String argv[]) throws IOException   {     String temp = "";     if (argv[0].equals("-c")) {       tablename = argv[1];       blobcolumnname = argv[2];       selectcolumnname = argv[3];       selectcolumnvalue = argv[4];       filename = argv[5];     } else {       System.out.println("Simple tool to insert BLOBS, by Bernard Van Haecke,        1996.\n");       System.out.print("Enter the url or [ENTER] for " + url  + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) url = temp;       System.out.print("Enter the login or [ENTER] for " +  login + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) login = temp;       System.out.print("Enter the passwd or [ENTER] for " +  passwd + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) passwd = temp;       System.out.print("\nEnter the table name : ");       System.out.flush();       tablename = kbd.readLine();       System.out.print("Enter the blob column name : ");       System.out.flush();       blobcolumnname = kbd.readLine();       System.out.print("Enter the row selection criteria  column name : ");       System.out.flush();       selectcolumnname = kbd.readLine();       System.out.print("Enter the row selection criteria  value : ");       System.out.flush();       selectcolumnvalue = kbd.readLine();       System.out.print("Enter the file name : ");       System.out.flush();       filename = kbd.readLine();     }     txblob session = new txblob();   }   public txblob() throws IOException   {     try {       Class.forName(driver);       curConn = DriverManager.getConnection(url, login,  passwd);     }     catch(java.lang.Exception ex) {       System.out.println("url : " + url);       System.out.println("login : " + login);       System.out.println("passwd : " + passwd);       ex.printStackTrace();       return;     }     processBlob();     finalize();   }   protected void finalize()   {     try {       curConn.close();     }     catch (SQLException ex) { }   }   private void processBlob() throws IOException   {     try {       java.io.File blobFile = new java.io.File(filename);       int blobFileLen = (int) blobFile.length();       java.io.InputStream fblob = new  java.io.FileInputStream(blobFile);       PreparedStatement myStmt = curConn.prepareStatement(        "UPDATE " + tablename + " SET " + blobcolumnname + " = ? WHERE " +        selectcolumnname + " = ?");       myStmt.setBinaryStream(1, fblob, blobFileLen);       myStmt.setString(2, selectcolumnvalue);       int res = myStmt.executeUpdate();       myStmt.close();     }     catch (SQLException ex) {       // Unexpected SQL exception.       System.out.println(ex);     }     catch (java.lang.Exception ex) {       // Got some other type of exception. Dump it.       ex.printStackTrace ();     }   } }
end example

Retrieving BLOBS

My next example is very similar to the previous one. It is a simple command-line tool to retrieve binary large objects from a table. It prompts for a database URL, a login, a password, the name of the table to be updated, the BLOB column name (which is the column that holds a BLOB), and the BLOB filename where this BLOB must be stored.

For example, if a table of employees contains a row for Jones and if a picture is available for this employee, it is possible to retrieve it. To locate this row, the program prompts for a column name and value, which represent search criteria. In the case of employee Jones, you can simply use name as the column name and Jones as the column value.

All parameters but the database URL, login, and password pass on the command line. In this case, the program uses the default URL, login, and password. This is extremely convenient for retrieving many BLOBs at once from a shell script.

Consider the script in Listing 11-5. Several batched calls to the program example are made in order to retrieve pictures from a database table.

Listing 11-5: Batch Command

start example
java rxblob -c employees pict name Jones /tmp/pictures/jones.jpg java rxblob -c employees pict name Dupont /tmp/pictures/dupont.jpg java rxblob -c employees pict name Duke /tmp/pictures/duke.jpg java rxblob -c employees pict name Jack /tmp/pictures/jack.jpg ... ...
end example

The batch command in Listing 11-5 retrieves the pictures of Jones, Dupont, Duke, and Jack from the table of employees and stores these pictures in different files. The filename and file type aren’t stored in the table. You could have stored the file type in the table by simply adding a file type record. In case the client application isn’t aware of the BLOB format, it is mandatory to store this type of information somewhere. It is possible to do so, but, in this case, special fields must be added to the table structure because it is considered extra information regarding BLOBs. Indeed, BLOBs are nothing more than untyped binary data.

It may be a good idea to hold the data type along with BLOBs, particularly when the information is to be extracted and sent to a Web browser. In this case, it is appropriate to store the BLOB’s Multimedia Internet Mail Extension (MIME) type in a specific field of the table so that the browser knows how to interpret the data (for example, should the browser display the data as a JPEG [Joint Photographic Experts Group] picture or MPEG [Motion Pictures Experts Group] movie file or play it as an .au [common audio file on UNIX machines] sound file).

Listing 11-6 contains the source code for this example.

Listing 11-6: rxblob.java

start example
import java.sql.*; import java.io.*; import java.util.*; /**  * Title:        rxblob  * Description: * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class rxblob {   static DataInputStream kbd = new DataInputStream(System.in);   static String url = "jdbc:odbc:netbank";   static String driver = "sun.jdbc.odbc.JdbcOdbcDriver";   static String login = "dba";   static String passwd = "javabank";   static String filename = "";   static String tablename = "";   static String blobcolumnname = "";   static String selectcolumnname = "";   static String selectcolumnvalue = "";   static Connection curConn = null;   public static void main(String argv[]) throws IOException   {     String temp = "";     if ((argv[0] != null) && (argv[0].equals("-c")))     {       tablename = argv[1];       blobcolumnname = argv[2];       selectcolumnname = argv[3];       selectcolumnvalue = argv[4];       filename = argv[5];     } else {       System.out.println("Simple tool to retrieve BLOBS, by Bernard Van Haecke,        1996.\n");       System.out.print("Enter the url or [ENTER] for " + url  + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) url = temp;       System.out.print("Enter the login or [ENTER] for " +  login + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) login = temp;       System.out.print("Enter the passwd or [ENTER] for " +  passwd + " : ");       System.out.flush();       temp = kbd.readLine();       if (!temp.equals("")) passwd = temp;       System.out.print("\nEnter the table name : ");       System.out.flush();       tablename = kbd.readLine();       System.out.print("Enter the blob column name : ");       System.out.flush();       blobcolumnname = kbd.readLine();       System.out.print("Enter the row selection criteria  column name : ");       System.out.flush();       selectcolumnname = kbd.readLine();       System.out.print("Enter the row selection criteria  value : ");       System.out.flush();       selectcolumnvalue = kbd.readLine();       System.out.print("Enter the file name : ");       System.out.flush();       filename = kbd.readLine();     }     rxblob session = new rxblob();   }   public rxblob() throws IOException   {     try {       Class.forName(driver);       curConn = DriverManager.getConnection(url, login, passwd);     }     catch(java.lang.Exception ex) {       System.out.println("url : " + url);       System.out.println("login : " + login);       System.out.println("passwd : " + passwd);       ex.printStackTrace();       return;     }     processBlob();     finalize();   }   protected void finalize()   {     try {       curConn.close();     }     catch (SQLException ex) { }   }   private void processBlob() throws IOException   private void processBlob() throws IOException   {     try {       java.io.File blobFile = new java.io.File(filename);       java.io.OutputStream fblob = new java.io.FileOutputStream(blobFile);       java.sql.Statement myStatement = curConn.createStatement();       ResultSet rs = myStatement.executeQuery("SELECT " + blobcolumnname +        " FROM " + tablename + " WHERE " + selectcolumnname + " = " +        selectcolumnvalue);       // we retrieve in 4K chunks       byte[] buffer = new byte[4096];       int size;       if (rs.next()) {         // fetch blob         java.io.InputStream strin =  rs.getBinaryStream(blobcolumnname);         for (;;)         {           size = strin.read(buffer);           if (size == 0)           {             break;           }           // Send the buffer to some output stream           fblob.write(buffer, 0, size);         }       }       else System.out.println("Row not found.");       myStatement.close();       rs.close();     }     catch (SQLException ex) {       // Unexpected SQL exception.       System.out.println(ex);     }     catch (java.lang.Exception ex) {       // Got some other type of exception. Dump it.       ex.printStackTrace ();     }   } }
end example



JDBC 3. 0. JAVA Database Connectivity
JDBC 3: Java Database Connectivity
ISBN: 0764548751
EAN: 2147483647
Year: 2002
Pages: 148

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net