RE: SQLRPG & SQLPKG

Yes, you'll need to create an SQL Package for this. 
Remember to specify the RDB parameter (even if it's *Local) on the
SQLRPG compile to make it a distributed SQL program.

When do we really need to create SQL Package?

When you want to execute SQL statements on a non-local database, afaik.


Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of praveen gunda
Sent: Wednesday, October 25, 2006 4:37 PM
To: RPG programming on the AS400 / iSeries
Subject: SQLRPG & SQLPKG

Hi all,
 I need to write and RPGSQL pgm that would connect to a remote i-series
database and fetch something.
Do I need to create an SQL package for this or not?
When do we really need to create SQL Package?

 *  Plant Number File
fpntnuml1  uf   e           k disk    infsr(*pssr)

 * Display file showing a message window if invoices conflict
fBM2111fm  cf   e             workstn infsr(*pssr)

 /TITLE
 * Variables Declaration:
 * ~~~~~~~~~~~~~~~~~~~~~~

 * System Data Structure

d                sds
d  @pgm             *proc
d  @pstat           *status
d  @jobna               244    253
d  @usrid               254    263
d  @jobno               264    269  0

 * Variables for connecting to the Remote Server(App serv
d@Environment     s              7
d@User            s             10
d@Password        s             10

 * Data Structure for checking Invoice Numbers between WM
 * Lawson

d                 ds                  inz
d Number                        10
d  Value                         2a   overlay(number)
d  LawPlantA                     3    overlay(number:3)
d   LawPlant                     3  0 overlay(LawPlantA)
d  LawInvA                       5    overlay(number:6)
d   LawInv                       5  0 overlay(LawInvA)

 * Entry Parameter
d EntryPlant      s              3
d EntryType       s              2

 * Key Variables
d KeyPlant        s              3  0
d KeyType         s              2

 * Others
d WmsInvNum       s              5  0

 * SQL CODES
 * ~~~~~~~~~
c/Exec SQL
c+ include sqlca
c/END-EXEC

 * Global error handler
c/Exec SQL
c+ whenever sqlerror goto error
c/END-EXEC

 /TITLE
 *****************************************************************
 *               M A I N L I N E     P R O C E S S I N G
 *****************************************************************

 /free
  exsr sr_clear;

  // Check in Lawson
  exsr sr_check;

  *inlr = *on;
  return;

 /end-free
c     error         tag
 /free
  exsr *pssr;

  //**************************************************************
  //    Sr_Check ==> Check in LAWSON
  //**************************************************************
  Begsr sr_check;

  Number = *blanks;
  // Connect Lawson server

 /end-free
c/Exec SQL
c+ connect to :@environment user :@user using :@password
c/End-Exec

c/Exec SQL
c+ select max(darotrnnbr) into :Number from lawdbf/dbararo1 where 
c+ substr(darotrnnbr,3,3) = :EntryPlant and
c+       substr(darotrnnbr,1,2) = '00'
c/End-Exec
c                   exsr      sr_clear
 /free
  if (Number <> *blanks);

     if wmsInvNum < lawInv;
        exfmt BM2111f1;
        chain kpntnum pntnuml1;
        if %found(pntnuml1);
           eval pnnum = lawInv;
           update pntnumr;

        endif;
     endif;
  endif;

  endsr;
  //*******************************************************
  //   *InzSr  ==> Perform Program initialisation
  //*******************************************************

  begsr *inzsr;

 /end-free
 * Entry Parameters
c     *entry        plist
c                   parm                    EntryPlant
c                   parm                    EntryType

 * Key Fields
c     kpntnum       klist
c                   kfld                    KeyPlant
c                   kfld                    KeyType
 /free
  KeyPlant = %Dec(EntryPlant:3:0);
  KeyType = EntryType;
  @environment = 'LAWSON';
  @user = 'User';
  @password  = 'Pwd';
  chain kpntnum pntnuml1;
  if %found(pntnuml1);
      eval wmsInvNum = pnnum;
  endif;


endsr;
  //***************************************
  // clear all
  //***************************************
  begsr sr_clear;
 /end-free
c/Exec SQL
*c+ disconnect all
*c/End-Exec

c/Exec SQL
c+ connect reset
c/End-Exec

 /free
  endsr;

  //************************************************************
  // *PSSR ==> Exception routine for program, Sql, File & Data.
  //************************************************************
  Begsr *pssr;
  ExSr Sr_Clear;
  *inlr = *on;
  return;
  endsr;

I would like some constructive criticism of the the above code. I am
reveiwing this code and what really annoys me is the *Disconnect all *
statement.
Most of the programs in our comp have very similar statements for
connecting to remote i-series db's and they create sql packages all the
time. R the packages really needed?

Thanks & Regards
Praveen
--
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.