CBL_OPEN_FILE Organization parameter

Your AI’s information is completely wrong. Please refer to the product documentation instead.

Here is a small example:

      *----------------------------------------------------------------*
      *                    Read Byte Stream File                       *
      *                                                                *
      *  This program demonstrates how to use the Visual COBOL callable*
      *  library routines to open and read a file as a simple byte     *
      *  stream. This program uses a data buffer size of 1000 and reads*
      *  the entire file in a single read so it assumes that the file  *
      *  is not greater that 1000 bytes in size. The data buffer size  *
      *  can be adjusted for larger files or the file could be read in *
      *  chunks using the file-offset parameter.                       *
      *                                                                *
      *  The first call to CBL_READ_FILE with flags set to 128 will    *
      *  return the file size into the file-offset parameter so we know*
      *  how many bytes to read.                                       *
      *                                                                *
      *  The data-buffer read will be moved to a dynamic length data   *
      *  item using the file-size as the length so that trailing spaces*
      *  in the buffer will be discarded.                              *
      *----------------------------------------------------------------*
       identification division.
       program-id. readbytestream.

       environment division.
       configuration section.

       data division.
       working-storage section.
       78 BUFFER-SIZE    value 1000.
       01 file-content   pic x dynamic.
       01 data-buffer    pic x(BUFFER-SIZE) value spaces.
       01 filename       pic x(256) value spaces.

       01 access-mode    pic x comp-x value 0.
       78 access-read-only value 1.
       78 access-write-only value 2.
       78 access-read-write value 3.
       78 access-greater-4GB value 64.

       01 deny-mode          pic x comp-x value 3.
       78 deny-read-write    value 0.
       78 deny-write         value 1.
       78 deny-read          value 2.
       78 deny-none          value 3.

       01 device             pic x comp-x value zeroes. *> always 0
       01 file-handle        pic x(4) comp-5 value zeroes.
       01 file-offset        pic x(8) comp-x value zeroes.
       01 byte-count         pic x(4) comp-x value zeroes.

       01 flags              pic x comp-x value zeroes.
       78 read-filesize-only value 128.
       78 read-data          value 0.
       procedure division.

           move "myfile.jpg" to filename
           compute access-mode = access-read-only + access-greater-4GB
           move deny-write to deny-mode

           call "CBL_OPEN_FILE" using filename
                                      access-mode
                                      deny-mode
                                      device
                                      file-handle
           end-call
           if return-code not = 0
               display "error on open = " return-code
               stop run
           end-if

           move read-filesize-only to flags
           *> File-offset will contain the filesize
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      data-buffer
           end-call
           if return-code not = 0
               display "error on get filesize = " return-code
               stop run
           end-if

           move read-data to flags
           move file-offset to byte-count *> read the entire file
           *> buffer must be large enough
           *> set the length of the dynamic length data-item that will
           *> receive the contents of the file.
           set length of file-content to byte-count
           *> First byte in the file is at offset 0
           move 0 to file-offset
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      data-buffer
           end-call
           if return-code not = 0
               display "error on read file = " return-code
               stop run
           end-if

           *> file-content will contain only the data read - extra bytes
           *> in data-buffer
           move data-buffer(1:byte-count) to file-content

           call "CBL_CLOSE_FILE" using file-handle

           goback.

Hi Chris, my name es Carmen de PAz, it's great to talk to you again. Let me tell you about my problem.I need to create a COBOL program (MF COBOL EL VC 8.0 for Eclipse, running on SUSE Linux) that reads a file from the PC and inserts it into a SQL Server database. The program is compiled with OpenSQL Server to access the database via ODBC.We normally work with Informix databases, and this is the first program that accesses an SQL server.I've read the documentation, and the recommended way to implement the program is as follows: read the binary file (using CBL_FILE_OPEN and CBL_FILE_READ) and insert it into the database using the UPDATE…WRITE statement.

The problem is that I can't get the bytes from the file. When I read it, I get the text representation of the binary file, something like PDF-1.6^%^^^^^^10 0 obj^<</Linearized 1/L 114516......... but what I need is its value in bytes. How can I get it?

