Cobol - Embedded SQL, PL/SQL in Cobol

Author

Embedded SQL, PL/SQL in Cobol

Jeff

2005-05-01, 3:55 pm

I am new to SQL and PL/SQL and my company is considering cutting out
some middleware software we have that talks to Oracle. Does anyone know
of any "real World" examples of embedded SQL or PL/SQL?

Chris

2005-05-01, 3:55 pm

Jeff,

Here is a very basic sample that will connect to an Oracle database.
You can always expand the EVALUATE statement to include other various
SQLCODEs so that you can incorporate your own error messages if
desired. Also, you'll need to run this with the Oracle run-time
(rtsora) or build a shared library that you make an initial call to in
order to load the Oracle functions.

Good luck.

Chris

identification division.
program-id. dbconnect.

data division.

working-storage section.

exec sql include sqlca end-exec.

exec sql declare dbname database end-exec.

exec sql begin declare section end-exec.

01 db-user pic x(10).
01 db-passwd pic x(10).

exec sql end declare section end-exec.

procedure division.

move "myLogin" to db-user
move "myPassword" to db-password

exec sql
connect
:db-user
identified by
:db-passwd
at
dbname
end-exec

evaluate sqlcode
when 0
display "You are connected!" end-display
when other
display "Error: " sqlerrmc(1:sqlerrml - 1)
end-display
end-evaluate

goback returning 0

Frederico Fonseca

2005-05-01, 3:55 pm

On 28 Apr 2005 10:35:21 -0700, "Jeff" <jmoore207@hotmail.com> wrote:

>Chris,
>
>How well do you know SQL? I was wondering if I could email you a
>program that uses our middleware and see if you could just point to
>where I need changes etc.

Jeff,

If you start something on a newsgroup it is all so correct that you
keep it within the group unless you reach a point where it is better
to, while a solution is found, proceed to private email.
If dealing with a technical problem you should then return with the
solution.

You have not yet reached the mentioned point here as you have not
given anyone the opportunity of helping you.

Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com

Chris

2005-05-01, 3:55 pm

Jeff,

As Frederico points out - its best to keep this on the newsgroup for
now.

While I have extensive experience with Emdedded SQL in COBOL (as
pertaining to Oracle), by no means am I the end all autority on the
subject. By limiting your resources to just myself, you'd be losing out
on a multitude of other valuable opinions and insight here in the
group.

Perhaps if you would post a chunk of the code using the middleware, as
well as a short explanation of its context, you will get more than one
possible solution for your problem. If no one is able to help out via
your post, then by all means you may e-mail me your code and I'll have
a look. But, if form holds true as I suspect it will, you will get
numerous offerings to your post.

What is it that your doing with the middleware? Here is a simple SELECT
statement for you to look at:

