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.

Thanks!

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.

Thanks Again Chris! You're the best!