The CBL_OPEN_FILE, CBL_READ_FILE is for doing byte stream I-O which means it knows nothing about the format of files, it simply reads the raw bytes in the file, which is what it appears to be doing in your case.  PDF files contain header information as well as formatting information, etc for the text or images within the PDF. It would not be possible to just read the data within a PDF using these byte stream routines unless you have a deep understanding of PDF file formats.

To extract just the data you would need to convert it to a readable format like a text file and use that in the byte stream routines.

What do you think is the better way to do what a want to do?

A COBOL program that takes a file, either from the PC or the Linux server, and inserts it into a SQL Server database.

I am not sure what you mean by insert the file into SQL Server. If you wish to actually insert the entire contents of a file, including its control information, into the database, then you could use the CBL_ byte stream routines as outlined above. If you wish to extract just the data from the file, then you would need a method to do this before inserting the data.

Do you wish to store an actual PDF file, in its entirety into a BLOB column, so that it can be retrieved at a later time, when it can be accessed, again as a PDF file or are you wishing to do something else?

I  want to store a complete PDF, JPG,GIF,…  file in a BLOB column.

You can use the CBL_ byte stream method to do this.

Here is a modified example that reads a PDF and inserts it into a BLOB column in a SQL Server table. After the insert it will select the same row back and display the length of the column. If it worked successfully, the length should be the same as the original size of the PDF file. This example will work with any binary file but the maximum size of the file, in this example is 100MB.

      $set sql(dbman=odbc)
      *----------------------------------------------------------------*
      *         Insert PDF File into SQL Server BLOB Column            *
      *                                                                *
      *  This program demonstrates how to use the Visual COBOL callable*
      *  library routines to open and read a PDF file as a simple byte *
      *  stream. This program uses a data buffer size of 100MB and     *
      *  reads the entire file in a single read so it assumes that the *
      *  file is not greater that 100MB in size. The data buffer size  *
      *  can be adjusted for larger files or the file could be read in *
      *  chunks using the file-offset parameter.                       *
      *                                                                *
      *  The first call to CBL_READ_FILE with flags set to 128 will    *
      *  return the file size into the file-offset parameter so we know*
      *  how many bytes to read.                                       *
      *                                                                *
      *  The data-buffer read will be moved to a dynamic length data   *
      *  item using the file-size as the length so that trailing spaces*
      *  in the buffer will be discarded.                              *
      *                                                                *
      *  Once the file contents have been retrieved, they will be      *
      *  moved to a BLOB host variable, the length will be set to the  *
      *  number of bytes read from the PDF and the BLOB row will be    *
      *  inserted into a database table called testblob.               *
      *                                                                *
      *  The program will then select the same row back into the BLOB  *
      *  host variable and the length will be displayed. If the insert *
      *  and select worked then the length displayed should be the same*
      *  as the size of the original PDF file.                         *
      *----------------------------------------------------------------*
       identification division.
       program-id. storepdftoblob.
       data division.
       working-storage section.
       exec sql include sqlca end-exec
       01 testblob-mykey    pic s9(9) comp-5.
       01 testblob-myblob   sql type is blob(100M).

       78 BUFFER-SIZE    value 100000000.  *> 100MB
       01 file-content      pic x dynamic.
       01 data-buffer       pic x(BUFFER-SIZE) value spaces.
       01 filename          pic x(256) value spaces.

       01 access-mode       pic x comp-x value 0.
       78 access-read-only   value 1.
       78 access-write-only  value 2.
       78 access-read-write  value 3.
       78 access-greater-4GB value 64.

       01 deny-mode         pic x comp-x value 3.
       78 deny-read-write    value 0.
       78 deny-write         value 1.
       78 deny-read          value 2.
       78 deny-none          value 3.

       01 device            pic x comp-x value zeroes. *> always 0
       01 file-handle       pic x(4) comp-5 value zeroes.
       01 file-offset       pic x(8) comp-x value zeroes.
       01 byte-count        pic x(4) comp-x value zeroes.

       01 flags             pic x comp-x value zeroes.
       78 read-filesize-only value 128.
       78 read-data          value 0.
       procedure division.

           perform 100-read-pdf-file

           exec sql
              connect to 'SQLODBC64WA' 
           end-exec 
           if sqlcode not = 0
              display "error connect = " SQLCODE
              goback
           end-if

           perform 110-store-pdf-as-blob
           perform 120-read-blob
           *> uncomment the following line if you wish to save the database data
           *>exec sql commit work end-exec

           exec sql disconnect current end-exec
           goback.

       100-read-pdf-file.

           move "C:\tests\testblob\testblob\mypdf.pdf" to filename
           compute access-mode = access-read-only + access-greater-4GB
           move deny-write to deny-mode

           call "CBL_OPEN_FILE" using filename
                                      access-mode
                                      deny-mode
                                      device
                                      file-handle
           end-call
           if return-code not = 0
               display "error on open = " return-code
               stop run
           end-if

           move read-filesize-only to flags
           *> File-offset will contain the filesize
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      data-buffer
           end-call
           if return-code not = 0
               display "error on get filesize = " return-code
               stop run
           end-if

           move read-data to flags
           move file-offset to byte-count *> read the entire file
           *> buffer must be large enough
           *> set the length of the dynamic length data-item that will
           *> receive the contents of the file.
           set length of file-content to byte-count
           *> First byte in the file is at offset 0
           move 0 to file-offset
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      data-buffer
           end-call
           if return-code not = 0
               display "error on read file = " return-code
               stop run
           end-if

           *> file-content will contain only the data read - extra bytes
           *> in data-buffer
           move data-buffer(1:byte-count) to file-content

           call "CBL_CLOSE_FILE" using file-handle.

       110-store-pdf-as-blob.

           move file-content to testblob-myblob-data
           move byte-count to testblob-myblob-length
           move 1 to testblob-mykey

           exec sql 
             insert into dbo.testblob  
               (mykey
               ,myblob
               ) values (
                :testblob-mykey
               ,:testblob-myblob
               )
           end-exec 
           if sqlcode not = 0
              display "blob insert failed = " sqlcode
              goback
           end-if.
       120-read-blob.

           move 1 to testblob-mykey
           exec sql 
             select myblob into :testblob-myblob
             from dbo.testblob
             where mykey = 1
           end-exec 
           if sqlcode not = 0
              display "error on blob select = " sqlcode
              goback
           end-if

           display "length read should be same as pdf size = "
              TESTBLOB-MYBLOB-LENGTH.

