Re: Pass column name in preparedstatement

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

rgds
Antony Paul

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

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(at)opencloud(dot)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
>

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

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

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(at)opencloud(dot)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
>

Antony Paul wrote:

> On Wed, 16 Feb 2005 11:59:56 +1300, Oliver Jowett <oliver(at)opencloud(dot)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