RE: Committing data without assigning to a variable in SQL

/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 SQL2
C/End-exec
C*
C/Exec SQL
C+ commit
C/End-exec

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Brian Piotrowski
Sent: Wednesday, June 29, 2005 12: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

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