DbPages version 1.0.5-10

Page generated by DBPages System on Oracle Database writed in PL/SQL

Authors: Piotr Różnicki, Andrzej Kałuża

Short instruction

Into Apache Tomcat 6.x lib folder put below jars:
- ojdbc6.jar (or compatible)
- activation.jar
- commons-fileupload-1.2.1.jar
- commons-io-1.4.jar
- mail.jar
Create user DBPAGES with password DBPAGES in Oracle database
Call dbpages.zip/oracle/ORACLE.SQL script by oracle sqlplus on created user.
Into Apache Tomcat 6.x webapps folder put content of dbpages.zip/oracle/tomcat-webapps
Configure files config.properties, context.xml and web.xml explained below.

In web browser go to http://host:port/dbpages/http_help.home

DATABASE VARIABLES:

NLS_LANGUAGE = POLISH
NLS_TERRITORY = POLAND
NLS_CURRENCY = zł
NLS_ISO_CURRENCY = POLAND
NLS_NUMERIC_CHARACTERS = ,
NLS_CALENDAR = GREGORIAN
NLS_DATE_FORMAT = RR/MM/DD
NLS_DATE_LANGUAGE = POLISH
NLS_CHARACTERSET = EE8MSWIN1250
NLS_SORT = POLISH
NLS_TIME_FORMAT = HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT = RR/MM/DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT = HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT = RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY = zł
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_COMP = BINARY
NLS_LENGTH_SEMANTICS = BYTE
NLS_NCHAR_CONV_EXCP = FALSE

SYSTEM VARIABLES:

REMOTE_ADDR = 127.0.0.1
REMOTE_USER =
SERVER_NAME = localhost
SERVER_PORT = 8080
SCRIPT_NAME = /dbpages
PATH_INFO = /
QUERY_STRING =
HTTP_USER_AGENT = Opera/9.80 (Windows NT 5.1; U; pl) Presto/2.6.30 Version/10.63
REQUEST_IANA_CHARSET = utf-8
SESSION_ID = 342E34F686944C5193619A85D770C467
DBPAGES = 1.0.4-07

Upload file to HTTP_DOCS table

Structure of HTTP_DOCS table

CREATE TABLE HTTP_DOCS (
  NAME         VARCHAR2(256) NOT NULL,
  MIME_TYPE    VARCHAR2(128),
  DOC_SIZE     NUMBER,
  LAST_UPDATED DATE,
  CONTENT      BLOB
)
/
ALTER TABLE HTTP_DOCS ADD CONSTRAINT HTTP_DOCS_PK
  PRIMARY KEY (NAME)
/

Download files from HTTP_DOCS table

NameMime typeSizeUpdatedActionUpdate name
jquery.jsapplication/x-javascript7232810/06/30Delete

Structure of HTTP_MAIL table

CREATE TABLE HTTP_MAIL(  
  NMAL NUMBER NOT NULL ENABLE, 
  RECIPIENT VARCHAR2(4000) NOT NULL ENABLE, 
  MESSAGE CLOB, 
  SUBJECT VARCHAR2(1000), 
  CREATED TIMESTAMP DEFAULT sysdate, 
  UPDATED TIMESTAMP, 
  STATE VARCHAR2(1) DEFAULT 'N' NOT NULL ENABLE, 
   CONSTRAINT HTTP_MAIL_PK PRIMARY KEY (NMAL) ENABLE
   )
/
CREATE INDEX HTTP_MAIL_STATE_I ON HTTP_MAIL (STATE)
/

Send email



Config file (config.properties in %PROJECT_DIR%/WEB-INF/classes directory):

# Configuration file of DBPAGES System
#connection - DB connection name configured for Application Context
connection = db
#useCompress - compress pages (true/false)
useCompress = false
#useSecureConnection - allow only
useSecureConnection = false
# startup - startup prodecure
startup = http_help.home
# beforeSession - procedure fired before set session variables
beforeSession = 
# beforePage - procedure fired before generating page
beforePage = 
# afterPage - procedure fired after generating page
afterPage =
# afterSession - procedure fired after all operations width page
afterSession = http.reset
# allowedProcedures - list of procedures that user can run
# * means all procedures accesible to database user application is logged in.
allowedProcedures = *
# procedures for download binary files
downloadProcs = http.process_download
# procedures to upload binary files
uploadProcs = http.process_upload
#packageName - name of dbpages package or schema. For Oracle, Microsoft SQLServer database http, for MySQL leave empty.
packageName = http
#namedParams - set to true if DB engine uses named parameters (eg. Oracle, Microsoft SQL Server)
namedParams = true
#charset - charset for output
charset = utf-8
#smtpHost - hostname of smtp server
smtpHost = smtp.server.pl
#smtpUser - username of smtp server
smtpUser = user@server.pl
#smtpPasswd - password of smtp server
smtpPasswd = password
# email address for sending emails
smtpFrom = user@server.pl
# turno on/off debug mode, extended info put into STDOUT log
#debug=true

