This discussion is archived
0 Replies Latest reply: Dec 13, 2012 6:51 AM by brheitner RSS

Problem with Updating BLOB Column with OracleDefaultConnection

brheitner Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points