exec_sql

Executes a SQL statement.

Available in:

Apps (win)	Apps (char)	Reportwriter	RPC	Standalone PL
X	X	X	X	X

Syntax

int exec_sql(statement[,host-variable,...])
string       statement
expr         host-variable

Description

Executes a SQL statement and returns the number of affected rows for INSERT, UPDATE, and DELETE. For other operations, function returns 0. If an error occurs in the SQL statement and exec_sql() is part of an expression, the function returns -1. If exec_sql() is not part of an expression and error occurs, the function escapes to the window trigger error trap or, for reports and stand-alone TRIMpl, operating system command shell.

host-variable (optional) specifies the bind variables (either as variables or hard-coded) for the statement. If host-variable is a list, all host-variable references are resolved from the list.

The host variable reference character varies between databases. To maintain database portability, use the Oracle ``:n'' notation; on all other systems, the ``:n'' is automatically replaced by ``?'' notation.

Sybase

Sybase stored procedures can be executed by using exec_sql(), which returns either the number of rows processed or the raiserror value in case of error. The raiserror or print message can be retrieved with db_msg().

count = exec_sql("insert_proc",id,name);

Example

Increases the salary of everyone in department 58; a prompt asks for the percent increase for each person.

if (DEPT == 58)
  exec_sql("UPDATE staff SET sal = sal * " ^^
            1 + prompt("Enter increase for " ^^ NAME ^^ " ==> ") / 100 ^^
           " where NAME = " ^^ NAME);

Delete all employees whose salary is greater than max_salary.

if (exec_sql("DELETE FROM staff WHERE sal > :1",max_salary) < 0)
  printf("Error occurred while deleting overpaid employees");