Building Multimedia Repositories
Listing 1:
The following code (Steps 25) inserts MyResume.doc in the résumé column of sam_emp table.
Step 1: Create a directory object in Oracle.
Here¹s how to create a directory object called MY_FILES that represents C:\MY_DATA directory.
You must have the create directory privilege in Oracle.
create or replace directory
MY_FILES as 'C:\MY_DATA';
Step 2: Insert a row with an empty BLOB in your table and return the locater.
Step 3: Point to the Word file to be loaded from the directory created in Step 1 using bfile data type.
Step 4: Open the file and use the locater from Step 2 to insert the file.
Step 5: Close the file and commit the transaction.
declare
f_lob bfile;
b_lob blob;
begin
insert into sam_emp(empno,ename,resume)
values ( 9001, 'Samir',empty_blob() )
return résumé into b_lob;
f_lob := bfilename( 'MY_FILES', 'MyResume.doc' );
dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
dbms_lob.loadfromfile
( b_lob, f_lob, dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
commit;
end;
/
Listing 2: Building index to search documents
The steps listed below index all the Microsoft Word-formatted résumés stored in the résumé column to the sam_emp table. The résumés can then be searched using SQL.
Step 1: Add a primary key to your table if it does not exist. To make empno primary key of the sam_emp table execute the follow
ing command.
alter table sam_emp add constraint
pk_sam_emp primary key(empno);
Step 2: Get the privileges (ctxapp role) to create text indexes from administrators.
Step 3: Create the index with the appropriate filter object. Filters
determine how to extract text for document indexing from the
word processor, formatted documents as well as plain text.
See Oracle8i intermedia Text for complete list of filters.
create index ctx_doc_idx on sam_emp(résumé)
indextype is ctxsys.context parameters
('filter CTXSYS.INSO_FILTER');
Listing 3: Searching documents using SQL and JDBC
package package1;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
/**
This servlet searches documents stored in an Oracle8i database repository using SQL and JDBC. The hit list is displayed in an HTML table with hyperlinks. JDK 1.1.7 and an Oracle Thin JDBC 1.22 compliant driver used.
*
* @author Samir Shah
* @version 1.0
**/
public class MyServletCtx extends HttpServlet{
Connection cn;
public void init(ServletConfig parm1)
throws ServletException {
super.init( parm1);
try{
DriverManager.registerDriver(
(new oracle.jdbc.driver.OracleDriver()));
cn =DriverManager.getConnection
("jdbc:oracle:thin:@sshah:1521:o8i",
"scott", "tiger");
}
catch (SQLException se){se.printStackTrace();}
}
public void doGet(HttpServletRequest req,
HttpServletResponse res) throws IOException{
doPost(req,res);
}
public void doPost(HttpServletRequest req,
HttpServletResponse res) throws IOException{
PrintWriter out = res.getWriter();
res.setContentType("text/html");
//The term to search in resume column
String term = req.getParameter("term");
if (term == null)
term="security";
out.print("<html>");
out.print("<body>");
out.print("<H1>Search Result</H1>");
out.print("<table border=1 bgcolor=lightblue>");
out.print("<tr><th>ID#</th><th>Name</th></tr>");
out.print("<tr>");
try{
Statement st = cn.createStatement();
//search the term in resume column using SQL
String query =
"Select empno,ename from sam_emp" +
" where contains(resume,'"+term+"')>0";
ResultSet rs = st.executeQuery(query);
while (rs.next()){
out.print("<td>"+ rs.getInt(1)+"</td>");
out.print("<td>" +
"<A HREF=http://sshah:8080/" +
"servlet/MyServlet?term=" +
rs.getString(1) +
" target=Document>" +
rs.getString(2) +
"</A></td>");
out.print("</tr>");
}
out.print("</table>");
out.print("</body>");
out.print("</html>");
}//try
catch (SQLException se){se.printStackTrace();}
}
}
Listing 4
package package1;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.sql.*; //for oracle.sql.BLOB
/**
This class reads the entire document from the résumé LOB column. It takes one parameter, term, to search a specific employee from the sam_emp table and returns the document stored in that row.
* JDK 1.1.7, Oracle Thin JDBC 1.22 compliant driver
* with Oracle type extension classes (oracle.sql)
*
* @author Samir Shah
* @version 1.0
**/
public class MyServlet extends HttpServlet{
Connection cn;
public void doGet(HttpServletRequest req,
HttpServletResponse res)
{
try{
doPost(req,res);
}catch (IOException ie){ie.printStackTrace();}
}
public void init(ServletConfig parm1)
throws ServletException
{
super.init( parm1);
try{
DriverManager.registerDriver(
(new oracle.jdbc.driver.OracleDriver()));
cn =DriverManager.getConnection(
"jdbc:oracle:thin:@sshah:1521:o8i",
"scott", "tiger");
}
catch (SQLException se){se.printStackTrace();}
}
public void doPost(HttpServletRequest req,
HttpServletResponse res) throws IOException
{
InputStream is=null;
oracle.sql.BLOB blob=null;
res.setContentType("application/msword");
OutputStream os = res.getOutputStream();
String term = req.getParameter("term");
if (term==null)
term="9001";
try{
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery
("Select resume from sam_emp"+
" where empno="+term);
while (rs.next()){
blob=((OracleResultSet)rs).getBLOB(1);
is=blob.getBinaryStream();
}
int pos=0;
int length=0;
byte[] b = new byte[blob.getChunkSize()];
while((length=is.read(b))!= -1){
pos+=length;
os.write(b);
}
}//try
catch (SQLException se)
{
se.printStackTrace();
}
finally {
is.close();
}
}
}
No comments:
Post a Comment