*NB: PosgreSQL is for Unix based platforms. To run PostgreSQL on MS Windows platforms it is necessary to layer it over an emulation such as WINE or Cygwin. I personally do not recommend going to this trouble, especially since a PostgreSQL database server on a Linux box can integrate across a network, or even the internet, with the middleware serving from a Windows Web server.
I. Installing PostgreSQL into a Linux Server
My installation is on a RedHat 7.0 Linux development box. I was unable to install PostgreSQL from the various RPMs I downloaded from a
PostgreSQL mirror site. (It may be likely, however, to use PostgreSQL RPMS to upgrade an existing installation.) I installed PostgreSQL-7.0.3 from the PostgreSQL installer CD that came with the RedHad 7.0 distro. I used the text based (non-GUI) installer from a plain console session as root without the X server running.
A few details to note are as follows:
- To start the PostgreSQL server one must issue the command:
/etc/rc.d/init.d/postgresql start -i
The -i option allows clients to connect to PGSQL databases using TCP/IP, a very desirable functionality. This option must cooridinate with the settings in the pg_hba.conf file.
- The full path specification of the pg_hba.conf file is /var/lib/pgsql/data/pg_hba.conf. It is used to configure various PSGQL DBs for access privileges for IP mapped host computers and individual users. A fair amount of security nuance can be granted from this file. I am presently using some very basic and promiscuous settings while in development mode:
#
|
| # TYPE |
DATABASE |
IP_ADDRESS |
MASK |
AUTHTYPE |
| host |
all |
0.0.0.0 |
0.0.0.0 |
trust |
#
# The above would allow anyone anywhere to connect to any
# database under any username.
#
# By default, allow anything over UNIX domain sockets and
# localhost.
#
|
| local |
all |
|
|
trust |
| host |
all |
127.0.0.1 |
255.255.255.255 |
trust |
#
|
- The PostgreSQL server, when stared, launches a daemon which listens on port 5432, by default, for DB access commands. When accessing PGSQL DBs remotely you should use this port. Of course, this port number assignment is configurable to something else.
- The overall PostgreSQL installation includes an interactive tool, psql, which is useful for quick checks of SQL query syntax and fast DB prototyping. The full specification for this command is /usr/bin/psql. Typically a database name is passed to it as a command argument. This tool is run in console mode only. (The Linux tool, Webmin, can also provide a nice web based graphical interface into the PostgreSQL server.)
Back to top of page.
II. PostgreSQL JDBC Driver Installation JDBC for JSP on a J2EE Server
It will behoove you greatly to go to the following link and get fully acquainted:
http://jdbc.postgresql.org/. Even after getting fully acquainted, you will have integration problems and information deficits. Also, for a more specifically targeted discussion of the JDBC interface please look at the
Client Interfaces section of this
online PostgreSQL document.
Using JDBC (Java Database Connectivity) drivers in the J2EE (Java 2 Enterprise Edition) server environment requires a dovetailing of many installation details. A PostgreSQL JDBC dirver typically is in the form of a JAR file, such as postgresql.jar - the one I am using is jdbc7.1-1.2.jar. (JAR, Java Application Resource, files are analogous to MS Windows DLLs.)
This document assumes that you are not building a driver from source code.
- The PostgreSQL JDBC driver version needs to be compatible with the version of the JDK, Java Development Kit - in my case JDK 1.3. This is confusing, but here goes: the PostgreSQL JDBC driver version 7.1.x, which runs with PostgreSQL version 7.0.3, is supported by the Java2 platform using either JDK 1.2.x or JDK 1.3.x. (This is further complicated by discussions of JDBC versions used by J2EE. The PGSQL JDBC driver I am using supports both JDBC 1.2 and JDBC 2.0, the latter being the version included with the J2EE j2sdk1.3.)
In shorthand, for my installation: J2EE j2sdk1.3, PostgreSQL 7.0.3, PGSQL JDBC 7.1-1.2 (jdbc7.1-1.2.jar) will interoperate.
- The J2EE environment must point to the PGSQL JDBC driver for use in a JSP web based inferface. At least one of the two J2EE environmental variables, CLASSPATH or J2EE_CLASSPATH, must include the path to the PGSQL JDBC JAR (postgresql.jar). I recommend setting the JDBC driver path in the CLASSPATH variable since it is invariably subsumed into the J2EE_CLASSPATH setting. These J2EE environment variables, along with the other two relevant ones, JAVA_HOME and J2EE_HOME, should be set in, and exported, from the system file /etc/profile.
Please examine the following excerpt from the end of /etc/profile to see how this may be done:
#
# j2sdk and j2ee environment setup - JSB
#
JAVA_HOME=/usr/java/jdk1.3.1_01
J2EE_HOME=/usr/java/j2sdkee1.3
CLASSPATH=${JAVA_HOME}/lib:/usr/lib/pgsql/jdbc7.1-1.2.jar\
:/usr/lib/mysql/mm.mysql-2.0.4-bin.jar
J2EE_CLASSPATH=${CLASSPATH}
PATH=${PATH}:${JAVA_HOME}/bin:${J2EE_HOME}/bin
export JAVA_HOME J2EE_HOME PATH CLASSPATH J2EE_CLASSPATH
#
|
* Nota Bene:
- It is important to make sure that you specifiy the name of the JAR file, jdbc7.1-1.2.jar, in the actual path specification.
- To effect the environment changes exported from the /etc/profile file, after editing its contents as root, it is necessary to log out of your Linux session and then log back on.
- Note that I have also included the JDBC driver for MySQL, version 2.0.4, mm.mysql-2.0.4-bin.jar by Mark Matthiews.
- The use of a JDBC driver requires that the driver be registered with Java driver manager used by the J2EE server. Loading the JDBC driver programmatically implicitly registers it with the driver manager. (Most JDBC drivers these days are implicitly registered with the driver manager when loaded.) Observe the following Java Server Pages code excerpt:
try {
Class.forName("org.postgresql.Driver");
}
catch (ClassNotFoundException cnfe) {
out.println("Could not load database driver:" + cnfe.getMessage());
}
|
Certain JDBC drivers, but neither of those needed currently for PostgrSQL or MySQL, need to be explicitly registered with the Java driver manager before they can be loaded into a JSP application. This can be done two ways: 1) through the use of a command line tool, java -Djdbc.drivers=theJdbc.Driver example.Example, or 2) programmatically in JSP servlet programming code, using the DriverManager.registerDriver() method.
- Once the JDBC driver has been loaded what must follow programmatically in your JSP code is the creation of a connection object. The establishment of a database connection requires three distinct components: a URL specification to the database, a user ID who has access privileges to that database, and an associated password. In some cases the the user ID and password are appended to the URL specification in the form of a query string. In the case of PostgreSQL they passed as arguments to the DriverManager.getConnection() method.
The user ID and password argument complements to the URL specification are set as string types. Their values are self explanatory. The URL specification, however, requires a modicum of explanation to be parsed by the JSP programmer. In the following code box the pgsqlURL string, "jdbc:postgresql://host:5432/mydb", has three basic components, delimited by colons: <protocol>:<subprotocol>:<DBspec> .
String pgsqlDriver = "org.postgresql.Driver";
String pgsqlURL = "jdbc:postgresql://host:5432/mydb";
String pgsqlUser = "User";
String pgsqlPW = "password";
|
The first component, <protocol>, is always "jdbc". The second, <subprotocol>, marks which driver is to be used; the string itself is specific to the vendor of the driver, which, in our case, is "postgresql". The third part of the URL specification, <DBspec>, has up to three parts describing it: 1) an optional computer hostname (or IP address), which, if omitted, is assumed to be the default, localhost, 2) an optional port number appended to the hostname with a colon - if omitted the PosgreSQL default value of 5432 is assumed, and 3) a required database name. If both host and port values are omitted the above string, pgsqlURL, could appear as "jdbc:postgresql:mydb".
(JDBC-ODBC bridge drivers almost always use the subprotocol, "odbc", yielding a URL something like: "jdbc:odbc:DBspecification".)
Observe the previous JSP code excerpts in the context of complete programming logic:
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
String pgsqlDriver = "org.postgresql.Driver";
String pgsqlURL = "jdbc:postgresql://host:5432/mydb";
String pgsqlUser = "User";
String pgsqlPW = "password";
String pgsqlQuery = null;
Connection pgsqlConn = null;
Statement pgsqlStatement = null;
ResultSet pgsqlRSet = null;
try {
Class.forName(pgsqlDriver);
pgsqlConn = DriverManager.getConnection(pgsqlURL, pgsqlUser, pgsqlPW);
pgsqlStatement = pgsqlConn.createStatement();
pgsqlQuery = "SELECT * FROM foo;";
pgsqlRSet = pgsqlStatement.executeQuery(pgsqlQuery);
}
catch (ClassNotFoundException cnfe) {
out.println("Could not load database driver:" \
+ cnfe.getMessage());
}
catch (SQLException sqle) {
out.println("Could not connect to the database: " \
+ sqle.getMessage());
}
finally {
try {
if ( pgsqlConn != null ) {
// Close the connection no matter what.
pgsqlConn.close();
}
}
catch (SQLException sqle) {
out.println("Could not connect to the database(2): " \
+ sqle.getMessage());
}
}
|
* Please Note:
- The use of "<%@ page session='false' %>" is to speed up the serving of a JSP page by reducing the overhead of adding session classes into the compiled JSP code. This significantly reduces the JSP turn around time generated by serving re-editied JSP code during prototyping.
- The use of the exception from the DrverManager class, ClassNotFoundException, which communicates through the method ClassNotFoundException.getMessage() when this type of exception is thrown.
- Catching the exception, SQLException, must be implemented not only for trying to make the database connection object, but also when trying to disconnect from the database, as in the finally{ } block above.
- If connecting by means of a DataSource object the javax.naming.* and javax.sql.* classes must be imported. Refer to the DataSource code samples in the appendix below.
Back to top of page.
APPENDIX
A. Accessing PostgreSQL from a Windows Web Server
As stated in the openning paragraph at the top of this document,
PosgreSQL is for Unix based platforms. It is not practical to run PostgreSQL in a Unix emulation overlayed upon a MS Windows environment. It is much more practical to access a Linux/Unix based PostgreSQL database server remotely from Windows. In both the ASP and JSP serving contexts, though, databases on remote PostgreSQL servers must be accessed as
ODBC Data Sources registered with the MS Windows operating system. This is inconvenient and I have, as yet, not discovered how to make database connection objects programmatically from within an ASP or JSP servelet on a MS Windows based platform.
- The first thing to be done before registering ODBC Data Source is to install the requisite ODBC-JDBC bridge driver, which is in the form of a .dll file, whose intaller registers it with the Windows operating system, usually by means of an automated registry edit. In my case the driver is called psqlodbc.dll and resides in c:\WINNT\system32. You may find a suitable PostgreSQL JDBC-ODBC driver for MS Windows at this link Win32 Binary Distribution maintained by PostgreSQL.org.
- To register an ODBC Data Source with the MS Windows operating system: Click Start > Programs > Administrative Tools > Data Sources (ODBC). Clicking on the System DSN tab will yield the following display. (PostgreSQL wants data sources in MS Windows to be registered as System DSNs, whereas MySQL will use either a User DSN or a System DSN.)
Now click the Add button giving you the following interface. (A sign that you have properly installed the PostgreSQL JDBC-ODBC driver is that it will enumerate as a driver type in the Create New Data Source interface.)
Highlight the PostgreSQL driver entry and click the Finish button to bring up the following panel. (Notice the default port setting of 5432.)
Now, by example as in my case, fill in the Desciption, "PG Test", the Database, "mydb", and the Server, "linux-dev.skybuilders.com" - of course, use fields pertinent to your own database installation. I leave the User and Password fields blank and fill them in programmatically in the ASP and JSP code (examples following).
Make sure to click OK or Apply as you close each display panel to save your edits.
Back to top of page.
- What follows is a code sample for accessing PostgreSQL remotely within a JSP servelet on a MS Windows platform. Please note that bolding here is used to emphasize the differences between the previous JSP code sample, as seen above in section II (bullet 4), and this one using a data source.
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>
javax.naming.DataSource datasource = null;
String pgsqlURL = \
"jdbc:postgresql://linux-dev.skybuilders.com:5432/mydb";
String pgsqlUser = "jesse";
String pgsqlPW = "";
String pgsqlQuery = null;
Connection pgsqlConn = null;
Statement pgsqlStatement = null;
ResultSet pgsqlRSet = null;
try {
Class.forName(pgsqlDriver);
pgsqlConn = datasource.getConnection(pgsqlURL, pgsqlUser, pgsqlPW)
pgsqlStatement = pgsqlConn.createStatement();
pgsqlQuery = "SELECT * FROM foo;";
pgsqlRSet = pgsqlStatement.executeQuery(pgsqlQuery);
}
catch (NameNotFoundException nnfe) {
out.println("Could not load database driver:" \
+ nnfe.getMessage());
}
catch (SQLException sqle) {
out.println("Could not connect to the database: " \
+ sqle.getMessage());
}
finally {
try {
if ( pgsqlConn != null ) {
// Close the connection no matter what.
pgsqlConn.close();
}
}
catch (SQLException sqle) {
out.println("Could not connect to the database(2): " \
+ sqle.getMessage());
}
}
|
- This is a code sample for remotely accessing PostgreSQL within an ASP servelet. Pay particular attention to the bolded line in this sample and notice how the driver, server, database, user (UID) and password (pwd) references correspond to those in the JSP version above. (Be sure to employ curly braces, not parentheses, where appropriate.)
<%@ LANGUAGE = VBScript %>
<%
Dim OBJpgsqlConn, connStatus, pSQLQuery, pRSetCT
' 1. Open/Create remote PGSQL DB connnection.
Set OBJpgsqlConn = Server.CreateObject("ADODB.Connection")
' The following line is wrapped for readablility.
OBJpgsqlConn.Open("Driver={postgreSQL};
Server={linux-dev.skybuilders.com:5432};
DATABASE=mydb; UID=jesse; pwd=;")
' 2. Get all our mydb DB data.
pSQLQuery = "SELECT * FROM foo ;"
Set pRSetCT = OBJpgsqlConn.Execute(pSQLQuery)
' 3. Server Cleanup.
Set OBJpgsqlConn = nothing
OBJpgsqlConn = empty
%>
|
Back to top of page.
