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.
With the Where phrase using lower/greater or with Order phrase!
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"?
Can you provide a link where I can "read up on cursors"?
Anything containing a sample code would be really helpful.
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.
77 4th Avenue
Waltham, MA 02451 USA
