0 Replies Latest reply: Dec 13, 2012 8:51 AM by brheitner RSS

    Problem with Updating BLOB Column with OracleDefaultConnection

    brheitner
      Hi,

      I am trying to update a table BLOB column using a Database Stored Java Procedure in Oracle Database 10.1.2 (Java 1.4). When I pass over the database credentials (username, password, database connection, database server) the BLOB column gets updated correctly. When I update with either OracleDefaultConnection (if I change the code to use import oracle.jdbc.* or use the "jdbc:default:connection" or "jdbc:default:connection:" with java.sql.* packages it updates the BLOB column to NULL). There are no java, SQL or PL/SQL errors that are returned and the value returned is that it updated 1 row. In testing, I found that it was updating the BLOB column to null unless I don't use the default oracle connection. The default oracle connection works for other columns within the same update.
      Are there any known issues with Oracle 10g (10.1.2) Database with a database stored procedure using OracleDefaultConnection and updating/inserting BLOB contents to a column?

      Here is the Java Database Stored Procedure (NOTE: I changed the server name to "prodserver", "devserver", "testserver" for anonymity, )

      CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DBABlob"
      AS
      import java.io.File;
      import java.io.FileInputStream;
      import java.io.FileNotFoundException;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;

      import oracle.jdbc.OraclePreparedStatement;

      +public class DBABlob {+
      static Connection conn;

      +public static String storeBlob (String directory, String filename, String table, String blobColumn, String where, String delimeter, String dbaUser, String dbaPassword, String dba) {+
      conn = null;
      PreparedStatement ps = null;
      int rowCount = -1;
      long totalBytes = -1;
      String dbaServer = "";

      +try {+
      Class.forName("oracle.jdbc.driver.OracleDriver");
      +} catch (ClassNotFoundException e) {+
      e.printStackTrace();
      return returnError(e);
      +}+

      +try {+
      +if ((dbaUser==null||dbaUser.trim().length()==0)||(dbaPassword==null||dbaPassword.trim().length()==0)||(dbaPassword==null||dbaPassword.trim().length()==0)) {+
      System.out.println("DEFAULT DATABASE CONNECTION");
      conn = DriverManager.getConnection("jdbc:default:connection");
      +}+
      +else {+
      +if (dba.toUpperCase().indexOf("PROD")!=-1) {+
      dbaServer = "prodserver";
      +}+
      +else if (dba.toUpperCase().indexOf("DEV")!=-1) {+
      dbaServer = "devserver";
      +}+
      +else if (dba.toUpperCase().indexOf("TEST")!=-1) {+
      dbaServer = "testserver";
      +}+
      System.out.println("DBA SERVER:"dbaServer);+
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"dbaServer+":1521:"+dba, dbaUser, dbaPassword);+
      +}+

      +} catch (SQLException e) {+
      return returnError(e);
      +}+
      OraclePreparedStatement pstmtUpdate = null;
      +try {+
      pstmtUpdate = (OraclePreparedStatement)conn.prepareStatement("update "table+" set "+blobColumn+" = ? where "+where);+
      +} catch (SQLException e) {+
      return returnError(e);
      +}+
      directory = directory.trim();
      +if (   !directory.endsWith("/") && !directory.endsWith("\\")) {+
      directory = directorydelimeter;+
      +}+

      File blob = new File(directoryfilename);+
      FileInputStream in = null;

      +// the cast to int is necessary because with JDBC 4 there is+
      +// also a version of this method with a (int, long)+
      +// but that is not implemented by Oracle+
      +try {+
      in = new FileInputStream(blob);
      +} catch (FileNotFoundException e) {+
      e.printStackTrace();
      +}+

      +try {+
      pstmtUpdate.setBinaryStream(1, in, (int)blob.length()); /*, (int)blob.length()*/
      totalBytes = blob.length();
      rowCount = pstmtUpdate.executeUpdate();
      conn.commit();
      pstmtUpdate.close();
      conn.close();

      +} catch (SQLException e) {+
      return returnError(e);
      +}+
      return "UPDATED "rowCount+" ROWS. ("+totalBytes+" BYTES)";+
      +}+

      +public static String returnError (Exception e) {+
      StringBuffer errorString = new StringBuffer();
      int errCnt = e.getStackTrace().length;
      errorString.append(e.getMessage()"|");+
      for (int x=0;x<errCnt;x+){+
      +errorString.append(e.getStackTrace()[x].toString());+
      +if(x<errCnt-2) {+
      errorString.append("|");
      +}+
      +}+
      return errorString.toString();
      +}+

      +public static void main(String[] args) {+
      DBABlob dBABlob = new DBABlob();
      dBABlob.storeBlob("F:\\forms\\test\\scandocs\\patil\\", "2012_12_06_16_29_14.jpg", "SL16SCANIMG", "BLIMAGE", "CDIMAGE='TEST'", "/", "", "", "");
      +//dBABlob.storeBlob("/aconn/development/forms/test/scandocs/patil/", "2012_12_06_16_29_14.jpg", "SL16SCANIMG", "BLIMAGE", "CDIMAGE='TEST'", "/");+
      +}+
      +};+


      Here is my PL/SQL WRAPPER for the Java code

      CREATE OR REPLACE FUNCTION Store_DBA_Blob (
      in_directory_s   IN VARCHAR2,
      in_nafile_s_   IN VARCHAR2,
      in_natable_s_   IN VARCHAR2,
      in_nablobcol_s  IN VARCHAR2,
      in_dewhere_s  IN VARCHAR2,
      in_delimeter_s  IN VARCHAR2,
      in_nauser_s   IN VARCHAR2,
      in_napassword_s IN VARCHAR2,
      in_nadba_s IN VARCHAR2
      +)+
      RETURN VARCHAR2
      AS
      LANGUAGE JAVA
      NAME 'DBABlob.storeBlob (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return java.lang.String ';

      Here is my call to the PL/SQL Wrapper that uses the default oracle connection. NOTE: The blank username, password, and database connection parameters can be blank or null.

      ++DECLARE+
      l_msg_s   VARCHAR2(4000);
      BEGIN
      l_msg_s := store_dba_blob('/aconn/development/forms/test/scandocs/patil/', '2012_12_06_16_29_14.jpg', 'SL16SCANIMG', 'BLIMAGE', 'CDIMAGE=''TEST''', '\', '', '', '');
      dbms_output.put_line('MSG:'||l_msg_s);
      END;

      Here is my call to the PL/SQL Wrapper passing username password database connection. NOTE: The your_username, your_password, your_dba_connection need to be replaced with your actual credentials.

      DECLARE
      l_msg_s   VARCHAR2(4000);
      BEGIN
      l_msg_s := store_dba_blob('/aconn/development/forms/test/scandocs/patil/', '2012_12_06_16_29_14.jpg', 'SL16SCANIMG', 'BLIMAGE', 'CDIMAGE=''TEST''', '\', 'your_username', 'your_password', 'your_dba_connection');
      dbms_output.put_line('MSG:'||l_msg_s);
      END;

      Here is the SQL to create the TABLE

      CREATE TABLE"SL16SCANIMG"
      +(+
      +"BLIMAGE" BLOB,+
      +"NATYPE" VARCHAR2(10 BYTE),+
      +"NAFILE" VARCHAR2(100 BYTE),+
      +"NADIRECTORY" VARCHAR2(500 BYTE),+
      +"NATXNUPDUSER" VARCHAR2(20 BYTE),+
      +"NATXNORGUSER" VARCHAR2(20 BYTE),+
      +"DTTXNORIGIN" TIMESTAMP (6),+
      +"DTTXNUPDATE" TIMESTAMP (6),+
      +"CDSTATUS" VARCHAR2(2 BYTE),+
      +"CDIMAGE" VARCHAR2(20 BYTE) NOT NULL ENABLE+
      +);+

      NOTE: If you see any +s in the code that are at the beginning and the end of the line, ignore these for they were not part of the code, those were added when I posted the message and tried to make the code italic.

      Edited by: brheitner on Dec 13, 2012 9:45 AM

      Edited by: brheitner on Dec 13, 2012 9:49 AM

      Edited by: brheitner on Dec 13, 2012 9:50 AM