Skip to main content

Writing a blob into Oracle-Database

  • September 30, 2013
  • 5 replies
  • 0 views
D

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)
    


 

    5 replies

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • September 30, 2013

    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)

    D

    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)
        


     

    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

    Chris Glazier
    Forum|alt.badge.img+2
    • Chris GlazierModerator
    • Moderator
    • September 30, 2013

    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)
        


     

    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.
    D

    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)
        


     

    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.

    Chris Glazier
    Forum|alt.badge.img+2

    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)
        


     

    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.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement