RE: Committing data without assigning to a variable in SQL

Thanks to everyone, it works now.

Brian.

-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx] 
Sent: Wednesday, June 29, 2005 1:49 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Committing data without assigning to a variable in SQL

Still incorrect.

With EXECUTE IMMEDIATE, you don't even need the PREPARE.

RTFM:
1) The EXECUTE statement executes a prepared SQL statement.

2) The EXECUTE IMMEDIATE statement:
        v Prepares an executable form of an SQL statement from a
character string form of
        the statement
        v Executes the SQL statement
EXECUTE IMMEDIATE combines the basic functions of the PREPARE and
EXECUTE statements. It can be used to prepare and execute SQL statements
that
contain neither host variables nor parameter markers.


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Brian Piotrowski
> Sent: Wednesday, June 29, 2005 1:37 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: Committing data without assigning to a variable in SQL
> 
> 
> So based on this revision, I only need this, correct?:
> 
> /free
>   SQLMlist = 'insert into sswdppp (';
>   SQLMlist = SQLMlist + 'select pskdl1, pskdl2, pskdl3, 
> psyear, psmdl,';
>   SQLMlist = SQLMlist + ' pstyp, psexcl,psinbt, psuqty';
>   SQLMlist = SQLMlist + ' from sst25 where concat(PSKDL2, PSKDL3) ';
>   SQLMlist = SQLMlist + ' between ' + apos + shortyy + cmm + apos;
>   SQLMlist = SQLMlist + ' AND '+ apos + nextsyy + nmm + apos + ')';
> /end-free
> C*
> C/Exec SQL
> C+ PREPARE SQL2 FROM :SQLMlist
> C/End-exec
> C*
> C/Exec SQL
> C+ EXECUTE IMMEDIATE :SQLMlist
> C/End-exec
> C*
> C/Exec SQL
> C+ commit
> C/End-exec
> 
> I tried it this way, but still come up empty.  However, if I copy the
> data in the SQLMlist variable and run it directly in the SQL 
> interpreter
> it appears to work.
> 
> Thanks again,
> 
> Brian.
> 
> -----Original Message-----
> From: Tyler, Matt [mailto:mattt@xxxxxxxxxxxxxx] 
> Sent: Wednesday, June 29, 2005 1:15 PM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Committing data without assigning to a variable in SQL
> 
> Try EXECUTE IMMEDIATE, since you are not getting data into the program
> there
> is no need to create a cursor.  If this is temporary file and the
> program
> does not do anything else that requires commitment control, 
> compile the
> program with COMMIT(*NONE).  Otherwise you will have to control
> commitment
> just like any other situation with SQL.
> 
> C/Exec SQL EXECUTE IMMEDIATE :SQLMlist 
> 
> 
> Thank you,
> Matt Tyler
> WinCo Foods, LLC
> mattt@xxxxxxxxxxxxxx
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Brian Piotrowski
> Sent: Wednesday, June 29, 2005 11:07 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Committing data without assigning to a variable in SQL
> 
> Hi All,
> 
>  
> 
> I have a two tables from which I read from one table and write the
> results to another.  I changed my code to use a PREPARE statement, but
> the system doesn't appear to write anything to the target table.  Do I
> need to do a FETCH command into a variable in order to write to the
> table? If so, how would I get it to write to the other table?
> 
>  
> 
> Here's the snippet of code:
> 
>  
> 
> /free
> 
>   SQLMlist = 'insert into sswdppp (';
> 
>   SQLMlist = SQLMlist + 'select pskdl1, pskdl2, pskdl3, 
> psyear, psmdl,';
> 
>   SQLMlist = SQLMlist + ' pstyp, psexcl,psinbt, psuqty';
> 
>   SQLMlist = SQLMlist + ' from sst25 where concat(PSKDL2, PSKDL3) ';
> 
>   SQLMlist = SQLMlist + ' between ' + apos + shortyy + cmm + apos;
> 
>   SQLMlist = SQLMlist + ' AND '+ apos + nextsyy + nmm + apos + ')';
> 
> /end-free
> 
>  
> 
> C/Exec SQL
> 
> C+ PREPARE SQL2 FROM :SQLMlist
> 
> C/End-exec
> 
> C*
> 
> C/Exec SQL
> 
> C+ DECLARE C2 CURSOR for SQL2
> 
> C/End-exec
> 
> C*
> 
> C/Exec SQL
> 
> C+ OPEN C2
> 
> C/End-exec
> 
> C*
> 
> C* Do I need to add another /EXEC SQL here???
> 
> C*
> 
> C/Exec SQL
> 
> C+ Close C2
> 
> C/End-exec
> 
> C*
> 
> C/Exec SQL
> 
> C+ commit
> 
> C/End-exec
> 
>  
> 
> Another question I have is if I can compress the /Exec SQL lines down,
> or must they remain on separate lines?
> 
>  
> 
> Thank you!
> 
>  
> 
> Brian.
> 
>  
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-
> 
> Brian Piotrowski
> 
> Specialist - I.T.
> 
> Simcoe Parts Service, Inc.
> 
> Ph: 705-435-7814 x343
> 
> Fx: 705-435-6746
> 
> bpiotrowski@xxxxxxxxxxxxxxx
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-
> 
>  
> 
> -- 
> 
> This is the RPG programming on the AS400 / 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 AS400 / 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 AS400 / 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.
> 
>