Writing a blob into Oracle-Database

I need an example for writing  a blob into our Oracle-Database from a Cobol JVM Application .

My little example does not compile:

    program-id. Incident as "Incident".
       data division.
       working-storage section.
       EXEC SQL INCLUDE SQLCA END-EXEC.
       EXEC SQL BEGIN DECLARE SECTION END-EXEC
         01 myBlob type java.sql.Blob.
       EXEC SQL END DECLARE SECTION END-EXEC
         01 myString pic x(32000).
       procedure division.
          move myString to myBlob
       goback.
       end program Incident.
   
 [cobol]     34    move myString to myBlob
 [cobol] COBCH1624S Cannot implicitly convert string to type java.sql.Blob.  : D:\java\rfmOracleTest\Incident\src\Incident.cbl(10,33,28)
    


 

  • I believe that this would have to look something like the following:

       invoke myBlob::setBytes(1, myString::getBytes)

  • Now the example compiles.

    But if i start the program:

    java.lang.VerifyError: (class: Incident, method: Incident signature: ()I) Inconsistent args_size for opc_invokeinterface

  • There does appear to be a bug in the COBOL JVM version when using the setBytes method.
    It works fine in a Java program but not in COBOL.

    I will write this up as a bug.

    I did manage to get this to work using a different method though.

     

          		
           program-id. cobolblob as "cobolblob".
           data division.
           working-storage section.
           01 connection type java.sql.Connection.
           01 strContent string  value "Hello World".
           01 byteArray  type byte occurs any.
           01 ps1 type java.sql.PreparedStatement.
           01 blob type java.sql.Blob.
           01 ba type java.io.ByteArrayInputStream.
           procedure division.
              display "-------- Oracle JDBC Connection Testing ------"
              try 
                set connection to type java.sql.DriverManager::getConnection("jdbc:oracle:thin:scott/tiger@localhost:1521:orcl")
              catch e as type java.sql.SQLException
                invoke e::printStackTrace 
    	   goback
              end-try
    	 set strContent to "Hello World"
              set ps1 to connection::prepareStatement("update BLOBTAB set MYBLOB=? where BLOBNO=1")
    	 set blob to connection::createBlob
              set ba to new type ByteArrayInputStream(strContent::getBytes)
              invoke ps1::setBinaryStream(1, ba, strContent::length)
              invoke ps1::executeUpdate
    	 invoke connection::commit
    	 invoke connection::close
              goback.            
    
  • Sorry, but it does not compile:

     [cobol] Compiling cobolblob.cbl...

      [cobol] COBCH0845S Unknown class 'ByteArrayInputStream'  :   C:\javaMf\NeueDialogsteuerung\BlobTesting\src\cobolblob.cbl(21,49,30)

       [cobol] Compilation complete with 1 errors, 0 warnings, 0 notices and an exit code of 12

    I also need to know how to read a blob.

    Really i need to read and write 16000 byte binary data into Oracle. I believe for this requirement the correct oracle-data-type is long raw or blob. I need an example, how i can read and write the binary data with ESQL.

  • Verified Answer

    If you are using OpenESQL then you can insert and select blob columns using SQL TYPE BINARY data type.

    The following example inserts rows consisting of an integer column called BLOBNO and a BLOB column called MYBLOB into table BLOBTAB.

          $set sql(dbman=jdbc, allownullchar)
           program-id. esqlbob as "esqlbob".
           data division.
           working-storage section.
           exec sql include sqlca end-exec
           01 blob-no  pic 9(9) comp-5.
           01 my-blob  sql type is binary(16000).
           01 connect-string pic x(200) value "DRIVER=oracle.jdbc.driver.OracleDriver;URL=jdbc:oracle:thin:scott/tiger@localhost:1521:ORCL".
           procedure division.
    
               exec sql connect using :connect-string end-exec
               if sqlcode not = 0
                  display "error on connect = " sqlcode
                  goback
               end-if
               
               exec sql delete from BLOBTAB where BLOBNO > 0 end-exec
               display sqlcode
               
               move 1 to blob-no
               move "This is Blob #1" to my-blob
               
               exec sql 
                  insert into BLOBTAB
                     (BLOBNO, MYBLOB)
                     values 
                     (:blob-no, :my-blob)
               end-exec
               
               display sqlcode
               
               move 2 to blob-no
               move "This is Blob #2" to my-blob
               
               exec sql 
                  insert into BLOBTAB
                     (BLOBNO, MYBLOB)
                     values 
                     (:blob-no, :my-blob)
               end-exec
               
               display sqlcode
               
               move 1 to blob-no
               exec sql 
                  select
                     BLOBNO
                    ,MYBLOB 
                  into 
                     :blob-no
                    ,:my-blob 
                  from BLOBTAB 
                  where BLOBNO = :blob-no 
               end-exec
              
               move 2 to blob-no
               exec sql 
                  select
                     BLOBNO
                    ,MYBLOB 
                  into 
                     :blob-no
                    ,:my-blob 
                  from BLOBTAB 
                  where BLOBNO = :blob-no 
               end-exec
    
               exec sql commit work end-exec
               exec sql disconnect end-exec
               goback.
               
               
           end program esqlbob.