
  JDBWC - Installation

Document Revision: 1

Release Information
Project Name 	JDBWC
Project Type 	Type 3 Java JDBC Driver
Version 	1.0.0-4beta (bugfixes by ngeyer)
License 	GNU GPLv3 http://www.gnu.org/licenses/
Web 	http://sourceforge.net/projects/jdbwc
Support 	https://sourceforge.net/projects/jdbwc/support

Contents

    * Introduction <#intro>
    * Requirements <#require>
    * Installation <#install>
    * Basic Use <#use>
    * Connection URL in detail <#params>
    * What's new? <#new>
    * Limitations <#limits>
    * TODO <#todo>
    * Known issues <#issues>
    * Reporting Bugs <#bugs>

Introduction

This document requires you have some knowledge of web-servers and the
Java programming language (and ideally JDBC). Its beyond the scope of
this document to explain these subjects in great detail.

Throughout this document there are some descriptive terms that may be
unclear. Their meanings are clarified below.

*Client Side: *
the Java JDBC part of the JDBWC Driver.
Also referred to as _JDBC_ (/Java DataBase Connector/).

*Server-Side: *
the PHP side (/with a web-server and one or more database-server/s/).
Also referred to as _WC_ (/Web Connector/).

*Upstream: *
data going from the Client-side to the Server-side.

*Downstream: *
data going from the Server-side to the Client-side.


    Description

JDBWC is an acronym for Java-Data-Base-Web-Connector. In essence, JDBWC
is a Java JDBC Driver with its own server written in PHP. Ideally suited
to Java Desktop applications that require JDBC access across wide area
networks like the internet but do not wish to expose the remote
database-server through the remote hosts firewall. EG: the database
server does not need to be exposed to the internet at all.

Requirements

*Client-side:*
Java JRE >= 1.6.0_19 (/you can also compile from source to suit your
target JRE/)

*Server-side: *
Apache-Web-server >= 1.3.41
PHP >= 5.1.x with MySQL >= 5.x.x and/or PostgreSQL >= 7.4.x

If your using MySQL you should enable the PHP mysqli extension for full
metadata functionality.
The existence of the mysqli extension is determined on the server-side.

*Supplied dependencies (licensed under an extended Apache-2 license)*
HttpClient 4.0.1 (GA) <http://hc.apache.org/index.html> + dependencies

    * apache-mime4j-0.6.jar
    * commons-codec-1.3.jar
    * commons-logging-1.1.1.jar
    * httpclient-4.0.1.jar
    * httpcore-4.0.1.jar
    * httpmime-4.0.1.jar

Installation


    Client-side (JDBC)

   1. Unpack the compressed download if you got it from the releases
      area <https://sourceforge.net/projects/jdbwc/files/>.
      The latest version should also be on the SourceForge SVN server
      <https://sourceforge.net/projects/jdbwc/develop> (/as of 2010-04-18/).
   2. Copy the "|jdbwc-lib|" folder containing "|jdbwc.jar|" and
      dependencies to a location relative to your Java application files.
          * add "jdbwc.jar" to your applications classpath. If your app
            is an executable jar, you would usually include the file in
            your jar manifest's class-path using a relative path
            EG: |Class-Path: jdbwc-lib/jdbwc.jar or Class-Path:
            jdbwc.jar| depending on where you put the jar files in
            relation to your java app.
   3. If your app is not a packaged jar, you will need to organise
      updating the classpath via some other mechanism.
   4. If your using an IDE, add the jars to your build path as user libs
      (/system libs are ok too but not necessary/).


      Notes:

IDE's usually require all of the jars in the build path as do SQL
Clients (like SQuirreL, SQLWorkBench, DBVisualizer, etc.).
For apps, the required jdbwc dependencies will be added to your
classpath by jdbwc.jar

------------------------------------------------------------------------


    Server-side (WC)


      Notes:

Once the Server-side portion is installed and configs are set you can
forget about it. It only interacts with the JDBC portion and doesn't
need to be maintained.
It does maintain an event.log file that you may want to look at
occasionally to see what's been happening on your server.
The event.log can be invaluable for debugging, however any exceptions or
errors encountered on the server will be thrown back to the JDBC driver
as an SQL exception (or extension of).
Most PHP warnings will only be logged, not relayed as they usually
aren't critical and don't warrant halting the driver.
SQL errors will contain the database servers original message with the
offending SQL query below that. The usual java exception trail will
follow. It will also be logged in the servers event log as an SQL-ERROR.


        Back to the installation:

