Re: Write to db containing values defined by DB2

Yeah, my syntax was wrong; sorry.  I thought I could get by with that on a
non-RPG list.  Ha!  :)

I am not the originator; I just had a little insight into the
requirement/need - and heck, I might even have gotten it wrong.  For this, I
would drop to SQL but I'm loathe to suggest someone use any particular
technology (like RPG, SQL or Java) for a particular need just because that's
the solution I know.

Surely RPG does this via appropriate parameters to QDBPUT (or maybe a
sibling), right?

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"God bless the Holy Trinity"
        -- placard in a Dublin parade 


>   That's a workable alternative.  The downside (on a more global
> level, vs. the ) is that is requires explicit coding of all the
> fields to be updated in all the programs that you do a WRITE to this
> file.  A plain WRITE would override the fields using the data type's
> default.
> 
>   Just to throw an older style technique out there that should also
> work nicely is to use EXCEPT, specifying the fields you want to
> update.  Note that field ending positions are omitted.
> 
> OFILE   EADD            ADDREC
> O                       GRPNAME
> O                       CUSER
> 
> 
>   BTW, is the syntax of the write you have below correct?  I think you
> mean:
> 
>   Write myFormat %Fields(col1: col3: col5: col6) ;
> 
>   -mark
> 
> At 9/16/2010 11:20 AM, you wrote:
> >If I may step in, the RPG syntax capability that Erik would like to
> >duplicate is like this:
> >
> >   Write (col1, col3, col5, col6) myFormat ;
> >
> >This writes to the select fields, and asks DB2 to fill in defaults (or
> null)
> >for the omitted values (presumably col2, col4 in this case).
> >
> >Dennis Lovelady
> >http://www.linkedin.com/in/dennislovelady
> >--
> >"Get your facts first, and then you can distort them as much as you
> please."
> >         -- Mark Twain
> >
> > > That RPG is using it's own way of initializing the date column in
> my
> > > example
> > > to the value '0001-01-01' (ISO format), i.e. ignoring the DB2
> default,
> > > is
> > > one issue, yes.
> > >
> > > But my primary question is how you get a default value to be
> written at
> > > all
> > > when you don't have a write opcode that implicitly handles this for
> you?
> > > Using the C runtime's _Rwrite in move mode one has to specify a
> full
> > > record
> > > and if you don't have a valid date in the appropriate place it
> doesn't
> > > compile. Is there another way beyond the LF/View solution?
> > >
> > > Best wishes,
> > > Erik
> > >
> > > 2010/9/16 M. Lazarus <[email protected]>
> > >
> > > > Erik,
> > > >
> > > >  Create a LF over the PF, specifying the fields in the file.  The
> > > > trick is to OMIT the fields you want DB2 to automatically
> initialize,
> > > > so RPG doesn't use its own rules to populate those fields.  If
> this
> > > > case, just include the GRPNAME and CUSER fields.
> > > >
> > > >  -mark
> > > >
> > > > At 9/16/2010 09:51 AM, you wrote:
> > > > >Here's one using ROWID and a date column:
> > > > >
> > > > >CREATE TABLE STEO01/TEMPTEST (
> > > > >   GRPID SMALLINT GENERATED ALWAYS AS IDENTITY (
> > > > >   START WITH 1 INCREMENT BY 1
> > > > >   NO MINVALUE NO MAXVALUE
> > > > >   NO CYCLE NO ORDER
> > > > >   CACHE 20 )
> > > > >   ,
> > > > >   GRPNAME CHAR(25) CCSID 278 NOT NULL DEFAULT '' ,
> > > > >   CUSER CHAR(10) CCSID 278 NOT NULL DEFAULT '' ,
> > > > >   CDATE DATE NOT NULL DEFAULT CURRENT_DATE )
> > > > >   ;
> > > > >
> > > > >And here's how I'd code it in RPG (the formatting will be off,
> > > sorry):
> > > > >
> > > > >h option(*nodebugio)
> > > > >ftemptest  uf a e             disk    rename(temptest: rtemptest)
> > > > >  /free
> > > > >   *inlr = '1';
> > > > >
> > > > >   grpname = 'xyz';
> > > > >   cuser = 'someone';
> > > > >   write rtemptest;
> > > > >
> > > > >   return;
> > > > >  /end-free
> > > > >
> > > > >I specify writing to two columns and DB2 puts in values for the
> > > other two
> > > > >(identity column and date).
> > > > >Is this behaviour possible to imitate?
> > > > >
> > > > >Thank you.
> > > > >
> > > > >2010/9/16 <Gene_Gaunt-HhiojMv268KEK/[email protected]>
> > > > >
> > > > > > Can you post the DDL and an SQL CREATE and an SQL INSERT ?
> > > > > >
> > > > > >
> > > > > >
> > > > > > From:   Erik Olsson <[email protected]>
> > > > > > To:     mi400-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > > > > Date:   09/16/2010 08:49 AM
> > > > > > Subject:        [MI400] Write to db containing values defined
> by
> > > DB2
> > > > > > Sent by:        mi400-bounces-Zwy7GipZuJhWk0Htik3J/[email protected]
> > > > > >
> > > > > >
> > > > > > Hello,
> > > > > >
> > > > > > Posted this on the C list but was adviced to try out here as
> well.
> > > > > >
> > > > > > If I have DDL defined tables with columns holding DB2
> generated
> > > values
> > > > such
> > > > > > as ROWID, defaults as CURRENT_TIMESTAMP etc - how do I code a
> > > write to
> > > > > > columns like that using (the C runtime) _Rwrite (I don't know
> > > what one
> > > > > > would
> > > > > > use in MI)?
> > > > > >
> > > > > > That is, is there a way to emulate how an RPG write opcode
> > > handles the
> > > > same
> > > > > > situation?


_______________________________________________
This is the MI Programming on the AS400 / iSeries (MI400) mailing list
To post a message email: MI400-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mi400
or email: MI400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mi400.