EXEC SQL
SELECT
<column name>
INTO
:host-variable
FROM
<table name>
WHERE
<column name> = ( numeric value, quoted literal(') or
:host-variable )
END-EXEC

As I said, with a look at a piece of your code, and some understanding
of its context, I'm sure you'll get the assistance you need.

Chris

Jeff

2005-05-01, 3:55 pm

The middleware is Open turbo. I will paste the calls to the database.
The program is not very big, I am starting with a small report program
that does a few seperate gets(call).
The middleware uses modes to go "get records"

005900 77 BASE PIC X(24) VALUE " CONSDB.DATABASE;".
006000 77 PASS PIC X(8) VALUE "INQUIRY;".
006100 01 PROCID PIC X(8) VALUE "ALASTSUV".

020200 CALL "DBOPEN" USING BASE, PASS, MODE5, DB-STATUS.
020300 IF C-W NOT = 0
020400 PERFORM DB-ERROR.

022100 MOVE ZERO TO TAPE-REC.
022200 CALL "DBGET" USING BASE, DMBRDETL, MODE2, DB-STATUS,
022300 DALLITEM, MEMBERDETL, DB-ARG-SEP.
* Base is path to database, MEMBERDETL is the memberdetl table, mode2
reads next record serially, DB-ARG-SEP is variable passed.
022400 IF C-W = 11
022500 GO TO RRS-9999-EXIT.
C-W i- 11 is end of file
022600 IF C-W NOT = 0
022700 PERFORM DB-ERROR.
C-W = 0 means found record
022800 MOVE MBRSEP OF MEMBERDETL TO DB-ARG-SEP.
022900
023000 RRS-0200.
023100 CALL "DBGET" USING BASE, DMSMSTR, MODE7, DB-STATUS,
023200 DALLITEM, MBRSEPMSTR, DB-ARG-SEP.
Mode7 reads master record given a search value
023300 IF C-W NOT = 0
023400 PERFORM DB-ERROR.
023500 IF ACCTSTATUS OF MBRSEPMSTR(1) NOT = "A"
023600 GO TO RRS-0100.
023700 PERFORM MOVE-FIELDS THRU MOVE-FIELDS-EXIT.
023800
023900 MOVE RATE OF MEMBERDETL TO WSCR-RATE-CODE OF TAPE-REC.
024000 MOVE CLASX OF MEMBERDETL TO WSCR-SRVC-CODE OF TAPE-REC.
024100
024200 MOVE 0 TO CHECK-FLAG, I, HISTORY-COUNT.
024210 MOVE CLASX OF MEMBERDETL TO WW-CLASS.
024300 PERFORM CHECK-CLASS THRU CHECK-CLASS-EXIT.
024400 IF CHECK-FLAG = 1
024500 GO TO RRS-0100.
024700 MOVE MBRSEP OF MEMBERDETL TO DB-ARG-SEP.
024800 CALL "DBFIND" USING BASE, DMBRHIST, MODE1, DB-STATUS,
024900 SRCH-SEP, DB-ARG-SEP.
Find= sets up chain read
025000 IF C-W = 17
025100 GO TO RRS-0100.
C-W = 17 is no master entry
025200 IF C-W NOT = 0
025300 PERFORM DB-ERROR.
025400 MOVE 0 TO I.
025500 GET-NEXT-HISTORY.
025600 IF I > 11 GO TO RRS-0500.
025700 CALL "DBGET" USING BASE, DMBRHIST, MODE6, DB-STATUS,
025800 DALLITEM, MBRHISTDETL, DB-ARG-SEP.
Mbrhistdetl table, Mode6 reads previous record in chain
025900 IF C-W = 14
026000 GO TO RRS-0500.
C-w = 14 beginning of file
026100 IF C-W NOT = 0
026200 PERFORM DB-ERROR.
026300 IF BILLTYPE OF MBRHISTDETL = 9
026400 GO TO GET-NEXT-HISTORY.
026410 CALL "DATE2Y2K" USING READDATE OF MBRHISTDETL, Y2K-DATE1.
99025037
026420 CALL "DATE2Y2K" USING TDUEDATE, Y2K-DATE2.
99025037
026500*Y2K IF READDATE OF MBRHISTDETL > TDUEDATE
99025037
026510 IF Y2K-DATE1 > Y2K-DATE2
99025037
026600 GO TO GET-NEXT-HISTORY.
026610 CALL "DATE2Y2K" USING READDATE OF MBRHISTDETL, Y2K-DATE1.
99025037
026620 CALL "DATE2Y2K" USING FDUEDATE, Y2K-DATE2.
99025037
026700*Y2K IF READDATE OF MBRHISTDETL < FDUEDATE
99025037
026710 IF Y2K-DATE1 < Y2K-DATE2
99025037
026800 GO TO RRS-0500.
026900 IF READDATE OF MBRHISTDETL NOT = HOLD-READDATE
027000 ADD 1 TO I
027100 MOVE READDATE OF MBRHISTDETL TO HOLD-READDATE
027200 WSCR-DATE(I)
027300 MOVE KWH OF MBRHISTDETL TO WSCR-KWH(I)
027400 MOVE ENERGY OF MBRHISTDETL TO WSCR-REVENUE(I)
027500 ADD FUEL OF MBRHISTDETL TO WSCR-REVENUE(I)
027600 ELSE
027700 ADD KWH OF MBRHISTDETL TO WSCR-KWH(I)
027800 ADD ENERGY OF MBRHISTDETL TO WSCR-REVENUE(I)
027900 ADD FUEL OF MBRHISTDETL TO WSCR-REVENUE(I).
028000 GO TO GET-NEXT-HISTORY.
028100

I am trying to figure out where to start, do I use embedded SQL or
PL/SQL. I have very little knowledge, only what I have been reading in
the Oracle manuals. If I had an idea of where to start and what the
code looks like, I would feel much better about it. I appreciate
everyone's help and sorry of I came across as a smarta$$. I am very
anxious to get one program under my belt. Thank you again and I look
forward to your responses.

Jeff

2005-05-01, 3:55 pm

Also, not sure if I should use host tables to hold info?

Chris

2005-05-01, 3:55 pm

Jeff,

Looking at this quickly, I'd say it's a prime candidate for Embedded
SQL.

The call to "DBOPEN" would be replaced by a SQL connect statement.

The call to "DBREAD" would be replaced by a SQL select statement.

The call to "DBFIND" would be replaced by a SQL cursor declaration and
open statement.

The call to "DBNEXT" would be replaced by a SQL fetch statement (from
your previously established cursor).

The only area you will run into a snag is your call to DBGET with
mode6. There is no "read previous" function in Oracle (not 100% sure
about other databases). What I would do in this circumstance is simply
sort the cursor in the proper decending sequence (time, transactions
nbr, etc) so the history comes out in sequence when simply using "read
next" (FETCH) logic.

I hope this helps point you in the right direction.

Chris

Jeff

2005-05-06, 8:55 am

Thank you Chris!

Sponsored Links