midrange.com code scratchpad
Name:
SQL File Encapsulation
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
04/03/2012 04:18:00 pm
IP:
Logged
Description:
Example of file encapsulation with SQL. Wondering if the Chain could be streamlined at all.

Flow of code:
Call to $DeclarePacName1. This sets the keys that will be used.
The intent right now is to have different $Declare... per logical/index. I had toyed with the idea of only one, where you send the fields you want to use and *omit the others, but I think that's less intuitive. (The name of the procedure could probably also change to be like $SetPacNameKey or something.
After that is called, which dynamically creates the cursor, the caller would call $ChainPacNameP. Even if there were 10 different $Declare procedures, only the one $Chain procedure would be needed as the SQE would pick the appropriate index based on the where statement.
Code:
  1.       //=======================================================================*
  2.       //  Declare PacName1 Cursor                                              *
  3.       //                                                                       *
  4.       //   Parameters:                                                         *
  5.       //    Input  - Lock Record for Update                                    *
  6.       //    Input  - (optional) Customer Number                                *
  7.       //    Input  - (optional) PAC Name                                       *
  8.       //                                                                       *
  9.       //   Return Value: File Status                                           *
  10.       //                                                                       *
  11.       //-----------------------------------------------------------------------*
  12.      P $DeclarePacName1...
  13.      P                 B                   Export
  14.      D                 PI            10i 0
  15.      D  LockRec                        n   Const
  16.      D  keyCust#                           Const Options( *omit: *nopass )
  17.      D                                     Like( dsT_PacNameP.Cust# )
  18.      D  keyPacNam                          Const Options( *omit: *nopass )
  19.      D                                     Like( dsT_PacNameP.PacNam )
  20.  
  21.       //  Local Constants
  22.      D DCLSQL_And      C                   ' And '
  23.      D DCLSQL_Read     C                   ' For Read Only'
  24.      D DCLSQL_Start    C                   'Select * From PacNameP'
  25.      D DCLSQL_Update   C                   ' For Update'
  26.      D DCLSQL_Where    C                   ' Where '
  27.  
  28.       //  Local Variables
  29.      D DeclareString   S          32000a   Static Varying
  30.      D upperPacNam     S                   Static Like( keyPacNam )
  31.      D rtnStatus       S             10i 0 Inz( SUCCESS )
  32.      D whereUsed       S               n   Static
  33.       //-----------------------------------------------------------------------*
  34.       /Free
  35.  
  36.        Monitor;
  37.  
  38.          //  Initialize the Declare String
  39.          DeclareString = DCLSQL_Start;
  40.          whereUsed = *Off;
  41.  
  42.          //  Load Key Field
  43.          If %parms >= %parmNum( keyCust# ) and %addr( keyCust# ) <> *null;
  44.            ExSr concatWhere;
  45.            DeclareString += 'Cust# = ' + %char( keyCust# );
  46.          EndIf;
  47.  
  48.          //  Load Key Field
  49.          If %parms >= %parmNum( keyPacNam ) and %addr( keyPacNam ) <> *null;
  50.            ExSr concatWhere;
  51.            upperPacNam = %xlate( CASE_LOWER: CASE_UPPER: keyPacNam );
  52.            DeclareString += 'Upper(PacNam) = ''' + upperPacNam + '''';
  53.          EndIf;
  54.  
  55.          //  Load Read for Update Option
  56.          If LockRec;
  57.            DeclareString += DCLSQL_Update;
  58.          Else;
  59.            DeclareString += DCLSQL_Read;
  60.          EndIf;
  61.  
  62.  
  63.          //  Open the cursor.
  64.          If not OpenFileCursor( DeclareString );
  65.            rtnStatus = FAIL;
  66.          EndIf;
  67.  
  68.        On-Error;
  69.          rtnStatus = FAIL;
  70.        EndMon;
  71.  
  72.        //  Return the File Status.
  73.        Return rtnStatus;
  74.  
  75.        //======================================================================*
  76.        //  Concatenate the Where Condition                                     *
  77.        //----------------------------------------------------------------------*
  78.        BegSr concatWhere;
  79.  
  80.          //  If the Where condition hasn't been used yet, use it.
  81.          //  Turn on the indicator that flags that it has been used.
  82.          If not whereUsed;
  83.            DeclareString += DCLSQL_Where;
  84.            whereUsed = *On;
  85.  
  86.          //  If Where was already used, use And
  87.          Else;
  88.            DeclareString += DCLSQL_And;
  89.          EndIf;
  90.  
  91.        EndSr;
  92.  
  93.       /End-Free
  94.      P $DeclarePacName1...
  95.      P                 E
  96.       //-----------------------------------------------------------------------*
  97.  
  98.       //=======================================================================*
  99.       //  Chain to the PAC Name Record                                         *
  100.       //                                                                       *
  101.       //   Note:  $DeclarePacName* must have been called previous to this.     *
  102.       //                                                                       *
  103.       //   Parameters:                                                         *
  104.       //    Input  - n/a                                                       *
  105.       //                                                                       *
  106.       //   Return Value: File Status                                           *
  107.       //                                                                       *
  108.       //-----------------------------------------------------------------------*
  109.      P $ChainPacNameP  B                   Export
  110.      D                 PI            10i 0
  111.  
  112.       //  Maximum # of records to read on a Chain is always 1.
  113.      D MAX_CHAIN_READ  C                   1
  114.  
  115.       //  Local Variables
  116.      D recordsRead     S             10i 0
  117.      D rtnStatus       S             10i 0 Inz( SUCCESS )
  118.       //-----------------------------------------------------------------------*
  119.       /Free
  120.  
  121.        Monitor;
  122.  
  123.          //  Read the cursor
  124.          If rtnStatus = SUCCESS;
  125.            rtnStatus = FetchFileCursor( MAX_CHAIN_READ: recordsRead );
  126.          EndIf;
  127.  
  128.          If rtnStatus = SUCCESS;
  129.            gReadIndex = 1;
  130.          Else;
  131.            Clear gReadIndex;
  132.          EndIf;
  133.  
  134.        On-Error;
  135.          rtnStatus = FAIL;
  136.          Clear gReadIndex;
  137.        EndMon;
  138.  
  139.        //  Return the File Status.
  140.        Return rtnStatus;
  141.  
  142.       /End-Free
  143.      P $ChainPacNameP  E
  144.       //-----------------------------------------------------------------------* 
  145.  
  146.       //=======================================================================*
  147.       //  Close the SQL File Cursor                                            *
  148.       //                                                                       *
  149.       //   Parameters:                                                         *
  150.       //    Input  - n/a                                                       *
  151.       //                                                                       *
  152.       //   Return Value: n/a                                                   *
  153.       //                                                                       *
  154.       //-----------------------------------------------------------------------*
  155.      P CloseFileCursor...
  156.      P                 B
  157.      D                 PI
  158.  
  159.       //  Local Constants
  160.       //  Local Variables
  161.       //-----------------------------------------------------------------------*
  162.       /free
  163.  
  164.        EXEC SQL Close FileCursor;
  165.  
  166.        Select;
  167.  
  168.          //  Cursor was closed normally
  169.          When %subst( SqlState: 1: 2 ) = '00';
  170.  
  171.          //  Alert the troops!
  172.          Other;
  173.        EndSl;
  174.  
  175.        Return;
  176.  
  177.       /end-free
  178.      P CloseFileCursor...
  179.      P                 E
  180.       //-----------------------------------------------------------------------*
  181.  
  182.       //=======================================================================*
  183.       //  Open the SQL File Cursor                                             *
  184.       //                                                                       *
  185.       //   Parameters:                                                         *
  186.       //    Input  - Declare String                                            *
  187.       //                                                                       *
  188.       //   Return Value: Fail Code                                             *
  189.       //                                                                       *
  190.       //-----------------------------------------------------------------------*
  191.      P OpenFileCursor  B
  192.      D                 PI              n
  193.      D  DeclareString             32000a   Varying
  194.  
  195.       //  Local Constants
  196.  
  197.       //  Local Variables
  198.      D rtnCode         s               n   Static
  199.       //-----------------------------------------------------------------------*
  200.  
  201.       /free
  202.  
  203.        rtnCode = *Off;
  204.  
  205.  
  206.        //  Close the Cursor in case any previous versions of it exist.
  207.        CloseFileCursor();
  208.  
  209.        //  Prepare the statement to be declared
  210.        Exec Sql Prepare PrepSQL from :DeclareString;
  211.  
  212.        //  Declare the Cursor
  213.        Exec Sql Declare FileCursor Cursor for PrepSQL;
  214.  
  215.        //  Open the Cursor
  216.        Exec Sql Open FileCursor;
  217.  
  218.        Select;
  219.  
  220.          When %subst( SqlState: 1: 2 ) = '00';
  221.            rtnCode = *On;
  222.  
  223.        EndSl;
  224.  
  225.        Return rtnCode;
  226.  
  227.       /end-free
  228.      P OpenFileCursor  E
  229.       //-----------------------------------------------------------------------* 
  230.  
  231.       //=======================================================================*
  232.       //  Fetch by File by Cursor                                              *
  233.       //                                                                       *
  234.       //   Parameters:                                                         *
  235.       //    Input  - Maximum Number of Record to Read                          *
  236.       //   Output  - Number of Records Read                                    *
  237.       //                                                                       *
  238.       //   Return Value: Fail Code                                             *
  239.       //                                                                       *
  240.       //-----------------------------------------------------------------------*
  241.      P FetchFileCursor...
  242.      P                 B
  243.      D                 PI            10i 0
  244.      D  epMaxRead                    10i 0 Const
  245.      D  RecordsRead                  10i 0
  246.  
  247.       //  Local Constants
  248.       //  Local Variables
  249.      D rtnStatus       s             10i 0
  250.       //-----------------------------------------------------------------------*
  251.       /free
  252.  
  253.        Exec SQL
  254.          Fetch FileCursor FOR :epMaxRead ROWS into :ds_PacNameP;
  255.  
  256.        Select;
  257.  
  258.          //  Row was received, normal
  259.          When %subst( SqlState: 1: 2 ) ='00';
  260.            rtnStatus = SUCCESS;
  261.  
  262.          //  Same as %eof, sooner or later this is normal
  263.          When %subst( SqlState: 1: 2 ) = '02';
  264.            rtnStatus = FILESTS_EndOfFile;
  265.  
  266.          //  Alert the troops!
  267.          Other;
  268.            rtnStatus = FAIL;
  269.  
  270.        EndSl;
  271.  
  272.        //  If successful, pass back the number of records read.
  273.        If rtnStatus = SUCCESS or rtnStatus = FILESTS_EndOfFile;
  274.          RecordsRead = SQLErrD(3);
  275.        Else;
  276.          Clear RecordsRead;
  277.        EndIf;
  278.  
  279.        Return rtnStatus;
  280.  
  281.       /end-free
  282.      P FetchFileCursor...
  283.      P                 E
  284.       //-----------------------------------------------------------------------* 
© 2004-2019 by midrange.com generated in 0.006s valid xhtml & css