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



"Robert" <no@xxxxxx> wrote in message
news:i8hqe4p7ec1o2bmh83qg0irkcmjm3el506@xxxxxxxxxx
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 was. But I can assure you I am only using host variables for data. For a
comparison relation (>, =, < , for example...) I can't put that into a host
variable, so I build it dynamically. However if the relation is the same and
it is the same key of reference, then I don't need to re-PREPARE the
statement. So I "remember" the last used relation and key of reference. (KOR
data is set to a host variable and this is a parameter to the dynamic SQL.


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.

It seems to vary in different ODBC drivers... :-)

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.

Thanks for that. That is the kind of thing I was looking for; an alternative
to what I did. Actually what I have for SELECT is very similar to that and
it DOES work, so I have no doubt that your code above will also work. The
problem is with INSERT and UPDATE. Neither of them works.

I haven't given up yet and have a couple of ideas to check out tomorrow.

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.

That's a very good point. I hadn't considered that. I could argue that with
such a smallo table, changes in structure are unlikely, but in principle, I
see what you're saying. Thanks for that.


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

I agree wholeheartedly.


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.

Ah, more good stuff...

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.

Now, it's really funny you should say that... :-) I came to exactly that
conclusion this afternoon. I haven't yet decided where to source the two
lists from. I COULD get it from the system structure tables in the DB (the
"dictionary" you suggest), but I also have a metadata file which is
generated when the tables are created. This has more information on it, like
what is OCCURring and what is REDEFINED and, as of quite recently, includes
lengths and offsets :-), so I can easily lift Key of Reference contents from
a data buffer and place them in a Host Variable, without being too concerned
about the data contents and structure. Remember, my goal here is to maintain
a COBOL record layout (which has structures, REDEFINES, and OCCURS on it),
from a database which has none of these things. That record layout is the
interface between my data access layer (DAL) and the applications. The
applications need to function exactly as they always have, except that the
data source has become an RDB instead of an ISAM file. It's the first step
in a Migration process which is described on the COBOL structure tool
website.



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


Yes, I want to avoid INCLUDE and COPY. As the MOSTGEN program has to write
the entire code for the DAL Class that handles this table set, there is no
advantage to me in using COPY or INCLUDE. (And there is a DISadvantage
inasmuch as I have to set and enforce Libraries and Library paths on sites I
really have no control over.) I can simply generate the code into the
program anyway. There will only ever be ONE data access Class that uses
these two lists (for any given ISAM file/Table set) and if anyone was to
modify the underlying ISAM or table set they would have to regenerate the
MOST handler, anyway. The MOSTGEN program is being written in C# so it may
be updated automatically and I may want changes in the way the lists are
generated. For example, a DATE on the RDB has to be mapped against a PIC
9(6) field in the record layout. I can't just move it to a Host variable.

The 'Cstr' function, combined with the IIF and IsNULL functions, converts
the RDB date to a 10 character string as dd/mm/yyyy (it is kind of an ACCESS
equivalent to COALESCE) if it is valid, or returns '01/01/1900' if it
isn't.

009620* Date type field
009621*
009622 "IIF (NOT
IsNull([ARPMF0-CREATED-ON]),CStr([ARPMF0-CREATED-ON]),'01/01/1900'),"
009623

The above would be part of the "field list" and, if it is in a SELECT it
will correspond to a Host Variable of 10 bytes defined by INTO. which would
be in the "HV list"...


I then reformat that into the 6 digits (or 8 or 10 - have to check metadata
during code generation) required by the COBOL record layout...

ARPMF0-CREATED-ON is the field in the ISAM record layout that we are using
as an interface, not the field on the database...

007863 *> date field
007864 move 10 to srcLength
007865 move 6 to tgtLength
007866 move 0 to outfmt8
007867 move HV-ARPMF0-CREATED-ON to src
007868 invoke SELF "DateHandler"
007869 using srcLength
007870 src
007871 tgtLength
007872 tgt
007873 outfmt8
007874 end-invoke
007876 compute ARPMF0-CREATED-ON = function NUMVAL (tgt)
007877 *>move HV-ARPMF0-CREATED-ON to ARPMF0-CREATED-ON

As you can see (and I haven't even shown what has to be done to get it back
to the RDB format, if you're going the other way...
:-)), the "lists" are far from simple and that's another reason I don't want
to use COPY and/or INCLUDE. All of the above is generated on the fly by
MOSTGEN.

The code in the DateHandler method is about 400 lines of COBOL which checks
for all possible date formats in the record layout and adapts the contents
so that the COBOL program sees what it expects, and the database sees a
valid Date type.

It is a lot of work but is running correctly and the process has stopped
crashing because of invalid dates... the COBOL system just moved zero or
spaces to the date field if it wasn't using it. This was disastrous when
trying to load the RDB or manipulate the data on it.

Prefixes like yours are handled with COPY REPLACING.

Yes, normally. Not in this case.

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)'), ...

I figured there's enough dynamic SQL in the process already... :-)

Here's my "brute force" approach...

009321 perform
009322 varying J
009323 from 1
009324 by 1
009325 until J > 6
009326 if ARPMF0-BAL (J) NOT = HV-ARPMF0-BAL (J)
009327 move ARPMF0-BAL (J) to HV-ARPMF0-BAL-SINGLETON
009328 move J to HV-J
009329 EXEC SQL
009330 UPDATE [ARPMF0-RECrg01]
009331 SET [ARPMF0-BAL] = :HV-ARPMF0-BAL-SINGLETON
009332 WHERE [LEVEL-J] = :HV-J AND
009333 [ARPMF0-CD] = :HV-DynamicParm
009334 END-EXEC
009335 *> SQLSTATE SQLMSG
009336 end-if
009337 end-perform

Usually there is only one changed balance. As the record has previously been
accessed with a multi-row fetch, it is still buffered by the RDB so,
overall, it isn't too bad. I increased the buffering in ACCESS and traced it
and it ran to my satisfaction. Updates were deferred until the commit point
as you would expect.



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)

That would be cool. I'll think more on this when the pressure's off. Right
now I just want to get MOSTGEN completed.

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

Thanks for your thoughts, Robert. I found this helpful.

Cheers,

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


.

Relevant Pages

  • Re: COBOL/DB2 Date edit question
    ... Table and column exist, database is connected. ... SQL below but I get a compile error. ... SQL0060W The "COBOL" precompiler is in progress. ... This is addressed by using Host Variables. ...
    (comp.lang.cobol)
  • Re: Dynamiv SQL and multi-row access SQL from COBOL... some quick questions
    ... EXECUTE IMMEDIATE is for one time dynamic SQL. ... Perhaps you were referring to something other than host variables. ... COBOL using ACCESS 2003. ...
    (comp.lang.cobol)
  • Re: COBOL/DB2 Date edit question
    ... SQL below but I get a compile error. ... DB2 V7.2 ... SQL0060W The "COBOL" precompiler is in progress. ... This is addressed by using Host Variables. ...
    (comp.lang.cobol)
  • Re: Dynamiv SQL and multi-row access SQL from COBOL... some quick questions
    ... EXECUTE IMMEDIATE is for one time dynamic SQL. ... Secondly I've had some spectacular failures with SQL multi-row access from ... COBOL using ACCESS 2003. ...
    (comp.lang.cobol)
  • Re: Definition of FACTORY
    ... > taking different elements in the record to create keys. ... COBOL provided nice syntactic file access support initially. ... the SQL part. ... > At a later stage, as need arises, I can include SQL syntax in other ...
    (comp.object)
Loading