BEGIN-EXECUTE

Note:

BEGIN-EXECUTE is specific to Production Reporting DDO ports only.

Function

Begins a new query or procedure execution. BEGIN-EXECUTE is only required when additional information about the DDO datasource or query is needed. In a BEGIN-EXECUTE paragraph, the syntax of BEGIN-SELECT varies as shown below.

Syntax

BEGIN-EXECUTE
[CONNECTION=uq_txt_lit]
[ON-ERROR=sqr_procedure[(arg1[,argi]...)]]
[RSV=num_var]
[STATUS=list_var|num_var|txt_var]
[PROPERTIES=({key_txt_lit|_var}={{value_txt_lit|_var| _col}|{num_lit|_var|_col},...)]
[SCHEMA={txt_lit|_var}]
[
  PROCEDURE={txt_lit|_var}
    [PARAMETERS=({{arg1 [IN|INOUT]}|NULL} [[,argi [IN|INOUT]]|NULL] ... )]
   (or)
  COMMAND={txt_lit|_var}
   (or)
  GETDATA={txt_lit|_var}
]
[BEGIN-SELECT[BEFORE=sqr_procedure[(arg1[,argi]...)]]
             [AFTER=sqr_procedure[(arg1[,argi]...)]]]
col-name TYPE=CHAR|TEXT|NUMBER|DATE [edit-mask]
[on-break]...
[{FROM ROWSETS=({m|m-n|m-|-n} [,...]}|{ALL})}|
 {FROM PARAMETER={txt_lit|_var}}|
 {FROM {table_name}}]
END-SELECT]
END-EXECUTE

Arguments

CONNECTION

Name previously defined using DECLARE-CONNECTION. If not specified, Production Reporting uses the default connection defined by the command-line entries for data source(DSN), username (USER), and password (PASSWORD). Name is not case-sensitive.

ON-ERROR

Procedure to execute if errors occur.

RSV

Row Set Variable. Global Production Reporting variable containing the row set retrieved.

STATUS

List or scalar variable that receives the stored procedure status.

PROPERTIES

Keyword/value pair(s) that represent modifications made to the Properties of the data source (defined by CONNECTION=).

SCHEMA

Data source location of the object queried. Valid options include:

PROCEDURE—Name of the data source/stored procedure to execute. If the data source is SAP R/3, this procedure is a BAPI. The name can include spaces.
PARAMETERS—Scalar and/or list variables of the form list_var|num_lit|txt_lit|txt_var|num_var|any_col. If you do not specify the keywords IN or INOUT, IN is the default. Define all parameters in order; leaving any parameters unnamed causes a syntax error. To ignore a parameter, fill its position with the keyword NULL.
COMMAND—Text string passed to the data source without modification by Production Reporting. Can include embedded Production Reporting variables.
GETDATA—Supports the Java (DDO) GetData paradigm for data access.

BEFORE/AFTER

Production Reporting procedure to execute before or after the row set. The procedure is not performed unless at least one row is returned from the specified rowset(s).

FROM ROWSETS

Special case addition to BEGIN-SELECT. Available for use with all data source types, including SAP R/3 and JDBC. Names the rowset(s) from which to retrieve the column variables. For multiple row sets, use identical column name/type signatures. Row set numbers must be sequential from left-to-right within the parentheses, and they must not overlap as in this example: (1-3, 2-4). Numeric literals or #variables are allowed.

In FROM ROWSETS, “m” and “n” are integer values (1, 2, 3, 4, 5). “m-n” is 3-5 (rowsets 3, 4, 5). “m-” is 4- (rowsets 4, 5). “-n” is -3 (rowset 1, 2, 3).

FROM PARAMETER

Special case addition to BEGIN-SELECT. Available only for SAP R/3 data sources. Use only with thePROCEDURE keyword. Names an output parameter containing one or more rows from which column variables are to retrieve.

Note:

This is similar to the PARAMETERS= statement in DECLARE-CONNECTION and ALTER-CONNECTION, except the properties specified here alter the flow of returned information, as opposed to simply setting login properties. Can be used with any data-access model (Procedure, Command, Getdata). An application of this statement would be in the MDB setting, where it might be used to specify such things as Level, Generation, or Include-Column. For example, PROPERTIES = ( ‘SetColumn’ = 5 )

FROM {table_name}

