× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Thanks for all the replies. I'm going to post another question which I think
better articulates what I need.

On Tue, Nov 30, 2010 at 9:12 AM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:

Michael,

I believe when Brigitta asked about the SELECTs being identical, she
was referring to only the SELECT and FROM clauses.

The FROM clause is really key, dynamic SQL is required if you want
read from different sets of tables.

For what you are doing, static SQL should work. You can offer dynamic
WHERE and even ORDER BY without resorting to dynamic SQL using either
Brigitta's CASE technique or my AND ( xxx OR xxx ) technique.

Why bother? As somebody else mentioned, the way you are using dynamic
SQL is subject to "SQL Injection" attacks and is a very, very, very,
very bad idea.

HTH,
Charles



On Tue, Nov 30, 2010 at 8:17 AM, Michael Ryan <michaelrtr@xxxxxxxxx>
wrote:
Hi Birgitta -

The SELECTs *aren't* identical except for the vendor - I should have made
that more clear. One SELECT case is when a SKU is passed in, and I need
to
search by SKU. Another is when a partnumber and vendor are passed in -
then
I need to search by partnumber and vendor. That's why I was thinking I
needed dynamic SQL.

On Tue, Nov 30, 2010 at 12:18 AM, Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx>wrote:

Hi

If the selects are identical except the vendor, dynamic SQL is not
mandatory:
In the following example either all rows for the specified PartNumber
and
the specified Vendor are returned or if the vendor is *Zeros (assumed it
is
a numeric field) all rows for the specified PartNumber and all vendors
will
be returned.

Exec SQL Declare Cursor CsrC01 For
Select ...
From ...
Where PartNumber = :ParPartNumber
And Vendor = Case When :ParVendor <> 0 then
:ParVendor
Else Vendor End
...
For Read Only;

Exec SQL Close CsrC01;
Exec SQL Open CsrC01;

Since Release V5R1 SET RESULT SET is no longer mandatory to return a
result
set.
Just Open the Cursor and let it open. (That's why I first closed the
cursor
in my example.)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:
rpg400-l-bounces@xxxxxxxxxxxx]
Im
Auftrag von Michael Ryan
Gesendet: Monday, 29. November 2010 23:14
An: RPG programming on the IBM i / System i
Betreff: Re: SQL Statement Not Allowed

I think I may have minimized my example and totally obfuscated what I'm
trying to do. I want my RPG code to be an external program for a stored
procedure that I want to execute from my PHP script. I would have two
different selection criteria depending on the parms passed into the RPG
program. One selection would be where I'm searching for a partnumber,
the
other would be when I'm searching with a partnumber and a vendor. That's
why
I was thinking along the lines of a dynamically prepared statement. I
could
change the statement (change the WHERE clause) depending on my selection
criteria.

So I would create the statement, prepare the statement, and then open
the
cursor. I was thinking my CREATE PROCEDURE would call my RPG program,
passing in the parms and returning an open cursor. My PHP script would
then
db2_exec the call to my stored procedure. Make sense? Am I way off base?
Thanks!

On Mon, Nov 29, 2010 at 4:27 PM, Alan Campin <alan0307d@xxxxxxxxx>
wrote:

Are you trying to run these SQL commands from an RPG program or PHP?
If
from
RPG, where are you dynamically preparing the SQL statement. Why not
just
use
a varaible.

Declare C1 Cursor With Return For
Select FLDA, FLDB
From FILE1
Where PART_NUMBER = :PartNumber;

If from PHP, just do the SQL command.

On Mon, Nov 29, 2010 at 2:03 PM, Scott Klement <
rpg400-l@xxxxxxxxxxxxxxxx
wrote:

Hi Michael,

You can't put the 'Declare C1 Cursor' part in a character string.
The
'Declare C1 Cursor' is instructions to the embedded SQL precompiler
telling it which code to generate... you can't put the contents in
a
character string, because the contents of a variable is something
that's
only used at run-time, not (pre-)compile time.

From what I can tell in your posted example, you don't really need
embedded SQL at all. You can write this as an SQL Stored procedure,
it's probably easier

If you _are_ going to use embedded SQL, why not use a static
statement?
Static statements are much easier to deal with that dynamic ones.

If there's more than meets the eye, and you really do need dynamic
statements, the SELECT stuff can go in a character string, but needs
to
be run through a 'Prepare', then the 'Declare Cursor' would run off
of
the prepared statement, and the 'Declare Cursor' would be static.




On 11/29/2010 2:06 PM, Michael Ryan wrote:
I'm trying to do this to return a result set that I can process
from
a
PHP
script:

MySQLStmt =
'Declare C1 Cursor With Return For +
Select Select FLDA, FLDB +
From INVENTORY +
Where PARTNUM =' +
cBlank +
pInModel +
cBlank +
'For Read Only';

I then prepare the statement, and I get an SQL0084 - SQL statement
not
allowed.

Is it because I'm on V5R4?

Thanks!

--
This is the RPG programming on the IBM i / System i (RPG400-L)
mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.