Re: OCESQL and Data Types

[Ron Norman <rjn@xxxxxxxxxxxx>]

Database does not really like fields/columns that are not well defined.

Following is an example of what you can do using GnuCOBOL XFD (trunk and hopefully soon to be 4.0)

       FD  TSPFILE
           BLOCK CONTAINS 5 RECORDS.

      $XFD NAME=tspfilesql DROP FILLER
       01  TSPFL-RECORD.
           05  TSPFL-REC.
      $XFD USE GROUP CUSTNUM
           10  CM-CUST-NUM.
             15  CM-CUST-PRE                   PICTURE X(3).
             15  CM-CUST-NNN                   PICTURE X(5).
           10  CM-STATUS                       PICTURE X.
           10  CM-COMPANY                      PICTURE X(25).
      $XFD USE GROUP VAR_LENGTH custaddr
           10  CM-ADDRESS.
             15  CM-ADDRESS-1                  PICTURE X(25).
             15  CM-ADDRESS-2                  PICTURE X(25).
             15  CM-ADDRESS-3                  PICTURE X(25).
           10  CM-TELEPHONE                    PICTURE 9(10).
           10  CM-DP-MGR                       PICTURE X(25).
           10  CM-MACHINE                      PICTURE X(8).
           10  CM-MEMORY                       PICTURE X(4).
      $XFD WHEN (CM-STATUS = 'A' && CM-TELEPHONE > 100)
      $XFD AND CM-MACHINE = 'B' || CM-COMPANY = ' '
           10  CM-MEMORYX REDEFINES CM-MEMORY.
             15  CM-MEMSZ                      PICTURE 9(2).
             15  CM-MEMUNIT                    PICTURE X(2).
           10  CM-DISK                         PICTURE X(8).
           10  CM-TAPE                         PICTURE X(8).  

The creates a table definition as follows:

CREATE TABLE tspfilex (
   custnum                                  CHAR(8) NOT NULL,
   status                                   CHAR(1),
   company                                  CHAR(25),
   custaddr                                 VARCHAR(75),
   telephone                                DECIMAL(10) NOT NULL,
   dp_mgr                                   CHAR(25) NOT NULL,
   machine                                  CHAR(8) NOT NULL,
   memory                                   CHAR(4),
   memsz                                    DECIMAL(2),
   memunit                                  CHAR(2),
   disk                                     CHAR(8) NOT NULL,
   tape                                     CHAR(8),
);
CREATE UNIQUE INDEX pk_tspfilex ON tspfilex (custnum);
CREATE INDEX k1_tspfilex ON tspfilex (telephone,machine);    

Then your COBOL code just uses normal COBOL I/O READ/WRITE etc...

 

On Sun, Feb 19, 2023 at 1:28 PM Sergey Kashyrin <ska@xxxxxxxxx> wrote:

Hi Kevin,

What is the problem ?

"unlimited" unfortunately is always limited :-)
ESQL can NOT read the CLOB/BLOB by chunks. You have to allocate the
memory for the whole CLOB/BLOB.

i.e. you have
            EXEC SQL BEGIN DECLARE SECTION END-EXEC.
        01  G.
            10 G1.
               49 G-LEN    PIC S9(8) USAGE COMP.
               49 G-TEXT   PIC X(15).
            EXEC SQL END DECLARE SECTION END-EXEC.

And G1 will be recognized as VARCHAR.
When you doing SELECT xxx INTO :G1
you can get SQL error that that data is truncated.
Than you should have the same structure in LINKAGE SECTION and allocate
enough memory to hold this CLOB.

Or, you can have initially
               49 G-TEXT   PIC X(15000000).

And not to worry about :-)

Regards,
Sergey


On 2/19/2023 4:13 PM, Kevin Monceaux wrote:
> James,
>
> On Sun, Feb 19, 2023 at 08:31:57AM -0500, James K. Lowden wrote:
>   
>> Can you give me a sense of the kind of information stored in that
>> column?  If it's what it should be -- an atomic datum -- then it's
>> misdefined in the database, and the question of how to handle it is a
>> management problem, not a technical one (because the display will
>> truncate the data arbitrarily).
> I tend to use unlimited character varying fields for text fields that might
> contain long-ish strings, though not necessarily ridiculously long strings.
>
> The database I was wanting to try out with OCESQL is one loaded with some
> basic title details from the IMDb's title basics data set available at:
>
>      https://datasets.imdbws.com/
>
> The description of the title.basics.tsv.gz file doesn't specify how long any
> of the string columns might be, so I defined them as unlimited character
> varying columns.  The longest column is the primaryTitle column.  At the
> moment, the longest primaryTitle in the data I have loaded is 419
> characters.
>
> I would like to set up a GnuCOBOL application to catalog my DVD/Blu-Ray
> collection, including details available from the IMDb's datasets, etc.  I
> would probably want to add a plot summary column, which might range in size
> from a few words to several paragraphs.
>
>
>
>

--

Cheers

Ron Norman