In the distribution package you will find a zip file called
|server-side-bundle.zip|.
It contains some optional testing files which are explained in the
README inside the zip.


          To setup the server-side part of this driver.

*NOTE:* The folder: "|jdbwc/includes/config/local|" is for testing
servers. Its supplied for convenience when developing. If it is present
on your server, its configs will be used in preference to the production
ones in: |jdbwc/includes/config|.
The "|jdbwc/includes/config/local|" folder should __NOT__ be uploaded to
production machines.

   1. Upload the contents of the upload folder to your webserver.
          * The |upload/jdbwc| folder can be uploaded to the public root
            dir or any subfolder.
          * EG: |http://yourserver.ext/*jdbwc*|,
            |http://yourserver.ext/subfolder/anotherfolder/*jdbwc*|, etc.
   2. Edit the file: "|jdbwc/includes/config/_*configure.php*_|"
          * Put in a username and password (create new ones).
          * Edit the paths and settings to suit your preferences and server.
   3. Edit the file: "|jdbwc/includes/config/_*databases.php*_|"
          * Put in the details for the databases you want access to.
          * If there's more than one, follow the directions in the
            comment to add multiple databases.
          * The database user must have full permissions (excluding
            grant perms).
   4. You will need to give the /.log/ files in:
      "|jdbwc/includes/wc_logs|" full write permissions.
      You will also need to give the folder: "|jdbwc/includes/sessions|"
      full write permissions and
      the "|jdbwc/includes/sessions/phpsess.db|" (sqlite database) write
      permissions if you chose sqlite as the session handler (in the
      |configure.php| file).

That's about it.
See the bundled |jdbwctest.zip| file in the distribution folder for
details on how to setup your java class/s to use the jdbwc.jar (JDBC
driver). Look in the |connect()| method for some examples.

Basic Use

Also have a look at the test class in the file |jdbwctest.zip| (included
in the distribution).

There are a number of optional connection parameters you can use to fine
tune the connection.
The list of optional params may change between different releases
depending on additional functionality supported by the driver.
See the section on Connection URL in detail <#params> for more info.

JDBWC is a JDBC Driver, so the standard JDBC syntax applies for general use.

To start a JDBC connection using JDBWC you need to register the driver
with the Java DriverManager and then start a new JDBC connection using a
JDBWC specific URL.
The url syntax and required parameters are demonstrated in the example.

Parameters (/everything after the ?/) can be in any order.
Registering the driver requires a |ClassNotFoundException| handler,
starting a new Connection requires an |SQLException| handler.

See the example below.
*NOTE:* SSL is highly recommended over sending sql in plaintext.
v1.0.0.3 will only work with validated certs.
v1.0.0.4 will work with validated and unvalidated/self-signed certs.
Un-validated/self-signed certs require the optional param:
|nonVerifiedSSL = true|

EXAMPLE

private java.sql.Connection connect() {

	*java.sql.Connection connection = null;*
	try {
		*/* register the JDBWC Driver with the Java DriverManager */*
		*Class.forName("com.jdbwc.core.Driver");*

		final String hostUrl = "http://localhost:8080/";
		final String hostUser = "xxxxxxxxxxxxxxx";
		final String hostPass = "xxxxxxxxxxxxxxx";

		final String databaseName = "uname_jdbwctest";
		final String databaseUser = "xxxxxxxxxxxxxxx";
		final String databasePass = "xxxxxxxxxxxxxxx";

		/* *********************************
		 * Starting a connection via a url *
		 * *********************************/
//		final String jdbwcUrlString = "jdbc:jdbwc:mysql//"
//			+ hostUrl
//			+ "?databaseName=" + databaseName
//			+ "&databaseUser=" + databaseUser
//			+ "&databasePassword=" + databasePass;
//
//		/* initialise a new JDBC Connection using the JDBWC Driver package */
//		*connection = DriverManager.getConnection(jdbwcUrlString, hostUser, hostPass);*

		/* ****************************************
		 * Using a Propeties object (recommended) *
		 * ****************************************/
		//final String jdbwcUrl = "jdbc:jdbwc:postgresql//";
		final String jdbwcUrl = "jdbc:jdbwc:mysql//";

		Properties props = new Properties();
		
		props.put("url", hostUrl);
		props.put("user", hostUser);
		props.put("password", hostPass);
		
		props.put("databaseName", databaseName);
		props.put("databaseUser", databaseUser);
		props.put("databasePassword", databasePass);

		*connection = DriverManager.getConnection(jdbwcUrl, props);*

	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	}
	*return connection;*
}

