When using the standard DB components connecting to e.g. a PostgreSQL backend, is there an event that fires between the connection being established and the query being issued?

I'm trying to send a SET TIMEZONE command to the server, to get DST correction applied to the resultset. This has to be done using the same handle as will be used for the query, otherwise the server thinks it's a different session and it has no effect.

Hello Lazarus-List,

Monday, June 13, 2011, 8:01:44 PM, you wrote:

MML> I'm trying to send a SET TIMEZONE command to the server, to get DST MML> correction applied to the resultset. This has to be done using the same MML> handle as will be used for the query, otherwise the server thinks it's a MML> different session and it has no effect.

Keep warning about that! In my life I had found a lot of troubles with that, your DST setting could be applied blindly to the data and it could result in wrong times. Many code checks DST against "now", get the offset and apply it to the time, which is wrong for any date/time different than "now".

Mark Morgan Lloyd wrote

When using the standard DB components connecting to e.g. a PostgreSQL backend, is there an event that fires between the connection being established and the query being issued?

Normally, I'd suggest TSQLConnection.AfterConnect ?

Hm.
I think for the Postgres engine, there is a connection per transaction. I don't think there is an event connected to the transaction. Joost will have to answer this one.

Michael.

Thanks Michael, I'll investigate.

I think you're right, I've "played" a lot with this in the context of using Postgres's listen/notify facility and for some things you have to use the (long-lived) connection object's handle and for others the (short-lived) query object. In the current case I've got two query objects being used alternately in a thread, I'm hoping to be able to apply the timezone setting immediately after each is reactivated so that timestamp presentation is adjusted in the resultset.

Thanks, but this is strictly read-only for data presentation, the underlying data is explicitly GMT which is why I'm not attempting to simply changes the database defaults.

Using the connection object's AfterConnect doesn't work unfortunately, the query object needs an AfterConnect that fires after the handle has been opened but before the query has been sent. Is Postgres the only backend that needs separate configuration commands like this?

I could possibly do something by fiddling with the user's default timezone immediately before and after the query object is activated, but it would be extremely messy.