Re: SQL Statement Not Allowed -- RPG400-L

Hi Dieter -

My thought is the stored procedure would eventually be expanded to do more
heavy lifting - more database access, more business rule processing - before
returning the result set. But that makes me think (always exciting <grin>)
that what I really want to do is have an RPG program that processes business
rules and returns a result set. Accessing the INVENTORY table may very well
be part of the business rule processing, but the result set could very well
return columns from multiple table, or internal variables.

So what I want to do is have my PHP script call a stored procedure on the i.
The stored procedure would call an RPG program that would do the processing.
The result set would be returned to the PHP script.

Hmmm...how do folks do this? Create a temporary table and access that and
return the result set? I guess I'm thinking here of the 'With' clause. Is
that doable?

Thanks...

On Tue, Nov 30, 2010 at 4:26 AM, <dieter.bender@xxxxxxxxxxxx> wrote:

... 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.

--
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.