The Great

Greenhorn
+ Follow
since Jul 28, 2001
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by The Great

rename the .cmd file to .bat file
22 years ago
hi,
I am not quite sure whether Ms-Access supports Blob, Clob Data types. But I know that Oracle 8.x supports
I can show u a few examples of how to use them with Oracle
/**
* @author Robin Paul (The Great)
*
* Development Environment : JDeveloper 2.0
* Name of the Application : LOBSample.java
* Creation/Modification History :
*
* srangan.in 10-JAN-1998 Created
*
* Overview of Application :
*
* This sample illustrates access and manipulation of CLOB and BLOB columns
* using JDBC. Oracle's JDBC driver, provides API to perform selects, updates
* inserts and deletes from LOB columns.
*
* The sample illustrates the above operations on CLOB and BLOB columns in the
* AIRPORT_LOB_DETAILS table. It inserts sample .gif and .txt file contents,
* into the LOB columns for the chosen airport. If LOB data already exists for
* the chosen airport, it retrieves and displays them. It also
* illustrates manipulation of LOB columns, by allowing users to enter text to
* be appended to the CLOB column.
*
*
*/
import java.io.*;
import java.sql.*; //Package for JDBC classes
//Package for Oracle Extensions to JDBC
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class LOBSample {
Connection m_connection; //Database Connection Object
LOBFrame m_GUI; // The GUI handler for the sample
// Sample file names
static final String s_MapFileName = "map.gif";
static final String s_SugBookFileName = "suggestionbook.txt";
/**
* Constructor. Initializes GUI
**/
public LOBSample() {
try {
m_GUI = new LOBFrame(this);
} catch (Exception ex) {
m_GUI.putStatus("Error in GUI initialization\n"+ex.toString());
}
}
/**
* Main entry point for the class. Instantiates the root frame,
* sets up the database connection and populates the JTable with AIRPORTS rows
**/
public static void main(String[] args) {
LOBSample lobs = new LOBSample(); // Instantiate root frame
lobs.m_GUI.setVisible(true);
lobs.dbConnection(); // Setup db connection
if (lobs.m_connection != null)
lobs.populateAirports(); // Populate the JTable with all airports rows
}
/**
* Dispatches the GUI events to the appropriate method, which performs
* the required JDBC operations. This method is invoked when event occurs
* in the GUI (like table Selection, Button clicks etc.).
**/
public void dispatchEvent (String p_eventName) {
//Dispatch Event
if (p_eventName.equals("Load Sample Files"))
loadSamples(m_GUI.getSelectedCode());
else if (p_eventName.equals("Add Suggestions")) {
String l_suggestions = m_GUI.getSuggestionText();
if (!l_suggestions.equals("CANCEL")) {
addSuggestions(m_GUI.getSelectedCode(),l_suggestions.toCharArray());
m_GUI.m_sugArea.append(new String(l_suggestions));
}
} else if (p_eventName.equals("Airport Selected in Table"))
airportSelected(m_GUI.getSelectedCode());
else if (p_eventName.equals("EXIT"))
exitApplication();
}

/**
* Creates a database connection object using JDBC. Please substitute the
* database connection parameters with appropriate values in
* ConnectionParams.java
**/
public void dbConnection() {
try {
m_GUI.putStatus("Connecting to the Database. Please wait..");
//Load the Oracle JDBC Driver and register it.
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Form the database connect string(TNSNAMES entry) as a name-value pair
//using the connection parameters as specified in ConnectionParams.java
String l_dbConnectString =
"(DESCRIPTION=(ADDRESS=(HOST="+ConnectionParams.s_hostName+")"+
"(PROTOCOL=tcp)(PORT="+ConnectionParams.s_portNumber+"))"+
"(CONNECT_DATA=(SID="+ConnectionParams.s_databaseSID+")))";
//The following statement creates a database connection object
//using the DriverManager.getConnection method. The first parameter is
//the database URL which is constructed based on the connection
//parameters specified in ConnectionParams.java.
//The URL syntax is as follows:
//"jdbc racle:<driver>:@<db connection string>"
//<driver>, can be 'thin' or 'oci8'
//<db connect string>, is a Net8 name-value, denoting the TNSNAMES entry
m_connection = DriverManager.getConnection(
"jdbc racle:thin:@"+l_dbConnectString,
ConnectionParams.s_userName, ConnectionParams.s_password);
//Sets the auto-commit property for the connection to be false.
//By default it is true.
m_connection.setAutoCommit(false);
m_GUI.putStatus("Connected to "+ConnectionParams.s_databaseSID+
" Database as "+ConnectionParams.s_userName);
} catch(Exception ex){ //Trap SQL errors
m_GUI.putStatus("Error in Connecting to the Database "+ex.toString());
}
}
/**
* Queries all rows from the AIRPORTS table and populates the JTable with
* the returned rows
**/
public void populateAirports() {
try {
m_GUI.appendStatus("\nPopulating Airports. Please wait...");
// Statement object for issuing SQL statements
Statement l_stmt = m_connection.createStatement();
// Execute the query that returns all airport rows
ResultSet l_resultSet = l_stmt.executeQuery(
"SELECT AIRPORT_CODE, DESCRIPTION, NAME"+
" FROM AIRPORTS, CITIES"+
" WHERE CTY_ID = ID");
// Loop through the result set and populate JTable with all airports
while (l_resultSet.next()) {
//Retrieve column values for this row
String l_code = l_resultSet.getString(1);
String l_desc = l_resultSet.getString(2);
String l_city = l_resultSet.getString(3);
m_GUI.addToJTable(l_code, l_desc, l_city); //Insert into Jtable
}
l_stmt.close(); // Close the statement, which also closes the ResultSet
m_GUI.putStatus("Connected to database and retrieved all airport rows");
m_GUI.appendStatus("\nPlease Choose an airport ");
} catch (SQLException ex) { // Trap SQL errors
m_GUI.putStatus("Error Querying Airports table: \n"+ex.toString());
}
}
/**
* Creates a new row for the selected airport in AIRPORT_LOB_DETAILS.
* It then loads the sample files into the LOB columns, using JDBC.
**/
void loadSamples(String p_airportCode) {
try {
m_GUI.putStatus("Creating row for airport in AIRPORT_LOB_DETAILS.. ");
// Form a SQL statement for inserting a row into AIRPORT_LOB_DETAILS
// The LOB column values are initialized to empty in this step, and
// will be loaded in the steps below.
PreparedStatement l_pstmt=m_connection.prepareStatement(
"insert into AIRPORT_LOB_DETAILS (AIRPORT_CODE,AIRPORT_MAP,"+
"AIRPORT_SUG_BOOK) values(? , empty_blob() , empty_clob())");
l_pstmt.setString(1,p_airportCode); // Bind AIRPORT code
l_pstmt.execute(); // Execute SQL statement
l_pstmt.close(); // Close statement
m_GUI.appendStatus("Created.\nLoading <map.gif> into BLOB column for airport...");
// Retrieve the row just inserted, and lock it for insertion of the
// LOB columns.
Statement l_stmt = m_connection.createStatement();
ResultSet l_lobDetails = l_stmt.executeQuery(
"SELECT AIRPORT_MAP,AIRPORT_SUG_BOOK FROM AIRPORT_LOB_DETAILS "+
"WHERE AIRPORT_CODE = '"+p_airportCode+"' FOR UPDATE");
// Retrieve BLOB and CLOB streams for AIRPORT_MAP and AIRPORT_SUG_BOOK
// columns, and load the sample files
if (l_lobDetails.next()) {
// Get the BLOB locator and open output stream for the BLOB
BLOB l_mapBLOB = ((OracleResultSet)l_lobDetails).getBLOB(1);
OutputStream l_blobOutputStream = l_mapBLOB.getBinaryOutputStream();
// Open the sample file as a stream for insertion into the BLOB column
File l_mapFile = new File(s_MapFileName);
InputStream l_sampleFileStream=new FileInputStream(l_mapFile);
// Buffer to hold chunks of data to being written to the BLOB.
// In Oracle8.1.5 JDBC drivers a method getBufferSize() is available
// in the BLOB class, that returns the optimal buffer size
byte[] l_buffer = new byte[10* 1024];
// Read a chunk of data from the sample file input stream, and write the
// chunk to the BLOB column output stream. Repeat till file has been
// fully read.
int l_nread = 0; // Number of bytes read
while ((l_nread= l_sampleFileStream.read(l_buffer)) != -1) // Read from file
l_blobOutputStream.write(l_buffer,0,l_nread); // Write to BLOB
// Close both streams
l_sampleFileStream.close();
l_blobOutputStream.close();
// Load the suggestion book sample file into the CLOB column
m_GUI.appendStatus("Done\nLoading <suggestionbook.txt> into CLOB column ..");
// Get the CLOB locator and open an output stream for the CLOB
CLOB l_sugBookCLOB = ((OracleResultSet)l_lobDetails).getCLOB(2);
Writer l_clobWriter = l_sugBookCLOB.getCharacterOutputStream();
// Open the sample file as a stream for insertion into the CLOB column
File l_sugbookFile = new File(s_SugBookFileName);
FileReader l_sugFileReader = new FileReader(l_sugbookFile);
// Buffer to hold chunks of data to being written to the CLOB.
// In Oracle8.1.5 JDBC drivers a method getBufferSize() is available
// in the CLOB class that returns the optimal buffer size
char[] l_cbuffer = new char[10* 1024];
// Read a chunk of data from the sample file input stream, and write the
// chunk into the CLOB column output stream. Repeat till file has been
// fully read.
l_nread = 0;
while ((l_nread= l_sugFileReader.read(l_cbuffer)) != -1) // Read from File
l_clobWriter.write(l_cbuffer,0,l_nread); // Write to CLOB
// Close both streams
l_sugFileReader.close();
l_clobWriter.close();
m_GUI.putStatus("Done Loading sample files");
m_GUI.appendStatus("\nRetrieving and displaying sample files..");
// Retrieve and display the LOB data just inserted
drawBLOB(l_mapBLOB,p_airportCode);
writeCLOB(l_sugBookCLOB,p_airportCode);
m_GUI.putStatus("Done loading and displaying LOB data");
}
// Close Result Set and statement
l_lobDetails.close();
l_stmt.close();
} catch (Exception ex) { // Trap SQL errors
m_GUI.putStatus("Error loading sample files for the selected airport");
m_GUI.appendStatus("\n"+ex.toString());
}
}
/**
* Accepts suggestions from an JOptionPane and appends the entered
* suggestions to the CLOB column
**/
void addSuggestions(String p_airportCode, char[] p_suggestions) {
try {
m_GUI.putStatus("Appending entered suggestions to CLOB column. Please wait...");
Statement l_stmt = m_connection.createStatement(); // Prepare a statement
// Retrieve the CLOB locator and also lock the row, for the selected
// Airport
ResultSet l_lobDetails = l_stmt.executeQuery(
"SELECT AIRPORT_SUG_BOOK "+
"FROM AIRPORT_LOB_DETAILS "+
"WHERE AIRPORT_CODE='"+p_airportCode+"' FOR UPDATE");
// Obtain the CLOB locator and append suggestions
if (l_lobDetails.next()) {
// Get the CLOB locator
CLOB l_clob = ((OracleResultSet)l_lobDetails).getCLOB(1);
// Append the entered suggestions to the end of the CLOB data.
// The first parameter to plsql_write, is the offset from which to start
// writing, and the second parameter is the data to be written.
// plsql_length(), returns the length of the data in the CLOB column
l_clob.plsql_write(l_clob.plsql_length()+1,p_suggestions);
m_GUI.appendStatus("Done");
}
l_stmt.close(); // Close statement which also closes open result sets
} catch(SQLException ex) {
m_GUI.putStatus("Error appending suggestions to the CLOB column");
m_GUI.appendStatus("\n"+ex.toString());
}
}
/**
* This method is called when a row is selected from the airport JTable.
* It checks if there exists data in AIRPORT_LOB_DETAILS for the selected
* airport. If there exists data, it calls drawBLOB and writeCLOB to
* display the data
**/
void airportSelected(String p_airportCode) {
try {
m_GUI.putStatus("Retrieving LOB details for selected airport..");
// Create a SQL statement
Statement l_stmt = m_connection.createStatement();
// Query AIRPORT_LOB_DETAILS for the selected AIRPORT
ResultSet l_lobDetails = l_stmt.executeQuery(
"SELECT AIRPORT_MAP,AIRPORT_SUG_BOOK FROM AIRPORT_LOB_DETAILS "+
"WHERE AIRPORT_CODE='"+p_airportCode+"'");
// Check if LOB columns exist
if (l_lobDetails.next()) {
// LOB details exist
// Display airport map and suggestion book (LOB details)
drawBLOB(((OracleResultSet)l_lobDetails).getBLOB(1),p_airportCode);
writeCLOB(((OracleResultSet)l_lobDetails).getCLOB(2), p_airportCode);
m_GUI.putStatus("Done retrieving and displaying LOB details");
} else {
// No LOB details
m_GUI.m_loadButton.setEnabled(true);
m_GUI.putStatus("No airport map and suggestion book exist for selected airport");
m_GUI.appendStatus("\nPress <Load Sample Files> to load LOB details");
}
l_lobDetails.close();
l_stmt.close();
} catch (Exception ex) { // Trap SQL errors
m_GUI.putStatus("Error retrieving LOB Details for the selected airport");
m_GUI.appendStatus(ex.toString());
}

}
/**
* Retrieve the BLOB data from input BLOB column into a local file,
* and draws the image
**/
void drawBLOB(BLOB p_blob, String p_airPCode) {
try {
// Open a stream to read the BLOB data
InputStream l_blobStream = p_blob.getBinaryStream();
// Open a file stream to save the BLOB data
FileOutputStream l_fileOutStream = new FileOutputStream(p_airPCode+".gif");
// Read from the BLOB data input stream, and write to the file output
// stream
byte[] l_buffer = new byte[10]; // buffer holding bytes to be transferred
int l_nbytes = 0; // Number of bytes read
while ((l_nbytes = l_blobStream.read(l_buffer)) != -1) // Read from BLOB stream
l_fileOutStream.write(l_buffer,0,l_nbytes); // Write to file stream
// Flush and close the streams
l_fileOutStream.flush();
l_fileOutStream.close();
l_blobStream.close();
m_GUI.drawMap(p_airPCode); // Draw retrieved image to GUI
} catch (Exception ex) { // Trap SQL and IO errors
m_GUI.putStatus("Error in retrieving and drawing map for selected airport");
m_GUI.appendStatus("\n"+ex.toString());
}
}
/**
* Retrieve the character data from the input CLOB, save in a
* StringBuffer and display the StringBuffer contents in GUI
**/
void writeCLOB(CLOB p_clob, String p_airPCode) {
try {
// Open a stream to read CLOB data
Reader l_clobStream = p_clob.getCharacterStream();
// Holds the CLOB data when the CLOB stream is being read
StringBuffer l_suggestions = new StringBuffer();
// Read from the CLOB stream and write to the stringbuffer
int l_nchars = 0; // Number of chanracters read
char[] l_buffer = new char[10]; // Buffer holding characters being transferred
while ((l_nchars = l_clobStream.read(l_buffer)) != -1) // Read from CLOB
l_suggestions.append(l_buffer,0,l_nchars); // Write to StringBuffer
l_clobStream.close(); // Close the CLOB input stream
m_GUI.m_sugArea.append(new String(l_suggestions)); // Display in GUI
} catch (Exception ex) { // Trap SQL and IO errors
m_GUI.putStatus("Error in getting and drawing CLOB for the airport, "+p_airPCode+":");
m_GUI.appendStatus(ex.toString());
}
}
/**
* Close the database Connection and exit the application
**/
public void exitApplication() {
try {
m_GUI.putStatus("Closing the connection....please wait.....");
if (m_connection != null)
m_connection.close(); //Close the m_connection object.
} catch(SQLException ex){ //Trap SQL Errors
m_GUI.putStatus(ex.toString());
}
System.exit(0); //Exit the application
}
}