Dynamiv SQL and multi-row access SQL from COBOL... some quick questions
- From: "Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 8 Oct 2008 23:22:55 +1300
Does anyone KNOW (not just think... I can do THAT myself... :-)) whether a
dynamic SQL statement has to be PREPARED EVERY time you use it, or can it be
PREPARED once and, if the parameters don't change, can you EXECUTE it again,
WITHOUT having to prepare it?
I have some fairly complex dynamic SQL and I think building it and preparing
every time I use it is going to be an overhead I don't really want. I can
"remember" the variable bits and check whether they've changed, in which
case I need to rebuild and PREPARE it again, but if they havent I'd like to
just EXECUTE the same statement that was already PREPARED.
Secondly I've had some spectacular failures with SQL multi-row access from
COBOL using ACCESS 2003.
Consider a table with 6 rows and 3 columns.... (It is a table attached to a
main table and contains 6 currency figures)
The table is called: ARPMF0-RECrg01 where the "rg01" indicates it was
generated to support the first repeating group encountered in the original
COBOL data definition.
the columns are: ARPMF0-CD *> primary key of the master record
ARPMF0-BAL *> a currency balance
LEVEL-J *> an index that ranges from 1 to
6 for any given ARPMF0-CD value
ARPMF0-CD and Level-J together are the primary key of this table.
The table has been generated in response to a COBOL 'OCCURS 6' In the
orginal ISAM data definition.
Of course, by increasing the value of LEVEL-J, any number of balances can be
attached to a given master key, and splitting
out the repeating groups enables proper normalization to at least 2NF.
Here are the Host Variable definitions, designed to allow multi-row
access...
001945
001946* HOST VARIABLE definitions for table: ARPMF0-RECrg01
001947* Generated by MOSTGEN on: 13-Nov-2007 at: 10:21:26.89
001948*
001949* PRIMARY Key field(s): ARPMF0-CD, LEVEL-J
001950*
001951* This is a multi-row structure (table) generated by MOSTGEN
001952* from the actual DECLGEN. It allows multi-row SQL access
001953* although this has been found not to work for insert and update.
001954 01 HV-ARPMF0-RECrg01.
001955 12 HV-ARPMF0-CD-MULTI pic x(8) occurs 6. *>
Master generic key
001956 12 HV-ARPMF0-BAL pic s9(14)v9(4) occurs 6. *>
Currency field
001957 12 HV-ARPMF0-LEVEL-J pic s99 occurs 6. *>
primary key component
Here's some program code...
009284*
009285* NONE OF THIS 'ELEGANT' CODE WORKS!!!
009286* Compiles OK... but
009287* (This should load the 6 rows with each of the 3 fields, using a
single SQL statement)
009288* set RG-x1 to 1
009289* EXEC SQL
009290* SELECT * FROM [ARPMF0-RECrg01]
009291* INTO :HV-ARPMF0-RECrg01
009292* WHERE [ARPMF0-CD] = :HV-DynamicParm
009293* END-EXEC
009294*
009295*... this loads the data fine but gives incorrect decimal alignment in
the balances. They are all shifted by 100.
009296**
009297* (This should update the 6 rows on the table with a single
statement...)
009298* EXEC SQL
009299* UPDATE [ARPMF0-RECrg01]
009300* SET [ARPMF0-BAL] = :HV-ARPMF0-BAL
009301* WHERE [ARPMF0-CD] = :HV-ARPMF0-CD-MULTI AND
009302* [LEVEL-J] = :HV-ARPMF0-LEVEL-J
009303* END-EXEC
009304*
009305*...this multi-row update returns SQLSTATE S1C00 "Optional feature not
implemented"
009306** Looks like MS ACCESS does not support multi-row update so we have
to do
009307* it by brute force... only change the fields we need to.
009308* Let's see what's changed...
009309* This single select loads all 6 balances, correctly aligned... so we
can compare with what we are trying to update
009310* Unfortunately it doesn't work with INSERT and UPDATE...
(This is the only multi-row access that works exactly as it
should...it even loads the key correctly 6 times and the values of LEVEL-J)
009311 set RG-x1 to 1
009312 EXEC SQL
009313 SELECT [ARPMF0-BAL]
009314 INTO :HV-ARPMF0-BAL
009315 from [ARPMF0-RECrg01]
009316 WHERE [ARPMF0-CD] = :HV-DynamicParm
009317 END-EXEC
009318 move SQLERRD (3) to RG-noloaded (RG-x1) *> returns the number of
rows loaded
009319
I checked out the SQL trace and found that Fujitsu COBOL Version 6 generates
deprecated SQL to handle Parameters for the INSERT and UPDATE calls, and
that is where the process fails...
There is very little I can do about that except scrub round it...
If anyone has had any experience with multi-row INSERT/UPDATE and can
suggest an alternative construct, I'd be glad to hear it. I tried everything
I could think of.
If I were doing this for myself, in C#, it would be a multi-row resultset
through OLE or ODBC and none of these problems would arise. Unfortunately,
it isn't for me and has to be in COBOL...
Cheers,
Pete.
--
"I used to write COBOL...now I can do anything."
.
- Follow-Ups:
- Prev by Date: Re: Here's one problem
- Next by Date: Linkage Section variables
- Previous by thread: Re: Flowchart of Cobol code
- Next by thread: Re: Dynamiv SQL and multi-row access SQL from COBOL... some quick questions
- Index(es):
Relevant Pages
|
Loading
