Re: a cheep interface to database

From: Warren Simmons (wsimmons5_at_optonline.net)
Date: 07/06/04

  • Next message: PAUL RAULERSON: "Re: Cobol Opportunity Available" 
    Date: Tue, 06 Jul 2004 02:38:47 GMT

    LX-i wrote:

    > yos wrote:
    >
    >> Hi all,
    >> I am an Acucobol user, and I have to write a program that stores the
    >> data into
    >> a database. I don't know SQL, and I would prefer not to learn ESQL.
    >
    >
    > Then you'd prefer to not learn the simplest way to do what you want,
    > with tools you've already got...
    >
    >> I had been told that I have a couple of choice:
    >> -using Acu4GL, that lets me to write normal Cobol statements (OPEN,
    >> WRITE, START, READ, etc) to interface to a database like Oracle,
    >> Informix, MSSQL, DB2, ODBC, etc
    >> -using DBMaker/DCI that basically allow me to work like with Acu4GL,
    >> but it is
    >> only for DBMaker.
    >
    >
    > I'm not familiar with either of these tools. I just don't see SQL as
    > that tough.
    >
    > SQL COBOL
    > ------ -------
    > SELECT = READ
    > INSERT = WRITE
    > UPDATE = REWRITE
    > DELETE = DELETE
    >
    > You might end up writing much more efficient queries to do it this way.
    > Say you've got a table with five columns (name, address, city, st, zip,
    > and phone) and you want to retrieve a list based on a particular city
    > and state, which was input by the user.
    >
    > In COBOL, you'd have to make an FD for the file - something like...
    >
    > 8<------ COBOL FD ------->8
    > fd people-file.
    > 01 people-rec.
    > 12 people-name pic x(50).
    > 12 people-address pic x(255).
    > 12 people-city-st.
    > 16 people-city pic x(25).
    > 16 people-st pic x(02).
    > 12 people-zip pic x(09).
    > 8<-------
    >
    > In COBOL using Embedded SQL, you could have something just like the
    > above, but for clarity, let's say we've got it like this...
    >
    > 8<------ Embedded SQL Declaration -------->8
    > 01 my-name pic x(50).
    > 01 my-address pic x(255).
    > 01 my-city pic x(25).
    > 01 my-st pic x(02).
    > 01 my-zip pic x(09).
    > 8<-------
    >
    > In COBOL, to open the file and get it there (assuming you had an index
    > on city and state), you'd say...
    >
    > 8<--------- COBOL File I/O --------->8
    > open i-o people-file
    > move input-city to people-city
    > move input-st to people-st
    > start people-file
    > key not < people-city-st
    > *<---------
    >
    > To do the same thing in SQL, it's called "opening a cursor" (or you may
    > hear the term "result set" - same difference). Here's how you would do
    > that.
    >
    > 8<---------- Embedded SQL --------->8
    > exec sql
    > declare people_cursor cursor for
    > select name, address, city, st, zip
    > from people_table
    > where city = ? and st = ?
    > order by name
    > end-exec
    >
    > exec sql
    > open people_cursor
    > using :input-city, :input-st
    > end-exec
    > 8<----------
    >
    > Now, to retrieve the records from the COBOL file, you'd use the
    > statement...
    >
    > 8<---------- COBOL File I/O ---------->8
    > read people-file next record
    > at end set were-done to true
    > end-read
    > 8<----------
    >
    > Which, I'll grant you, is less typing than the ESQL version, but if you
    > put the ESQL in a separate paragram (or in an inline perform) you only
    > have to type it once...
    >
    > 8<----------- Embedded SQL ----------->8
    > exec sql
    > fetch next people_cursor
    > into :my-name, :my-address, :my-city, :my-st, :my-zip
    > end-exec
    >
    > if sqlstate not = zeroes
    > set were-done to true
    > end-if
    > 8<-----------
    >
    > Closing is just as easy...
    >
    > 8<----------- COBOL File I/O ----------->8
    > close people-file
    > 8<-----------
    >
    > versus
    >
    > 8<------------ Embedded SQL ------------>8
    > exec sql
    > close people_cursor
    > end-exec
    > 8<------------
    >
    > I know this may not be what you're looking for, but I think involving a
    > third-party tool, unless you're only going to do this once and never
    > again (and never maintain it), it might be worth your effort to look at
    > it. If you've been coding a while, it shouldn't take long at all to get
    > up to speed on the basics.
    >
    >

    I thought your reply was great. I learned something I suspected
    all along. It's hard to review a method without examples, and
    what you did here is deserving a lot os cheers.

    Warren Simmons

  • Next message: PAUL RAULERSON: "Re: Cobol Opportunity Available"

    Relevant Pages

    • COBOL stored procedure for DB2
      ... exec sql begin declare section end-exec. ... display "calling spprog" ... This used to be a little longer and had embedded SQL commands in it ...
      (comp.lang.cobol)
    • Re: Pro*COBOL and CLOB Columns
      ... >EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... >01 LogBuffer. ... EXEC SQL VAR LogBuffer-ARR is LONG VARCHAR END-EXEC. ... EXEC SQL LOB ENABLE BUFFERING:MyCLOB-pointer END-EXEC. ...
      (comp.lang.cobol)
    • Re: Dynamic SQL
      ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. ... EXEC SQL VAR WS-SQL-STR IS CHARF END-EXEC. ...
      (comp.lang.cobol)
    • Re: Dynamic SQL
      ... > PREPARE SQLSTMT FROM:WS-SQL ... > 01 WS-SQL PIC XVALUE SPACES. ... > EXEC SQL BEGIN DECLARE SECTION END-EXEC. ...
      (comp.lang.cobol)