Lists: | pgsql-jdbc |
---|
From: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | time zone setting. |
Date: | 2011-08-24 10:25:31 |
Message-ID: | 1314181531882-4729960.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
I have played about a bit with time zones, and think I am getting a grip on
them.
If I use PgAdmin to submit queries, I can put
set time zone 'us/hawaii';
in front of some other queries, and they will operate as I expect. In fact I
find the change persist across multiple PgAdmin queries, so it is obviously
a "session setting"
I'm aware that I can change the JVM timezone suing setdefault.
But I can't put the "set time zone" stretring on the front of a jdbc
preparedStatement - it just barfs.
So the question is - can I, from a java program, set the time zone for the
database [ not for the jvm ].
--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4729960.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
From: | Maciek Sakrejda <msakrejda(at)truviso(dot)com> |
---|---|
To: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-24 17:27:37 |
Message-ID: | CAH_hXRZLOF=ZBGip1kY36ciRHBPDzcw4hZHMvEAEXEqPKyvCrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
> in front of some other queries, and they will operate as I expect. In fact I
> find the change persist across multiple PgAdmin queries, so it is obviously
> a "session setting"
Right (you can use SET LOCAL for this-transaction-only changes).
> But I can't put the "set time zone" stretring on the front of a jdbc
> preparedStatement - it just barfs.
You can't just prepend it to the statement text because JDBC prepared
statements need to correspond to a single SQL statement. But you
should be able to issue a separate "set time zone" statement (either
normal or prepared) and get the session behavior you see through
pgAdmin. Does that not work? What is the error?
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
From: | Oliver Jowett <oliver(at)opencloud(dot)com> |
---|---|
To: | Maciek Sakrejda <msakrejda(at)truviso(dot)com> |
Cc: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com>, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-24 21:42:44 |
Message-ID: | CA+0W9LPqALfrHfPSUvh=eLDEQ0ZARKaNmbM0RNgYVdzH-E7rrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
On 25 August 2011 05:27, Maciek Sakrejda <msakrejda(at)truviso(dot)com> wrote:
> You can't just prepend it to the statement text because JDBC prepared
> statements need to correspond to a single SQL statement.
Actually, the driver is happy to handle multiple semicolon-separated
SQL statements in a single PreparedStatement.
(No idea what's going wrong with the timezone stuff without an error to look at)
Oliver
From: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-30 09:51:25 |
Message-ID: | 1314697885778-4749496.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Well I know I tried it and I know it barfed.
I'll try both methods again.
Thanks all
David
> Actually, the driver is happy to handle multiple semicolon-separated
> SQL statements in a single PreparedStatement.
> (No idea what's going wrong with the timezone stuff without an error to
> look at)
>
--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4749496.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
From: | Kris Jurka <books(at)ejurka(dot)com> |
---|---|
To: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-30 17:21:35 |
Message-ID: | alpine.BSO.2.00.1108301249310.10923@leary.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
On Tue, 30 Aug 2011, dexdyne wrote:
> Well I know I tried it and I know it barfed.
>
I think your problem is that not all postgresql commands can take
parameters. For example you cannot do "SET TIMEZONE = ?" with a prepared
statement. Instead you can do "SELECT set_config('timezone', ?,
true/false)" with a prepared statement.
Kris Jurka
From: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-31 09:36:45 |
Message-ID: | 1314783405027-4753538.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
Kris,
Good Lord, how difficult this stuff is :-) I really don't do databases as my
main living - and it's darned hard to learn enough to get a proper grip.
Thank you for your suggestion - I had assumed the substitutions for ? chars
were simply done by search-and-replace, so the context wasn't important. Now
I know better.
--------
as an aside, to aid my learnign process...
1. "you cannot do "SET TIMEZONE = ?" with a prepared statement"
Where would I read up on what can and cannot be done with prepared
statement? Are we agreeijng that it has to be just what would be separated
by semi-colons interactively - in other words one statement per jdbc
execute? Or is the other comment right that sometimes you can include
semicolon-separated actions within a single preparedStatement
2. Any idea what tokens can appear as the first parameter to set_config? I
tried searching the on-line manual for a list but couldn't find one.
TVM
David
--
View this message in context: http://postgresql.1045698.n5.nabble.com/time-zone-setting-tp4729960p4753538.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
From: | Maciek Sakrejda <msakrejda(at)truviso(dot)com> |
---|---|
To: | dexdyne <from_postgresql_forum(at)dexdyne(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: time zone setting. |
Date: | 2011-08-31 18:47:08 |
Message-ID: | CAH_hXRY-67mbeuiye5Wfiywya6aP8Bks5+NjptjdxqwmHg6f9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-jdbc |
> Or is the other comment right that sometimes you can include
semicolon-separated actions within a single preparedStatement
I haven't tried it, but I'd trust Oliver on this one. I was thinking of the
underlying wire-level extended query protocol, which requires a single
"statement" per "query".
> Any idea what tokens can appear as the first parameter to set_config? I
tried searching the on-line manual for a list but couldn't find one.
Any server settings (
http://www.postgresql.org/docs/9.0/static/runtime-config.html ) that are
updatable at runtime. For a quick reference, you can run:
select name, short_desc, extra_desc from pg_settings where context = 'user';
(context determines when you can change it).
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com