SYS_MGR.ENGINE

How to:

You can issue DBMS commands directly (SQL Passthru) from a Maintain procedure using the SYS_MGR.ENGINE command.

Note: Problems with direct commands are not reported in FOCERROR. You will need to use DBMS_ERRORCODE to determine the success or failure of these commands.

Syntax: How to Use the SYS_MGR.ENGINE Command

The syntax for the SYS_MGR.ENGINE command is 

SYS_MGR.ENGINE("enginename", "command");

where:

enginename

Is the name of the RDBMS to which you are passing the command. For a complete list of the possible values, see the Adapter Administration manual.

command

Is any valid SQL command, including CREATE, DROP, and INSERT.

Example: Issuing the DROP TABLE Command

The following command drops the table NYACCTS. The error code is saved in a variable named rc. 

Compute rc/i8;
rc = sys_mgr.engine("SQLMSS", "DROP TABLE NYACCTS");
Type "Return Code=<<rc  DBMS Err=<<SYS_MGR.DBMS_ERRORCODE" ;

Example: Setting Connection Attributes for an MS SQL Server 

Compute rc/i8;
rc=sys_mgr.engine("SQLMSS","set connection_attributes mssxyz/ibiusr1,foo"
);
Type "RC from set is <<rc  DBMS Err=<<SYS_MGR.DBMS_ERRORCODE";

Example: Inserting a Row Into a Table (MS SQL) 

Compute rc/i8;
Type "Inserting row into table MNTTAB2 ";
rc=sys_mgr.engine("SQLMSS","insert into mntbtab2 
values('X2','XDAT2222');");
Type"ReturnCode=<<rc DBMS Err=<<SYS_MGR.DBMS_ERRORCODE";

You will need to test the return code to determine whether the record was inserted successfully (RC = 0).

If you are using MS SQL, and the value you wanted to insert was a duplicate record, you would expect to see the following return codes: 

Return Code= -1 DBMS Err=    2627