Re: Opinions on approach, please...



"Frederico Fonseca" <real-email-in-msg-spam@xxxxxxxxx> wrote in message
news:7bos345far6d9sglmuc59ga9mfdlu1qe4n@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.)

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

When using dynamic SQL you almost always need to PREPARE.
You also need to use DESCRIBE in some situations, but NOT in all of
them.
small example below. Take in consideration that dependign on how you
go on the dynamic sql route, you will not be able to do it exactly as
follows.
move "SELECT TABNAME FROM SYSCAT.TABLES
- " ORDER BY 1

"ORDER BY 1" ? I don't understand this.

- " WHERE TABNAME <> ?" to st.
EXEC SQL PREPARE s1 FROM :st END-EXEC.
EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.

Yes, this is pretty close to what I'm looking at. I used the examples in the
Fujitsu docs and it is the same as this, except that it makes the declare of
the cursor part of the statement to be prepared. It is interesting to see
that it it possible to prepare part of a statement. Thanks.

move "STAFF" to parm-var.
EXEC SQL OPEN c1 USING :parm-var END-EXEC.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.

EXEC SQL CLOSE c1 END-EXEC.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :table-name END-EXEC. if
SQLCODE not equal 0
go to End-Fetch-Loop.
display "TABLE = ", table-name.
End-Fetch-Loop. exit.

Yes, that's all understood. In my case there will be a series of invokes
from the application, rather than a loop of fetches within the module
preparing the dynamic statement. My code uses SQLSTATE as I had some
problems with SQLCODE many years ago, but otherwise, I follow what you have
posted and it is very close to what I am intending. Thanks for this,
Frederico.

The above SQL does not need DESCRIBE to work.

No, I didn't intend to use DESCRIBE. (I don't know anything about it and
Robert says it is advanced...)



At this stage i would like to know what database you will be using.

I posted way back at the beginning of this thread that it will be SQL
Server. However, it may also be Access 2003. Shouldn't matter as they are
both the same engine.
Reason behind this is to see if you are restricted by the internal
precompiler from Fujitsu(e.g. if you will be using SQL Server) or if
you can use the specific database precompiler.

Yes, I am restricted by the SQL pre-compiler from Fujitsu. I don't expect it
to be a problem. It seems to support all the stuff I intend to use.

I am looking in my programs for a sample that will cover most of what
you need to do, and will post (and send by email) to you today most
likely.


That is very generous of you Frederico and I'll be glad to receive whatever
you send. As discussed I'll return the final code for your perusal.

Thanks again 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? ... Hard code WHERE clauses. ...
    (comp.lang.cobol)
  • 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)
  • 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: UPDATE query in Access 2003 raising error
    ... string into a variable strSQL but create the qrydef from a string strSQLx (I ... Runtime error 3066 Query must have at least one destination field. ... The SQL works fine if I use it in the QBF Design mode. ... Elsewhere in the code I use the same technique to execute an SQL ...
    (microsoft.public.access.formscoding)
  • Re: Poor performance when executing stored procedure
    ... > Generally I would write stored procedures to do only one job. ... If SQL Server ... > which is even worse those sps can execute each other. ... > then executed spOrders which is executed in spCustomers and then got stuck ...
    (microsoft.public.sqlserver.programming)