JDBWC - Installation

Document Revision: 1

• Introduction
• Requirements
• Installation
• Basic Use
• Connection URL in detail
• What's new?
• Limitations
• Known issues
• TODO
• Reporting Bugs

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.

Client-side:
Java JRE >= 1.6.0_20 (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) + 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

Client-side (JDBC)

1. Unpack the compressed download if you got it from the releases area.
   The latest version should also be on the SourceForge SVN server (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.

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 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

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;
}

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
2. Server-Side URL
3. Parameters

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.

OR

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)
• 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).
In rare circumstances custom port usage may incur up to 7ms overhead per query (100 queries = +0.7 of a second). This rare delay seems to be related to the webserver, not the driver.

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 file.

password
The password you specified in the server-side configure.php file.

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

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

databasePassword
One of the database passwords you specified in the server-side databases.php 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)

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:
  • timeout
  • nonVerifiedSSL
  • 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.
  • Combined required port param with server url. (EG: https://myserver.ext:445/mysubfolder/)
  • 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.
  • SQL Workbench - build 108
  • DB Visualizer - Free 7.0.6
  • 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.
  • Implemented the new optional parameters in connect() method.
  • Implemented a test code block for demonstrating the use of Driver.getPropertyInfo() (un comment to use it).
  • Improved the main() method with time taken display and the ability to run the test in loops for reliability and stress testing.
  • Test main() can accept one external parameter (int) to trigger multiple passes.
• Added NotImplemented exceptions to all methods that are not yet full implemented. Added an additional message for clarity and to make finding the cause of these exceptions easier.
• Minor improvements to the Ant build file.
• The following improvements were back ported from v1.0.0-5:
  • Fixed java.sql.Driver.getPropertyInfo() so it works properly with SQL Client software and exposes all parameters accepted by the driver.
  • Dropped the version number from the compiled data-handler.jar's name to eliminate the need to update classpaths if the package changes.
  • Added better ResultSetMetadata support for the mysql extension. The mysqli extension has always had good support.
  • Added a variable to expose improved sql extensions to the driver so the appropriate processing can take place. MySQLi is currently the only improved extension Im aware of.

• JDBWC is aimed at JDBC 3/4 specifications with a few limitations (also see the other limitations and TODO's).
• No support for some of the newer JDBC 3 and 4 data-types.
• Data sizes of queries should be kept at a reasonable size. This applies to sending and receiving. Sending is limited to the $_POST limit set on the server (32MB is a common size for the post limit, but it could be much larger depending on your web servers configuration). Receiving is limited by the amount of free memory on the machine running the Driver (and the JRE running the driver). JDBWC caches the entire resultset/s from a query while it is open (this isnt so much of a problem as most modern machines have ample RAM and can fallback to pagefile. Falling back to pagefile would reduce performance. < 10MB is a safe size).
• Connection Limits. In early versions <= v1.0.0-3 multiple connections caused issues. Version v1.0.0-4 seems OK with multiple connections, but be carefull if latency or high error rates are an issue, as you could potentially 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. Much like un-synchonized Java code can in certain circumstances. For now, I would recommend using single connections when communicating across large unknown networks like the internet unless they are consistently reliable. This limitation is planned to be resolved in a future release by using a manual synchronization system for queries. See the TODO's for more information.
• Also see 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 usually aren't a problem.
• * PostgreSQL ResultSetMetaData does not return key references. Unfortunately PostgreSQL is fairly awkward to extract ResultSetMetaData from. The current implementation does not correctly indentify table keys (primary, unique and multi). This is in part due to some limitations with PostgreSQL. Work is in progress to try and overcome this; however its not the main focus and a release time is not yet planned. DatabaseMetaData does NOT have this limitation.
• 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.

• The main focus for the next release will be restructuring database specific code into brand specific packages. This is to make further database integration faster and easier.
• Add a unique key to each statement for the purpose of processing results in the same order they were sent. If a required query has not responded, this will also give the driver the ability to detect it and resend the lost query if required. This is an important aspect for wide area networks and data relaying (particularly latent networks or/and those with high error rates). It will also make the prospect of threading queries more feasable, which would in turn provide a good controlled base for limiting the amount of active statements, gracefully timing out from the client side, cancelling and closing statements. This single TODO will most likely be the entire focus of a new version/code-branch when it gets implemented.
• Add more database support (EG: any database that can be easily accessed from PHP).
• 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.
• JUnit tests (not the highest priority at this point but would be good later).

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