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
Parents
  • 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.
    
Reply
  • 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.
    
Children
No Data