Programming Guide

dm_gen_change_execute_using

Adds or replaces a bind value in a DBMS EXECUTE statement for SQL generation 

#include <tmusubs.h>
int dm_gen_change_execute_using(char *arg, char *bind_parm, char *bind_val, int occ, int relative, int flag);

arg
Reserved for future use.

bind_parm
Specifies the bind parameter.; if a null pointer or empty string, the clause is not built.

bind_val
Specifies the bind value; if a null pointer or an empty string, the clause is not built.

occ
Specifies the occurrence number.

relative
Specifies how to use the occurrence number with one of the following values: 
DM_GEN_ABSOLUTE_OCCUR
DM_GEN_RELATIVE_TO_PARENT
DM_GEN_RELATIVE TO CHILD

flag
Specifies the type of change to make with one of the following constants:

DM_GEN_APPEND
When flag is set to this value, bind_val is added to end of the USING clause. This produces the following statement: 
DBMS WITH CURSOR cursor EXECUTE USING
 existing_parentTV_binds,
 existing_childTV_binds,
 bind_parm = bind_val[occ]

DM_GEN_PREPEND
When flag is set to this value, bind_val is added to the beginning of the USING clause. This produces the following statement: 
DBMS WITH CURSOR cursor EXECUTE USING
 bind_parm = bind_val[occ],
 existing_parentTV_binds,
 existing_childTV_binds

DM_GEN_REPLACE_ALL
When flag is set to this value, bind_val replaces the previous USING clause. This produces the following statement:

DBMS WITH CURSOR cursor EXECUTE USING
bind_parm = bind_val[occ]
If flag is set to this value and the other arguments are empty strings, the USING clause is removed.

Returns

Description

dm_gen_change_execute_using lets you edit the USING clause of a DBMS EXECUTE statement. The data structure for the SELECT statement, which is built by a call to dm_gen_sql_info (generally in the TM_SEL_GEN event), must already exist before this function is called. Note that this function must be called once for each bind value you wish to change.

Often, a call to dm_gen_change_execute_using follows a call to the function dm_gen_change_select_where. If new parameters are added to the WHERE clause's search conditions, those parameters must also be added to the EXECUTE USING statement.

This function can be implemented as part of a transaction manager event function that processes the TM_SEL_BUILD_PERFORM event. If you are modifying the select processing for a server view, call the dm_gen_change_execute_using function from an event function attached to the first parent table view in the server view.

To view a sample event function written in JPL, refer to the example in this section. For more information on writing transaction event functions, refer to Chapter 32, "Writing Transaction Event Functions," in Application Development Guide.

The settings for relative and occurrence determine the value for occ, the occurrence number used in the statement.

If relative is set to DM_GEN_RELATIVE_TO_PARENT or DM_GEN_RELATIVE_TO_CHILD, the current occurrence in the parent or child table view is used as the basis for the occurrence number. Then, the setting for occurrence is checked. If occurrence is 0, the current occurrence in that table view is used in the statement. If occurrence is greater than 0, the occurrence is calculated by adding the specified occurrence to the current occurrence.

If you only need to substitute an occurrence number in the statement processing, set relative to DM_GEN_ABSOLUTE_OCCUR and set occurrence to be greater than 0.

Example

# JPL Procedure:
	# Generate IN clause using binding parameters.
	# Function property is set to titles_exec.

proc titles_exec (event)
	if (event == TM_SEL_BUILD_PERFORM)
	{
	  vars retval(5), occ(3), i(3), in_buffer(255), comma(1)
	
  occ = @widget("qbe_titleid")->num_occurrences
	
# If the array "qbe_titleid" contains data, 
	# build a SQL "in" clause.
	
  if (occ > 0)
 
# First loop through qbe_titleid and build an IN clause 
# in the form "title_id" in (::p1, ::p2, ::p3).
  {
    for i=1 while i <= occ
    {
      if (qbe_titleid[i] != "")
      {
        %.0 i = i
        in_buffer = in_buffer ## comma ## "\:\:p" ## i \
          comma = ","
      }
    }
    in_buffer = "title_id in (" ## in_buffer ## ")"
    retval = dm_gen_change_select_where \
      ("", in_buffer, DM_GEN_APPEND)
 
# Now loop through qbe_titleid and change the EXECUTE 
# USING statement. This could be done in the previous loop.
# It is separated for clarity.

    for i=1 while i <= occ
    {
      if (qbe_titleid[i] != "")
      {
        %.0 i = i
        retval=dm_gen_change_execute_using \
          ('', "p:i", "qbe_titleid", i, \
          DM_GEN_ABSOLUTE_OCCUR, DM_GEN_APPEND)
      }
    }

    if (retval != 0)
    return TM_FAILURE
  }
}
return TM_PROCEED

Example

The following example uses the current occurrence in the parent table view to specify the occurrence number. The parent table view in this sequential link is a list of customers. When you enter one of the rental_status codes for a customer in the qbe_status field, the rentals for that customer which match that status populate the child table view. 

# JPL Procedure:
# Generate WHERE and EXECUTE USING clause using occurrence
# in parent table view. The Function property for rentals
# table view is set to rentals_hook.
proc rentals_hook(event)
{
  vars whexp(100) retval(5)
  if (event==TM_SEL_BUILD_PERFORM)
  {
  # Build the following: correlation.rental_status = ::qbe1
    whexp=dm_gen_get_tv_alias(sm_tm_pinquire(TM_TV_NAME)) \
      ## ".rental_status" \
      ## "=" \
      ## "::::qbe1"

  # Add it to the WHERE clause.
    retval = dm_gen_change_select_where("", whexp,\
      DM_GEN_APPEND)

  # Append to the EXECUTE USING clause in the form:
  # qbe1 = qbe_stat[<occ>]
  # where occ is the same occurrence number as the current
  # occurrence in parent table view.
    retval = dm_gen_change_execute_using\
      ("", "qbe1", "qbe_stat", \
      0, DM_GEN_RELATIVE_TO_PARENT, DM_GEN_APPEND)
  }
   return TM_PROCEED
}

See Also

dm_gen_sql_info