%Bind meta-SQL element

Syntax

%Bind([recordname.]fieldname [,
NOQUOTES][, NOWRAP][, STATIC])

Description

Use the %Bind construct to retrieve a field value from a state record. You can use %Bind anywhere in a SQL statement. When run, %Bind returns the value of the state record field identified within its parentheses.

Parameters

Parameter Description

Recordname

The name of a state record. If you do not specify a particular state record, Application Engine uses the default state record to resolve the %Bind (fieldname).

Fieldname

The field defined in the state record.

NOQUOTES

If the field specified is a character field, its value is automatically enclosed in quotes unless you use the NOQUOTES parameter. Use NOQUOTES to include a dynamic table and field name reference, even an entire SQL statement or clause, in an Application Engine SQL action.

NOWRAP

If the field is of type Date, Time, or DateTime, the system automatically wraps its value in %DateIn or %DateOut, unless you use the NOWRAP parameter. Therefore, if the state record field is populated correctly, you do not need to be concerned with the inbound references, although you can suppress the inbound wrapping with the NOWRAP modifier inside the %Bind. Furthermore, Application Engine skips the inbound wrapper if the %Bind (date) is in the select field list of another %Select statement. This is because the bind value is already in the outbound format, and the system selects it into another state record field in memory. In this circumstance there is no need for either an outbound wrapper or an inbound wrapper. For example,

First SQL action:

%Select(date_end)
SELECT %DateOut(date_end )
FROM PS_GREG

Second SQL action:

INSERT INTO ps_greg
VALUES(%Bind(date_end))

STATIC

The STATIC parameter enables you to include a hard-coded value in a reused statement. For %Bind instances that contain dynamic SQL, this parameter must be used in conjunction with the NOQUOTES parameter for proper execution of a reused statement.

Example

UPDATE PS_REQ_HDR
   SET IN_PROCESS_FLG = %Bind(MY_AET.IN_PROCESS_FLG),
      PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
   WHERE IN_PROCESS_FLG = ‘N’
      AND BUSINESS_UNIT || REQ_ID
      IN (SELECT BUSINESS_UNIT ||REQ_ID
         FROM PS_PO_REQRCON_WK1
         WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE))

In the previous example, %Bind (PROCESS_INSTANCE) assigns the value of the field PROCESS_INSTANCE in the default state record to the PROCESS_INSTANCE field in table PS_REQ_HDR.

The %Bind construct is also used in a Where clause to identify rows in the table PS_PO_REQRCON_WK1, in which the value of PROCESS_INSTANCE equals the value of PROCESS_INSTANCE in the default state record.

Notes About %Bind

Typically, when you use %Bind to provide a value for a field or a Where condition, the type of field in the state record that you reference with %Bind must match the field type of the corresponding database field used in the SQL statement.

On most platforms, you cannot use a literal to populate a Long Varchar field. You should use the %Bind(recordname.fieldname) construct.

In the case of an external call to a section in another program, if the called program has its own default state record defined, then Application Engine uses that default state record to resolve the %Bind(fieldname). Otherwise, the called program inherits the default state record of the calling program.

All fields referenced by a %Select construct must be defined in the associated state record.

You must use the Date, Time, and DateTime output wrappers in the Select list that populates the state record fields to ensure compatibility across all supported database platforms.

For example:

  • First SQL Action

    %Select(date_end)
   SELECT %DateOut(date_end ) 
      FROM PS_EXAMPLE

  • Second SQL Action

    INSERT INTO PS_EXAMPLE
   VALUES(%Bind(date_end))

Bind Variables and Date Wraps

The behavior of bind variables within Application Engine PeopleCode and normal PeopleCode is the same.

If you compare Application Engine SQL to PeopleCode (of any type), then the system processes bind variables differently.

If you use the following approach:

AND TL_EMPL_DATA1.EFFDT <= %P(1))

Then in PeopleCode you issue

%SQL(MY_SQL, %DateIn(:1))

which assumes that you referenced the literal as a bind variable.

Or in Application Engine SQL, you issue 

%SQL(MY_SQL, %Bind(date_field))
%SQL(MY_SQL, %Bind(date_field, NOWRAP))