Kbase P135678: How to use RUN STPRED_PROC through ODBC DataServer to as/400?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
7/2/2009 |
|
Status: Verified
GOAL:
How to use RUN STPRED_PROC through ODBC DataServer to as/400?
GOAL:
How to override a data base file using RUN STORED-PROC through ODBC DataServer to as/400?
GOAL:
How to run equivalent QCMD of progress/400 using send-sql-statement through ODBC DataServer to as/400?
FACT(s) (Environment):
OpenEdge 10.x
ODBC DataServer
Oracle DataServer
Windows NT 32 Intel/Windows 2000
FIX:
In order to run QCMD with a minimum afford, a simple C program is being used on the following code sample:
1. First create a sample C program to run QCMD on the AS/400:
#include <stdio.h>
#include <string.h>
int main(int argc, char *argv[])
{
return system(argv[1]);
}
2. Create the module and program as follows (replace TEST library with your library name):
CRTCMOD MODULE(TEST/SP_CMD) SRCFILE(TEST/C) SRCMBR(SP_CMD)
CRTPGM PGM(TEST/SP_CMD) MODULE(TEST/SP_CMD)
3. Use STRSQL command and create the stored procedure as follows (replace TEST library with your library name):
CREATE PROCEDURE TEST/SP_CMD(IN CMD CHAR ( 4096)) LANGUAGE C NOT DETERMINISTIC NO SQL EXTERNAL NAME TEST/SP_CMD PARAMETER STYLE GENERAL NOT FENCED PROGRAM TYPE MAIN
4. Pull SP_CMD into your schema holder using Update/Add table definition... utility from Data Admin > DataServers > ODBC Utilities.
5. Run a stored procedure on the ABL procedure editor to override Data base file as follows(replace TEST library with your library name):
DEFINE VAR hdl AS INT.
RUN STORED-PROC SP_CMD hdl = PROC-HANDLE
("ovrdbf file(customer) tofile(test/customer) mbr(testmbr) ovrscope(*job)").
CLOSE STORED-PROC SP_CMD WHERE PROC-HANDLE = hdl.
/*
FOR EACH customer NO-LOCK:
DISPLAY name.
END.
*/
/* Return value test */
DEF VAR res AS INTEGER NO-UNDO.
/* Create a multi-member test file:
CRTPF FILE(MYLIB/OVRFILE) SRCFILE(MYLIB/DDS) MBR(MYMBR) MAXMBRS(3)
addpfm FILE(MYLIB/OVRFILE) MBR(MYMBR2)
Execute test: 1st time will succeed
*/
RUN STORED-PROC SP_CMD hdl = PROC-HANDLE
("ADDPFM FILE(MYLIB/OVRFILE) MBR(MYMBR3)").
CLOSE STORED-PROC SP_CMD res = PROC-STATUS WHERE PROC-HANDLE = hdl.
IF res = 0 THEN
DISPLAY "SP_CMD succeeded".
ELSE
DISPLAY "SP_CMD failed, ret = " res.
/* Rerun this test: Now fails since MYMBR3 already exists */
/* Re-test success by externally removing the member:
rmvm file(mylib/ovrfile) mbr(mymbr3)
Execute test: test succeeds again
*/
/* IMPORTANT NOTE: OVRDBF does not validate entries and therefore does not
return failures or messages for invalid overrides !!
This is not a problem with this test program, it is a
function of OVRDBF
Replace the sp_cmd in the above w/legitimate override ...
OVRDBF FILE(ovrfile) TOFILE(mylib/ovrfile) MBR(mymbr2) OVRSCOPE(*job)")
Or an illegitimate overrid ...
OVRDBF FILE(ovrfileX) TOFILE(mylibX/ovrfileX) MBR(mymbr2X) OVRSCOPE(*job)")
Neither test fails.
*/