Connection URL in detail


    The connection URL

The connection URL consists of 3 main parts. Optional parameters can be
added to the connection to customise the connection object. Parameters
can be in any order.

   1. JDBC URL <#jdbcurl>
   2. Server-Side URL <#url>
   3. Parameters <#reqprops>

Example:*|
jdbc:jdbwc:mysql//||https://example.org:445/exampledirectory/|*

Theres 2 ways to pass the information into the driver in order to start
a new connection.

*The parameters can be appended to the end of the URL:*

|String fullUrl =
|"*|jdbc:jdbwc:mysql//https://example.org:445/exampledirectory/||?databaseName=uname_mydatabase&databaseUser=uname_myuser&databasePassword=mysecretpassword|*";
|connection = DriverManager.getConnection(*fullUrl*, "myhostusername",
"mysecrethostpassword");|

The parameters are seperated from the URL parts with a question symbol:
"*?*" then each /key=>//parameter/ pair is seperated with the ampersand
symbol: "*&*"
There should be no spaces.

OR

* The parameters are stored in a |java.util.Properties| object:* |String
jdbcUrl = "*jdbc:jdbwc:mysql//*";

Properties props = new Properties();
props.put("*url*", "https://example.org:445/exampledirectory/");
props.put("*user*", "myhostusername");
props.put("*password*", "mysecrethostpassword");
props.put("*databaseName*", "uname_mydatabase");
props.put("*databaseUser*", "uname_myuser");
props.put("*databasePassword*", "mysecretpassword");

connection = DriverManager.getConnection(*jdbcUrl*, *props*);|

------------------------------------------------------------------------


    JDBC Driver URL's


          Use only one of:

    * jdbc:jdbwc://
    * jdbc:jdbwc:mysql//
    * jdbc:jdbwc:postgresql//


          What they do:

|jdbc:jdbwc://|
Requests a default connection from JDBWC. The default is MySql.

|jdbc:jdbwc:mysql//|
Requests a MySql connection from JDBWC.

|jdbc:jdbwc:postgresql//|
Requests a PostgreSql connection from JDBWC.

------------------------------------------------------------------------


    Required properties


          Use all of:

    * url
    * port (has been removed since v1.0.0.4. See: url <#url>)
    * user
    * password
    * databaseName
    * databaseUser
    * databasePassword


          What they do:

|url|
The website URL containing the JDBWC server-side PHP bundle.
The url must start with a protocol (http or https) and have a trailing /
Examples:

    * |https://mydomain.ext:445/myfolder/myotherfolder/| (will use port 445)
    * |http://mydomain.ext:8080/myfolder/myotherfolder/| (will use port
      8080)
    * |https://mydomain.ext/myfolder/myotherfolder/| (will default to
      port 443)
    * |http://mydomain.ext/myfolder/myotherfolder/| (will default to
      port 80)

/The port number is only required for non standard ports in the range
[1-0xfffe] (anything other than 80 or 443)/.
Custom port usage may incur up to 7ms overhead per query (100 queries =
+0.7 of a second).

The JDBWC driver will append |jdbwc/index.php| to the end of the URL
before requesting a connection from the server.
/If the domain name is unknown or not in DNS an IP can be used.
/

|user|
The user name you specified in the server-side |configure.php
<#configure>| file.

|password|
The password you specified in the server-side |configure.php
<#configure>| file.

|databaseName|
One of the database names you specified in the server-side
|databases.php <#databases>| file.

|databaseUser|
One of the database user-names you specified in the server-side
|databases.php <#databases>| file.

