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

-=-=-=-=-=-=-=-=-=-=-=-=-=-