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.
The syntax for the SYS_MGR.ENGINE command is
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.
Is any valid SQL command, including CREATE, DROP, and INSERT.
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" ;
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";
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