EASY400 WEBSQL

by Antoon van Os

WebSql

SQL power to Db2XTools

Public Source DB2 utility for IBM System i

About it

WebSql is a public source utility written in ILE-RPG and ILE-CL, originated by Antoon van Os, Belgium.

WEBSQL allows to

define and run from a WEB browser an SQL query over System i database files
display the result of the query in XML, HTML, CSV or TXT format through the WEB browser itself.

WEBSQL is based on the famous Db2XTools utility, by the same author.

Prerequisites

OS/400 Release 5.2 or subsequent
System library QIWS (57xxSS1 option 12 - Host Services)
System library QSQL (Product number 57xxST1 option *BASE - DB2 Query Manager and SQL Development Kit)
System library QRPGLE (Product number 57xxWDS option 31 - Compiler - ILE RPG IV)
DB2XTOOLS public source free utility, download it from the Easy400.net site.

Download

To download this utility, just press the "Download now" button.
Though everything is free, you must register.

Installation

unzip PC file websql.zip
transmit (binary) PC file websql.savf
to IBM System i save file qgpl/websql
rstlib lib(websql) dev(*savf) savf(qgpl/websql)
strrexprc srcmbr(install) srcfile(websql/qrexsrc)

HTTP instance

WEBSQL must be invoked through an URL, like http://.../websql, which must be made available in a local HTTP instance.
You have two choices for activating this URL:

If you are NOT familiar with HTTP directives, you may easily create an HTTP istance to run WebSql from a given HTTP port.
Just type command dspf '/websql/conf/httpd.conf' and read the instructions.

If you ARE familiar with HTTP directives, add the following directives to an HTTP instance of your choice:

#---- DB2XTOOLS directives ----------------------------------
ScriptAliasMatch /db2xtoolsp/(.*)  /qsys.lib/db2xtools.lib/$1       
ScriptAliasMatch ^/db2xtools$  /qsys.lib/db2xtools.lib/db2xtools.pgm
Alias /db2xtools/ /db2xtools/
<Directory /db2xtools>
   Options None
   order allow,deny
   allow from all
</Directory>
<Directory /qsys.lib/db2xtools.lib>
   AllowOverride None
   order allow,deny
   allow from all
   Options -ExecCGI
   CGIConvMode %%EBCDIC/EBCDIC%%
</Directory>
<LocationMatch (^/db2xtoolsp/(.*)$|^/db2xtools$)>
   AuthType Basic
   AuthName "DB2XTOOLS utility"
   PasswdFile %%SYSTEM%%
   UserID %%CLIENT%%
   Require valid-user                            
</LocationMatch>
#----  WEBSQL directives -----------------------------------
ScriptAliasMatch /websqlp/(.*)  /qsys.lib/websql.lib/$1
ScriptAliasMatch ^/websql$  /qsys.lib/websql.lib/websql1.pgm
Alias /websql/html/ /websql/html/
<Directory /websql>
   Options None
   order allow,deny
   allow from all
</Directory>
<Directory /qsys.lib/websql.lib>
   AllowOverride None
   order allow,deny
   allow from all
   Options -ExecCGI
   CGIConvMode %%EBCDIC/EBCDIC%%
</Directory>
<LocationMatch (^/websqlp/(.*)$|^/websql$)>
   AuthType Basic
   AuthName "WEBSQL utility"
   PasswdFile %%SYSTEM%%
   UserID %%CLIENT%%
   Require valid-user
</LocationMatch>

Should you need to allow access only to a limited number of users, instead of the whole user profiles set, you may use a validation list. See this page.

Operating WebSql

In the location line of your WEB browser enter the following:

http://IP_address : port_number/websql

where

IP_address is the IP address of your System i
port_number is the number of the port where your HTTP instance is listening (if you are using the pre-defined HTTP instance configured in /websql/conf/httpd.conf, the port number is 8033).

More ...