Re: Opinions on approach, please...



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


I REALLY don't want to do that. As stated elsewhere, I don't want ANY SQL
in
the applications at all. All I want to see there is an invocation of the
MOST interface, with the original COBOL ISAM access commented out above
it.

It is important for future language migration to create a data access
layer
that is not embedded in COBOL.

When I suggested writing data layers in C#, you said they HAD to be in
Cobol.

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.

It's pretty hard to explain this, and I know I haven't done a good job of
it... sorry :-).


Obviously, I can't write specific accesses into MOST manually.

I worked at one place that had all the SQL in the database. Every
operation requaired two,
one to get the SQL and a second to execute it. It was a horrible solution.
That's what you
will wind up with if you refuse to write SQL in data layer code.

No, the SQL IS in the data layer code. It is implemented as a COM component
written in COBOL.


The real problem is how to process the data from the interface, regarding
the relation in the "condition" clause of the START statement, inside
MOST.

START is the easiest case. It is almost always 'key not less than
:variable'. Singleton
selects have more variation.

Yes, I'm realizing that... :-) I'm hoping to get a general mechanism that
can build ANY WHERE clause, whether it comes from a START condition or a
discrete ISAM access. It is certainly keeping me off the streets...:-)

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.

I can certainly do it with dynamic SQL and MOST can build a WHERE
statement
from the data in the interface, then add that to the cursor statement and
EXECUTE it. It will incur a performance hit, but I don't know how critical
that is likely to be.

Very few databases store a compiled version of embedded SQL. In most
cases, it is
dynamically PREPAREd on the first execution. If you don't believe me, look
in the
executable, where you'll find the original SQL as a literal with its host
variables
tokenized.

Robert, why would I not believe you? I asked for help; it would be a bit
stupid to disbelieve help being offered when it comes from a knowledgeable
source.

Some precompilers have an option to prepare SQL at compilation time, just
to check column
names. Oracle calls the option 'semantics'. If you don't use that, you
first learn about
invalid SQL at execution time. Obviously, it cannot be done for
dynamically created SQL
and requires access to a test database at compilation time.

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?

It really depends how much of their current
application is doing lengthy sequential processing, how often, in what
sequences, etc. I know they use READ NEXT quite a lot for building drop
down
lists for their PowerCOBOL GUI, but these are typically fairly small
accesses incurred only when a form is loaded and driven from that event.

Aren't most GUI controls 'bound' to a data source? In other words, load
their own data.

No. These days, in languages other than COBOL, they certainly are, but in
PowerCOBOL I don't think there was a facility to bind to a datasource. (I
could be wrong...) All of the PowerCOBOL I've seen doesn't use bound
datasources. Anyway, it doesn't matter; I have inspected the code and they
don't use bound datasources...


The other alternative is to have the process that scans the application
code
for replacement with MOST invocations, statically build the WHERE clause
and
insert it into the application, but I don't want to do that as it violates
my goal of having no SQL in the application. (Besides, I'd then need to
put
consequent FETCHes on the cursor into the application as well, and that
gets
ugly very quickly... :-))

Trying to make the conversion 100% automated is a mistake.

Why? I've always found that trying to do something is a positive step
towards achieving it. Certainly, it won't happen if I don't try.


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. There has
to be a better way. If the COM server is generalised to handle whatever the
application program requires, then I don't need to do that. The only part
that is problematic, is the conditions on things like singletons and START.
SQL provides the capability to build a query at run time and execute it. I
believe the application can pass enough information through the interface to
allow the COM server to do that.


Conversion is a ONE TIME process. It
doesn't have to be done every time you compile a program.

I think there may be confusion here between what is application code and
what is data layer code. Each application will be "converted" by having its
current ISAM access changed into INVOKES of the COM server. Yes, that SHOULD
be a one time process. I plan to automate the process of replacing the ISAM
verbs in the application with code that will build the interface and invoke
the COM server. The tool that does this can have enough "intelligence" to
parse the conditions and decompose them to a form that can be passed through
the interface.


When I fnally get to solve this (it should be by the end of this week),
I'll
post the code here and let the pundits pick it over... :-)

It will be too late to change by then.

Robert, it is NEVER too late to change something that is wrong...:-)

Thanks for your post. I do consider what you write.

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


.

Relevant Pages

  • Re: Opinions on approach, please...
    ... Write the where clause manually, ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... If I do that I'd need to tailor manually every single COM server, ...
    (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...
    ... 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...
    ... 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)
  • Need help with Master/Detail scenario
    ... // call data layer to add new header record, ... // call data layer to do all updates on detail tables, ... That's the sql code, which is easy enough to do in a transaction. ...
    (microsoft.public.dotnet.languages.csharp)