Maintain and Relational Databases

by Mark Derwin

When maintaining relational databases, you may want to interact with the database or environment on a more native level. Information Builders' Maintain gives you the ability to issue commands, both to the environment and the database, directly. This helps developers ensure the most efficient interaction between Maintain and database management system (DBMS) servers.

This article deals with three types of those commands: controlling the environment, retrieving DBMS error codes, and issuing native commands to the database.

When issuing SQL commands to the database, both error and successful messages appear on the screen. Maintain allows you to retrieve these error codes and branch accordingly. The first thing you want to do is stop the messages from being echoed to the screen. To accomplish this from inside Maintain, issue a SYS_MGR command. The syntax is as follows:

SYS_MGR.FOCSET("EMGSRV", "OFF");

The FOCSET command is used to change the environment. The command turns off EMGSRV so no messages are echoed to the screen.

Now that the errors aren't being displayed, we need to retrieve them inside the Maintain application. We do this with another SYS_MGR command:

SYS_MGR.DBMS_ERRORCODE;

Once you have this value, you can issue the proper messages to guide your users.

Lastly, we can issue commands directly to SQL. Some of the things you can do with this command are create and drop tables, set connection attributes, and insert data into a table. Here is the syntax for this command:

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

In my example I have a simple SQLMSS data-base named mderwin containing three fields: Field1, Field2, Field3. The form prompts the user to enter values for these fields. Pressing "Include" builds a SQL statement, and issues it to the interface.

Form 1 - Input Data

Looking at the code we see that the SYS_MGR.FOCSET command turns off messages to the screen. The SYS_MGR.ENGINE command issues the insert statement directly to SQL.

MAINTAIN FILE mderwin

$$Declarations

Case Top
SYS_MGR.FOCSET("EMGSRV", "OFF");
infer field1 into stk
perform getdata
Winform Show Form1;
EndCase

case includer
Compute rc/i8;
Compute SQLCMD/A200 =
"insert into mderwin " |
"(field1, field, field3) " |
"values (' " || stk.field1 || "', ' " || stk.field2 || "', " | stk.field3 ||");"
RC = sys_mgr.engine("SQLMSS", SQLCMD);
compute error/i3 = rc;
compute sqlerror/i5 = sys_mgr.dbms_errorcode;
perform getdata
endcase

case getdata
reposition field1
stack clear stk2
for all next field1 into stk2
endcase

END

Lastly, I display this included record and any error codes that may have been received from the interface using the SYS_MGR.dbms_errorcode command.

There was no problem including this record in the database. However, if I make a mistake in the naming of a column I may receive a SQLCODE of 207. Instead of the error being echoed to the screen, I control what is displayed.

These are just some of the ways that you can manipulate the database and environment from inside of Maintain. To see a complete list of commands search on SYS_MGR in the online documentation.