Re: Dynamiv SQL and multi-row access SQL from COBOL... some quick questions

On Thu, 9 Oct 2008 11:22:12 +1300, "Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:



"Robert" <no@xxxxxx> wrote in message
news:02cpe4he45h8ptt42n3ntt0mua667ho8sm@xxxxxxxxxx
On Wed, 8 Oct 2008 23:22:55 +1300, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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?

Yes, a PREPAREd statement can be EXECUTEd multiple times. That's why it
has a handle.

EXECUTE IMMEDIATE is for one time dynamic SQL.

Thanks, Robert.

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.

A PREPAREd statement can and should use bind variables rather than
literals. Remember that
the statement executes on the server, which doesn't have access to the
client's memory,
thus the EXECUTE must pass values. That's done differently on each
database. On Oracle,
names in the PREPARE are meaningless positional tokens, replacement is
done by counting.
Read the manual.

I tried to warn you about this a month ago.

I took your warning on board. and am NOT using literals.

You wrote " 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."

That sounds like you put literal values in SQL. If you are using host variables, there is
no reason to rebuild and PREPARE when values change.

Perhaps you were referring to something other than host variables.

I understand
exactly how it works after spending several hours going through SQL traces.
I hoped that the PREPARE could be reused and thought it probably could be, I
just needed to have it confirmed by someone with more experienc of this than
I have :-)

Telling me to read a manual that is not relevant to my environment is
neither productive nor helpful. I HAVE read everything I can get my hands
on that is pertinent to what I'm trying to do. Thanks for your confirmation
anyway.

I meant the syntax for defining and passing parameters in dynamic SQL varies, because it
was non-Standard in '92.

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.

Of course they're shifted, because you did not explicitly name the fields.

This is an exaact code sample from the Fujitsu manuals , with my field names
substituted. I don't NEED to name the fields if I load to a "Table". COBOL
is supposed to generate conversion for me. It doesn't. Well, actually it
does, it just gets it wrong in this case.

You're counting on the precompiler to expand [ARPMF0-RECrg0] into a list
of components,

No, Parameters...

and on the server to expand * into a list of columns. The expansion of *
is too late for
the precompiler to do a type conversion from number to Cobol type.

Hmmm...maybe... It seems to do it OK when there is only one row involved....

In that case, it does sound like a precompiler bug.

The preferred syntax is

SELECT CD, BAL, LEVEL_J
FROM [ARPMF0-RECrg01]
INTO
: HV-ARPMF0-CD-MULTI,
: HV-ARPMF0-BAL,
:HV-ARPMF0-LEVEL-J
WHERE CD = :HV-DynamicParm

I would expect that to work where the abbreviated form failed.

SELECT * in embedded SQL is an amateurish no-no. There is no excuse
laziness in generated
code.

Tell Fujitsu. Besides, I've been writing SELECT * for a number of years
without problem. (Admittedly, I don't do it that often - because it means
fetching every column - but for tables with a small number of columns (like
this one), and where I need all of them, I see no reason not to do it. It is
an efficient and time-saving shorthand (I wonder if that's why they included
it in SQL...?)What specifically makes you think it is amateurish? Don't just
hand down a lofty verdict, support your case.

The reason is future changes in table structure. If someone adds a column to the table,
programs will all stop working until host variable structures and code are regenerated.

Database structure should not be coupled to programs. Adding a column should not affect
running programs, they just won't see the new column.

For the same reason, the new column should not have a NOT NULL constraint without a
default value. If it does, old programs will fail on INSERT.

The fact that this code is being generated by other code I wrote does mean I
COULD reference every column. But until the construct works, I won't be
generating it, and that means I have to test it by hand...

Big shops handle this by reading the data dictionary (in the database) and generating two
source code files (per language), one "select list" containing column names, the other
"variable list" containing host variable names. This is run by the Make file. The SQL then
looks like

SELECT
INCLUDE arpmf0_select_list
INTO
INCLUDE arpmf0-variable-list
WHERE CD = :HV-DynamicParm

Prefixes like yours are handled with COPY REPLACING.

01 ()--RECrg01.
12 ()-CD-MULTI pic x(8).

COPY arpmf0-variable-list REPLACING ==()== BY ==:HV-ARPFM0==.

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.

Try doing it inside a T-SQL loop.

That's a good suggestion. I'm actually doing it inside a COBOL loop. I only
update changed balances.

A brute force approach is to dynamically construct an INSERT like this:

INSERT [ARPMF0-RECrg01] VALUES
(' : HV-ARPMF0-CD-MULTI(1)', ': HV-ARPMF0-BAL(1)', ':HV-ARPMF0-LEVEL-J(1)'),
(' : HV-ARPMF0-CD-MULTI(2)', ': HV-ARPMF0-BAL(2)', ':HV-ARPMF0-LEVEL-J(2)'), ...

But a triggered procedure might solve the problem
equally well and it then becomes a background process that is no longer my
responsibility... :-)

You would need a stored procedure that accepts a Cobol array and returns a SQL variable of
type table. Then you could INSERT table (col, col) EXEC your_procedure(@param)

Given that Fujitsu can pass a Cobol array to an EXEC of a prepared SELECT, the above has
a good chance of working.
.

Relevant Pages
Loading