Thread: 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@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 >
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@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 >
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