RE: SQL Statement Not Allowed

What About:

Low_Vendor = *Loval
High_Vendor = *HiVal

If Passed_Vendor <> *Zeros
Eval Low_vendor = Passed_Vendor
Eval High_Vendor = Passed_Vendor
EndIf


Declare C1 Cursor With Return For
Select FLDA, FLDB

From FILE1

Where PART_NUMBER = :PartNumber;
and Vendor_NO Between :Low_Vendor and :High_Vendor ?


“If you think education is expensive, try ignorance"
(Derek Bok, pres. of Harvard Univ. 1971-1990)

Date: Mon, 29 Nov 2010 17:14:07 -0500
Subject: Re: SQL Statement Not Allowed
From: michaelrtr@xxxxxxxxx
To: rpg400-l@xxxxxxxxxxxx

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.