I realized that there was some extra code in the sample program to demonstrate dynamic length tables that was not required so here is a streamlined version without it. I also added code to create the testblob table so the program should be self-contained. All you need to do is change the DSN name to your own and populate the filename parameter with your own PDF or other binary file name. The program will handle files up to 100MB in size.

      $set sql(dbman=odbc)
      *----------------------------------------------------------------*
      *         Insert PDF File into SQL Server BLOB Column            *
      *                                                                *
      *  This program demonstrates how to use the Visual COBOL callable*
      *  library routines to open and read a PDF file as a simple byte *
      *  stream. This program uses a data buffer size of 100MB and     *
      *  reads the entire file in a single read so it assumes that the *
      *  file is not greater that 100MB in size. The data buffer size  *
      *  can be adjusted for larger files or the file could be read in *
      *  chunks using the file-offset parameter.                       *
      *                                                                *
      *  The first call to CBL_READ_FILE with flags set to 128 will    *
      *  return the file size into the file-offset parameter so we know*
      *  how many bytes to read.                                       *
      *                                                                *
      *  Once the file contents have been retrieved into a BLOB host   *
      *  variable, the length will be set to the number of bytes read  *
      *  from the PDF and the BLOB row will be inserted into a database*
      *  table called testblob. testblob will be created each time this*
      *  program is run.
      *                                                                *
      *  The program will then select the same row back into the BLOB  *
      *  host variable and the returned length will be compared to the *
      *  size of the original PDF file. If they are the same the test  *
      *  worked.                                                       *
      *----------------------------------------------------------------*
       id division.
       program-id. storepdftoblob.
       data division.
       working-storage section.
       exec sql include sqlca end-exec
       01 testblob-mykey    pic s9(9) comp-5.
       01 testblob-myblob   sql type is blob(100M).

       01 filename          pic x(256) value spaces.
       01 access-mode       pic x comp-x value 0.
       78 access-read-only   value 1.
       78 access-write-only  value 2.
       78 access-read-write  value 3.
       78 access-greater-4GB value 64.

       01 deny-mode         pic x comp-x value 3.
       78 deny-read-write    value 0.
       78 deny-write         value 1.
       78 deny-read          value 2.
       78 deny-none          value 3.

       01 device            pic x comp-x value zeroes. *> always 0
       01 file-handle       pic x(4) comp-5 value zeroes.
       01 file-offset       pic x(8) comp-x value zeroes.
       01 byte-count        pic x(4) comp-x value zeroes.

       01 flags             pic x comp-x value zeroes.
       78 read-filesize-only value 128.
       78 read-data          value 0.
       procedure division.

           perform 100-read-pdf-file
           perform 110-connect-to-database
           perform 120-create-testblob-table
           perform 130-store-pdf-as-blob
           perform 140-read-blob
           perform 150-disconnect-from-database

           goback.

       100-read-pdf-file.

           move "C:\tests\testblob\testblob\mypdf.pdf" to filename
           compute access-mode = access-read-only + access-greater-4GB
           move deny-write to deny-mode

           call "CBL_OPEN_FILE" using filename
                                      access-mode
                                      deny-mode
                                      device
                                      file-handle
           end-call
           if return-code not = 0
               display "error on open = " return-code
               stop run
           end-if

           move read-filesize-only to flags
           *> File-offset will contain the filesize
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      testblob-myblob-data
           end-call
           if return-code not = 0
               display "error on get filesize = " return-code
               stop run
           end-if

           move read-data to flags
           move file-offset to byte-count *> read the entire file
           *> buffer must be large enough - currently 100MB
           *> First byte in the file is at offset 0
           move 0 to file-offset
           call "CBL_READ_FILE" using file-handle
                                      file-offset
                                      byte-count
                                      flags
                                      testblob-myblob-data
           end-call
           if return-code not = 0
               display "error on read file = " return-code
               stop run
           end-if

           call "CBL_CLOSE_FILE" using file-handle.

       110-connect-to-database.

           exec sql
              connect to 'SQLODBC64WA' 
           end-exec 
           if sqlcode not = 0
              display "error connect = " SQLCODE
              goback
           end-if.

       120-create-testblob-table.

           exec sql drop table testblob end-exec           
           exec sql
               create table testblob (
                   mykey int primary key,
                   myblob varbinary(MAX)
               )
           end-exec
           if sqlcode not = 0
              display "error on create table = " SQLCODE
              goback
           end-if.

       130-store-pdf-as-blob.

           move byte-count to testblob-myblob-length
           move 1 to testblob-mykey

           exec sql 
             insert into dbo.testblob  
               (mykey
               ,myblob
               ) values (
                :testblob-mykey
               ,:testblob-myblob
               )
           end-exec 
           if sqlcode not = 0
              display "blob insert failed = " sqlcode
              goback
           end-if.

       140-read-blob.

           initialize testblob-myblob
           move 1 to testblob-mykey
           exec sql 
             select myblob into :testblob-myblob
             from dbo.testblob
             where mykey = 1
           end-exec 
           if sqlcode not = 0
              display "error on blob select = " sqlcode
              goback
           end-if

           display "length read should be same as pdf size"
           if testblob-myblob-length = byte-count
              display "test OK, BLOB length read is same!"
           else 
              display "test failed, BLOB length read is different!"
           end-if.

       150-disconnect-from-database.
           *> uncomment the following line if you wish to save the database data
           *>exec sql commit work end-exec

           exec sql disconnect current end-exec.

Thaks Chris, Your code has helped me a lot.