Programming Guide

dm_exec_sql

Generates and executes SQL statements 

#include <tmusubs.h>
int dm_exec_sql(int type, char *cursor_name);

type
Type of SQL statement specified by one of the constants listed in Table 5-1.

cursor_name
Name of the cursor associated with the SQL statement.

Returns

Description

dm_exec_sql is called from a transaction model or a user event function to generate and execute SQL statements according to one of the following constants supplied for the type parameter:

Table 5-1 SQL statement types

Argument Description

BUILD_SELECT

Examines screen properties and builds structures for a SELECT statement including a distinct string, if specified, a select list (column names and/or expressions), and a WHERE clause.

BUILD_VALIDATE

Examines screen edits and builds structures for a SELECT statement used to process a validation link.

DECLARE_DELETE_NBR
DECLARE_DELETE_OCC

Builds and executes the following statement for database deletions:

DBMS DECLARE cursor CURSOR FOR DELETE FROM current-table-view
 WHERE primary-key-column = :w_primary-key-column ...

DECLARE_INSERT

Builds and executes the following statement for database insertions:

DBMS DECLARE cursor CURSOR FOR INSERT INTO current-table-view
(column-name ...)
VALUES (:v_column-name...)

DECLARE_UPDATE

Builds and executes the following statement for database updates:

DBMS DECLARE cursor CURSOR FOR UPDATE current-table-view
 SET column-name = :s_widget-name ...
WHERE primary-key-column = :w_primary-key-column ...

EXEC_DELETE_NBR
EXEC_DELETE_OCC

Builds and executes the following statement for database deletions:

DBMS WITH CURSOR cursor EXECUTE USING
w_primary-key-column = @bi(primary-key-widget)[occ] ...

EXEC_INSERT

Builds and executes the following statement for database insertions:

DBMS WITH CURSOR cursor EXECUTE USING
v_column-name = widget-name[occ] ...

EXEC_UPDATE

Builds and executes the following statement for database updates:

DBMS WITH CURSOR cursor EXECUTE USING
s_column-name = widget-name[occ] ...
w_primary-key-column = @bi(primary-key-widget)[occ] ...

PERFORM_SELECT

Executes the following statements for database queries:

DBMS DECLARE cursor CURSOR FOR SELECT [ DISTINCT ] select-list
 FROM tables-in-current-server-view
 [ WHERE [ join-clause ] [ AND search-condition ] ]
[ GROUP BY column-list ]
[ HAVING search-condition ]
[ ORDER BY column-position { ASC|DESC }, ... ]

DBMS WITH CURSOR cursor ALIAS widget-list

DBMS WITH CURSOR cursor <EXECUTE
[ USING [ join-values ] [ where-values ] [ having-values ] ]

PERFORM_VALIDATE

Executes the following statements for validation links:

DBMS DECLARE cursor CURSOR FOR
SELECT {1 | look-up list} FROM child-table-view WHERE ...
DBMS WITH CURSOR cursor ALIAS ...
DBMS OCCUR
DBMS WITH CURSOR cursor EXECUTE
DBMS CLOSE CURSOR cursor

Selecting Data

dm_exec_sql(BUILD_SELECT) and dm_exec_sql(BUILD_VALIDATE) should not be called without a prior call to dm_gen_sql_info to initialize the statement structures. In the standard transaction models, dm_exec_sql and other related functions are called by the following requests:

Table 5-2

Request dm_exec_sql (and related) calls

TM_SEL_GEN

dm_gen_sql_info(SELECT, cursor)

TM_SEL_BUILD_PERFORM

dm_exec_sql(BUILD_SELECT, cursor)
dm_exec_sql(PERFORM_SELECT, cursor)
dm_free_sql_info(SELECT)

TM_VAL_GEN

dm_gen_sql_info(VALIDATE, cursor)

TM_VAL_BUILD_PERFORM

dm_exec_sql(BUILD_VALIDATE, cursor)
dm_exec_sql(PERFORM_VALIDATE, cursor)

TM_VAL_CHECK

dm_free_sql_info(VALIDATE)

Modifying Data

dm_exec_sql(DECLARE_xxx) should not be called without a prior call to sm_bi_initialize. The transaction manager calls sm_bi_initialize automatically when sm_tm_command("NEW") or sm_tm_command("SELECT") is executed. In the standard transaction models, dm_exec_sql and other related functions are called by the following requests:

Table 5-3

Request dm_exec_sql calls

TM_DELETE_DECLARE

dm_exec_sql(DECLARE_DELETE_NBR)
dm_exec_sql(DECLARE_DELETE_OCC)

TM_DELETE_EXEC

dm_exec_sql(EXEC_DELETE_NBR)
dm_exec_sql(EXEC_DELETE_OCC)

TM_INSERT_DECLARE

dm_exec_sql(DECLARE_INSERT)

TM_INSERT_EXEC

dm_exec_sql(EXEC_INSERT)

TM_UPDATE_DECLARE

dm_exec_sql(DECLARE_UPDATE)

TM_UPDATE_EXEC

dm_exec_sql(EXEC_UPDATE)