Tuesday, August 22, 2006

Remote access to a PostgesQL server

A quick note to myself in case I have to do this again, or in case it helps someone else get through the same task more quickly. Gotchas to watch out for when enabling access to a PostgresQL server from another machine:

  • The line in postgres.conf that says listen_addresses = 'localhost' needs to be changed to the actual hostname of the server, because localhost only enables listening on the loopback interface (i.e. the server can listen to connections from clients running locally, but not on any other machines)
  • To allow connection from any host on the same network, add a line to the pg_hba.conf saying: host all all password
    This says: allow access from any (remote) host, where any user (first all) can access any database (second all) if identified by password, and the IP address of the remote host begins 16. - counting the first eight (from the /8) binary digits as significant. The non-matched digits have to be zero, hence the 0.0.0 at the end.

Friday, August 18, 2006

Oracle error messages: could try harder

I've not been able to find a way of loading a complete Oracle PL/SQL script via a JDBC connection (i.e. analogous to using @ in sqlplus). So, least worst thing to do (it's a small script) is to load the script into a string, and execute that string with Statement.executeUpdate. One. Statement. At. A. Time. Sigh.

Nevertheless, things are going OK until I take a line of my script which works when invoked by @, but from JDBC I see:

java.sql.SQLException: ORA-00911: invalid character
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)

"ORA-00911 Invalid character." That's all you get. Which character for pity's sake? And whereabouts in the string? Surely that's not asking too much?

The answer, by the way, is that the trailing semi-colon that statements require in sqlplus is an error when executing statements via JDBC.