Tuesday, July 8, 2008

[JDBC] Patch to allow setting schema/search_path in the connectionURL

Many years ago, Andreas Joseph Krogh submitted a patch to allow the
setting of the default schema in a JDBC connection string:

http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00187.php

It was rejected with some objections:

http://archives.postgresql.org/pgsql-jdbc/2002-12/msg00189.php

I've made a modern version of this patch against the current CVS head
that hopefully addresses the earlier objections on this list. In this
version, however, the parameter is called 'searchpath' instead of 'schema'

I find this functionality useful for a data reporting tool like BIRT:

http://www.eclipse.org/birt/phoenix/
that expects you to provide the JDBC connection parameters but where the
tool will build appropriate SQL queries for report generation - based
on your manipulation of the report designer interface. Our DBA likes to
use postgresql schemas for namespace management. Setting a searchpath
keyword in the JDBC connection parameters allows the BIRT reports to be
easily portable for use with differently-named schemas.

E.g.:

jdbc:postgresql://localhost:5432/mydatabase?searchpath=myschema

Incidentally, I noticed that the npgsql developers added similar
functionality to their Posgresql driver in November of last year:

http://archives.postgresql.org/pgsql-committers/2007-11/msg00506.php

Regards,

Scott Langley
--
Systems Analyst/Programmer
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Fred Hutchinson Cancer Research Center
Seattle, Washington
slangley@scharp.org

No comments: