Thread: Pass column name in preparedstatement

Pass column name in preparedstatement

From
Antony Paul
Date:
Hi all,
    Is it possible to pass column name to a PreparedStatement as a
parameter. Or should I use Statement.

rgds
Antony Paul

Re: Pass column name in preparedstatement

From
Oliver Jowett
Date:
Antony Paul wrote:
> Hi all,
>     Is it possible to pass column name to a PreparedStatement as a
> parameter.

No, you can only use parameter placeholders in places which the backend
grammar allows parameters (namely, in expressions). Column names aren't
one of those places.

> Or should I use Statement.

You can use either Statement or PreparedStatement so long as you insert
the column name into the SQL query string yourself before giving it to
the driver.

-O

Re: Pass column name in preparedstatement

From
Antony Paul
Date:
In 7.3.3 is there any use in using PreparedStatement since the columns
may change for each query.

rgds
Antony Paul


On Wed, 16 Feb 2005 00:54:09 +1300, Oliver Jowett <oliver@opencloud.com> wrote:
> Antony Paul wrote:
> > Hi all,
> >     Is it possible to pass column name to a PreparedStatement as a
> > parameter.
>
> No, you can only use parameter placeholders in places which the backend
> grammar allows parameters (namely, in expressions). Column names aren't
> one of those places.
>
> > Or should I use Statement.
>
> You can use either Statement or PreparedStatement so long as you insert
> the column name into the SQL query string yourself before giving it to
> the driver.
>
> -O
>

Re: Pass column name in preparedstatement

From
Thomas Hallgren
Date:
Antony Paul wrote:
> In 7.3.3 is there any use in using PreparedStatement since the columns
> may change for each query.
>
Depends on the use case. I created a solution a while back where I used
a MRU cache to hold the 10 or so most frequently generated
PreparedStatements. In my case that improved performance a whole lot.
This was not a PostgreSQL database though but I guess the effect could
be similar.

Regards,
Thomas Hallgren


Re: Pass column name in preparedstatement

From
Oliver Jowett
Date:
Antony Paul wrote:
> In 7.3.3 is there any use in using PreparedStatement since the columns
> may change for each query.

It depends on your application; are there non-column-name parameters? If
so, using PreparedStatement still gives you the benefit that you do not
have to escape those parameters by hand.

Under 7.3.x there's no real performance benefit to reusing
PreparedStatements, as the v3 protocol (supporting server-side query
parameterization and reuse) was only introduced in 7.4. Under 7.4 or
8.0, there is some scope for improved performance if you reuse
PreparedStatements. As your query strings are changing, you may want to
have a cache to preserve a PreparedStatement for frequently used queries.

You really should upgrade if you're still using 7.3.3, at least to the
latest 7.3.x.

-O

Re: Pass column name in preparedstatement

From
Antony Paul
Date:
The one glitch I have with 7.3.3 is with functional index and it is
solved in 8.x only. But the client is not ready to upgrade to 8.x
series until it ages 4-6 months. So I dont find any use in upgrading.
I have to consider the work invloved in upgrading.

rgds
Antony Paul


On Wed, 16 Feb 2005 11:59:56 +1300, Oliver Jowett <oliver@opencloud.com> wrote:
> Antony Paul wrote:
> > In 7.3.3 is there any use in using PreparedStatement since the columns
> > may change for each query.
>
> It depends on your application; are there non-column-name parameters? If
> so, using PreparedStatement still gives you the benefit that you do not
> have to escape those parameters by hand.
>
> Under 7.3.x there's no real performance benefit to reusing
> PreparedStatements, as the v3 protocol (supporting server-side query
> parameterization and reuse) was only introduced in 7.4. Under 7.4 or
> 8.0, there is some scope for improved performance if you reuse
> PreparedStatements. As your query strings are changing, you may want to
> have a cache to preserve a PreparedStatement for frequently used queries.
>
> You really should upgrade if you're still using 7.3.3, at least to the
> latest 7.3.x.
>
> -O
>

Re: Pass column name in preparedstatement

From
Oliver Jowett
Date:
Antony Paul wrote:

> On Wed, 16 Feb 2005 11:59:56 +1300, Oliver Jowett <oliver@opencloud.com> wrote:
>
>>> You really should upgrade if you're still using 7.3.3, at least to the
>>> latest 7.3.x.

> The one glitch I have with 7.3.3 is with functional index and it is
> solved in 8.x only. But the client is not ready to upgrade to 8.x
> series until it ages 4-6 months. So I dont find any use in upgrading.
> I have to consider the work invloved in upgrading.

There are data-loss bugs in 7.3.3 that have been fixed in later 7.3.x
releases. You don't need to upgrade to 7.4/8.0 to fix these. See the
7.3-series release notes for details.

-O