Consultor Eletrônico

Returning info from DB schema trigger to ESQL/C application

INTRODUCTION
============
This knowledgebase entry discusses how to send information
back to an ESQL/C application from a database schema trigger
with the MESSAGE statement.

RETURNING STRING
================
If you have a string of information that you need to pass
back to an ESQL/C application from a database schema
trigger you can use MESSAGE in the trigger 4gl and then use
sqlgetmsg() in the ESQL/C application to get the string.

For example, you could have the following trigger:

TRIGGER PROCEDURE FOR Write OF Customer OLD BUFFER oldCustomer.

IF customer.cust-num = 1 THEN
MESSAGE "trigger string".

and the following ESQL/C segment which will print "trigger string":

EXEC SQL UPDATE customer SET name = "new name"
WHERE cust\-num = 1;

while ((msg = (char *) sqlgetmsg()) != (char *) 0)
printf("%s\n",msg);

When this C program is run, "trigger string" will be displayed.

RETURNING STRING AND ERROR
==========================
You could also RETURN ERROR for validation purposes along with MESSAGE
to stop the update if validation has failed for the write and
provide additional information with the string from the MESSAGE
statement.

For example, your trigger would look like this:

TRIGGER PROCEDURE FOR Write OF Customer OLD BUFFER oldCustomer.

IF customer.cust-num = 1 THEN DO:
MESSAGE "trigger string".
RETURN ERROR.
END.

And your ESQL/C segment could have:

EXEC SQL UPDATE customer SET name = "new name"
WHERE cust\-num = 1;

if (SQLCODE != 0){
printf("Error %-31d occured.\n", SQLCODE);
while ((msg = (char *) sqlgetmsg()) != (char *) 0)
printf("%s\n",msg);
}

This will display the following when the C application is run:

Error -1 occured.
trigger string

Be aware that if you have a Progress application that uses the
same database you will now get these messages when updating
records in your Progress application.

REFERENCES TO WRITTEN DOCUMENTATION
===================================
Please refer to 3GL Access to Database Schema Triggers section
of the Embedded SQL Guide and Reference for more information
about ESQL/C and schema triggers.


Progress Software Technical Support Note # 16891