Re: Opinions on approach, please...



"Robert" <no@xxxxxx> wrote in message
news:j8ip341si77r6t66q7suk382081bbpf28v@xxxxxxxxxx
On Wed, 28 May 2008 11:55:01 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:



"Robert" <no@xxxxxx> wrote in message
news:l80o34ttp1n7q9bch9na06r6f3h45s020d@xxxxxxxxxx
On Tue, 27 May 2008 23:29:35 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

"Robert" <no@xxxxxx> wrote in message
news:dbrm34h6fnvtl296cbteg5d6j76e3cl3a0@xxxxxxxxxx
On Tue, 27 May 2008 11:45:57 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:



"Robert" <no@xxxxxx> wrote in message
news:5nvl34pdmegh0jld4fnl3t0e80phj5k5an@xxxxxxxxxx
On Tue, 27 May 2008 01:47:26 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

I can detect which key field is being used, I can detect the
relation,
I
can
get the data for the condition, and I can probably handle up to 3
connected
conditions, but I still don't havea clear idea of how I'll get this
into
MOST... :-)

Write the where clause manually, one time.

Do you mean in the Application?

In the data layer. Create a data layer program per table, containing
all
the SQL for that
table.

That is exactly what I'm doing. It cannot contain manually written WHERE
clauses because these are application dependent. I am looking for it to
have
a GENERAL WHERE clause structure which it can populate with data from
the
interface and EXECUTE. It is tricky, but I don't think it is impossible.
There may have been some misunderstanding here.

It's not that simple. The statement will say

EXECUTE :handle USING :host-variable1, host-variable2, ...

The number of host variable pointers, which is fixed at compilation
time,
must equal the
number of bind variable tokens in your dynamic SQL.

Suppose I PREPARE a statement that references 1 or more Host variables and
then simply EXECUTE that without passing it any parameters?

Example:

instead of: PREPAREing a parameterized statement like:

SELECT CustName FROM Customers WHERE CustID = ?

...and executing it as: EXECUTE handle USING "10001"

I was to say:

SELECT CustName FROM Customers WHERE CustID = :HVCustID

...then...

Move "10001" to HVCustID
EXECUTE handle

Can I do that? (I know I could set it up and try it, but I'm hoping to
save
some time and also get your opinion on the approach taken.)

You can't do that. HVCustID is in the client's memory.

Not sure what you mean by "Client", this would be in the MOST server and the
HV would have been declared in the program doing the PREPARE.

Nevertheless, I'll take it that that's non-viable. Thanks.


The server can't access the
client's memory. In the case of statically compiled SQL, the preprocesor
tokenizes the
reference, called a bind variable, and passes the value in a message, or
receives a value
from the server and copies it into your host variable. In the case of
dynamic SQL, you
explicitly code dummy words as bind variable placekeepers, put the host
variables on the
other side of USING/INTO. In both cases, the server never sees the host
variable names,
only the placekeeper bind variables.

OK, thanks for the clarification.


PREPARE xyz FROM 'SELECT CustName FROM Customers WHERE CustID = :BV1'

EXECUTE xyz USING :HVCustID INTO :HVCustName

The number of bind variables must equal the number of USING host
variables. The number of
columns in the select list must match the number of INTO host variables.

As I said, there is a way to control the number of variables at execution
time using
DESCRIBE. My guess is that 1 programmer in 100 has ever used DESCRIBE.

If I can legally do this and PREPARE late binds my statement I wouldn't
need
to worry about parameter lists at all.

You wouldn't have to worry about parameter lists for USING if you replaced
:BV1 with the
value in HVCustID, written as a literal. But you'd have to PREPARE
(compile) the statement
every time you execute it. That is slow and abusive. It doesn't solve the
problem in the
other direction, the INTO.

Noted. Thanks.

That's why I recommended doing it once to see what's involved.


I'm still building infrastructure, but I will do this as you suggest, when
I
get to the nitty-gritty.

If you don't consider it now, you won't appreciate that you're stepping
into a mine field
with dynamic SQL.


I have negotiated a few minefields in my time... I'm still walking :-)

I once used dynamic SQL when I had to load and unload several hundred
tables. I read the
data dictionary, the REAL one in the database not my own home-grown
version, and
dynamically built the SQL to load or unload ANY table. Why did I write my
own when Oracle
supplies several such tools? Because it was an Informix database. Because
the program
could read Cobol indexed files as well as flat files. Mostly, because it
was fun. :)

To me, that is usually reason enough to do anything :-)


I mention that to show, even though I have the skill to do it, I wouldn't
use dynamic SQL
if I were in your position.

Yes, I understand your point, Robert. I just can't do this with manual
amendments. I'd rather not do it at all. We have computer technology and it
is good. I want to use it.


There IS a way to modify the bind variable list at execution time. It is
the DESCRIBE
verb, which is in guru territory. Your frustration trigger will be
pulled
when you
discover DESCRIBE changes the number of arguments and their names, but
not
their types.
You'll wind up converting everything to and from strings (varchars) or
much worse,
enbedding data in the SQL 'just to get it working'.

I'll try and avoid that and keep my statements simple.

The practical solution is a separate entry point (method) for each SQL
statement.
Usually, the only difference is the hard coded WHERE clause.


Interesting. Thanks.

