Highlighted
Absent Member.
Absent Member.
3690 views

Writing a blob into Oracle-Database

Jump to solution

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)
    


 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Writing a blob into Oracle-Database

Jump to solution

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.

View solution in original post

0 Likes
5 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Writing a blob into Oracle-Database

Jump to solution

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

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

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Writing a blob into Oracle-Database

Jump to solution

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

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Writing a blob into Oracle-Database

Jump to solution

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.            
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Writing a blob into Oracle-Database

Jump to solution

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.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Writing a blob into Oracle-Database

Jump to solution

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.

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.