Absent Member.
Absent Member.
2587 views

Looking for database programming example for green screen

Jump to solution

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

0 Likes
2 Solutions

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

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.

View solution in original post

0 Likes
Absent Member.
Absent Member.

Thanks Again Chris! You're the best!

View solution in original post

0 Likes
5 Replies
Micro Focus Expert
Micro Focus Expert

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.

View solution in original post

0 Likes
Absent Member.
Absent Member.

Thanks!

0 Likes
Absent Member.
Absent Member.

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

0 Likes
Micro Focus Expert
Micro Focus Expert

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

Thanks Again Chris! You're the best!

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.