|databasePassword|
One of the database passwords you specified in the server-side
|databases.php <#databases>| file.

------------------------------------------------------------------------


    Optional properties


          Use or omit any of:

    * proxyUrl
    * timeout
    * nonVerifiedSSL
    * debug
    * debugLogger
    * debugLevel
    * useDummyAgent


          What they do:

|proxyUrl|
The proxy server URL to communicate through.
The url must start with a protocol (http or https). NO trailing /
Examples:

    * |http://myproxy.ext:8120|
    * |http://myproxy.myserver.ext:8000|

/The port number is ALWAYS required for proxy servers/. If this
parameter is used and there is no proxy, the connection will fail.

|timeout|
Connection timeout. The maximum amount of time before a connection will
timeout if it hasn't received a response from the server. If the server
is offline, the driver will throw an exception as soon as it determines
there's no one home.
OPTIONS: a time in milliseconds. 1 second = 1000 milliseconds

|nonVerifiedSSL|
Allows the driver to use different qualities of SSL. By default its set
to false and the driver will perform full SSL verification including
strict hostname checking (similar to a web browser). If this option is
set to true the driver will attempt to ignore the usual checks. If the
driver is unable to ignore the SSL verification the driver will throw an
exception from the |java.security| package or/and the |javax.net.ssl|
package.
OPTIONS: true, false (or not used. defaults to false)

|debug|
Turns debugging on or off. Its off by default. The other |debugXX|
options will only take effect if this option is set to |true|.
OPTIONS: true, false (or not used. defaults to false)

|debugLogger|
The type of logger to use for logging. Although there are 3 options,
_only SimpleLog support is built in to this release_.
/|java.util.logging| and |Log4J| both require external property files./
To find out more about the logger see:
http://hc.apache.org/httpcomponents-client-4.0.1/logging.html
OPTIONS: SimpleLog, /Jdk14Logger, Log4JLogger/

|debugLevel|
Debugging levels:
0 JDBWC informative/debugging,
1 JDBWC debugging,
2 JDBWC debugging and Enable header wire + context logging - Best for
Debugging,
3 JDBWC debugging and Enable full wire + context logging,
4 JDBWC debugging and Enable context logging for connection management,
5 JDBWC debugging and Enable context logging for connection management /
request execution.
OPTIONS: [0-5]

|useDummyAgent|
This is rarely required. Its for situations where the host server blocks
the default HttpClient |User-Agent|. The server would need to have been
configured to deliberately block the HttpClient user agent.
OPTIONS: true, false (or not used. defaults to false)

