Re: Embedded SQL question

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Bill

VALUES is a very useful statement - there is more detail in the SQL 
reference, that is not practical to repeat all here - so I heartily 
recommend looking it up.

A similar statement is SET, as I recall - check me by looking at the 
reference - I've used it in an embedded SQL RPG program to get a 
scan/replace before IBM provided %scanrpl - it requires that whatever 
function you use returns a single value. Here's an idea of it - it does 
use a host variable -

set :currtimestamp = current timestamp;

That gets microseconds, while I think RPG returned only milliseconds (at 
one time, at least).

So there are lots of things that come from SQL that you can use to set 
variables in RPG directly.

HTH
Vern

On 9/17/2014 7:31 AM, Bill Howie wrote:
> Birgitta,
>
> Thanks for the info.   So "VALUES" is a separate SQL statement then?  I
> don't need an "INSERT INTO" or anything like that before it?
>
> Bill
>
> On Wed, Sep 17, 2014 at 1:47 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
> wrote:
>
> > Correction the closing parenthesis at the end must be removed and replaced
> > with a quote.
> >
> > MySQLTEXT = 'Values(Select RRN(a), ' + %Trim(Field_Name) +
> >                  '  From ' + %Trim(Library_Name) + '/' + %Trim(File_Name) +
> >                  ' a Where RRN(a) = 1) ' +
> >                  '  into ?, ?';
> >
> > 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 [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von
> > Birgitta
> > Hauser
> > Gesendet: Wednesday, 17.9 2014 06:52
> > An: 'RPG programming on the IBM i (AS/400 and iSeries)'
> > Betreff: AW: Embedded SQL question
> >
> > SELECT INTO cannot be used in composition with dynamic SQL.
> > You either need a cursor, performing a PREPARE, DECLARE, OPEN, FETCH and
> > CLOSE statement.
> > Or VALUES INTO which is supported in conjunction with dynamic SQL
> >
> > MySQLTEXT = 'Values(Select RRN(a), ' + %Trim(Field_Name) +
> >                  '  From ' + %Trim(Library_Name) + '/' + %Trim(File_Name) +
> >                  ' a Where RRN(a) = 1) +
> >                  ' into ?, ?);
> >
> > Exec SQL  Prepare DynSQL From :MySQLText;
> >
> > Exec SQL Execute DynSQL using :HostVarRRN, :HostVarField;
> >
> > 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 [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Bill
> > Howie
> > Gesendet: Wednesday, 17.9 2014 06:35
> > An: rpg400-l@xxxxxxxxxxxx
> > Betreff: Embedded SQL question
> >
> > Hello all,
> >
> > I'm trying to use dynamic embedded SQL to retrieve a value in a program.
> > I was originally trying to use host variables for the needed data but then
> > I
> > found out that you can't use host variables in dynamic SQL and that you
> > have to use parameter markers instead.   So here's what I have:
> >
> >   MySQLText = 'Select rrn(a), a.' + %Trim(Field_Name) +
> >               ' Into ?,?' + ' From ' +
> >               %Trim(Library_Name) + '/' + %Trim(File_Name) +
> >               ' a where rrn(a) = 1';
> >
> >   exec sql
> >      SET OPTION
> >      commit=*none,
> >      datfmt=*iso;
> >
> >   exec sql prepare selectRecord from :MySQLText;  exec sql execute
> > selectRecord using :rrn,:OldCompany;
> >
> > When I run the program in debug, the MySQLText field looks like this:
> >
> > Select rrn(a), a.CONO15 Into ?,? From COPYAULT2/INP15 a wher e rrn(a) = 1
> >
> > and I get the following errors:
> >
> > Token ? was not valid. Valid tokens: : <IDENTIFIER>.
> > Prepared statement SELECTRECORD not found.
> >
> >
> > I'm stumped as to what to do from here.   This SQL statement should yield
> > one record (and does when run interactively) and the thing I'm trying to
> > grab is the value in :OldCompany.
> >
> > Any help would be MOST appreciated.   Thanks!
> >
> > Bill
> > --
> > This is the RPG programming on the IBM i (AS/400 and iSeries) (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 (AS/400 and iSeries) (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 (AS/400 and iSeries) (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.