Apache Tomcat 6.x context file (context.xml in %PROJECT_DIR%/META-INF directory):

Resource name="db" is the connection parameter name from config file

<?xml version='1.0' encoding='utf-8'?>
<Context>
  <Resource 
    name="db" 
    scope="Shareable" 
    type="javax.sql.DataSource"
    password="dbpages"
    username="dbpages"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@localhost:1521:orcl"
  />
</Context>

Apache Tomcat 6.x access web file (web.xml in %PROJECT_DIR%/WEB-INF directory):

<?xml version="1.0" encoding="ISO-8859-1"?>

<!DOCTYPE web-app
    PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
    "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>

  <servlet>
    <servlet-name>DBPagesHttpServlet</servlet-name>
    <servlet-class>DBPagesHttpServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
  </servlet>

  <servlet>
    <servlet-name>others</servlet-name>
    <servlet-class>
      org.apache.catalina.servlets.DefaultServlet
    </servlet-class>
    <init-param>
      <param-name>debug</param-name>
      <param-value>0</param-value>
    </init-param>
    <init-param>
      <param-name>listings</param-name>
      <param-value>false</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>

  <servlet-mapping>
    <servlet-name>DBPagesHttpServlet</servlet-name>
    <url-pattern>/</url-pattern>
  </servlet-mapping>

  <servlet-mapping>
    <servlet-name>others</servlet-name>
    <url-pattern>*.gif</url-pattern>
  </servlet-mapping>

  <servlet-mapping>
    <servlet-name>others</servlet-name>
    <url-pattern>*.jpg</url-pattern>
  </servlet-mapping>

  <servlet-mapping>
    <servlet-name>others</servlet-name>
    <url-pattern>*.png</url-pattern>
  </servlet-mapping>

  <servlet-mapping>
    <servlet-name>others</servlet-name>
    <url-pattern>*.css</url-pattern>
  </servlet-mapping>

  <servlet-mapping>
    <servlet-name>others</servlet-name>
    <url-pattern>*.html</url-pattern>
  </servlet-mapping>

  <error-page>
    <error-code>500</error-code>
    <location>/docs/error/500.html</location>
  </error-page>

</web-app>

Simple CreateReadUpdateDelete (CRUD) Technique - Create Interface

Simple CreateReadUpdateDelete (CRUD) Technique - Edit Table

Charset Test

Polish chars: ąęćłń󿟥ĘĆŁŃÓŻŹ

HTTP Package

TypeNameOverload
PROCEDUREANCHOR1
FUNCTIONANCHOR2
PROCEDUREBODYCLOSE 
PROCEDUREBODYOPEN 
PROCEDUREBUTTON1
FUNCTIONBUTTON2
PROCEDURECLEAR 
PROCEDUREDELETE_FILE 
PROCEDUREFORMCHECKBOX 
PROCEDUREFORMCLOSE 
PROCEDUREFORMFILE 
PROCEDUREFORMHIDDEN 
PROCEDUREFORMOPEN 
PROCEDUREFORMPASSWORD1
FUNCTIONFORMPASSWORD2
FUNCTIONFORMRADIO1
PROCEDUREFORMRADIO2
FUNCTIONFORMRESET1
PROCEDUREFORMRESET2
PROCEDUREFORMSELECTCLOSE 
PROCEDUREFORMSELECTOPEN 
PROCEDUREFORMSELECTOPTION 
FUNCTIONFORMSUBMIT1
PROCEDUREFORMSUBMIT2
PROCEDUREFORMTEXT1
FUNCTIONFORMTEXT2
PROCEDUREFORMTEXTAREA 
PROCEDUREFORMTEXTAREACLOSE 
PROCEDUREFORMTEXTAREAOPEN 
FUNCTIONGET_CGI_ENV 
PROCEDUREGETPAGE 
FUNCTIONGET_SSID 
PROCEDUREHEADCLOSE 
PROCEDUREHEADOPEN 
PROCEDUREHTMLCLOSE 
PROCEDUREHTMLOPEN 
PROCEDUREHTTP_DOCS_UPDATE 
PROCEDURELINK 
PROCEDUREMETA 
PROCEDUREP1
PROCEDUREP2
PROCEDUREPRINT1
PROCEDUREPRINT2
PROCEDUREPRINT_CGI_ENV 
PROCEDUREPROCESS_DOWNLOAD 
PROCEDUREPROCESS_UPLOAD 
PROCEDUREREDIRECT_URL 
PROCEDURERESET 
FUNCTIONSEND_MAIL 
PROCEDURESEND_MAIL_UNSENDED 
PROCEDURESET_CONTENT_TYPE 
PROCEDURESETSSID 
PROCEDURETITLE