midrange.com -- source code scratchpad

midrange.com code scratchpad
Name:
SQL File Encapsulation
Scriptlanguage:
Plain Text
Tabwidth:
Date:
04/03/2012 09: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:

          //=======================================================================*

          //  Declare PacName1 Cursor                                              *

          //                                                                       *

          //   Parameters:                                                         *

          //    Input  - Lock Record for Update                                    *

          //    Input  - (optional) Customer Number                                *

          //    Input  - (optional) PAC Name                                       *

          //                                                                       *

          //   Return Value: File Status                                           *

          //                                                                       *

          //-----------------------------------------------------------------------*

         P $DeclarePacName1...

         P                 B                   Export

         D                 PI            10i 0

         D  LockRec                        n   Const

         D  keyCust#                           Const Options( *omit: *nopass )

         D                                     Like( dsT_PacNameP.Cust# )

         D  keyPacNam                          Const Options( *omit: *nopass )

         D                                     Like( dsT_PacNameP.PacNam )

     
          //  Local Constants

         D DCLSQL_And      C                   ' And '

         D DCLSQL_Read     C                   ' For Read Only'

         D DCLSQL_Start    C                   'Select * From PacNameP'

         D DCLSQL_Update   C                   ' For Update'

         D DCLSQL_Where    C                   ' Where '

     
          //  Local Variables

         D DeclareString   S          32000a   Static Varying

         D upperPacNam     S                   Static Like( keyPacNam )

         D rtnStatus       S             10i 0 Inz( SUCCESS )

         D whereUsed       S               n   Static

          //-----------------------------------------------------------------------*

          /Free

     
           Monitor;

     
             //  Initialize the Declare String

             DeclareString = DCLSQL_Start;

             whereUsed = *Off;

     
             //  Load Key Field

             If %parms >= %parmNum( keyCust# ) and %addr( keyCust# ) <> *null;

               ExSr concatWhere;

               DeclareString += 'Cust# = ' + %char( keyCust# );

             EndIf;

     
             //  Load Key Field

             If %parms >= %parmNum( keyPacNam ) and %addr( keyPacNam ) <> *null;

               ExSr concatWhere;

               upperPacNam = %xlate( CASE_LOWER: CASE_UPPER: keyPacNam );

               DeclareString += 'Upper(PacNam) = ''' + upperPacNam + '''';

             EndIf;

     
             //  Load Read for Update Option

             If LockRec;

               DeclareString += DCLSQL_Update;

             Else;

               DeclareString += DCLSQL_Read;

             EndIf;

     
     
             //  Open the cursor.

             If not OpenFileCursor( DeclareString );

               rtnStatus = FAIL;

             EndIf;

     
           On-Error;

             rtnStatus = FAIL;

           EndMon;

     
           //  Return the File Status.

           Return rtnStatus;

     
           //======================================================================*

           //  Concatenate the Where Condition                                     *

           //----------------------------------------------------------------------*

           BegSr concatWhere;

     
             //  If the Where condition hasn't been used yet, use it.

             //  Turn on the indicator that flags that it has been used.

             If not whereUsed;

               DeclareString += DCLSQL_Where;

               whereUsed = *On;

     
             //  If Where was already used, use And

             Else;

               DeclareString += DCLSQL_And;

             EndIf;

     
           EndSr;

     
          /End-Free

         P $DeclarePacName1...

         P                 E

          //-----------------------------------------------------------------------*

     
          //=======================================================================*

          //  Chain to the PAC Name Record                                         *

          //                                                                       *

          //   Note:  $DeclarePacName* must have been called previous to this.     *

          //                                                                       *

          //   Parameters:                                                         *

          //    Input  - n/a                                                       *

          //                                                                       *

          //   Return Value: File Status                                           *

          //                                                                       *

          //-----------------------------------------------------------------------*

         P $ChainPacNameP  B                   Export

         D                 PI            10i 0

     
          //  Maximum # of records to read on a Chain is always 1.

         D MAX_CHAIN_READ  C                   1

     
          //  Local Variables

         D recordsRead     S             10i 0

         D rtnStatus       S             10i 0 Inz( SUCCESS )

          //-----------------------------------------------------------------------*

          /Free

     
           Monitor;

     
             //  Read the cursor

             If rtnStatus = SUCCESS;

               rtnStatus = FetchFileCursor( MAX_CHAIN_READ: recordsRead );

             EndIf;

     
             If rtnStatus = SUCCESS;

               gReadIndex = 1;

             Else;

               Clear gReadIndex;

             EndIf;

     
           On-Error;

             rtnStatus = FAIL;

             Clear gReadIndex;

           EndMon;

     
           //  Return the File Status.

           Return rtnStatus;

     
          /End-Free

         P $ChainPacNameP  E

          //-----------------------------------------------------------------------* 

     
          //=======================================================================*

          //  Close the SQL File Cursor                                            *

          //                                                                       *

          //   Parameters:                                                         *

          //    Input  - n/a                                                       *

          //                                                                       *

          //   Return Value: n/a                                                   *

          //                                                                       *

          //-----------------------------------------------------------------------*

         P CloseFileCursor...

         P                 B

         D                 PI

     
          //  Local Constants

          //  Local Variables

          //-----------------------------------------------------------------------*

          /free

     
           EXEC SQL Close FileCursor;

     
           Select;

     
             //  Cursor was closed normally

             When %subst( SqlState: 1: 2 ) = '00';

     
             //  Alert the troops!

             Other;

           EndSl;

     
           Return;

     
          /end-free

         P CloseFileCursor...

         P                 E

          //-----------------------------------------------------------------------*

     
          //=======================================================================*

          //  Open the SQL File Cursor                                             *

          //                                                                       *

          //   Parameters:                                                         *

          //    Input  - Declare String                                            *

          //                                                                       *

          //   Return Value: Fail Code                                             *

          //                                                                       *

          //-----------------------------------------------------------------------*

         P OpenFileCursor  B

         D                 PI              n

         D  DeclareString             32000a   Varying

     
          //  Local Constants

     
          //  Local Variables

         D rtnCode         s               n   Static

          //-----------------------------------------------------------------------*

     
          /free

     
           rtnCode = *Off;

     
     
           //  Close the Cursor in case any previous versions of it exist.

           CloseFileCursor();

     
           //  Prepare the statement to be declared

           Exec Sql Prepare PrepSQL from :DeclareString;

     
           //  Declare the Cursor

           Exec Sql Declare FileCursor Cursor for PrepSQL;

     
           //  Open the Cursor

           Exec Sql Open FileCursor;

     
           Select;

     
             When %subst( SqlState: 1: 2 ) = '00';

               rtnCode = *On;

     
           EndSl;

     
           Return rtnCode;

     
          /end-free

         P OpenFileCursor  E

          //-----------------------------------------------------------------------* 

     
          //=======================================================================*

          //  Fetch by File by Cursor                                              *

          //                                                                       *

          //   Parameters:                                                         *

          //    Input  - Maximum Number of Record to Read                          *

          //   Output  - Number of Records Read                                    *

          //                                                                       *

          //   Return Value: Fail Code                                             *

          //                                                                       *

          //-----------------------------------------------------------------------*

         P FetchFileCursor...

         P                 B

         D                 PI            10i 0

         D  epMaxRead                    10i 0 Const

         D  RecordsRead                  10i 0

     
          //  Local Constants

          //  Local Variables

         D rtnStatus       s             10i 0

          //-----------------------------------------------------------------------*

          /free

     
           Exec SQL

             Fetch FileCursor FOR :epMaxRead ROWS into :ds_PacNameP;

     
           Select;

     
             //  Row was received, normal

             When %subst( SqlState: 1: 2 ) ='00';

               rtnStatus = SUCCESS;

     
             //  Same as %eof, sooner or later this is normal

             When %subst( SqlState: 1: 2 ) = '02';

               rtnStatus = FILESTS_EndOfFile;

     
             //  Alert the troops!

             Other;

               rtnStatus = FAIL;

     
           EndSl;

     
           //  If successful, pass back the number of records read.

           If rtnStatus = SUCCESS or rtnStatus = FILESTS_EndOfFile;

             RecordsRead = SQLErrD(3);

           Else;

             Clear RecordsRead;

           EndIf;

     
           Return rtnStatus;

     
          /end-free

         P FetchFileCursor...

         P                 E

          //-----------------------------------------------------------------------*
generated in 0.007s
valid xhtml & css