Re: embedded SQL in COBOL

LX-i wrote:
Frank Swarbrick wrote:

Frank,

Just read Daniel's detailed reply. This is NOT THE SOLUTION for you, but just an outline of what I do using OO with Net Express. Maybe you can get some ideas from it, and if possible try and emulate OO by using Entry Points.

I still have 'Learner' plates up for SQL - fortunately with N/E, having defined your DB (MS Access in my case), it allows you to select Query Type and it generates from EXEC to END-EXEC including the troublesome NULLs. (The N/E feature also generates a copyfile required by both SQL and your COBOL programs).

Pete Dashwood advised 'don't get seduced by add-on features from various DBs available and use the (R)DBMS as a 'repository'. I took him at his word sticking with a Three-Tier System approach :-

..............
. DBI Main .
..............
^
^
.............. ............. ............ .........
.. Edit . . Materials . . Materials. .SQL .
.. Materials .>>>. DBI .>>>. Table .>>>.Errors .
.............. ............. ............ .........

DBI above = Database Interface. Not shown in the above flowchart is a template for Dialogs to which I pass creation parameters(properties) and subsequently send data or receive data back - using the class Edit Materials as the Controller, (The Stromboli to Pinocchio and his little friends :-) ).

Materials DBI - solely devoted to the one Table and is used to pass invokes to the Materials Table to create a Sorted Collection which I want in a Listbox in the Materials Dialog. Rather than repeat code in DBI Materials there's a super class DBI Main where I construct any empty Collections/Lists I require. The actual filling, (population) of the Collection occurs in Materials DBI.

Materials Table - that so far is the only 'program' that contains any SQL statements and obviously if I hit errors I invoke SQL Errors to generate a message box.

I recall you were concerned with one or more programmers writing programs updating different fields in a common table - so was I - so my only accessing is in Materials Table. The methods I have are :-

Delete Record - DELETE FROM
getSpecNoGrade - SELECT DISTINCT (just so I can print the ID)
MakeCollection - CURSOR SELECT DISTINCT
ReadRecord - SELECT DISTINCT
RewriteRecord - UPDATE
WriteRecord - INSERT INTO

Rewrite and Write with a DB ? I stayed with COBOL names - then EditMaterials could use the same invoke messages with either a DB Table or a COBOL File.

This particular Table "MakeCollection", I only require a listing in ascending sequence by Material IDs (ANSI codes used by ASME) - so my requirement is simple. Look back at the flowchart when I invoke MaterialsTable from MaterialsDBI. See code below. I pass the object reference of MaterialsDBI (lnk-DBI) and the method literal (lnk-MethodName) that the data is to be returned to. When in the SQL Table I pass each row back to the DBI - best explained with code :-

(Nothing unique about the Cursor #'s - they are just the next one that N/E generates).

EXEC SQL
DECLARE CSR57 CURSOR FOR SELECT DISTINCT
`A`.`MatID`
,etc....
FROM `Materials` A
END-EXEC

EXEC SQL OPEN CSR57 END-EXEC
PERFORM UNTIL SQLSTATE <> "00000"
EXEC SQL
FETCH CSR57 INTO
:A-MatID:A-MatID-NULL *> NULLs are what N/E generates

,:A-MainType:A-MainType-NULL
,etc....
END-EXEC

Evaluate true

when SQLSTATE = "00000"
initialize ls-MatListRecord
move A-MatId to Mat-Id
move A-SpecNo to Mat-SpecNo
move A-GradeNo to Mat-GradeNo
move A-MainType to Mat-MainType
move A-SubType to Mat-SubType
invoke lnk-DBI lnk-MethodName using ls-MatListRecord

*> Note : above data is returned to lnk-DBI to add
*> the appropriate collection/dictionary entry

***If you like, at this point there is a 'break' in the CURSOR routine ***with some row info being returned to a specific method in ***MaterialsDBI

when SQLSTATE = "02000" *> no more rows
EXIT PERFORM
when other
move A-MatID to ws-ErrorID
invoke self "setErrorMessage"
set TableError to true
EXIT METHOD
End-evaluate

END-PERFORM
EXEC SQL CLOSE CSR57 END-EXEC

Now given up front that I had a series of regularly used CURSOR SELECTS, yes I would be inclined to put them into Materials Table - and still could, just so long as I recompile the Materials DBI and Table with any added methods.

An alternative to that could be one standard CURSOR SELECT and have the data returned to new additional method-names in Materials DBI where the relevant columns are stripped off to give me new collections.

I don't know how you could 'interrupt' the CURSOR SELECT as illustrated above - that's one for you :-).

Your UPDATES, without knowing the impact/performance because of volumes, in my ignorance, I would go with ONE BIG BANG and have just one method of INSERT or UPDATE - with 400 columns you would have a HUGE method spreading over several sheets of paper. In my case I pass the full record from Edit Materials to the appropriate method in the SQL Table. But if viable, I would suggest write once and it works for all situations.

When it comes to DB Tables, it's not a comparison of apples to oranges, but apples(McIntosh) to apples(Granny Smith).

As I said this is not a solution but some of what I'm doing may give you ideas. If interested I can send you the source code.

BTW - Daniel - God speed and protection. With your youthful enthusiasm you are kinda 'special' to many of us.

Jimmy, Calgary AB
.