Inserting a BLOB to Oracle not working, undefined SQL_TYPE_BLOB ?

Targets Web
1

Hello All,

In the following snippets: MYTABLE is an Oracle 12.x table. FILE_CONTAINER is a BLOB column.

Any insights and suggestions to make the following experiments (#2 and/or #3) work would be much appreciated.

Thank you!
-Simon


Experiment #1 of 3:

The following works, but only an empty BLOB object is created in the BLOB column.

sql="INSERT INTO MYTABLE"_
+ "("_
+ " ATTACHMENT_ID"_
+ ",FILE_NAME"_
+ ",FILE_CONTAINER"_
+ ",FILE_SIZE"_
+ ",USERID"_
+ ")"_
+ " VALUES"_
+ "("_
+ lvID.ToText+","_
+ "'"+parmFN+"',"_
+ "EMPTY_BLOB(),"_
+ lvFileSize.ToText+","_
+ "'"+Session.propLoginUID+"',"_
+ ")"

app.propdb.SQLExecute(sql)
app.propdb.Commit

Experiment #2 of 3:

The following terminates the WebApp.
With this message displayed on the web browser: “The application has gone off-line. Please try again later.”

“The application has gone off-line. Please try again later.”

sql=“INSERT INTO MYTABLE”_

  • “(”_
  • " ATTACHMENT_ID"_
  • “,FILE_NAME”_
  • “,FILE_CONTAINER”_
  • “,FILE_SIZE”_
  • “,USERID”_
  • “)”_
  • " VALUES"_
  • “(?”_
  • “,?”_
  • “,?”_
  • “,?”_
  • “,?”_
  • “)”

Dim ps As OracleSQLPreparedStatement = OracleSQLPreparedStatement(app.propdb.Prepare(sql))
ps.BindType(0, OracleSQLPreparedStatement.SQL_TYPE_INTEGER)
ps.BindType(1, OracleSQLPreparedStatement.SQL_TYPE_STRING)
ps.BindType(2, OracleSQLPreparedStatement.SQL_TYPE_CLOB) // same with …_STRING
ps.BindType(3, OracleSQLPreparedStatement.SQL_TYPE_INTEGER)
ps.BindType(4,OracleSQLPreparedStatement.SQL_TYPE_STRING)

ps.Bind(0, lvID)
ps.Bind(1, parmFN)

Dim fN As String = “…/” + parmFN
Dim fTarget as FolderItem = new FolderItem(fN)
If fTarget.Exists Then MsgBox(“File Found!”) // Yes - File Found
Dim bs As BinaryStream = BinaryStream.Open(fTarget, False)
ps.Bind(2, bs)
ps.Bind(3, lvFileSize)
ps.Bind(4, Session.propLoginUID)

ps.SQLExecute
app.propdb.Commit


Experiment #3 of 3:

The following also terminates the WebApp.
The same message was displayed on the web browser: "The application has gone off-line. Please try again later."

sql="INSERT INTO MYTABLE"_
+ "("_
+ " ATTACHMENT_ID"_
+ ",FILE_NAME"_
+ ",FILE_CONTAINER"_
+ ",FILE_SIZE"_
+ ",USERID"_
+ ")"_
+ " VALUES"_
+ "(?"_
+ ",?"_
+ ",?"_
+ ",?"_
+ ",?"_
+ ")"

Dim fTarget as FolderItem = new FolderItem(fN) 
Dim bs As BinaryStream = BinaryStream.Open(fTarget, False)

Dim ps As OracleSQLPreparedStatement = OracleSQLPreparedStatement(app.propdb.Prepare(sql))

ps.SQLExecute(lvID,parmFN,bs,lvFileSize,Session.propLoginUID)
app.propdb.Commit
2

Hello All,

FWITW, I have devised the following solution for this.
This solution works even though it is not as elegant as I would like it to be since it requires non-Xojo tools be used.

Firstly, I created j2orablob.jar (and put it in c:/yourFolder/yourSubFolder/) from the following Java codes.

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.; // include external library ojdbc.jar, from https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

public class j2orablob {

public static String parm1UID;
public static String parm2PWD;
public static String parm3ORCLConn;
public static String parm4ATTID;
public static String parm5ATTFN;
static final String JDBC_DRIVER = "oracle.jdbc.OracleDriver";
static String DB_URL;

public static void main(String[] args) {
	Connection conn = null;
	Statement stmt = null;
	BufferedWriter bufferFileWriter = null;

	parm1UID	= args[0];
	parm2PWD	= args[1];
	parm3ORCLConn	= args[2];
	parm4ATTID	= args[3];
	parm5ATTFN	= args[4];		

	//System.out.println("1:"+parm1UID);
	//System.out.println("2:"+parm2PWD);
	//System.out.println("3:"+parm3ORCLConn);
	//System.out.println("4:"+parm4ATTID);
	//System.out.println("5:"+parm5ATTFN);
	
	try {
		Class.forName(JDBC_DRIVER);
		DB_URL = "jdbc:oracle:thin:@"+parm3ORCLConn;	
			// (3) = "jdbc:oracle:thin:@hostservername.domain.com:1521/svcname";
		conn = DriverManager.getConnection(DB_URL, parm1UID, parm2PWD);
			// (1) & (2) = dbUserName, dbUserPassword
		PreparedStatement pstmt =  conn.prepareStatement("update table_owner.relevant_attachments set blob_column= ? where attachment_id= ?");
		File blobFile = new File(parm5ATTFN);
			// (5) = file to attach
		FileInputStream in = new FileInputStream(blobFile);
		pstmt.setBinaryStream(1, in, (int)blobFile.length());
		pstmt.setInt(2, Integer.parseInt(parm4ATTID));
			// (4) = relevant_Doc_ID
		pstmt.executeUpdate();
		conn.commit();
		pstmt.close();		
		conn.close();
		in.close();
		blobFile.delete();
	} catch (SQLException se) {
		se.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (stmt != null) conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		}
		try {
			if (conn != null) conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		}
		if (bufferFileWriter != null) {
			try {
				bufferFileWriter.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}

}

Then, using the solution from Experiment #1 of 3 (as posted earlier), I appended to it the the following codes:
…
Dim sh As New Shell
Dim shParams As String = “”""+App.propdb.UserName.ToText+""" “”"+App.propdb.Password.ToText+""" “”"+App.propdb.DatabaseName.ToText+""" “”"+relevant_Doc_ID+""" ““c:/Users/yourUID/youFolder/yourSubFolder/”+yourNameOfFileToUpload+”"""
sh.Execute("START /MIN /HIGH CMD.EXE /C java -jar c:/yourFolder/yourSubFolder/j2orablob.jar "+shParams)
…

Cheers,
-Simon