What's new?


          Since v1.0.0.3beta

    * Communication layer changed from being powered by
      Apache-HttpClient 3.1 (legacy) to Apache-HttpClient 4.0.1.
    * Host connection can use _any valid port_ in the range [1-0xfffe].
      Non standard port use may incur up to 7ms delay per query (100
      queries = +0.7 of a second).
    * Added _support for 1 proxy_ server.
    * Added optional connection parameters:
          o timeout
          o nonVerifiedSSL
          o proxyUrl
    * Added 2 more debug levels.
    * Trimmed down the exception classes to only those currently
      required by the driver.
    * Added support for un-validated server-side SSL.
    * Added transparent gzip support for incoming data (from server-side
      going to JDBC side).
    * Added JDBC Fatal Server-Side Error handling.
    * Lots of work on MySql DatabaseMetaData and ResultSetMetaData.
    * New ResultSetMetaData backend v3. Uses a connection
      resource-economic approach, is faster and suitably accurate. Works
      with any SQL that produces a MySQL resultset including SHOW and
      EXPLAIN.
    * Rewrote and fine tuned the MySQL Types class (handles most of the
      type juggling between MySQL and Java.sql).
    * Added a static metadata feed class for the java.sql metadata classes.
    * Better handling of null and empty values by the drivers underlying
      data type. This versions uses a new implementation better suited
      to SQL data.
    * Completed numerous (previously un-implemented methods) in the core
      implementation classes.
    * Simplified connection parameters (reduced amount of params by
      including more information in the remote url's.
          o Combined required port param with server url. (EG:
            |https://myserver.ext:8081/mysubfolder/|)
          o Combined use proxy, proxy protocol, domain and port into 1
            url. (EG: |http://myproxyserver.ext:8085|)
    * The Driver is now compatible and very functional in the following
      SQL clients.
          o SQL Workbench - build 108
          o DB Visualizer - Free 7.0.6
          o SQuirreL SQL Client 3.1
    * Changed main server file to look for the |config/local| folder
      rather than a host named |localhost| (this is usually for testing
      setups).
    * Updated valid parameter key strings for com.jdbwc.Driver
    * Migrated connection param verification and setup to the Driver
      class (from the connection class).
    * Updated javadocs.
    * Improved JDBWC test class.
          o Implemented the new optional parameters in connect() method.
          o Implemented a test code block for demonstrating the use of
            Driver.getPropertyInfo() (un comment to use it).
          o Improved the main() method with time taken display and the
            ability to run the test in loops for reliability and stress
            testing.
          o Test main() can accept one external parameter to trigger
            multiple passes.
    * Added NotImplemented exceptions to all methods that are not yet
      full implemented. Added an additional message to make finding the
      cause of these exceptions easier.
    * Minor improvements to the Ant build file.

Limitations

    * JDBWC is aimed at JDBC 3 specifications with a few limitations
      (/also see the other limitations and TODO <#todo>'s/).
    * No support for some of the newer JDBC 3 and 4 data-types.
    * Shared IP's don't agree with the driver. Dedicated IP's are fine.
    * Single connections? My advice would be no, multiple connections
      are OK, but be carefull. If latency isn't an issue It should be
      fine. If latency is an issue, you may find things happening out of
      order. EG: a delete query sent after a select query, could end up
      getting to the server first, in which case your select query would
      probably not be correct based on the order it was sent. I would
      recommend using single connections when communicating across large
      unknown networks like the internet.
    * Also see Known Issues <#issues>.

TODO

    * Finish PreparedStatement (/almost done/), CallableStatement,
      ParameterMetaData (/almost done/) and anything else not
      implemented yet.
    * Implement support for more of the newer JDBC 3 and 4 data types.
    * Implement support for the N data types.
    * JUnit tests (/not the highest priority at this point/).
    * Implement support for more databases. This will occur with or
      after the next release.

Known issues

    * Shared IP's don't agree with the driver (or previous versions).
      Dedicated IP's, localhost testing servers or production servers on
      the same machine as the driver aren't usually a problem.
    * Not really an issue but worth mentioning. Server-Side testing in
      relation to Fatal Errors: Given the amount of different
      os/apache/php setups its not possible to test on all platforms. At
      least 3 variations (os/apache/php - version, build, configuration)
      are used during WC testing.
      The only reason I can see for any Fatal-Errors with WC would be if
      the web/php server didn't have a required PHP package installed.
      Care has been taken to avoid packages that are commonly not
      installed on many shared hosts.

*NOTE:* Its _strongly_ advised to use an SSL connection when
communicating across a network or the internet as you will be sending
raw sql. To correctly install SSL your web-server _must have a dedicated
IP_.

Reporting Bugs

If you notice any, please report them in the projects bug-tracking area
at: http://sourceforge.net/tracker/?group_id=233279&atid=1089227

_Please_ be specific with bug reports so they can be properly attended
to. Vague reports will not be attended to unless the error is quite obvious.
Include some code or a description of the conditions you encountered the
bug in so it can be reproduced. The more detail, the higher the chance
of it being fixed quickly.

*
NOTE:* |NotImplemented| exceptions are not bugs. They are features that
have not been fully implemented yet and will vary for each release. This
technique of dealing with unfinished areas is part of the JDBC
specification for drivers
[http://java.sun.com/products/jdbc/driverdevs.html].

If you would like to see a particular feature implemented, please make a
feature request at:
http://sourceforge.net/tracker/?group_id=233279&atid=1089230

Copyright ©2008 Tim Gall, All rights reserved.

*LEGAL FOOTNOTES:*
Java is a trademark of Sun Microsystems Inc. (Also see: Oracle Corporation)
MySql is a trademark of MySql AB. (Also see: Oracle Corporation)
PostgreSql is copyright of: PostgreSQL Global Development Group

