Java and Postgres95

First in a series of articles detailing the creation of a Java interface to Postgres95.
Hoop #3: You Can't Get a Stream's FILE*; (trace() and formatTuples() Explained)

This is one hoop I think the JavaSoft team should've solved for us. There is simply no way to get a FILE * (or a file descriptor) from a FileStream. PQtrace() expects a FILE *, so we simply open one, based on a filename passed in by the user. We check to see if it's “stdout” or “stderr”, and act accordingly.

We see the problem again when we try to implement Postgres95's printTuples (or displayTuples for 1.1). It also expects a FILE*, but this time the solution is a little messier. Here, we want the output in a String, so we open a temporary file, send it to the libpq function, rewind it, read it, and close it. This is pretty messy, but it does work, and is actually pretty quick about it. If we wanted to write a cleaner version, we could certainly rewrite displayTuples() completely in Java code, using PGResult's native methods fname() and getValue() that we have already defined.

The Finish Line

:

After writing all the C code, we are ready to generate our shared library.

First, we have to compile the .c files:

gcc -O2 -fPIC -I/usr/local/java/include/  \
              -I/usr/local/java/include/solaris \
              -c PGConnectionNative.c
gcc ...                                (repeat for each .c file)

Then we link them:

gcc -shared -Wl,-soname,libJgres.so.1
-o libJgres.so.1.0 *.o -lpq

The -lpq tells the dynamic loader to load libpq.so when Java loads this library.

And finally, put them somewhere the dynamic loader can find them (in your LD_LIBRARY_PATH, or in a standard location (i.e. /usr/local/lib) and rerun /sbin/ldconfig -v).

That's all there is to it. Now we can use PGConnection and PGResult just like any other Java classes.

A Simple libJgres Example

To finish up this section, let's use our new classes to implement a simple SQL client. The client will connect to a database “foo” and accept query strings from standard input. PGConnection.exec() will process the queries, and print the results to the terminal using formatTuples(). The connection to the database is made on line 17 in Listing 3 (QueryTest.java).

We use the libpq convention of sending NULL (the empty Java string "" translates into a NULL char *) for any parameters we don't know. Notice that the call to PGConnection's constructor is surrounded by a “try” block. If an exception is thrown within this block, we have a problem with the connection and exit nicely (lines 54-58, Listing 3).

At line 24 of Listing 3, we test some of the simple functions to print out information about what we're connected to. We then read a query string and quit if it is “q” or “Q”.

We process the query on line 33 of Listing 3, by calling exec(). Note that we nest another “try” block here, because if we get a PostgresException on an exec(), we want to simply print the error and continue (we handle the exception on lines 43-46). If we reach line 34, we know that the PGResult is valid. We check to see if it returned any tuples, and use formatTuples() to print them if it did. If not, we simply print the current status and continue.

Conclusion

In this segment, we've shown how to create simple Java wrappers for C library functions. In the next installment, we'll show how to use Java's Streams to wrap Postgres95's Large Objects, and finally, we'll create a multi-threaded interface to its Asynchronous Notification system.

Charles “Bill” Binko graduated from the University of Florida with a BS in Computer Engineering in 1994. Currently a software engineer in Atlanta, GA, he has been a Linux enthusiast since 1993. His main computer interests are in simulation, genetic algorithms and distributed programming, and he finds Java an excellent platform for all of these.

______________________

Geek Guide
The DevOps Toolbox

Tools and Technologies for Scale and Reliability
by Linux Journal Editor Bill Childers

Get your free copy today

Sponsored by IBM

Upcoming Webinar
8 Signs You're Beyond Cron

Scheduling Crontabs With an Enterprise Scheduler
11am CDT, April 29th
Moderated by Linux Journal Contributor Mike Diehl

Sign up now

Sponsored by Skybot