Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Facing problem in loading embadded image from xls sheet into oracle database

 
G.T. Reddy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I have been facing one problem in reading an embadded image from excel sheet and loading that image into the oracle database as a blob . When I am reading this image using jxl (API as shown below code) some junk value is read into the database.So if any body has some suggestions just help me out..

I�m writing the code which I am using. and I�m not able to attach the the excel as it is very big but just to let u know it uses EMBED("MSPhotoEd.3","") in the formula bar of excel sheet to embed image in the excel sheet. Here i don't know the image type also.

code:
-----

import jxl.Cell;
import jxl.Image;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

// reading the drawing

Workbook workbook = Workbook.getWorkbook("excelfilename");
Sheet sheet = workbook.getSheet("CUSTOMER");
Image img = sheet.getDrawing(0);

// putting it in byte array
byte[] imageData = img.getImageData();

System.out.println("byty array length : "+imageData.length);// it is giving some valid value

// inserting into database blob object

prepareStatement.setBytes(1, imageData);

while loading these bytes into database (as blob) it is not giving any error and loading properly I assume. After loading I am trying to fetch this blob and creating jpg image.while displaying it is not displaying that image.


//this is one more way of loading byte array into database.still we are not able to display the image in the browser.

public int loadImage(InputStream byte[] imageBytes, String estimateId, int length)
throws ApplicationException, FatalException {

sdcLogger.logDebugMessage("Start of loadImageTest method");
PreparedStatement pstmt = null;
Connection con = null;
ResultSet blobRs = null;
int returnValue = 1;
java.sql.Blob blob = null;
Statement stmt;
OutputStream outStream;
try {
sdcLogger.logDebugMessage("estimateId :" + estimateId);
sdcLogger.logDebugMessage("length :" + length);
con = DatabaseHelper.prepareConnection();
con.setAutoCommit(false);
pstmt = con.prepareStatement(SDCQueries.UPDATE_EMPTY_BLOB);
pstmt.setString(1, estimateId);
pstmt.executeUpdate();
stmt = con.createStatement();
blobRs =
stmt.executeQuery(
"Select part_image from Estimate "
+ "where estimate_id ="
+ estimateId
+ " for update");
blobRs.next();
blob = blobRs.getBlob(1);
outStream =((OracleBlob) blob).getBinaryOutputStream();
outStream.write(imageBytes);
/*
//byte[] chunk = new byte[fis.available()];
//int i = -1;
//while ((i = fis.read(chunk)) != -1) {
// outStream.write(chunk, 0, i);
// }
*/
//Close the input and output stream.
fis.close();
outStream.close();
con.commit();
sdcLogger.logDebugMessage("**********************************");
sdcLogger.logDebugMessage("image uploaded successfully");
sdcLogger.logDebugMessage("************************************");

} catch (SQLException sqlException) {
sdcLogger.logError(
"An error occured in search method of RefPartSearchDAO",
"getEstimateHistory method",
sqlException);
returnValue = -1;

} catch (Exception exception) {
sdcLogger.logError(
"Fatal Exception occured",
"getEstimateHistory method",
exception);
returnValue = -1;
} finally {
try {
if (blobRs != null) {
blobRs.close();
}

if (pstmt != null) {
pstmt.close();
}
if (pstmt != null) {
pstmt.close();
}
if (con != null) {
con.close();
}
} catch (SQLException sqlException) {

sdcLogger.logError(
"An error occured in search method of RefPartSearchDAO",
"getEstimateHistory method",
sqlException);
} catch (Exception exception) {
sdcLogger.logError(
"Fatal Exception occured",
"getEstimateHistory method",
exception);
}
} //finally

return returnValue;
} //end of loadimage


if any body can come with some suggestions about how can i do it.It will be quite helpful fro me.

Thanks,
GTR
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic