Re: Powerbuilder + Informix
dennisp@informix.com (Dennis Pimple) writes:
>pfinkel@cbnews.att.com (paul.d.finkel) writes:
>>Problem: how to use Powerbuilder as a means of kicking off a UNIX level
>>command. We need to have I4GL reports run on the server side so that they
>>can be printed directly to UNIX printers. We think this idea might have merit:
>>run a stored procedure that runs a 4gl report. Does any one see any problems
>>with that? ...
>This is how we implented the same sort of need for a large project just
>recently. I trust my partner in crime, David Berg, will give you a blow-by-blow
>accounting. If he doesn't answer or post here in the next day or so, contact
>him (dberg@informix.com) and I'm sure he'll give you exhaustive details when he
>has time.
>See how good I am at volunteering other Informix employee's time?
It's always good to know that your co-workers are so liberal with your
time. Thank you, Dennis.
We implemented this concept using the SYSTEM command in stored procedures.
The PowerBuilder client must execute a procedure with a parameter list
sufficient to build a command string to execute the 4GL program. The 4GL
program is then executed with a system call from the stored procedure.
I'll illustrate two examples. First is a simple,generic example. In this
example, the entire formatted command string, including the name of the 4GL
program followed by its paramter list, is passed from the PowerBuilder client.
---------------------------------------------------------------------
CREATE PROCEDURE system_call(p_userid_srl INTEGER,
p_command_string VARCHAR(255))
RETURNING INTEGER;{
Arguments: Userid serial, Command String
Purpose: Pass a command string to a background system call.
Used principally to start background report processes.
Returns: Status
----------------------------------------------------------------------}
DEFINE GLOBAL pg_sql_code INTEGER DEFAULT 0;
DEFINE GLOBAL pg_isam_code INTEGER DEFAULT 0;
DEFINE GLOBAL pg_error_value VARCHAR(80) DEFAULT "";
DEFINE GLOBAL pg_errlog_info VARCHAR(80) DEFAULT "";
DEFINE p_status INTEGER;
DEFINE p_execute_path VARCHAR(120);
ON EXCEPTION SET pg_sql_code, pg_isam_code, pg_error_value
ROLLBACK WORK;
CALL ins_errorlog(p_userid_srl) RETURNING p_status;
RETURN p_status;
END EXCEPTION;
BEGIN
LET p_status = 0;
LET pg_errlog_info = "system_call.01";
LET p_execute_path = NULL;
SELECT execute_path INTO p_execute_path FROM profile, personnel
WHERE profile.locn_code = personnel.locn_code
AND profile.court_type = personnel.court_type
AND personnel.userid_srl = p_userid_srl;
SYSTEM p_execute_path || p_command_string;
END
RETURN p_status;
END PROCEDURE;
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
A more complex example illustrates a triggered procedure interrogating an
error log table for a row that might have been inserted by the 4GL program,
and then RAISEing an EXCEPTION to the client if such a row exists. It also
illustrates building the command string from paramters passed to the
stored procedure and values selected from the database.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
This is a triggered procedure which spawns a 4GL process to perform
the shadowing processes. If it detects that the 4GL process aborted
due to an error, it raises an exception to its triggering event.
}
CREATE PROCEDURE shadow(p_userid_srl INTEGER, p_key_value INTEGER,
p_function_code VARCHAR(1))
DEFINE GLOBAL pg_errlog_srl INTEGER DEFAULT 0;
DEFINE GLOBAL pg_sql_code INTEGER DEFAULT 0;
DEFINE GLOBAL pg_isam_code INTEGER DEFAULT 0;
DEFINE GLOBAL pg_error_value VARCHAR(80) DEFAULT "";
DEFINE GLOBAL pg_errlog_info VARCHAR(80) DEFAULT "";
DEFINE p_int_case_num LIKE case.int_case_num;
DEFINE p_current DATETIME YEAR TO SECOND;
DEFINE p_errlog_srl INTEGER;
DEFINE p_execute_path VARCHAR(120);
DEFINE p_command_string VARCHAR(255);
DEFINE p_status INTEGER;
BEGIN
LET p_current = CURRENT;
LET pg_errlog_info = "shadow.spl.01";
IF p_function_code = "C" THEN
SELECT clerk_id, int_case_num INTO p_userid_srl, p_int_case_num
FROM case_me
WHERE case_me.me_id = p_key_value;ELSE
LET p_int_case_num = p_key_value;
END IF;
LET pg_errlog_info = "shadow.spl.02";
LET p_execute_path = NULL;
SELECT execute_path INTO p_execute_path FROM profile, personnel
WHERE profile.locn_code = personnel.locn_code
AND profile.court_type = personnel.court_type
AND personnel.userid_srl = p_userid_srl;
LET p_command_string = "shadow " || p_userid_srl || " " ||
p_int_case_num || " " || p_function_code;
LET pg_errlog_info = "shadow.spl.03";
SYSTEM p_execute_path || p_command_string;
LET pg_errlog_info = "shadow.spl.04";
LET pg_errlog_srl = NULL;
SELECT errlog_srl, errlog_info, sql_code, isam_code, error_value
INTO pg_errlog_srl, pg_errlog_info, pg_sql_code, pg_isam_code, pg_error_value
FROM errorlog
WHERE userid_srl = p_userid_srl
AND errlog_datetime >= p_current
AND errlog_info MATCHES "shadow*";IF pg_errlog_srl IS NOT NULL THEN
RAISE EXCEPTION pg_sql_code, pg_isam_code, pg_error_value;
END IF;
END
END PROCEDURE;
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
___ ___ Senior Consultant
/ ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210
_/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111