Re: Opinions on approach, please...

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

The above SQL does not need DESCRIBE to work.



At this stage i would like to know what database you will be using.
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.

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.


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.

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: 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)
  • 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)