I also had to hack the JDBC driver to use timestamp (which is the
appropriate mapping for java.util.Date). Apparently the JDBC driver
receives timestamps as strings in one of 5 billion formats, so it needs
to figure out which format to convert back into a long. There is a
discussion about this problem on the PostgreSQL jdbc dev list going back
a year. I'm guessing that the format changes depending on what OS the
server is running on, whether the hardware clock is kept in GMT, etc. I
have the most vanilla RH7 installation you can imagine, and I'm in
California (along with the people in Berkeley who wrote PostgreSQL), yet
neither the 7.0.2 nor the CVS version of the JDBC driver could handle my
timestamps.
I had to modify
pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java, the
method getTimestamp(). I've included all the code for it just so you
can see what crap this is (yes I'm a little bitter). My modifications
are labeled with my name (Jeff Schnitzer).
This modification may or may not be necessary for you; it's obvious from
reading the mailing list (and the code) that nobody working on the JDBC
driver has any clue what PostgreSQL is supposed to send and that the
code was built up via "<hack hack hack>... hmm, this works for me". I
found that in GMT-08:00, my string was one character longer than anyone
else's, so my hack didn't seem to disturb anything.
Note that this is from a CVS copy.
Also note that this code has got to be hideously slow. Why are they
putting strings on the wire?!?!?
All that said, PostgreSQL works, and it's free. I haven't gone into
production yet, so I don't know how it will perform.
--- BEGIN ---
public Timestamp getTimestamp(int columnIndex) throws SQLException
{
String s = getString(columnIndex);
if(s==null)
return null;
// This works, but it's commented out because Michael Stephenson's
// solution is better still:
//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// Modification by Jan Thomae
String sub = s.substring(s.length() - 3, s.length()-2);
if (sub.equals("+") || sub.equals("-")) {
s = s.substring(0, s.length()-3) + "GMT"+
s.substring(s.length()-3,
s.length())+":00";
}
// -------
// Michael Stephenson's solution:
SimpleDateFormat df = null;
// Modification by Jeff Schnitzer to make it work on his box dammit
if (s.length()>28)
{
df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzzzzzzzz");
} else
// -------
// Modification by Jan Thomae
if (s.length()>27) {
df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
} else
// -------
if (s.length()>21 && s.indexOf('.') != -1) {
df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
} else if (s.length()>19 && s.indexOf('.') == -1) {
df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
} else if (s.length()>19 && s.indexOf('.') != -1) {
df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
} else if (s.length()>10 && s.length()<=18) {
df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
} else {
df = new SimpleDateFormat("yyyy-MM-dd");
}
try {
return new Timestamp(df.parse(s).getTime());
} catch(ParseException e) {
throw new PSQLException("postgresql.res.badtimestamp",new
Integer(e.getError
Offset()),s);
}
}
--- END ---
>-----Original Message-----
>From: Sach Jobb [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, January 17, 2001 10:08 AM
>To: Orion-Interest
>Subject: RE: Is combining Orion and PostgreSQL a good choice
>
>
>We had a problem with either timestamp or date (i can't remember which
>now) with the default postgres JDBC driver, so we had to hack
>the driver
>to make it work right. So if you don't need anything to do
>with time and
>you don't need to store large binary data you should be fine. ;)
>
>Actually, it works quite well. We deployed a couple of months
>ago with 33
>million records in the database (4 processor, 4GB RAM, Sun Server) and
>orion on two application boxes (dual processor, 4GB sun servers). It's
>incredibly fast.
>
>Thanks,
>sach
>robot6
>
>%s/windows/linux/g
>
>On Wed, 17 Jan 2001, Randahl Fink Isaksen wrote:
>
>> Interesting... but are you stating that without fixing bugs
>in the JDBC
>> driver for PostgreSQL, it just will not work? If so, I would
>of course like
>> to hear what you fixed exactly...
>>
>> Yours
>>
>> Randahl
>>
>> -----Original Message-----
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED]]On Behalf Of Mike
>> Cannon-Brookes
>> Sent: 17. januar 2001 13:51
>> To: Orion-Interest
>> Subject: RE: Is combining Orion and PostgreSQL a good choice
>>
>>
>> I'm using PostgreSQL on an application that is about to go
>live (within the
>> next month). Seems to be working fine in testing so far,
>we're moving off
>> Sybase 11.0.3.3 on Linux to PostgreSQL (probably 7.1 by the
>time we go
>> live).
>>
>> I've personally fixed a bug or two in the JDBC driver, but
>other than that
>> the latest driver seems very solid.
>>
>> I use the default schema (the latest one from Orion has a
>lot of fixes
>> contributed by myself and others, the original was
>horrible). Let me know if
>> there are any problems with the latest schema.
>>
>> -mike
>>
>> > -----Original Message-----
>> > From: [EMAIL PROTECTED]
>> > [mailto:[EMAIL PROTECTED]]On Behalf Of
>Randahl Fink
>> > Isaksen
>> > Sent: Wednesday, January 17, 2001 11:01 PM
>> > To: Orion-Interest
>> > Subject: Is combining Orion and PostgreSQL a good choice
>> >
>> >
>> > I am wondering if using PostgreSQL with Orion would be a good
>> > option. So my
>> > questions are:
>> >
>> > 1. Has anyone tried using PostgreSQL with Orion in a
>real-life application
>> > (running for a long period with no errors) - and was it a success?
>> >
>> > 2. Do you use the default database schema for PostgreSQL
>that comes with
>> > Orion?
>> >
>> >
>> > Yours
>> >
>> > Randahl
>> >
>> >
>> >
>> >
>> >
>>
>>
>>
>>
>
>
>
