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



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



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....



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 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...


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. But a triggered procedure might solve the problem
equally well and it then becomes a background process that is no longer my
responsibility... :-)

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...

COBOL has nothing to do with it. This is a SQL problem.

No, it isn't. COBOL generates a PREPARE and Parameters for these multi row
update constructs, and passes these low-level calls to the ODBC driver. They
simply don't work because the call being used is deprecated and should be
replaced by a modern equivalent.

The offending call is about setting the parameters for the generated
PREPARE. It SHOULD use a "BIND Parameters" type call, and it uses an
earlier, now deprecated, form instead. The result is that the call it does
use has a zero parameter address, causing it to return -1 and raising the
SQLSTATE S1C00. (This is a bit of a "catch-all" and has little to do with
options not being implemented.)

I have no control over the actual SQL calls generated, but I have watched
the traces and know what it does.

It isn't an SQL problem. It is a COBOL problem because it is generating
outdated calls. Possibly, using a later version of the compiler would solve
it, but I don't have version 7 and I have no intention of getting it. I CAN
work around this problem; I just thought there might be an alternative
construct for multi-row access that could change the generated calls.

If I used C# I wouldn't get these stupid, primitive low-level calls, I
could return an entire result set with one call and disconnect from the DB,
with subsequent processing carried out in memory, and the result set would
automatically reconnect and update when I was finished.

OK, Thanks for your response. You confirmed one thing for me and made me
think about another :-)

Pete.
--
"I used to write COBOL...now I can do anything."


.

Relevant Pages