JdbcUrlConventions
From Eigenpedia
Contents |
Introduction
This document specifies the conventions followed for Farrago JDBC URL's. The JDBC drivers provided by the Farrago project are intended as reference implementations and are used mainly by the framework test suites. DBMS projects specialized from Farrago typically define their own JDBC drivers and URL's, but in most cases these specialized driver classes can inherit from Farrago base classes. These base classes supply generic behavior, including URL parsing.
Conventions
- use OLE DB connect string syntax (adapted from Mondrian URL-parsing code); details in references section below
- parsing implemented in Farrago, with specialized projects inheriting
- use AS/400, jTDS param precedence: defaults (if any) superseded by Properties object, superseded by URI params
- precedence rules implemented in Farrago, with specialized projects inheriting
- JDBC URL's as below
| Description | Driver Class | URL | Notes |
| Direct-access driver for local clients | net.sf.farrago.jdbc.engine.FarragoJdbcEngineDriver | jdbc:farrago: | caller and Farrago run in same JVM |
| Remote-access driver | net.sf.farrago.jdbc.client.FarragoJdbcClientDriver | jdbc:farrago:rmi://server-address[:port] | default port number is 5433 |
| Routine loopback driver | net.sf.farrago.jdbc.engine.FarragoJdbcRoutineDriver | jdbc:default:connection | standard mechanism (defined in SQL/JRT) for calling back into SQL engine from a user-defined Java routine |
As an example of driver specialization, the table below documents the conventions for LucidDB:
| Description | Driver Class | URL | Notes |
| Direct-access driver for local clients | com.lucidera.jdbc.LucidDbLocalDriver | jdbc:luciddb: | caller and LucidDB run in same JVM |
| Remote-access driver | com.lucidera.jdbc.LucidDbRmiDriver | jdbc:luciddb:rmi://server-address[:port] | default port number is 5434 |
Connection Attributes
The following table lists the supported client-specified connection parameters.
| Attribute | SESSIONS_VIEW Column | SQL Expression | Example |
| sessionName | SESSION_NAME | "Payments Logging" | |
| clientUserName | SYSTEM_USER_NAME | SYSTEM_USER | "fjfarrago" |
| clientUserFullName | SYSTEM_USER_FULLNAME | "Franklin J. Farrago" | |
| clientProgramName | PROGRAM_NAME | "Acme Accounts Payable" | |
| clientProcessId | PROCESS_ID | "12345" | |
| remoteProtocol(*) | "RMI" | ||
| schema | SCHEMA_NAME | "SALES" | |
| requireExistingEngine | "true" | ||
| label | LABEL_NAME | "2008-Aug-18 ETL" |
These may be specified as entries in a Properties object or as parameters on the URI, e.g.
jdbc:farrago:rmi://host:port;sessionName="Payments Logging";clientProgramName="Acme Accounts Payable"
Note that sqlLine splits arguments at blanks, regardless of quoting. You must eliminate the above example's embedded blanks when using sqlLine.
Farrago's Java drivers supply a default value for clientUserName from the user.name system property.
The default connection value for parameter requireExistingEngine is "false". If "true", will not load a new database if one does not exist.
The initial schema specified by schema is used in a
SET SCHEMA 'schema-name'
DDL statement when the connection is first created.
This name is by default converted to uppercase by Farrago.
To specify a case-sensitive mixed- or lower-case initial schema the value must be double-quoted.
To pass this property via a connection string parameter, the double-quoted value must then
be enclosed in single quotes to preserve the inner quotes, e.g.
jdbc:farrago:rmi://host:port;schema='"MySchema"'
Each attribute with a "SESSIONS_VIEW Column" name in the table above
can be queried using the Farrago system management "sessions_view", e.g.
SELECT session_name FROM sys_boot.mgmt.sessions_view
(*) NOTE on remoteProtocol attribute: in situations where farrago authentication is enabled for remote connections but disabled for local (in-process) ones, the remoteProtocol attribute is used to denote jdbc connections that originate from the outside. Therefore, jdbc connection proxies (such as the vjdbc RMI server or HTTP servlet) MUST overwrite this property. It is not meant to be specified by the end-user, but is a way for FarragoDbServer to discriminate between connection origins.
NOTE on setting properties from sqllineClient: When running sqllientClient on Linux, watch out for the fact that unquoted semicolons will be treated as shell command terminators. Here's an example of the correct way to invoke it: sqllineClient -u "jdbc:luciddb:rmi://localhost;schema=BIZARRO"
RMI Registry Ports
The following RMI Registry ports are reserved for use by Farrago and extension projects.
| RMI Port | Project |
| 5433 | Farrago |
| 5434 | LucidDB |
| 5435 | SQLstream |
| 5437 | DynamoDB |
HTTP Ports
The following HTTP ports are reserved for use by Farrago and extension projects.
| HTTP Port | Project |
| 8033 | Farrago |
| 8034 | LucidDB |
| 8035 | SQLstream (unused) |
| 8037 | DynamoDB |
References
(Contributed by Steve Herskovitz)
The JDBC spec has a fairly soft specification for the JDBC URI:
- "recommendation" that it starts with jdbc:subprotocol:subname, e.g. jdbc:somedb:
- "recommendation" that the host be specified as part of the subname as //host:port/subsubname, e.g. jdbc:somedb://host:port
As examples, this Caucho/Resin page lists a variety of basic JDBC URIs:
[1] The JDBC spec offers no recommendation for the format of any additional connection parameters (also called attributes or properties by some vendors) included in the subsubname. At present, vendors seem to have converged on a semicolon-separated parameter list:
jdbc:somedb://host:port;name1=value1;name2=value2;...
Weblogic 5.1 used to support an RFC-1630-style query string:
jdbc:weblogic:t3?weblogic.t3.serverURL=t3://toyboat.bigbox.com:7001&name2=value2&...
See [2]
I cannot find this in WebLogic 8.1 docs, which specify the semicolon-separated parameter lists for each of the vendor-specific drivers discussed in
[3]. Microsoft OLE DB uses a connection string syntax that takes the basic semicolon-separated list and adds rules for quoting, escaping, and embedded-space handling. This is rather well-specified at
Connection parameters can also be specified to the JDBC API (DriverManager.getConnection, Driver.connect) in a Properties object. What are the semantics of a parameter supplied both in a Properties object and as part of the URI? I conducted an exasperating search of JDBC driver documentation looking for clear statements of precedence, but with one exception I could only find ambiguous phrases like "properties can be specified either on the URI or in a Properties object." IBM AS/400 driver documentation, the lone exception, states clearly, "If a property is set in both the URL and a Properties object, the value in the URL will be used."
[5]
Because no other driver documented its behavior, I examined four Open Source drivers to see what precedence they actually implemented:
- HSQLDB - Properties over URI params
- Mondrian - Properties over URI params
- Derby (Cloudscape) - URI params over Properties
- jTDS - URI params over Properties over Default Properties
I suppose it's not surprising that something so poorly specified and documented would be implemented in different ways by different developers. Does anyone have any practical or "inside" knowledge of the behavior of closed-source drivers written by Oracle, Microsoft, WebLogic, or OpenLink? I propose to use the jTDS approach: start with defaults, apply the Properties, apply the URI params. jTDS is a very widely used driver. Its approach is consistent with the AS/400 driver. In practice, most users will not put params in both places. However, we should state our policy clearly in our JavaDoc comments.
