Support for database specific SQL config

[Collin Peters <ca...@gmail.com>]

Hello again,

I am wondering if iBatis has any kind of support for database level
SQL config.  I am using PostgreSQL as my dbms and it has the ability
to set certain values to be used in the SQL 'session'.  For example,
if I am in a different timezone than the server, the call to 'SELECT
CURRENT_TIME' (or SELECT now()) should return my current time, not the
servers current time.

http://www.postgresql.org/docs/8.1/interactive/sql-set.html

In an old PHP project I had, I included this SQL in an abstraction
layer.  i.e. I would call db_query("SELECT ...")  instead of
pg_query("SELECT ...").   In the db_query abstraction call I would
prepend the following SQL "SET TIME ZONE 'PST8PDT;".  So the full SQL
executed would be:
SET TIME ZONE 'PST8PDT;
SELECT ...

PostgreSQL would then run that query as if it were in my timezone.
Hopefully that is clear.  I am wondering if iBatis has any
functionality built in for this.  I could not find anything in the
docs about this and didn't know where to begin for searching for it
either.  I am hoping there is some iBatis magic that makes this all
easier.  It would be a nightmare to have to manually add that bit of
SQL to every other piece of SQL I have.

Regards,
Collin

Re: Support for database specific SQL config

[Chris Lamey <cl...@localmatters.com>]

I don't know offhand, but I would a) first see if the postgres JDBC can
support what you want (because if it can't, iBATIS can't either) and b)
try a simple throwaway app with iBATIS to see what happens.

Personally, I would try another approach, using session and set like
that isn't something I've seen before in the Java world.  The postgres
now() function returns with a timezone (if your column is setup to use a
timezone), so I usually just have a SimpleDateFormat'er in a higher
layer that takes the returned value from the db and deals with it locale
specific.  That way if you cache a date from the db via iBATIS, it can
be interpreted into multiple locales.

Cheers,
Chris

On Thu, 2007-03-15 at 16:13 -0700, Collin Peters wrote:
> Hello again,
> 
> I am wondering if iBatis has any kind of support for database level
> SQL config.  I am using PostgreSQL as my dbms and it has the ability
> to set certain values to be used in the SQL 'session'.  For example,
> if I am in a different timezone than the server, the call to 'SELECT
> CURRENT_TIME' (or SELECT now()) should return my current time, not the
> servers current time.
> 
> http://www.postgresql.org/docs/8.1/interactive/sql-set.html
> 
> In an old PHP project I had, I included this SQL in an abstraction
> layer.  i.e. I would call db_query("SELECT ...")  instead of
> pg_query("SELECT ...").   In the db_query abstraction call I would
> prepend the following SQL "SET TIME ZONE 'PST8PDT;".  So the full SQL
> executed would be:
> SET TIME ZONE 'PST8PDT;
> SELECT ...
> 
> PostgreSQL would then run that query as if it were in my timezone.
> Hopefully that is clear.  I am wondering if iBatis has any
> functionality built in for this.  I could not find anything in the
> docs about this and didn't know where to begin for searching for it
> either.  I am hoping there is some iBatis magic that makes this all
> easier.  It would be a nightmare to have to manually add that bit of
> SQL to every other piece of SQL I have.
> 
> Regards,
> Collin

Re: Support for database specific SQL config

[Jeff Butler <je...@gmail.com>]

You could accomplish this by writing your own implementation for the
<dataSource> element.  Your implementation could delegate to some existing
implementation, but set up the connection before returning it to iBATIS.

Jeff Butler



On 3/15/07, Collin Peters <ca...@gmail.com> wrote:
>
> Hello again,
>
> I am wondering if iBatis has any kind of support for database level
> SQL config.  I am using PostgreSQL as my dbms and it has the ability
> to set certain values to be used in the SQL 'session'.  For example,
> if I am in a different timezone than the server, the call to 'SELECT
> CURRENT_TIME' (or SELECT now()) should return my current time, not the
> servers current time.
>
> http://www.postgresql.org/docs/8.1/interactive/sql-set.html
>
> In an old PHP project I had, I included this SQL in an abstraction
> layer.  i.e. I would call db_query("SELECT ...")  instead of
> pg_query("SELECT ...").   In the db_query abstraction call I would
> prepend the following SQL "SET TIME ZONE 'PST8PDT;".  So the full SQL
> executed would be:
> SET TIME ZONE 'PST8PDT;
> SELECT ...
>
> PostgreSQL would then run that query as if it were in my timezone.
> Hopefully that is clear.  I am wondering if iBatis has any
> functionality built in for this.  I could not find anything in the
> docs about this and didn't know where to begin for searching for it
> either.  I am hoping there is some iBatis magic that makes this all
> easier.  It would be a nightmare to have to manually add that bit of
> SQL to every other piece of SQL I have.
>
> Regards,
> Collin
>