Looking for database programming example for green screen

Hi All,


I'm a newbie to Cobol.

I'm looking for examples on the standard way to move forward and backward through a database table. When using Xbase/Clipper I would program a entry screen with Function keys that would allow the user to perform database actions, like add, delete move forward and backward through the table. What's the standard way of doing that with Cobol?


Are there any beginner examples that people could point me to?  Thanks for the help.

Best,

Peter

testpassdata.zip
  • Verified Answer

    Hi Peter,

    In Visual COBOL you can use embedded EXEC SQL statements to access a database. There are a number of options to use depending on the vendor of the database. The easiest is if the database has an available ODBC driver then you can use OpenESQL to connect to an available DSN that points to the database and then your program can access the rows via database statements.

    This is covered in the documentation here:

    There are also some videos that you can watch located in this playlist:

    The following is a real simple example that opens a connection to a SQL Server database and then uses a cursor to return a rowset containing all rows in the Customer table and then allows the user to fetch forward or backward depending on a selected option.

          $set sql(dbman=odbc) 
           identification division.
           program-id. Program1.
    
           environment division.
           configuration section.
    
           data division.
           working-storage section.
           01 func-code   pic x value spaces.
           exec sql include sqlca end-exec.
           exec sql include customers end-exec.
           procedure division.
               exec sql 
                 connect to 'sqlodbc32' 
               end-exec 
               
               exec sql 
                  declare custcursor dynamic cursor for select 
                         a.customerid
                        ,a.companyname
                        ,a.contactname
                     from dbo.customers a
               end-exec 
               exec sql 
                  open custcursor
               end-exec 
               if sqlcode = 0
                  perform until exit
                     display "Enter 'N'= Next, 'P'= Prev, 'Q'= Quit"
                     accept func-code
                     evaluate function upper-case(func-code)
                        when "N"
                           perform 100-fetch-next
                        when "P"
                           perform 110-fetch-prev
                        when "Q"
                           perform 120-quit
                           exit perform
                      end-evaluate
                  end-perform
               else
                  display "open cursor failed"
               end-if
               goback.
           
           100-fetch-next.           
               
               exec sql 
                  fetch next custcursor  into 
                     :customers-customerid
                    ,:customers-companyname
                    ,:customers-contactname:customers-contactname-null
               end-exec 
               perform 200-display-row.
           
           110-fetch-prev.           
               
               exec sql 
                  fetch previous custcursor  into 
                     :customers-customerid
                    ,:customers-companyname
                    ,:customers-contactname:customers-contactname-null
               end-exec 
               perform 200-display-row.
           
           120-quit.
           
               exec sql 
                 close custcursor
               end-exec 
               exec sql  disconnect current end-exec.
           
           200-display-row.    
           
               if sqlcode = 0 
                  display customers-customerid
                  display customers-companyname
                  display customers-contactname
               else
                  if sqlcode = 100
                     display "row not found"
                  end-if
               end-if.
                  
           end program Program1.
    
  • Chris,

    Could you post the copybooks that belong to this example. I'm looking through the online videos but have not  found one that goes into setting up the copybooks.

    Thanks.

    Peter

  • I believe that the video on Using OpenESQL Assistant covers how to generate the host variable copybooks for a particular table.

    The one generated for the example here which uses SQL Server Northwind database Customer table would be:

          *> -------------------------------------------
          *> DECLARE TABLE for Customers
          *> -------------------------------------------
               EXEC SQL DECLARE Customers TABLE 
               ( CustomerID           nchar(5)     NOT NULL
               , CompanyName          nvarchar(40) NOT NULL
               , ContactName          nvarchar(30)
               , ContactTitle         nvarchar(30)
               , Address              nvarchar(60)
               , City                 nvarchar(15)
               , Region               nvarchar(15)
               , PostalCode           nvarchar(10)
               , Country              nvarchar(15)
               , Phone                nvarchar(24)
               , Fax                  nvarchar(24)
               ) END-EXEC.
          *> -------------------------------------------
          *> COBOL HOST VARIABLES FOR TABLE Customers
          *> -------------------------------------------
           01  DCLCustomers.
               03 Customers-CustomerID            PIC N(5)
                                                  USAGE NATIONAL.
               03 Customers-CompanyName           PIC N(40)
                                                  USAGE NATIONAL.
               03 Customers-ContactName           PIC N(30)
                                                  USAGE NATIONAL.
               03 Customers-ContactTitle          PIC N(30)
                                                  USAGE NATIONAL.
               03 Customers-Address               PIC N(60)
                                                  USAGE NATIONAL.
               03 Customers-City                  PIC N(15)
                                                  USAGE NATIONAL.
               03 Customers-Region                PIC N(15)
                                                  USAGE NATIONAL.
               03 Customers-PostalCode            PIC N(10)
                                                  USAGE NATIONAL.
               03 Customers-Country               PIC N(15)
                                                  USAGE NATIONAL.
               03 Customers-Phone                 PIC N(24)
                                                  USAGE NATIONAL.
               03 Customers-Fax                   PIC N(24)
                                                  USAGE NATIONAL.
          *> -------------------------------------------
          *> COBOL INDICATOR VARIABLES FOR TABLE Customers
          *> -------------------------------------------
           01  DCLCustomers-NULL.
               03 Customers-ContactName-NULL      PIC S9(04)  COMP-5.
               03 Customers-ContactTitle-NULL     PIC S9(04)  COMP-5.
               03 Customers-Address-NULL          PIC S9(04)  COMP-5.
               03 Customers-City-NULL             PIC S9(04)  COMP-5.
               03 Customers-Region-NULL           PIC S9(04)  COMP-5.
               03 Customers-PostalCode-NULL       PIC S9(04)  COMP-5.
               03 Customers-Country-NULL          PIC S9(04)  COMP-5.
               03 Customers-Phone-NULL            PIC S9(04)  COMP-5.
               03 Customers-Fax-NULL              PIC S9(04)  COMP-5.
    
  • Verified Answer

    Thanks Again Chris! You're the best!