****************************************
* Program name: sql.cbl
* This program demonstrates the use of
* SQL with CS Professional Edition.
*
* Copyright 2000 Deskware, Inc.
****************************************
* Syntax: OPENDB USING
*
*
* .
*
* Syntax: CLOSEDB USING .
*
* Syntax: EXEC SQL
*
* END-EXEC.
*
*
* Example SQL Syntax
*
*
* CREATE TABLE
* EXEC SQL
* create table customer
* ( firstname varchar(20),
* lastname varchar(20),
* description varchar(50))
* END-EXEC.
*
* INSERT
* EXEC SQL
* insert into customer
* values (:customer_first_name,
* :customer_last_name,
* :customer_description)
* END-EXEC.
*
* DELETE
* EXEC SQL
* delete from customer
* where firstname = 'dean6'
* END-EXEC.
*
* UPDATE
* EXEC SQL
* update customer
* set description = 'update test again'
* where firstname = :customer_first_name and
* lastname = :customer_last_name
* END-EXEC.
*
* SELECT
* EXEC SQL
* select firstname, lastname, description
* into :customer_first_name,
* :customer_last_name ,
* :customer_description
* from customer
* where firstname = 'dean8 '
* END-EXEC.
*
* DECLARE
* EXEC SQL
* declare cust_cursor cursor for
* select firstname, dollar_amount
* from customer
* order by firstname
* END-EXEC.
*
* OPEN
* EXEC SQL
* open cust_cursor
* END-EXEC.
*
* CLOSE
* EXEC SQL
* close cust_cursor
* END-EXEC.
*
* FETCH
* EXEC SQL
* fetch relative :row_position cust_cursor
* into :customer_first_name, :customer-dollar-amount
* END-EXEC.
*
* FETCH Syntax:
* FETCH {NEXT | PRIOR | FIRST | LAST
* | ABSOLUTE {int-constant | host-constant }
* | RELATVIE {int-constant | host-constant }}
* cursor-name INTO host-variable [,...]
*
* COMMIT
* EXEC SQL
* commit
* END-EXEC.
*
* ROLLBACK
* EXEC SQL
* rollback
* END-EXEC.
* Include the SQL variable copybook.
COPY `sql.cpy`.
1 data_source_name PIC x(50).
1 user_id PIC x(10).
1 password PIC x(10).
1 return_code PIC s9(05).
1 row_position PIC s9(05).
1 formatted_balance PIC $$$,$$$.99.
1 customer_table.
5 customer_first_name PIC x(20).
5 customer_last_name PIC x(20).
5 customer_description PIC x(50).
5 customer_balance PIC 9(06).99.
MAIN.
PERFORM CONNECT_TO_DATABASE.
PERFORM CREATE_TABLE.
PERFORM ALTER_TABLE.
PERFORM CREATE_INDEX.
PERFORM INSERT_INTO_TABLE.
PERFORM SELECT_FROM_TABLE.
PERFORM BUILD_CURSOR.
PERFORM UPDATE_TABLE.
PERFORM DELETE_FROM_TABLE.
PERFORM DROP_INDEX.
PERFORM DROP_TABLE.
PERFORM DISCONNECT_FROM_DATABASE.
GOBACK.
CONNECT_TO_DATABASE.
MOVE `myaccess` TO data_source_name.
MOVE `test` TO user_id.
MOVE `testpass` TO password.
OPENDB USING data_source_name
user_id
password
return_code.
IF return_code = 1
DISPLAY `Connection to database established`
ELSE
DISPLAY `Connection to databse failed`
END-IF.
CREATE_TABLE.
EXEC SQL
create table customer
(firstname varchar(20),
lastname varchar(20),
description varchar(50) )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Create table successful`
ELSE
DISPLAY `Create table failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
ALTER_TABLE.
EXEC SQL
alter table customer add
balance currency
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Alter table successful`
ELSE
DISPLAY `Alter table failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
CREATE_INDEX.
EXEC SQL
create index cust_index on customer (firstname)
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Create index successful`
ELSE
DISPLAY `Create index failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
INSERT_INTO_TABLE.
MOVE `John` TO customer_first_name.
MOVE `Doe` TO customer_last_name.
MOVE `Senior Director at ACME Software House` TO customer_description.
MOVE 49.95 TO customer_balance.
EXEC SQL
insert into customer
values (:customer_first_name,
:customer_last_name,
:customer_description,
:customer_balance )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Insert statement #1 successful`
ELSE
DISPLAY `Insert statement #1 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
insert into customer
values ('Jane',
'Doe',
'Senior Programmer',
123.95 )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Insert statement #2 successful`
ELSE
DISPLAY `Insert statement #2 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
insert into customer
values ('Matt',
'Doe',
'Junior Programmer',
23.00 )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Insert statement #3 successful`
ELSE
DISPLAY `Insert statement #3 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
insert into customer
values ('Charles',
'Doe',
'GEM Programmer',
199.99 )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Insert statement #4 successful`
ELSE
DISPLAY `Insert statement #4 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
commit
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Commit of insert #1,2,3,4 successful`
ELSE
DISPLAY `Commit of insert #1,2,3,4 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
insert into customer
values ('Jason',
'Doe',
'Programmer',
99.00 )
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Insert statement #5 successful`
ELSE
DISPLAY `Insert statement #5 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
rollback
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Rollback of insert #5 successful`
ELSE
DISPLAY `Rollback of insert #5 failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
SELECT_FROM_TABLE.
EXEC SQL
select firstname, lastname, description, balance
into :customer_first_name,
:customer_last_name ,
:customer_description,
:formatted_balance
from customer
where lastname = 'Doe' and
firstname = 'Charles'
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Select into statement successful`
DISPLAY `firstname: ` & customer_first_name
DISPLAY `lastname: ` & customer_last_name
DISPLAY `description: ` & customer_description
DISPLAY `balance: ` & formatted_balance
ELSE
DISPLAY `Select into statement failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
BUILD_CURSOR.
EXEC SQL
declare cust_cursor cursor for
select firstname, lastname, balance
from customer
order by balance
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Declare cursor successful`
ELSE
DISPLAY `Declare cursor failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
open cust_cursor
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Open cursor successful`
ELSE
DISPLAY `Open cursor failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
DISPLAY ``.
DISPLAY `firstname lastname balance`.
DISPLAY `----------------------------------------------------`.
DISPLAY `last`.
EXEC SQL
fetch last cust_cursor
into :customer_first_name, :customer_last_name, :formatted_balance
END-EXEC.
IF sqlnativeerror = 0
DISPLAY customer_first_name & ` ` & customer_last_name & ` ` & formatted_balance
ELSE
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
MOVE -1 to row_position.
EXEC SQL
fetch relative :row_position cust_cursor
into :customer_first_name,:customer_last_name, :formatted_balance
END-EXEC.
IF sqlnativeerror = 0
DISPLAY customer_first_name & ` ` & customer_last_name & ` ` & formatted_balance
ELSE
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
PERFORM FETCH_DATA_FROM_DATABASE
until sqlnativeerror = 100.
DISPLAY `----------------------------------------------------`.
DISPLAY ``.
EXEC SQL
close cust_cursor
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Close cursor successful`
ELSE
DISPLAY `Close cursor failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
FETCH_DATA_FROM_DATABASE.
EXEC SQL
fetch next cust_cursor
into :customer_first_name,:customer_last_name, :formatted_balance
END-EXEC.
IF sqlnativeerror = 0
DISPLAY customer_first_name & ` ` & customer_last_name & ` ` & formatted_balance
END-IF.
UPDATE_TABLE.
MOVE `Doe` TO customer_last_name.
EXEC SQL
update customer
set lastname = 'Jones'
where firstname = 'Matt' and
lastname = :customer_last_name
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Update statement successful`
ELSE
DISPLAY `Update statement failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
commit
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Commit of update statement successful`
ELSE
DISPLAY `Commit of update statement failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
DELETE_FROM_TABLE.
EXEC SQL
delete from customer
where firstname = 'Charles' and lastname = 'Doe'
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Delete statement successful`
ELSE
DISPLAY `Delete statement failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
EXEC SQL
commit
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Commit of delete statement successful`
ELSE
DISPLAY `Commit of delete statement failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
DROP_INDEX.
EXEC SQL
drop index cust_index on customer
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Drop index successful`
ELSE
DISPLAY `Drop index failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
DROP_TABLE.
EXEC SQL
drop table customer
END-EXEC.
IF sqlnativeerror = 0
DISPLAY `Drop table successful`
ELSE
DISPLAY `Drop table failed`
DISPLAY `sqlstate: ` & sqlstate
DISPLAY `sqlnativeerror: ` & sqlnativeerror
DISPLAY `sqlerrormessage: ` & sqlerrormessage
END-IF.
DISCONNECT_FROM_DATABASE.
CLOSEDB USING return_code.
IF return_code = 1
DISPLAY `Connection to database closed successfully`
ELSE
DISPLAY `Connection to databse failed to close`
END-IF.