DbPages version 1.0.5-10

Page generated by DBPages System on Microsoft SQLServer writed in Transact SQL

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

Short instruction

Into Apache Tomcat 6.x lib folder put below jars:
- sqljdbc4.jar (3.0 or compatible)
- activation.jar
- commons-fileupload-1.2.1.jar
- commons-io-1.4.jar
- mail.jar
Create any database in Microsoft SQLServer
Call MSSQLSERVER.SQL script on created database.
Into Apache Tomcat 6.x webapps\%project%\WEB-INF\classes folder put all classes from dbpages.zip\build\classes
Configure files config.properties, context.xml and web.xml explained below.

SYSTEM VARIABLES:

REMOTE_ADDR = 127.0.0.1
REMOTE_USER =
SERVER_NAME = localhost
SERVER_PORT = 8080
SCRIPT_NAME = /sqlserver
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 = 4874D8A2B9CB7C8D3E4C9D75D6F89527
DBPAGES = 1.0.4-07

Upload file to HTTP_DOCS table

Structure of HTTP_DOCS table

CREATE TABLE HTTP_DOCS (
NAME NVARCHAR(256) NOT NULL,
MIME_TYPE NVARCHAR(128),
DOC_SIZE NUMERIC,
LAST_UPDATED DATETIME,
CONTENT VARBINARY(MAX)
)
/
ALTER TABLE HTTP_DOCS ADD CONSTRAINT HTTP_DOCS_PK
PRIMARY KEY (NAME)
/

Download files from HTTP_DOCS table

NameMime typeSizeUpdatedActionUpdate name

Structure of HTTP_MAIL table

CREATE TABLE HTTP_MAIL(
NMAL NUMBER NOT NULL,
RECIPIENT NVARCHAR(4000) NOT NULL,
MESSAGE NVARCHAR(MAX),
SUBJECT NVARCHAR(1000),
CREATED DATETIME DEFAULT (getdate()),
UPDATED DATETIME,
STATE NVARCHAR(1) DEFAULT 'N' NOT NULL,
CONSTRAINT HTTP_MAIL_PK PRIMARY KEY (NMAL)
)
/
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
# beforePage - procedure fired before generating page
beforePage =
# afterPage - procedure fired after generating page
afterPage =
# 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="user"
username="password"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:jdbc:sqlserver://localhost\sqlexpress;database=dbpages"
/>
</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>

Charset Test

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

HTTP Schema

TypeName
PROCEDUREclear
PROCEDUREdelete_file
FUNCTIONf_anchor
FUNCTIONf_bodyClose
FUNCTIONf_bodyOpen
FUNCTIONf_formCheckbox
FUNCTIONf_formClose
FUNCTIONf_formfile
FUNCTIONf_formHidden
FUNCTIONf_formOpen
FUNCTIONf_formPassword
FUNCTIONf_formRadio
FUNCTIONf_formReset
FUNCTIONf_formSelectClose
FUNCTIONf_formSelectOpen
FUNCTIONf_formSelectOption
FUNCTIONf_formSubmit
FUNCTIONf_formText
FUNCTIONf_formTextareaClose
FUNCTIONf_formTextareaOpen
FUNCTIONf_headClose
FUNCTIONf_headOpen
FUNCTIONf_htmlClose
FUNCTIONf_htmlOpen
FUNCTIONf_link
FUNCTIONf_meta
FUNCTIONf_title
PROCEDUREget_cgi_env
PROCEDUREgetpage
PROCEDUREp
PROCEDUREprint_cgi_env
PROCEDUREprocess_download
PROCEDUREprocess_upload
PROCEDUREredirect_url
PROCEDUREsend_mail
PROCEDUREsend_mail_unsended
PROCEDUREset_content_type
PROCEDUREsetssid