Reading Next/Previous Row in SQL

What would be the best way to read next and/or previous row in an SQL database using the “SELECT” statement in Managed COBOL? For example, suppose I have the following table and whereby the key is “Name”:

 

Name       Age

John         20

Jane         25

Rob           30

 

Is there another statement besides the “Select” that would be more appropriate? Thanks in advance for your help.

You'd typically use Cursor processing

1) Define your cursor using a select statement that will return 0 to all records in the table

2) Open your cursor

3) Loop through fetching the next record or previous record

4) When done close the cursor.



Bottom line read up on cursors.

You'd typically use Cursor processing

1) Define your cursor using a select statement that will return 0 to all records in the table

2) Open your cursor

3) Loop through fetching the next record or previous record

4) When done close the cursor.



Bottom line read up on cursors.

Can you provide a link where I can "read up on cursors"?

Anything containing a sample code would be really helpful. 

Look in the Samples browser in the Visual COBOL group on the start menu. There is a SQL category and the select sample demonstrates cursors.

Also look at this video here:

Can you provide a link where I can "read up on cursors"?

What database are you planning to use are there may be some differences in syntax just like regular SQL.

You'll find some sample code with the product. You can search the docs for Cursor

Google "embedded sql cursor processing" will get you some articles



****************************************************************

* (C) Copyright Micro Focus 2010-2019 or one of its affiliates.

*

* This sample code is supplied for demonstration purposes only

* on an "as is" basis and is for use at your own risk.

*

****************************************************************

working-storage section.



exec sql INCLUDE SQLCA end-exec



*> after an sql error this has the full message text

01 MFSQLMESSAGETEXT pic x(250).

01 IDX pic x(04) comp-5.



exec sql BEGIN DECLARE SECTION end-exec

*> Put your host variables here if you need to port

*> to other COBOL compilers



exec sql END DECLARE SECTION end-exec

exec sql INCLUDE mftest end-exec





procedure division..



exec sql

WHENEVER SQLERROR perform OpenESQL-Error

end-exec



exec sql

CONNECT TO Northwind

end-exec

*> Put your program logic/SQL statements here



exec sql

WHENEVER SQLERROR CONTINUE

end-exec



exec sql

DROP TABLE mftest

end-exec



exec sql

WHENEVER SQLERROR perform OpenESQL-Error

end-exec



exec sql

CREATE TABLE mftest (

mftablekey int NOT NULL,

mfchar1 char(50),

mfchar2 char(50),

mfint1 int,

mfint2 int,

mfvarchar varchar(100)

)

end-exec



perform varying mftest-mftablekey from 1 by 1

until mftest-mftablekey > 10000

exec sql

INSERT INTO mftest

(mftablekey

,mfchar1

,mfchar2

,mfint1

,mfint2

,mfvarchar

) VALUES

(:mftest-mftablekey

,:mftest-mfchar1:mftest-mfchar1-NULL

,:mftest-mfchar2:mftest-mfchar2-NULL

,:mftest-mfint1:mftest-mfint1-NULL

,:mftest-mfint2:mftest-mfint2-NULL

,:mftest-mfvarchar:mftest-mfvarchar-NULL

)

end-exec

end-perform



exec sql commit end-exec.



exec sql DISCONNECT CURRENT end-exec

goback.

stop run.

*> Default sql error routine / modify to stop program if needed

OpenESQL-Error Section.



display "SQL Error = " sqlstate " " sqlcode

display MFSQLMESSAGETEXT

goback.

Look in the Samples browser in the Visual COBOL group on the start menu. There is a SQL category and the select sample demonstrates cursors.

Also look at this video here:

Chris, thank you! This is exactly what I was looking for.