Inserting a BLOB to Oracle not working, undefined SQL_TYPE_BLOB ?

  1. 9 months ago

    Simon H

    24 Dec 2018 Pre-Release Testers, Xojo Pro Nashville, Tennessee
    Edited 9 months ago

    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

    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

  2. 8 months ago

    Simon H

    12 Jan 2019 Pre-Release Testers, Xojo Pro Answer Nashville, Tennessee

    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

or Sign Up to reply!