Yes, the layer itself has to be written in COBOL, but it will be a COM
server. As such it can be activated from ANY language. It would be
embedded
in application COBOL if I allowed SQL in the application programs.

That's why I don't want SQL in the application code. Just an invocation
of
the COM component.

That's the classical three layer client/server model.

Yes. I have been advocating and using it for nearly two decades now... :-)

Don't assume the data layer must execute on the same machine as the
application. It is
semi-common for the invocation to send a message through middleware.

I don't make assumptions and I have designed and developed distributed
applications. I was using distributed COM+ when everyone was saying how
vulnerable it was and how it could never work. It worked fine and we were
never hacked. (Since then, things have moved on and .NET is the way to go
for Win Users, now. This current exercise is a step in that direction.



The where clause on UPDATEs is simple if you get in the habit of
always
fetching a ROWID.
It's faster too. Same for IN followed by a subselect.

Is ROWID a feature of the RDBMS or is it a column you have defined? Not
familiar with this.

It's a database feature. ROWID is your friend.

Thanks. I've never used it but will check it out and do so.

Have you ever tried putting host variables into dynamic SQL?
No, I don't think so. I have limited experience with dynamic SQL in
general,
but I am learning rapidly... :-)


If not, do it once to see
what's involved before you go down that road. It's a pain and it is
error-prone.

I'll take your word for it. What alternative would you propose?

Hard code WHERE clauses.

I'm coming to the conclusion that that won't be an option, but I haven't
decided yet. Until I actually get to coding it and trying it, I'll keep an
open mind.

It works better if you design first, code second. In the Good Old Days, we
caught a lot of
criticism for trying to do them ambivalently. :)

Approaches to problem solving tend to be very subjective things. What works
well for one person doesn't work so well for another. I have tried many
approaches over the years, including "designing" and "evolving", and
sometimes just the "flash of insight" that comes after a night's sleep when
the subconscious has been beavering away without my knowledge... :-)
Sometimes coding first helps to reveal technical problems with a design, or
suggest an approach that will mean less coding, sometimes it is all
pointless and needs to be redone because the overall concept wasn't grasped
properly and designed first.

I use combinations of all of the above, although I have leaned towards
"evolving" systems during the last decade. This means having a clear concept
of "HOW" it can be done (not necessarily to the lowest level of detail) and
knowing what the goals and deliverables are, then iteratively coding a
solution towards that.


I'm still not clear on where this hard code would be and how it would
work.
You say in the data layer, but I don't see how I can hard code clauses
that
I don't know about until run time. I don't like this scenario and I don't
want to have to manually amend every MOST component before I run an
application against it. I may have misunderstood what you're suggesting or
you may not understand the approach I am taking. Either way, I can't
manually amend MOST.

Use MOST to write MOST (appropriate name) of the data layer. If you think
you can generate
the WHERE clause, give it a try. Then hand tailor the WHERE clauses in the
data layers.

Certainly MOST will contain ALL of the SQL code. I intend to try the dynamic
SQL approach. You may be right and I will end up making significant manual
"adjustments" but I have to try... :-)

Keep in mind, the Cobol ISAM is all static. There's no such thing as
dynamic ISAM. I
really doubt your applications are building FCDs on the fly.

No, of course they are not.


I see MOST as a precompiler, not a runtime thing.


No, the "precompiler"part is inserting the INVOKEs of MOST into the existing
app code. MOST is a compiled COM server that uses the data in the interface
to provide a service to the application programs.

It ISN'T a one time thing, but the action of amending the apps to INVOKE it,
should be. This pre-compile step will be part of my existing Migration
Toolset and is being written in C#.

Convert the ISAM IO to data
layer SQL, then manually tailor where clauses.

If I do that I'd need to tailor manually every single COM server, and
change
it manually for every application program that uses that particular ISAM
file, before inserting invokes into that particular application.

Yep. That's what we do here an every other place I've worked. We have
hundreds of data
layers, each containing about ten functions.

I reckon you guys haven't tried hard enough... :-)

Seriously, I value the experience you have and the information you have
provided, Robert.. Nevertheless, I need to have a go at this and I believe
I can get a solution. If I crash and burn, you can tell me "I told you so"
and no hard feelings... :-)



We have another layer, called services, that perform common
(multi-application) functions
involving multiple tables. For instance, creating a new customer hits
20-40 tables.


I think you are operating on a large commercial scale where you are probably
bound by considerations that I'm not. My solution is aimed at SMEs who don't
have loads of money and don't have HUGE (say, more than 500 ISAM files)
requirements either.

Thanks for your post.

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


.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... explicitly code dummy words as bind variable placekeepers, put the host variables on the ...
    (comp.lang.cobol)
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • Re: Opinions on approach, please...
    ... Write the where clause manually, ... In the data layer. ... I worked at one place that had all the SQL in the database. ... one to get the SQL and a second to execute it. ...
    (comp.lang.cobol)
  • Re: Access and GUID Column Type
    ... > clauses in the SQL's are exactly the same. ... Perhaps you could assign the SQL statement to ... - execute the statement that is returned by the Debug.WriteLinein Query ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Injection- Bypassing magic_quotes
    ... Because i was trying to execute: ... Don't terminate the query and you most ... Or try something like bobcat or one of the other SQL injection tools out ... Chief Information Security Officer ...
    (Pen-Test)