Sending SQL/Record Sets/Cursors To The iSeries

["Smith, Dave" <DSmith@xxxxxxxxx>]

Vern:

I may have overlooked the obvious here.   ODBC is a part of the equation
and they should be able to insert directly into the database file.   Our
overall security model is NO direct access to files, only access to
programs.  This may be an exception to the rule.  Thanks Vern!!

- Dave   

                message: 7
                date: Thu, 15 Jul 2004 10:42:28 -0500
                from: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>
                subject: Re: Sending SQL/Record Sets/Cursors To The
iSeries

                Dave, I've not used C#, but can ODBC or OLE/DB be a part
of the equation? 
                There are providers in iSeries Access for both of those,
as well as a .NET 
                provider for iSeries database that should allow writing
to the iSeries. 
                Multiple INSERT statements should get buffered, so that
it turns into only 
                a few calls over the network.

                Somebody help me here!

                                Vern

                                At 10:28 AM 7/15/2004, you wrote:
                                >Good Morning:
                                >
                                >Currently, we have programs written on
our iSeries that are executed via
                                >SQL procedures(
                                >
                                >  CREATE PROCEDURE XXLIB.X220(
                                >    IN SSR DECIMAL(3, 0),
                                >    IN DISTRIBUTOR DECIMAL(7, 0),
                                >    IN STATE CHAR(1),
                                >    IN EXPDATE DATE,
                                >    OUT MSGID CHAR(7) ,
                                >    OUT MSG CHAR(150) )
                                >    LANGUAGE RPGLE
                                >    SPECIFIC XXLIB.X220
                                >    NOT DETERMINISTIC
                                >    READS SQL DATA
                                >    CALLED ON NULL INPUT
                                >    EXTERNAL NAME 'XXLIB/XX220'
                                >    PARAMETER STYLE GENERAL
                                >)  from web applications written in C#.
                                >
                                >The SQLRPGLE programs pass back
parameters and sometimes record set(s)
                                >(cursors):
                                >
                                >
                                >C     *ENTRY        PLIST
                                >C                   PARM
pSSR
                                >C                   PARM
pDIST
                                >C                   PARM
pSTATE
                                >C                   PARM
pExpDate
                                >C                   PARM
pMSGID
                                >C                   PARM
pMSG
                                >
                                >C/EXEC SQL SET RESULT SETS CURSOR C2
                                >C/END-EXEC
                                >
                                >We now want a solution that we can pass
a record set (cursor) to the
                                >iSeries via C# and SQL so that I can
update/write this data to my
                                >DB2/UDB file.  We currently pass single
records via parameters and
                                >update our database but repeated calls
for say a thousand records would
                                >simply not be efficient.
                                >
                                >I would prefer a SQL solution to keep
it consistent with what we are
                                >doing going the other way.   I am
hoping it's as easy as receiving the
                                >record set as a parameter and doing an
insert into my PF.  Hey...a guy
                                >can hope can't he?
                                >
                                >I am open to other solutions but am
leaning towards a SQL solution at
                                >this time.
                                >
                                >We are at version V5R2.
                                >
                                >All responses are welcome and
appreciated.   Thanks.
                                >
                                >David J. Smith
                                >IT Contractor
                                >dsmith@xxxxxxxxx
                                >517.546.5400 Ext: 1513
                                >
                                >
                                >--
                                >This is the Midrange Systems Technical
Discussion (MIDRANGE-L) mailing list
                                >To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
                                >To subscribe, unsubscribe, or change
list options,
                                >visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
                                >or email:
MIDRANGE-L-request@xxxxxxxxxxxx
                                >Before posting, please take a moment to
review the archives
                                >at
http://archive.midrange.com/midrange-l.