Re: SQL Statement Not Allowed -- RPG400-L

Hi Michael,

- from a design perspective database is to store data in it and business rules are part of the application layer.
- from the database perspective SQL performs best, if you tell the database which data you need and the database retrieves it itself.
- from the client server perspective central business rules would be server components (and maybe it might be an option to call them via SQL stored procedure, as long as as/400 doesn't support a better way), but I would be carefull not to mix up business rules with the database layer.

Dieter

--------------------------------------------------
From: "Michael Ryan" <michaelrtr@xxxxxxxxx>
Sent: Tuesday, November 30, 2010 2:24 PM
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Subject: Re: SQL Statement Not Allowed

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.

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