Re: SQL Statement Not Allowed

... why don't you just send the sql statement from PHP to the database?

D*B

--------------------------------------------------
From: "Michael Ryan" <michaelrtr@xxxxxxxxx>
Sent: Monday, November 29, 2010 11:14 PM
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Subject: 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.