RE: Filling a Subfile with an SQL statement / Writing a CSV

Great.  Thanks to everyone who responded.

Brian.

-----Original Message-----
From: Geeky400 [mailto:Geeky400@xxxxxxxxxxx] 
Sent: Thursday, September 15, 2005 9:59 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Filling a Subfile with an SQL statement / Writing a CSV

   Brian...

   The line with SQLCOD should read: "Dow    SQLCOD = 0" (in my previous
post
   the 0 is on the next line). Also, SQL_Buffer is a data Structure
where you
   define the fields that are retrieved by your SQL statement. In the
example
   below, that would be a DS with 2 fields: Field1 and Field2

   G4!
   Geeky400 wrote:

    Hi Brian,

    Here's a bit of code which you could use:

    C                   ExSR      SelC1                 
    C                   ExSR      OpenC1                
    C                   ExSR      ReadC1                
                                                        
    C                   DoW       SQLCOD   =
    0                                                           
                        ** Do something here with the record read **
                                 
    C                   ExSR      ReadC1                
    C                   EndDo                           
                                                        
    C                   ExSR      CloseC1  
    C                   Eval      *InLR = *On

 
*=====================================================================
    * SUBROUTINES

 
*=====================================================================
 

 
*---------------------------------------------------------------------
    * SelC1      : SQL - Declare Cursor C1

 
*---------------------------------------------------------------------
         SelC1         BegSR

 

    /Exec SQL Declare C1 Cursor For

 

    +   Select     Field1, Field2                                       
    +   From       YourFileName                                 
 

    /End-Exec

 

                       EndSR  
 
*---------------------------------------------------------------------
     * OpenC1     : SQL - Open Cursor C1

 
*---------------------------------------------------------------------
    C     OpenC1        BegSR

 

    C/Exec SQL

    C+                  Open  C1

    C/End-Exec

 

    C                   EndSR

 

 
*---------------------------------------------------------------------
     * ReadC1     : SQL - Fetch Cursor C1

 
*---------------------------------------------------------------------
    C     ReadC1        BegSR

 

    C/Exec SQL                                    
    C+                  Fetch C1 into :SQL_Buffer

    C/End-Exec

 

    C                   EndSR

 

 
*---------------------------------------------------------------------
     * CloseC1    : SQL - Close Cursor C1

 
*---------------------------------------------------------------------
    C     CloseC1       BegSR

 

    C/Exec SQL

    C+                  Close C1

    C/End-Exec

 

    C                   EndSR     

    I hope this helps,

    G4!                                   
                                            

         

    Brian Piotrowski wrote:

  Thanks.  My only question is how do you move forward in the recordset?
  I'm assuming if you are writing the results, you would need to be able
  to go through the results one by one, correct?

  Brian.

  -----Original Message-----
  From: Geeky400 [mailto:Geeky400@xxxxxxxxxxx]
  Sent: Thursday, September 15, 2005 8:34 AM
  To: RPG programming on the AS400 / iSeries
  Subject: Re: Filling a Subfile with an SQL statement / Writing a CSV

  Hi Brian,

  ...and for the first question you can test SQLCOD, ie, DoW SQLCOD = 0

  Hope this helps,

  G4!


  Peter.Colpaert@xxxxxxxxxx wrote:

  

  Brian,

  regarding your second question,  you could always use the Jakarta POI
  classes to output a "real" excel sheet, and have the user pick it up
    

  from
  

  the IFS, or mail it to him/her.

  Alternatively, you could output to a workfile, and have the user
import
    

  it
  

  into excel using the iSeries Access data transfer add-in.

  HTH,

  Peter Colpaert
  Application Developer
  Massive - Kontich, Belgium
  -----
  Yoda of Borg are we.  Futile is resistance, assimilated will you be.
  -----




  "Brian Piotrowski" <bpiotrowski@xxxxxxxxxxxxxxx>
  Sent by: rpg400-l-bounces@xxxxxxxxxxxx
  15/09/2005 14:10
  Please respond to RPG programming on the AS400 / iSeries


         To:     "RPG programming on the AS400 / iSeries"
    

  <rpg400-l@xxxxxxxxxxxx>
  

         cc:
         Subject:        Filling a Subfile with an SQL statement /
    

  Writing a CSV
  

  Hi All,



  When I use an SQL statement to obtain information to a database, do I
  need to create a cursor in order to write the data to a subfile?  Is
  there a way to do something like a DoW not %eof(SQL returned records)?



  The other question I have regards writing a CSV file.  Right now, we
  have a null device where all of our "scratch" data is written (PRT01).
  One of the users on our system needs to have the results of a query
  written to a file that will be exported to an Excel spreadsheet.  I
was
  planning on simply writing the file to our scratch drive and have them
  grab the print file using Navigator.  Does anyone know if there is a
  better way to do this?



  Thanks!



  Brian.



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

  Brian Piotrowski

  Specialist - I.T.

  Simcoe Parts Service, Inc.

  Ph: 705-435-7814 x343

  Fx: 705-435-6746

  bpiotrowski@xxxxxxxxxxxxxxx

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



 

    



                
  ___________________________________________________________
  To help you stay safe and secure online, we've developed the all new
  Yahoo! Security Centre. http://uk.security.yahoo.com
  

    ___________________________________________________________ Yahoo!
    Messenger - NEW crystal clear PC to PC calling worldwide with
voicemail
    http://uk.messenger.yahoo.com
  

   ___________________________________________________________ Yahoo!
   Messenger - NEW crystal clear PC to PC calling worldwide with
voicemail
   http://uk.messenger.yahoo.com