Relational data source table name. Literals or variables are not allowed.

Examples

begin-setup
  declare-variable 
    date $when_ordered 
    text $ship_method
    integer #theRow
    integer #theStatus
    integer #howMany
  end-declare
end-setup

input #howMany type=integer 
input $pword
let %parm1 = list($when_ordered, $ship_method, #howMany)

declare-connection SAPR3
  user=scott 
  parameters=clientno=5;node=starfish;
end-declare

alter-connection 
  name=SAPR3
  password=$pword

begin-execute 
  connection=SAPR3 
    rsv=#theRow 
      status=#theStatus
        on-error=it_failed(#theStatus)
        procedure='CreditHistory version 5'
          parameters=(%parm1,'recalculate')
  print 'proc ran OK, status is '(+1,1)
  print #theStatus (,+5) edit 999

  begin-select before=do_eject after=cleanup
    city &col=char (1,1) on-break level=1 after=city-tot
    keyval type=number (1,+1) 
    rcvd type=date (0,+2)
    from Rowsets=(1)
  end-select
end-execute

Tip:

When you set up DECLARE-CONNECTION, you must use the same name defined in Registry.properties. For example, if Registry.properties contains:

XML_DATA.desc=Sample XML files 
XML_DATA.class=com.sqribe.xmlacc.XMLDataSource 
XML_DATA.lib= 
XML_DATA.load= 
XML_DATA.conn=D:\\SampleData\\XML_DATA

Then the Production Reporting code should look similar to:

begin-setup 
declare-connection default 
  DSN=XML_DATA        !  Use the same name as specified in the   
end-declare           !  Registry.properties file. Case sensitive. 
end-setup

begin-procedure domystuff 
  begin-execute 
    GetData='sample'  ! The filename is sample.xml. Substitute the                       ! filename of your xml file here. The path to the                       ! file is in the Registry.properties file.
     begin-select 
        CUSTOMERS.cust_num type=num (+1,1) edit 099999 
        CUSTOMERS.name type=char (,30) 
        from customers 
     end-select 
  end-execute 
end-procedure

The previous Production Reporting code produces the following output:

<CUSTOMERS> 
  <Customer cust_num='100013'> 
    <CUSTOMERS.CUST_NUM>100013</CUSTOMERS.CUST_NUM> 
    <CUSTOMERS.NAME>Gregory Stonehaven</CUSTOMERS.NAME> 
    <CUSTOMERS.ADDR1>Middlebrook Road</CUSTOMERS.ADDR1> 
    <CUSTOMERS.ADDR2>Grey Quarter</CUSTOMERS.ADDR2> 
    <CUSTOMERS.CITY>Everretsville</CUSTOMERS.CITY> 
    <CUSTOMERS.STATE>OH</CUSTOMERS.STATE> 
    <CUSTOMERS.ZIP>402331000</CUSTOMERS.ZIP> 
    <CUSTOMERS.PHONE>2165553109</CUSTOMERS.PHONE> 
    <CUSTOMERS.TOT>39</CUSTOMERS.TOT> 
  </Customer> 
</CUSTOMERS>

Tip:

BEGIN-EXECUTE is only required when additional information about the DDO data source or query is needed, such as 'Connection', 'Schema', 'Command','GetData', 'Procedure', or 'Parameters'. The following example does not require BEGIN-EXECUTE since it does not require information about the DDO datasource or query.

begin-setup
page-size 58 80
  declare-connection ORACLE_CONNECTION
    dsn=saw806
    user=jerryh
    password=canttellyou
  end-declare
end-setup

begin-procedure print_customers
  print 'FULL CUSTOMER LIST BY Customer Number' (+1) center

  begin-select
  cust_num   (+1,1,6) edit 099999
  name       (0,+2,30)
  addr1      (+1,12,30)
  addr2      (0,+4,30)
  city       (+1,12,16)
  state      (0,+2,2)
  zip              (0,+2,10)
  phone            (0, +2, 0) edit (xxx)bxxx-xxxx

  !Edit phone number for’ easy reading.
  next-listing  skiplines=2 need=3      
  !Skip 2 lines between listings. Since each listing takes 3 lines, we      !specify 'need=3' to prevent a customer's data from being broken   !across two pages.

  from customers
  order by cust_num
  end-select

end-procedure