=============
conn system/oracle
create directory IMG as 'C:\IMG';
-- I already add the oracle.gif and oraclelogo.gif files inside a c:\IMG
CREATE TABLE lob_table (filename VARCHAR2(128),blobdata BLOB);
CREATE OR REPLACE PROCEDURE LoadFILEIntoBLOB (myfilename IN VARCHAR2) IS
out_blob BLOB;
in_file BFILE := BFILENAME('IMG', myfilename);
blob_length INTEGER;
BEGIN
-- Obtain the size of the blob file
DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);
blob_length:=DBMS_LOB.GETLENGTH(in_file);
DBMS_LOB.FILECLOSE(in_file);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to out_blob.
INSERT INTO lob_table VALUES (myfilename, EMPTY_BLOB())
RETURNING blobdata INTO out_blob;
-- Load the image into the database as a BLOB
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE(out_blob);
DBMS_LOB.CLOSE(in_file);
COMMIT;
END;
/
exec LoadFileIntoBlob('oracle.gif');
--Ensure that picture in Loaded
SELECT filename,DBMS_LOB.GETLENGTH(blobdata)
lob_table;
FILENAME DBMS_LOB.GETLENGTH(BLOBDATA)
------------- ---------------------------------------------
oracle.gif 7868
Get the photo from JSP
<%
ServletOutputStream out1 = response.getOutputStream();
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
out1.write(buffer, 0, length);
}
in.close();
out1.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new Exception(""+e.getMessage());
}
catch(Exception e)
{
throw new Exception(""+e.getMessage());
}
%>
Get the photo from Servlet
package project1;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.driver.OracleResultSet;
public class DisplayPhoto extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=windows-1252";
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
// PrintWriter out = response.getWriter();
ServletOutputStream out = response.getOutputStream();
//out.println("");
//out.println("
//out.println("");
JDBConnection con = new JDBConnection();
Blob photo = null;
String query = " Select blobdata from lob_table ";
String sysdate= "";
try
{
PreparedStatement pst = con.conn.prepareStatement(query);
ResultSet rst = pst.executeQuery();
if (rst.next())
{
photo = rst.getBlob(1);
}
response.setContentType("image/gif");
InputStream in = photo.getBinaryStream();
int length = (int) photo.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1)
{
//System.out.println("writing " + length + " bytes");
out.write(buffer, 0, length);
}
in.close();
out.flush();
con.conn.close();
}
catch(SQLException e)
{
throw new ServletException(""+e.getMessage());
}
catch(Exception e)
{
throw new ServletException(""+e.getMessage());
}
//out.println("
Date: "+sysdate+"
");//out.println("");
//out.close();
}
}
package project1;
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.lang.*;
public class JDBConnection
{
public Connection conn=null;
public Statement stm=null;
public JDBConnection()
{
try
{
registerDB();
}
catch(Exception e)
{
System.out.println("Error...");
}
}
public void registerDB()throws Exception
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn=DriverManager.getConnection
("jdbc:oracle:thin:@michaelgeorgiou:1521:orcl","system","oracle");
stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}
}