Web page call RPG &SQL
Versionning
- 2005-10-21 first publication
last version : The SaveFiles
Calling a RPG program with NET.DATA
scripting |
apache conf in ...\httpd.conf : |
in Apache, link to NET.DATA interpreter
MapMatch ^/EcartPrix/Historique$ /QSYS.LIB/JPLHTTP.LIB/DB2WWW.PGM/QSYS.LIB/JPLHTTP.LIB/Qpgmsrc.FILE/NETDATA.MBR/HISTO
|
this line maps public link
"http://myAS400/EcartPrix/Historique" to DB2WWW.PGM
net.data interpreter (it could be directly your own pgm if you have a
sample of
program for handling web GET & POST. I have not, but there is
something
approaching at http://search400.techtarget.com/tip/1,289483,sid3_gci1043521,00.html
)
DB2WWW is furnished by IBM. It is recommended to make a copy in working
directory. here it is "/QSYS.LIB/JPLHTTP.LIB/". Some docs about
(particularly authorities for QTMH* profiles) are in iSeries HTTP
Server for iSeries Web Programming Guide
Version 5 December, 2001
GC41-5435-06
Chapter 2. Enabling your server to run CGI programs
the rest of the link give the path to the net.data macro (here it is
"/QSYS.LIB/JPLHTTP.LIB/Qpgmsrc.FILE/NETDATA.MBR")
and the starting macro (here "HISTO")
This means you uses SEU to update the macro.
it works, but now I prefer putting my macro in the IFS and I maintain
the macros
with notepad.
In this case, apache conf line becomes
MapMatch ^/EcartPrix/Historique$
QSYS.LIB/JPLHTTP.LIB/DB2WWW.PGM//MyIfsDir/myMacro.txt/HISTO
the net.data source member |
%FUNCTION(DTW_SYSTEM) pgmdirlist (IN DIRNAME, OUT DIRLIST) { %EXEC { /QSYS.LIB/JPLHTTP.LIB/ECARTPRIX.PGM %} %} %DEFINE { MYLIST = "" %} %HTML(Histo) { <HTML> <HEAD> <TITLE>Suivi des écarts de prix.</TITLE> </HEAD> <BODY LINK="#0000ff" VLINK="#800080"> <A href="ECART.XLS">Ecarts de prix du jour</A> <P></P> <table bgColor=silver background="" border=1> <TR> <TH>Fichier Excel<TH ALIGN=RIGHT>Taille @pgmdirlist("/JPLHTTP/EcartDePrix", MYLIST) $(MYLIST) </table> </BODY></HTML> %} %HTML(HelloWorld) { <P>Hello the World %}
|
in net.data syntax, an html entry point is a %HTML
here, it's %HTML(Histo) {
This macro return some html code and the output of "@pgmdirlist" a
system call (%FUNCTION(DTW_SYSTEM)) to my program
/QSYS.LIB/JPLHTTP.LIB/ECARTPRIX.PGM
note : the way I have used is not the simplest.
the DTW_SYSTEM can call pgm or cmd, support direct and indirect
parameters. Sample
excerpt from
Net.Data Administration and Programming Guide.pdf
:
%FUNCTION(DTW_SYSTEM) sndbrkmsg () { %EXEC { /QSYS.LIB/SNDBRKMSG.CMD MSG('$(msgToSend)') TOMSGQ(*ALLWS) %} %} %HTML(sndbrkmsg) { @sndbrkmsg() %}
|
But DTW_DIRECTCALL is impler. It can call pgm, support direct
parameters ; witch are a common way to work.
All the details are in the
Net.Data Administration and Programming Guide.pdf
Chapter 6. Using Language Environments.
excerpt :
This macro:
%define ilepgm = "/QSYS.LIB/NETDATADEV.LIB/TDCCLI01.PGM" %define out1 = "0" %FUNCTION(DTW_DIRECTCALL) dcFunction(IN INT inp1, OUT INT outp2){ %EXEC { $(ilepgm) %} %} %HTML(REPORT) { @dcFunction("123", out1) The value of out1 is: "$(out1)" %}
|
calls this ILE RPG program:
DINP1 S 10I00 DOUTP2 S 10I00 C* C *ENTRY PLIST C PARM INP1 C PARM OUTP2 C* C Z-ADD INP1 OUTP2 C* C SETON LR
|
or this CL program:
PGM PARM(&INP1 &OUTP2) DCLVAR(&INP1) TYPE(*CHAR) LEN(4) DCLVAR(&OUTP2) TYPE(*CHAR) LEN(4) CHGVAR VAR(&OUTP2) VALUE(&INP1) ENDPGM
|
And DTW_STSTEM support direct parameter :
%DEFINE INPARM1 = "SWITCH1" %FUNCTION(DTW_SYSTEM) sys1() { %EXEC{/QSYS.LIB/NETDATA.LIB/RPGCALL1.PGM ('$(INPARM1)' 'literalstring')%} %}
|
But, whatever the lost opportunity, hereafter is the complete code of
the program
here is some explanations
first : net.data insert in the shell variable list the 2 declarations
(DIRNAME, DIRLIST)
then load DIRNAME with provided parameter then call the native code
then retrieve DIRLIST
second : the reverse side, my pgm retrieve DIRNAME with QtmhGetEnv and
load DIRLIST with
QtmhPutEnv
now you are interested with using
Dgetenv PR extproc('QtmhGetEnv') Dputenv PR extproc('QtmhPutEnv')
|
* read DIRNAME C eval envNm = 'DIRNAME' C eval envNmLn = %len(%trim(envNm)) C callp getenv(envVal:envBufLn:envValLn: C envNm:envNmLn:qusec) C Eval directory = envval * prepare DIRLIST c eval html = 'DIRLIST=' c eval html = html +'<TR>' c + '<TD>' + c '<A href="/EcartPrix/' + c %trim(name) + '">' + c %trim(name) + '</A>' + c '</TD><TD ALIGN=RIGHT>' + c %trim(%editc(st_size:'Z')) + c '</TD></TR>' * Set DIRLIST value C eval envValLn = %len(html) c eval envval = html C callp putenv(envval:envValLn:qusec)
|
the rest of the code is a sample to read a directory.
If some french comment make you confuse, feed back, I will translate.
Note : you can test your program only in the server (the 400) by handly
setting
& getting the variables in the "unix" environment, the one that
works the IFS. Commands to use are
Commandes
1. Add Environment Variable ADDENVVAR
2. Change Environment Variable CHGENVVAR
3. Remove Environment Variable RMVENVVAR
4. Work with Environment Var WRKENVVAR
Menus des commandes associées
5. Commandes interpréteur QSHELL CMDQSH
the native program (CLP or RPG,
it's the same context) |
h debug * prototype for QtmhGetEnv API Dgetenv PR extproc('QtmhGetEnv') D Valeur 32767 D ValeurLngMax 10i 0 D ValeurLng 10i 0 D Variable 30 D VarLen 10i 0 D ErrorCode 116 * prototype for QtmhPutEnv API Dputenv PR extproc('QtmhPutEnv') D VarVal 32767 D VarValLen 10i 0 D ErrorCode 116
* variables used for QtmhGetEnv & QtmhPutEnv calls DenvVal S 32767 DenvBufLn S 10i 0 inz(%size(envVal)) DenvValLn S 10i 0 DenvNm S 30 DenvNmLn S 10i 0 * Error data structure from openness library, QSYSINC. * If not present, it can be installed (it comes with * OS/400 as part of the System Openness Includes component). /copy qsysinc/qrpglesrc,qusec D QUSMSGDTA 100 * Initialize the error data structure's bytes-provided subfield
D Directory S 1024 D Html S 32767 varying D DateIso S 10 D DateFichier S D D DateMin S D D DateJour S D inz(*sys)
* pour opendir D RC S 10I 0 D path S 10A D pathx S 10A D pathp S * INZ D diropn S 10I 0 D diropnp S * INZ * pour readdir Dentry ds BASED(entryp) D reserv1 16 D filenogenid 10U 0 D fileno 10U 0 D reclen 10U 0 D reserv3 10I 0 D reserv4 8A D ccsid 10I 0 D countryid 2A D langid 3A D nlsreserv 3A D namelen 10I 0 D namedir 256 D entryp S * Dname s 50 Dnamefile s 50 * * pour stat - attributs du fichier *stmf Dstatdta ds 131 D st_mod 10U 0 D st_ino 10U 0 D st_nlink 10U 0 D st_uid 10U 0 D st_gid 10U 0 D st_size 10U 0 D st_atime 10U 0 D st_mtime 10U 0 D st_ctime 10U 0 D st_dev 10U 0 D st_blksize 10U 0 D st_allocsize 10U 0 D st_objtype 10A * Dnomifs s 256 Dstatp S * INZ Dpathtot s 256 Dpathtotp s * INZ Dpathtotlg s 4 0 Dperrorp s * INZ Dperrordta s 20 INZ('path() error') *** on integre le prototypage des fonctions C Dperror PR 10I 0 EXTPROC('perror') Dconst * VALUE
Dsprintf PR 10I 0 EXTPROC('sprintf') D * VALUE D * VALUE D 10I 0 VALUE OPTIONS(*NOPASS) D * VALUE OPTIONS(*NOPASS)
* Open Operations * value returned = file descriptor 0 (OK), -1 (Error)
Dopen PR 10I 0 EXTPROC('open') D * VALUE D 10I 0 VALUE D 10U 0 VALUE OPTIONS(*NOPASS) D 10U 0 VALUE OPTIONS(*NOPASS)
* Read Operations * value returned = number of bytes read or , -1 (Error)
Dread PR 10I 0 EXTPROC('read') D 10I 0 VALUE D * Value D 10U 0 VALUE
* Write Operations * value returned = number of bytes Written or , -1 (Error)
Dwrite PR 10I 0 EXTPROC('write') D 10I 0 VALUE D * VALUE D 10U 0 VALUE
* Close Operations * value returned = 0 (OK) or , -1 (Error)
Dclose PR 10I 0 EXTPROC('close') D 10I 0 VALUE
* Open Directory Operation * value returned = file descriptor 0 (OK), -1 (Error)
Dopendir PR * EXTPROC('opendir') D * VALUE
* Read Directory Operation *
Dreaddir PR * EXTPROC('readdir') D * VALUE
* Open Directory Operation * value returned = 0 (OK) or , -1 (Error)
* stat file Operation *
Dstat PR 10I 0 EXTPROC('stat') D * VALUE D * VALUE
Dclosedir PR 10I 0 EXTPROC('closedir') D * VALUE
* Unlink a File from system... Delete File * value returned = 0 (OK) or , -1 (Error)
Dunlink PR 10I 0 EXTPROC('unlink') D * VALUE
C eval qusbprv =116 qusec leng c datejour subdur 30:*D datemin * Lire la variable DIRNAME C eval envNm = 'DIRNAME' C eval envNmLn = %len(%trim(envNm)) C callp getenv(envVal:envBufLn:envValLn: C envNm:envNmLn:qusec) * initialiser la reponse
c eval html = 'DIRLIST='
* ouvrir le repertoire c do opendir C Eval directory = envval C Eval pathp = %Addr(directory) C Eval statp = %Addr(statdta) * ouverture du repertoire a lister C EVAL diropnp = opendir(pathp) C IF diropnp = *null C leave C endif * lecture du repertoire C do *hival readdir C eval entryp= readdir(diropnp) C if entryp = *null C leave C endif C eval name=%subst(namedir:1:namelen) * on ignore le . et le .. C if (%subst(name:1:1) = '.') c iter c endif * on prends seulement les fichiers ECART C if (%subst(name:1:7) <> 'ECARTDU') c iter c endif * récents c eval dateiso= %subst(name:8:10) c *ISO- movel dateiso datefichier c if datefichier < datemin c iter c endif
c if %subst(directory:2:1) <> ' ' C eval pathtot = %str(pathp)+ '/'+ name c else C eval pathtot = %str(pathp)+ name C end C Eval pathtotp = %Addr(pathtot) C eval pathtotlg = %len(%trim(pathtot)) + 1 * ajout du caractere null a la fin du nom c eval nomifs = pathtot C eval %str(pathtotp : pathtotlg) = pathtot * appel de l'API stat pour recuperation du type : *stmf ou directory C eval rc = stat(pathtotp : statp) * traitement de l'erreur C if rc = -1 C Eval perrorp = %Addr(perrordta) C callp Perror(perrorp) c leave c endif ** vous pouvez lancer le traitement sur le fichier trouvé ** Le nom du fichier est dans la zone : nomifs ** Le type est contenu dans la zone st_objtype ** st_objtype vaudra *stmf si c'est un fichier ou *dir pour ** directory C eval namefile=%subst(nomifs:1:50) C* namefile dsply C* st_objtype dsply C* st_blksize dsply
c if st_objtype = '*STMF'
* preparer la ligne html c eval html = html +'<TR>' c + '<TD>' + c '<A href="/EcartPrix/' + c %trim(name) + '">' + c %trim(name) + '</A>' + c '</TD><TD ALIGN=RIGHT>' + c %trim(%editc(st_size:'Z')) + c '</TD></TR>' c endif c enddo readdir * fermer le repertoire C callp closedir(pathp) c enddo opendir * envoyer la reponse * Set DIRLIST value C eval envValLn = %len(html) c eval envval = html C callp putenv(envval:envValLn:qusec)
c**************** dump
c seton lr
as400
|
More interesting is the
availability of direct SQLing DB2 from a web page (a html page, or an
ASP tiers). I have made a SQLQUERY web page that samples this. I have
reduced the scope to SELECT only for many reasons, main are
- I don't like update coming from outside my 400
programs
- my DB2 400 library & files are not
journalized and net.data sql update prerequisite is system
journalization (IE library are created with sql CREATE COLLECTION)
sql tips : SQL
SQL Pivot table
ExecSql ExecuteSql
|
look at this page : it is able to ask the NET.DATA macro to
retrieve some
sqldata :
<HTML> <HEAD> <TITLE>Query SQL.</TITLE> <SCRIPT language="JavaScript"> <!-- begin script function questionner(SQL) { var anUrl = 'http://my400/sqlquery/CGI/xml?SQL=' + escape(SQL); var html; var jscript; XMLFRAME.innerHTML='nothing'; var xmldoc = new ActiveXObject("Microsoft.XMLDOM"); var xsldoc = new ActiveXObject("Microsoft.XMLDOM"); xmldoc.async = false; xsldoc.async = false; var docload = anUrl; xmldoc.load(docload); if((xmldoc.parseError.errorCode != 0) ) { XMLFRAME.innerHTML=xmldoc.parseError.reason; return; } XMLFRAME.innerHTML=xmldoc.xml; } function questionnerHtml(SQL) { var anUrl = 'http://my400/sqlquery/CGI/html?SQL=' + escape(SQL); window.open(anUrl); } function syscolumns() { sqlform.sql.value ="DBILIB library, DBIFIL filename, DBIFMT format, DBIFLD field, DBIATR pf_lf, DBIILN byte, DBIITP as_type, DBITYP sql_type, DBIPOS rang, DBICLN Char, DBINLN Digit, DBINSC decimals, DBIDLN DateLen,DBITXT Text, DBIHDG colsHeader FROM QSYS.QADBIFLD WHERE \nDBILIB='' and \nDBIFIL='' ORDER BY DBIPOS"; return; } //--> </script> </HEAD> <BODY LINK="#0000ff" VLINK="#800080" bgColor=#ffffff><!les 2 bandeaux de tete de page> <form> <H1 ALIGN="center">SQL Query</H1> <P> Select <p><TEXTAREA id=sql name=SQL rows=10 cols=100>* FROM mylib.mycalendar WHERE MyDate between 20040401 and 20040430 </TEXTAREA> <P> <INPUT type=button value="Ask for XML" onclick="questionner(sqlform.sql.value)" > <INPUT type=button value="clear XML" onclick="XMLFRAME.innerHTML='';" > <INPUT id=syscols type=button value="syscols" onclick="syscolumns()" > <INPUT id=Rafraichir type=button value="Ask for HTML" onclick="questionnerHtml(sqlform.sql.value)" > </P> </form> <hr> <span id=XMLFRAME></span> <hr> </BODY> </html> as400
|
actual web aspect is this :
and here is the corresponding NET.DATA macro :
%{*=---------------------------------- F:\JPLHTTP\sqlQuery test : http://My400/sqlquery/cgi/xml?SQL= ... ------------------------------=*%} %DEFINE REMOTE_ADDR=%ENVVAR %DEFINE SERVER_NAME=%ENVVAR %DEFINE SERVER_PORT=%ENVVAR %define DATABASE = "*LOCAL" %DEFINE DATABASE=%ENVVAR %DEFINE DB_CASE=%ENVVAR %DEFINE DB2INSTANCE=%ENVVAR %DEFINE DB2MSGS=%ENVVAR %DEFINE DB2PLAN=%ENVVAR %DEFINE DB2PLAN=%ENVVAR %DEFINE DB2SSID=%ENVVAR %DEFINE DB2SSID=%ENVVAR %DEFINE DefaultDBCp=%ENVVAR %DEFINE DSNAOINI=%ENVVAR %DEFINE DTW_APPLET_ALTTEXT=%ENVVAR %DEFINE DTW_ATTACHMENT_PATH=%ENVVAR %DEFINE DTW_CACHE_HOST=%ENVVAR %DEFINE DTW_CACHE_MACRO=%ENVVAR %DEFINE DTW_CACHE_MANAGEMENT_INTERVAL=%ENVVAR %DEFINE DTW_CACHE_PAGE=%ENVVAR %DEFINE DTW_CACHE_PORT=%ENVVAR %DEFINE DTW_CM_PORT=%ENVVAR %DEFINE DTW_COBOL_PARAMETER_BUFFER_SIZE=%ENVVAR %DEFINE DTW_CURRENT_FILENAME=%ENVVAR %DEFINE DTW_CURRENT_LAST_MODIFIED=%ENVVAR %DEFINE DTW_DEFAULT_ERROR_MESSAGE=%ENVVAR %DEFINE DTW_DEFAULT_MACRO=%ENVVAR %DEFINE DTW_DEFAULT_MESSAGE=%ENVVAR %DEFINE DTW_DEFAULT_REPORT=%ENVVAR %DEFINE DTW_DIRECT_REQUEST=%ENVVAR %DEFINE DTW_DO_NOT_CACHE_MACRO=%ENVVAR %DEFINE DTW_EDIT_CODES=%ENVVAR %DEFINE DTW_ERROR_LOG_DIR=%ENVVAR %DEFINE DTW_ERROR_LOG_LEVEL=%ENVVAR %DEFINE DTW_HTML_TABLE=%ENVVAR %DEFINE DTW_INST_DIR=%ENVVAR %DEFINE DTW_LOB_DIR=%ENVVAR %DEFINE DTW_LOB_LIFETIME=%ENVVAR %DEFINE DTW_LOG_DIR=%ENVVAR %DEFINE DTW_LOG_LEVEL=%ENVVAR %DEFINE DTW_LOG_LEVEL=%ENVVAR %DEFINE DTW_MACRO_FILENAME=%ENVVAR %DEFINE DTW_MACRO_LAST_MODIFIED=%ENVVAR %DEFINE DTW_MBMODE=%ENVVAR %DEFINE DTW_MBMODE=%ENVVAR %DEFINE DTW_MP_PATH=%ENVVAR %DEFINE DTW_MP_VERSION=%ENVVAR %DEFINE DTW_OUTPUT_DIR=%ENVVAR %DEFINE DTW_PAD_PGM_PARMS=%ENVVAR %DEFINE DTW_REMOVE_WS=%ENVVAR %DEFINE DTW_REMOVE_WS=%ENVVAR %DEFINE DTW_SET_TOTAL_ROWS=%ENVVAR %DEFINE DTW_SHOWSQL=%ENVVAR %DEFINE DTW_SMTP_CHARSET=%ENVVAR %DEFINE DTW_SMTP_SERVER=%ENVVAR %DEFINE DTW_SQL_ISOLATION=%ENVVAR %DEFINE DTW_SQL_NAMING_MODE=%ENVVAR %DEFINE DTW_STORE_PAGE=%ENVVAR %DEFINE DTW_TRACE_LOG_DIR=%ENVVAR %DEFINE DTW_TRACE_LOG_LEVEL=%ENVVAR %DEFINE DTW_UNICODE=%ENVVAR %DEFINE DTW_UPLOAD_DIR=%ENVVAR %DEFINE DTW_USE_DB2_PREPARE_CACHE=%ENVVAR %DEFINE DTW_USE_DB2_PREPARE_CACHE=%ENVVAR %DEFINE DTW_VARIABLE_SCOPE=%ENVVAR %DEFINE EXEC_PATH=%ENVVAR %DEFINE FFI_PATH=%ENVVAR %DEFINE HTML_PATH=%ENVVAR %DEFINE INCLUDE_PATH=%ENVVAR %DEFINE LOCATION=%ENVVAR %DEFINE LOGIN=%ENVVAR %DEFINE MACRO_PATH=%ENVVAR %DEFINE NLIST=%ENVVAR %DEFINE Nn=%ENVVAR %DEFINE NULL_RPT_FIELD=%ENVVAR %DEFINE NUM_COLUMNS=%ENVVAR %DEFINE NUM_ROWS=%ENVVAR %DEFINE PASSWORD=%ENVVAR %DEFINE RETURN_CODE=%ENVVAR %DEFINE ROW_NUM=%ENVVAR %DEFINE SHOWSQL=%ENVVAR %DEFINE SQL_CODE=%ENVVAR %DEFINE SQL_STATE=%ENVVAR %DEFINE TOTAL_ROWS=%ENVVAR %DEFINE TRANSACTION_SCOPE=%ENVVAR @dtw_assign(DTW_TRACE_LOG_DIR, "/JPLHTTP/sqlQuery/") @dtw_assign(DTW_TRACE_LOG_LEVEL, "APPLICATION") @dtw_assign(DTW_ERROR_LOG_DIR, "/JPLHTTP/sqlQuery/") @dtw_assign(DTW_ERROR_LOG_LEVEL, "ALL") %MACRO_FUNCTION traceVars(){ <br> ALIGN = $(ALIGN) <br> DATABASE = $(DATABASE) <br> DB_CASE = $(DB_CASE) <br> DB2INSTANCE = $(DB2INSTANCE) <br> DB2MSGS = $(DB2MSGS) <br> DB2PLAN = $(DB2PLAN) <br> DB2PLAN = $(DB2PLAN) <br> DB2SSID = $(DB2SSID) <br> DB2SSID = $(DB2SSID) <br> DefaultDBCp = $(DefaultDBCp) <br> DSNAOINI = $(DSNAOINI) <br> DTW_APPLET_ALTTEXT = $(DTW_APPLET_ALTTEXT) <br> DTW_ATTACHMENT_PATH = $(DTW_ATTACHMENT_PATH) <br> DTW_CACHE_HOST = $(DTW_CACHE_HOST) <br> DTW_CACHE_MACRO = $(DTW_CACHE_MACRO) <br> DTW_CACHE_MANAGEMENT_INTERVAL = $(DTW_CACHE_MANAGEMENT_INTERVAL) <br> DTW_CACHE_PAGE = $(DTW_CACHE_PAGE) <br> DTW_CACHE_PORT = $(DTW_CACHE_PORT) <br> DTW_CM_PORT = $(DTW_CM_PORT) <br> DTW_COBOL_PARAMETER_BUFFER_SIZE = $(DTW_COBOL_PARAMETER_BUFFER_SIZE) <br> DTW_COBOL_PARAMETER_BUFFER_SIZE = $(DTW_COBOL_PARAMETER_BUFFER_SIZE) <br> DTW_CURRENT_FILENAME = $(DTW_CURRENT_FILENAME) <br> DTW_CURRENT_LAST_MODIFIED = $(DTW_CURRENT_LAST_MODIFIED) <br> DTW_DEFAULT_ERROR_MESSAGE = $(DTW_DEFAULT_ERROR_MESSAGE) <br> DTW_DEFAULT_MACRO = $(DTW_DEFAULT_MACRO) <br> DTW_DEFAULT_MESSAGE = $(DTW_DEFAULT_MESSAGE) <br> DTW_DEFAULT_REPORT = $(DTW_DEFAULT_REPORT) <br> DTW_DIRECT_REQUEST = $(DTW_DIRECT_REQUEST) <br> DTW_DO_NOT_CACHE_MACRO = $(DTW_DO_NOT_CACHE_MACRO) <br> DTW_EDIT_CODES = $(DTW_EDIT_CODES) <br> DTW_ERROR_LOG_DIR = $(DTW_ERROR_LOG_DIR) <br> DTW_ERROR_LOG_LEVEL = $(DTW_ERROR_LOG_LEVEL) <br> DTW_HTML_TABLE = $(DTW_HTML_TABLE) <br> DTW_INST_DIR = $(DTW_INST_DIR) <br> DTW_LOB_DIR = $(DTW_LOB_DIR) <br> DTW_LOB_LIFETIME = $(DTW_LOB_LIFETIME) <br> DTW_LOG_DIR = $(DTW_LOG_DIR) <br> DTW_LOG_LEVEL = $(DTW_LOG_LEVEL) <br> DTW_LOG_LEVEL = $(DTW_LOG_LEVEL) <br> DTW_MACRO_FILENAME = $(DTW_MACRO_FILENAME) <br> DTW_MACRO_LAST_MODIFIED = $(DTW_MACRO_LAST_MODIFIED) <br> DTW_MBMODE = $(DTW_MBMODE) <br> DTW_MBMODE = $(DTW_MBMODE) <br> DTW_MP_PATH = $(DTW_MP_PATH) <br> DTW_MP_VERSION = $(DTW_MP_VERSION) <br> DTW_OUTPUT_DIR = $(DTW_OUTPUT_DIR) <br> DTW_PAD_PGM_PARMS = $(DTW_PAD_PGM_PARMS) <br> DTW_PRINT_HEADER = $(DTW_PRINT_HEADER) <br> DTW_REMOVE_WS = $(DTW_REMOVE_WS) <br> DTW_REMOVE_WS = $(DTW_REMOVE_WS) <br> DTW_SAVE_TABLE_IN = $(DTW_SAVE_TABLE_IN) <br> DTW_SET_TOTAL_ROWS = $(DTW_SET_TOTAL_ROWS) <br> DTW_SHOWSQL = $(DTW_SHOWSQL) <br> DTW_SMTP_CHARSET = $(DTW_SMTP_CHARSET) <br> DTW_SMTP_SERVER = $(DTW_SMTP_SERVER) <br> DTW_SQL_ISOLATION = $(DTW_SQL_ISOLATION) <br> DTW_SQL_NAMING_MODE = $(DTW_SQL_NAMING_MODE) <br> DTW_STORE_PAGE = $(DTW_STORE_PAGE) <br> DTW_TRACE_LOG_DIR = $(DTW_TRACE_LOG_DIR) <br> DTW_TRACE_LOG_LEVEL = $(DTW_TRACE_LOG_LEVEL) <br> DTW_UNICODE = $(DTW_UNICODE) <br> DTW_UPLOAD_DIR = $(DTW_UPLOAD_DIR) <br> DTW_USE_DB2_PREPARE_CACHE = $(DTW_USE_DB2_PREPARE_CACHE) <br> DTW_USE_DB2_PREPARE_CACHE = $(DTW_USE_DB2_PREPARE_CACHE) <br> DTW_VARIABLE_SCOPE = $(DTW_VARIABLE_SCOPE) <br> EXEC_PATH = $(EXEC_PATH) <br> FFI_PATH = $(FFI_PATH) <br> HTML_PATH = $(HTML_PATH) <br> INCLUDE_PATH = $(INCLUDE_PATH) <br> LOCATION = $(LOCATION) <br> LOGIN = $(LOGIN) <br> MACRO_PATH = $(MACRO_PATH) <br> NLIST = $(NLIST) <br> Nn = $(Nn) <br> NULL_RPT_FIELD = $(NULL_RPT_FIELD) <br> NUM_COLUMNS = $(NUM_COLUMNS) <br> NUM_ROWS = $(NUM_ROWS) <br> PASSWORD = $(PASSWORD) <br> RETURN_CODE = $(RETURN_CODE) <br> ROW_NUM = $(ROW_NUM) <br> RPT_MAX_ROWS = $(RPT_MAX_ROWS) <br> SHOWSQL = $(SHOWSQL) <br> SQL_CODE = $(SQL_CODE) <br> SQL_STATE = $(SQL_STATE) <br> START_ROW_NUM = $(START_ROW_NUM) <br> TOTAL_ROWS = $(TOTAL_ROWS) <br> TRANSACTION_SCOPE = $(TRANSACTION_SCOPE) <pre> @qshset() </pre>
%}%{*=----------------------------------------------------------------= *%}%{*=----------------------------------------------------------------=*%}
%FUNCTION(DTW_SQL) LireXml (IN SFW) { $(SFW) %REPORT { %ROW { <ROW> %IF ($(N1) != "") <$(N1)>@DTW_rHTMLENCODE($(V1))</$(N1)> %ENDIF %IF ($(N2) != "") <$(N2)>@DTW_rHTMLENCODE($(V2))</$(N2)> %ENDIF %IF ($(N3) != "") <$(N3)>@DTW_rHTMLENCODE($(V3))</$(N3)> %ENDIF %IF ($(N4) != "") <$(N4)>@DTW_rHTMLENCODE($(V4))</$(N4)> %ENDIF %IF ($(N5) != "") <$(N5)>@DTW_rHTMLENCODE($(V5))</$(N5)> %ENDIF %IF ($(N6) != "") <$(N6)>@DTW_rHTMLENCODE($(V6))</$(N6)> %ENDIF %IF ($(N7) != "") <$(N7)>@DTW_rHTMLENCODE($(V7))</$(N7)> %ENDIF %IF ($(N8) != "") <$(N8)>@DTW_rHTMLENCODE($(V8))</$(N8)> %ENDIF %IF ($(N9) != "") <$(N9)>@DTW_rHTMLENCODE($(V9))</$(N9)> %ENDIF %IF ($(N10) != "") <$(N10)>@DTW_rHTMLENCODE($(V10))</$(N10)> %ENDIF %IF ($(N11) != "") <$(N11)>@DTW_rHTMLENCODE($(V11))</$(N11)> %ENDIF %IF ($(N12) != "") <$(N12)>@DTW_rHTMLENCODE($(V12))</$(N12)> %ENDIF %IF ($(N13) != "") <$(N13)>@DTW_rHTMLENCODE($(V13))</$(N13)> %ENDIF %IF ($(N14) != "") <$(N14)>@DTW_rHTMLENCODE($(V14))</$(N14)> %ENDIF %IF ($(N15) != "") <$(N15)>@DTW_rHTMLENCODE($(V15))</$(N15)> %ENDIF %IF ($(N16) != "") <$(N16)>@DTW_rHTMLENCODE($(V16))</$(N16)> %ENDIF %IF ($(N17) != "") <$(N17)>@DTW_rHTMLENCODE($(V17))</$(N17)> %ENDIF %IF ($(N18) != "") <$(N18)>@DTW_rHTMLENCODE($(V18))</$(N18)> %ENDIF %IF ($(N19) != "") <$(N19)>@DTW_rHTMLENCODE($(V19))</$(N19)> %ENDIF %IF ($(N20) != "") <$(N20)>@DTW_rHTMLENCODE($(V20))</$(N20)> %ENDIF %IF ($(N21) != "") <$(N21)>@DTW_rHTMLENCODE($(V21))</$(N21)> %ENDIF %IF ($(N22) != "") <$(N22)>@DTW_rHTMLENCODE($(V22))</$(N22)> %ENDIF %IF ($(N23) != "") <$(N23)>@DTW_rHTMLENCODE($(V23))</$(N23)> %ENDIF %IF ($(N24) != "") <$(N24)>@DTW_rHTMLENCODE($(V24))</$(N24)> %ENDIF %IF ($(N25) != "") <$(N25)>@DTW_rHTMLENCODE($(V25))</$(N25)> %ENDIF %IF ($(N26) != "") <$(N26)>@DTW_rHTMLENCODE($(V26))</$(N26)> %ENDIF %IF ($(N27) != "") <$(N27)>@DTW_rHTMLENCODE($(V27))</$(N27)> %ENDIF %IF ($(N28) != "") <$(N28)>@DTW_rHTMLENCODE($(V28))</$(N28)> %ENDIF %IF ($(N29) != "") <$(N29)>@DTW_rHTMLENCODE($(V29))</$(N29)> %ENDIF %IF ($(N30) != "") <$(N30)>@DTW_rHTMLENCODE($(V30))</$(N30)> %ENDIF %IF ($(N31) != "") <$(N31)>@DTW_rHTMLENCODE($(V31))</$(N31)> %ENDIF %IF ($(N32) != "") <$(N32)>@DTW_rHTMLENCODE($(V32))</$(N32)> %ENDIF %IF ($(N33) != "") <$(N33)>@DTW_rHTMLENCODE($(V33))</$(N33)> %ENDIF %IF ($(N34) != "") <$(N34)>@DTW_rHTMLENCODE($(V34))</$(N34)> %ENDIF %IF ($(N35) != "") <$(N35)>@DTW_rHTMLENCODE($(V35))</$(N35)> %ENDIF %IF ($(N36) != "") <$(N36)>@DTW_rHTMLENCODE($(V36))</$(N36)> %ENDIF %IF ($(N37) != "") <$(N37)>@DTW_rHTMLENCODE($(V37))</$(N37)> %ENDIF %IF ($(N38) != "") <$(N38)>@DTW_rHTMLENCODE($(V38))</$(N38)> %ENDIF %IF ($(N39) != "") <$(N39)>@DTW_rHTMLENCODE($(V39))</$(N39)> %ENDIF %IF ($(N40) != "") <$(N40)>@DTW_rHTMLENCODE($(V40))</$(N40)> %ENDIF </ROW> %} %} %MESSAGE { -204: {<ERROR number="$(RETURN_CODE)" text="Table not found"/>%} :continue -104: {<ERROR number="$(RETURN_CODE)" text="Syntax error"/>%} :continue 100: {<ERROR number="$(RETURN_CODE)" text="Sélection vide"/>%} :continue +default: {<ERROR number="$(RETURN_CODE)" text="Warning $(RETURN_CODE)"/>%}: continue -default: {<ERROR number="$(RETURN_CODE)" text= "Unexpected SQLerror $(RETURN_CODE)"/>%} : continue %}
%}%{*=----------------------------------------------------------------=*%} %FUNCTION(DTW_SQL) LireHtml (IN SFW) { $(SFW) %REPORT { <TABLE border=3><TBODY><TR><TD colspan=99>@DTW_rHTMLENCODE($(SQL))</TD></TR> <tr><td>@DTW_rHTMLENCODE($(N1))</td> %IF ($(N2) != "") <Td>@DTW_rHTMLENCODE($(N2))</Td> %ENDIF %IF ($(N3) != "") <Td>@DTW_rHTMLENCODE($(N3))</Td> %ENDIF %IF ($(N4) != "") <Td>@DTW_rHTMLENCODE($(N4))</Td> %ENDIF %IF ($(N5) != "") <Td>@DTW_rHTMLENCODE($(N5))</Td> %ENDIF %IF ($(N6) != "") <Td>@DTW_rHTMLENCODE($(N6))</Td> %ENDIF %IF ($(N7) != "") <Td>@DTW_rHTMLENCODE($(N7))</Td> %ENDIF %IF ($(N8) != "") <Td>@DTW_rHTMLENCODE($(N8))</Td> %ENDIF %IF ($(N9) != "") <Td>@DTW_rHTMLENCODE($(N9))</Td> %ENDIF %IF ($(N10) != "") <Td>@DTW_rHTMLENCODE($(N10))</Td> %ENDIF %IF ($(N11) != "") <Td>@DTW_rHTMLENCODE($(N11))</Td> %ENDIF %IF ($(N12) != "") <Td>@DTW_rHTMLENCODE($(N12))</Td> %ENDIF %IF ($(N13) != "") <Td>@DTW_rHTMLENCODE($(N13))</Td> %ENDIF %IF ($(N14) != "") <Td>@DTW_rHTMLENCODE($(N14))</Td> %ENDIF %IF ($(N15) != "") <Td>@DTW_rHTMLENCODE($(N15))</Td> %ENDIF %IF ($(N16) != "") <Td>@DTW_rHTMLENCODE($(N16))</Td> %ENDIF %IF ($(N17) != "") <Td>@DTW_rHTMLENCODE($(N17))</Td> %ENDIF %IF ($(N18) != "") <Td>@DTW_rHTMLENCODE($(N18))</Td> %ENDIF %IF ($(N19) != "") <Td>@DTW_rHTMLENCODE($(N19))</Td> %ENDIF %IF ($(N20) != "") <Td>@DTW_rHTMLENCODE($(N20))</> %ENDIF %IF ($(N21) != "") <Td>@DTW_rHTMLENCODE($(N21))</Td> %ENDIF %IF ($(N22) != "") <Td>@DTW_rHTMLENCODE($(N22))</Td> %ENDIF %IF ($(N23) != "") <Td>@DTW_rHTMLENCODE($(N23))</Td> %ENDIF %IF ($(N24) != "") <Td>@DTW_rHTMLENCODE($(N24))</Td> %ENDIF %IF ($(N25) != "") <Td>@DTW_rHTMLENCODE($(N25))</Td> %ENDIF %IF ($(N26) != "") <Td>@DTW_rHTMLENCODE($(N26))</Td> %ENDIF %IF ($(N27) != "") <Td>@DTW_rHTMLENCODE($(N27))</Td> %ENDIF %IF ($(N28) != "") <Td>@DTW_rHTMLENCODE($(N28))</Td> %ENDIF %IF ($(N29) != "") <Td>@DTW_rHTMLENCODE($(N29))</Td> %ENDIF %IF ($(N30) != "") <Td>@DTW_rHTMLENCODE($(N30))</Td> %ENDIF %IF ($(N31) != "") <Td>@DTW_rHTMLENCODE($(N31))</Td> %ENDIF %IF ($(N32) != "") <Td>@DTW_rHTMLENCODE($(N32))</Td> %ENDIF %IF ($(N33) != "") <Td>@DTW_rHTMLENCODE($(N33))</Td> %ENDIF %IF ($(N34) != "") <Td>@DTW_rHTMLENCODE($(N34))</Td> %ENDIF %IF ($(N35) != "") <Td>@DTW_rHTMLENCODE($(N35))</Td> %ENDIF %IF ($(N36) != "") <Td>@DTW_rHTMLENCODE($(N36))</Td> %ENDIF %IF ($(N37) != "") <Td>@DTW_rHTMLENCODE($(N37))</Td> %ENDIF %IF ($(N38) != "") <Td>@DTW_rHTMLENCODE($(N38))</Td> %ENDIF %IF ($(N39) != "") <Td>@DTW_rHTMLENCODE($(N39))</Td> %ENDIF %IF ($(N40) != "") <Td>@DTW_rHTMLENCODE($(N40))</Td> %ENDIF </tr> %ROW { <tr> %IF ($(N1) != "") <td>@DTW_rHTMLENCODE($(V1))</td> %ENDIF %IF ($(N2) != "") <Td>@DTW_rHTMLENCODE($(V2))</Td> %ENDIF %IF ($(N3) != "") <Td>@DTW_rHTMLENCODE($(V3))</Td> %ENDIF %IF ($(N4) != "") <Td>@DTW_rHTMLENCODE($(V4))</Td> %ENDIF %IF ($(N5) != "") <Td>@DTW_rHTMLENCODE($(V5))</Td> %ENDIF %IF ($(N6) != "") <Td>@DTW_rHTMLENCODE($(V6))</Td> %ENDIF %IF ($(N7) != "") <Td>@DTW_rHTMLENCODE($(V7))</Td> %ENDIF %IF ($(N8) != "") <Td>@DTW_rHTMLENCODE($(V8))</Td> %ENDIF %IF ($(N9) != "") <Td>@DTW_rHTMLENCODE($(V9))</Td> %ENDIF %IF ($(N10) != "") <Td>@DTW_rHTMLENCODE($(V10))</Td> %ENDIF %IF ($(N11) != "") <Td>@DTW_rHTMLENCODE($(V11))</Td> %ENDIF %IF ($(N12) != "") <Td>@DTW_rHTMLENCODE($(V12))</Td> %ENDIF %IF ($(N13) != "") <Td>@DTW_rHTMLENCODE($(V13))</Td> %ENDIF %IF ($(N14) != "") <Td>@DTW_rHTMLENCODE($(V14))</Td> %ENDIF %IF ($(N15) != "") <Td>@DTW_rHTMLENCODE($(V15))</Td> %ENDIF %IF ($(N16) != "") <Td>@DTW_rHTMLENCODE($(V16))</Td> %ENDIF %IF ($(N17) != "") <Td>@DTW_rHTMLENCODE($(V17))</Td> %ENDIF %IF ($(N18) != "") <Td>@DTW_rHTMLENCODE($(V18))</Td> %ENDIF %IF ($(N19) != "") <Td>@DTW_rHTMLENCODE($(V19))</Td> %ENDIF %IF ($(N20) != "") <Td>@DTW_rHTMLENCODE($(V20))</> %ENDIF %IF ($(N21) != "") <Td>@DTW_rHTMLENCODE($(V21))</Td> %ENDIF %IF ($(N22) != "") <Td>@DTW_rHTMLENCODE($(V22))</Td> %ENDIF %IF ($(N23) != "") <Td>@DTW_rHTMLENCODE($(V23))</Td> %ENDIF %IF ($(N24) != "") <Td>@DTW_rHTMLENCODE($(V24))</Td> %ENDIF %IF ($(N25) != "") <Td>@DTW_rHTMLENCODE($(V25))</Td> %ENDIF %IF ($(N26) != "") <Td>@DTW_rHTMLENCODE($(V26))</Td> %ENDIF %IF ($(N27) != "") <Td>@DTW_rHTMLENCODE($(V27))</Td> %ENDIF %IF ($(N28) != "") <Td>@DTW_rHTMLENCODE($(V28))</Td> %ENDIF %IF ($(N29) != "") <Td>@DTW_rHTMLENCODE($(V29))</Td> %ENDIF %IF ($(N30) != "") <Td>@DTW_rHTMLENCODE($(V30))</Td> %ENDIF %IF ($(N31) != "") <Td>@DTW_rHTMLENCODE($(V31))</Td> %ENDIF %IF ($(N32) != "") <Td>@DTW_rHTMLENCODE($(V32))</Td> %ENDIF %IF ($(N33) != "") <Td>@DTW_rHTMLENCODE($(V33))</Td> %ENDIF %IF ($(N34) != "") <Td>@DTW_rHTMLENCODE($(V34))</Td> %ENDIF %IF ($(N35) != "") <Td>@DTW_rHTMLENCODE($(V35))</Td> %ENDIF %IF ($(N36) != "") <Td>@DTW_rHTMLENCODE($(V36))</Td> %ENDIF %IF ($(N37) != "") <Td>@DTW_rHTMLENCODE($(V37))</Td> %ENDIF %IF ($(N38) != "") <Td>@DTW_rHTMLENCODE($(V38))</Td> %ENDIF %IF ($(N39) != "") <Td>@DTW_rHTMLENCODE($(V39))</Td> %ENDIF %IF ($(N40) != "") <Td>@DTW_rHTMLENCODE($(V40))</Td> %ENDIF </tr> %} </TBODY></TABLE> %}
%}%{*=----------------------------------------------------------------= *%}%{*=----------------------------------------------------------------= *%}%{*=----------------------------------------------------------------=
*%}
%{********************************************************
*******************************************************%}
%{******************************************************** The functions seen below call the SQL functions above. The first function below calls SQL1, the second function below calls
SQL2.
*******************************************************%}%{*=----------------------------------------------------------------= *%} %FUNCTION(DTW_SYSTEM) qshset () { %EXEC { /QSYS.LIB/JPLHTTP.LIB/QSHSET.PGM %} %}%{*=----------------------------------------------------------------=
*%}%{*=----------------------------------------------------------------=*%} %HTML(xml) { <?xml version="1.0" encoding="iso-8859-15"?> <SQL> @dtw_assign(SQL, "select $(SQL)") <SELECT>@DTW_rHTMLENCODE($(SQL))</SELECT> @DTW_SUBSTR(REMOTE_ADDR, "1", "12", CallerIP) %IF (CallerIP != "111.111.111." && CallerIP != "111.111.222.") <ERROR> REMOTE_ADDR $(REMOTE_ADDR) is not allowed to call $(SERVER_NAME):$(SERVER_PORT) </ERROR> %ELSE @dtw_assign(SFW, $(SQL)) @LireXml ($(SFW)) %ENDIF </SQL> %}%{*=----------------------------------------------------------------= *%}%{*=----------------------------------------------------------------= *%} %HTML(html) { @dtw_assign(SQL, "select $(SQL)") <html><body> @dtw_assign(SFW, $(SQL)) @LireHtml ($(SFW)) </BODY></HTML> %}%{*=----------------------------------------------------------------=*%}
|
Note : in the XML entry point, there is an IP filter
hardcoded. The SQL
update is protected with insertion of "select " at the beginning of
the request. There is an other egg : the function tracevars that dumps
all
variables : the net.data set and the shell set. (correctly implemented
in source
code at top of this page)
And, last but not least, the method
to apply an XSL style sheet to an XML result in a web page |
If your purpose is to transform an XML document into a
complete web page, then you
have just to reference the XSL style sheet in the XML doc. It's enough
to the
browser to transform the XML with the XSL.
sample : an xml document : QuestionnerXml.xml
you can see data, efficient but not pretty. Add a reference to
this XSL : QuestionnerXml.xsl
the updated XML has only one line more (in the .xml sample it
is hidden in a
comment) : <?xml-stylesheet
type="text/xsl" href="QuestionnerXml.xsl" ?>
the page shown by the browser : QuestionnerXml.htm
But if the xml doc is just an extract that you have to use to
update a part of
your html page, you can use this method XslDebugger.htm
click the button, and you can see this page : XslDebuggerQuerySQL.htm.
Important note : you are always on the same page. it's not a
dynamic web,
it's a dynamic page.
These pages uses pre-resolved queries as shown before. Don't
forget that a
XMLDOM calls an URL, whitch here is replaced by a file. This sample
shows
querying an url : Query SQL..
Don't click the
buttons, look at source code.
This tool is a free source text; you can redistribute it
and/or modify it as much as you
need. Just add as comment in the code a reference to this site : http://jplamontre.free.fr/jpltools.htm
... and send me an email Contact
with "jpltools" in the subject (it's for my antispam rules). I will be
happy to know what you have done with the jpltools. This tool is
distributed in the hope that it will be useful, but WITHOUT ANY
WARRANTY :
THIS TOOLBOX IS PROVIDED ''AS IS'' AND ANY EXPRESSED OR IMPLIED
WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN
NO EVENT SHALL THE PROVIDER OR ITS CONTRIBUTORS BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
IN ANY WAY OUT OF THE USE OF THIS TOOLBOX, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.