Home > Archive > Cobol > April 2005 > Embedded SQL, PL/SQL in Cobol
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Embedded SQL, PL/SQL in Cobol
|
|
|
| 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?
| |
|
| 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
| |
|
| 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.
| |
|
| Sorry about my newsgroup ettiquette. I will wait until the mentioned
point on a solution.
| |
| Michael Mattias 2005-04-28, 8:55 pm |
| "Jeff" <jmoore207@hotmail.com> wrote in message
news:1114709720.966871.107470@o13g2000cwo.googlegroups.com...
> 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.
???
What happened to ...
"I am new to SQL and PL/SQL and my company is considering cutting out some
middleware software we have that talks to Oracle.."
What do you want to do... cut out the middleware (replacing with ESQL), or
fine-tune your SQL? Those are two totally different things.
If you are talking about doing some maintenance on this software because of
performance... sure, everyone's SQL can probably use a little tuning (mine
sure could!)... but I have to believe that eliminating the "middleware"
MIGHT solve any such problems, quickly.
If that "middleware" is a complete extra external program which starts and
ends each time you want to access the database, for sure that overhead is a
killer. If it's a linked library, then you should probably be looking at
tuning the SQL, since you probably will not be gaining a whole lot by
changing from one library (current) to another (the library linked when you
use embedded SQL) (unless it's a pretty crummy library).
Lastly (or maybe even firstly!), you should look at the overall database
access in your program. Sometimes seemingly 'little' things like PREPAREing
statements and executing with parameters instead of doing an EXECUTE DIRECT,
or using a cursor instead of doing multiple SELECTs can make a world of
difference.
--
Michael Mattias
Tal Systems, Inc.
Racine WI
mmattias@talsystems.com
| |
|
| 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
| |
| Frederico Fonseca 2005-04-28, 8:55 pm |
| On 28 Apr 2005 10:11:12 -0700, "Jeff" <jmoore207@hotmail.com> wrote:
>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?
As others have mentioned, and also myself on another forum, you really
have to give us some code examples of what you are trying to achieve
with the change.
I work with Oracle and COBOL(obviously), and I have created interfaces
between COBOL and Oracle (and DB2 and Sybase and Informix), and I
along with others on this group will be able to give you some
pointers/ideas AFTER we see your examples. Not before.
Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
| |
|
| 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.
| |
|
| Also, not sure if I should use host tables to hold info?
| |
|
| 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
| |
|
|
|
|
|