Re: SQL Statement Not Allowed -- RPG400-L

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.