Re: ANY subquery and PreparedStatements

From: Felipe Schnack <felipes(at)ritterdosreis(dot)br>
To: "Jason S(dot) Friedman" <jason(at)powerpull(dot)net>, pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: ANY subquery and PreparedStatements
Date: 2003-02-19 17:48:43
Message-ID: 1045676923.24757.97.camel@desenv1.ritterdosreis.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

You're right... but it's actually based on user interaction (i.e.:
selecting items using checkboxes)
I guess I'll just have to issue a query per item... ugly :-(
But much thanks for your help :-)

On Wed, 2003-02-19 at 14:42, Jason S. Friedman wrote:
> If the "virtually unlimited number of parameters" is itself being generated by a query, perhaps you could use an EXISTS clause:
>
> select * from <table> where EXISTS (
> SELECT 1
> FROM <othertables>, <table>
> WHERE <table.field> ...
> )
>
> On Wed, Feb 19, 2003 at 01:57:03PM -0300, Felipe Schnack wrote:
> > This is actually a very good solution to the problem... I'm sorry that
> > in my case I have a virtually unlimited number of parameters :-(
> >
> > On Wed, 2003-02-19 at 13:54, Csaba Nagy wrote:
> > > Hi Felipe,
> > >
> > > What we are doing in our code is to prepare a statement with a lot of
> > > parameter placeholders, something like:
> > >
> > > select * from <table> where <field> in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
> > >
> > > and then set some of the parameters with the available values, and set
> > > the rest to nulls. The nulls should be disregarded by the backend
> > > anyway... this works well with Oracle, I'm not sure how efficient is
> > > with Postgres.
> > > The only reason to use this is to have a prepared statement instead of
> > > building dynamic queries all the time. You only must make sure the nr.
> > > of ? signs is at least as much as many parameters you can have at
> > > maximum.
> > >
> > > HTH,
> > > Csaba.
> > >
> > > On Wed, 2003-02-19 at 13:24, Felipe Schnack wrote:
> > > > I'm afraid it kind of impossible to use PreparedStatements with the
> > > > "ANY" subquery keyword... For example, if I take this query and prepare
> > > > it:
> > > > "select * from <table> where <field> any (?)"
> > > > How can I set my parameter as a list of values, so my query searches
> > > > for for different values of the specified field?
> > > >
> > > > --
> > > >
> > > > Felipe Schnack
> > > > Analista de Sistemas
> > > > felipes(at)ritterdosreis(dot)br
> > > > Cel.: (51)91287530
> > > > Linux Counter #281893
> > > >
> > > > Centro Universitário Ritter dos Reis
> > > > http://www.ritterdosreis.br
> > > > ritter(at)ritterdosreis(dot)br
> > > > Fone/Fax.: (51)32303341
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > > >
> > >
> > >
> > --
> >
> > Felipe Schnack
> > Analista de Sistemas
> > felipes(at)ritterdosreis(dot)br
> > Cel.: (51)91287530
> > Linux Counter #281893
> >
> > Centro Universitário Ritter dos Reis
> > http://www.ritterdosreis.br
> > ritter(at)ritterdosreis(dot)br
> > Fone/Fax.: (51)32303341
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>
> --
>
> Regards,
>
> Jason S. Friedman
> mailto:jason(at)powerpull(dot)net
> http://www.powerpull.net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--

Felipe Schnack
Analista de Sistemas
felipes(at)ritterdosreis(dot)br
Cel.: (51)91287530
Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter(at)ritterdosreis(dot)br
Fone/Fax.: (51)32303341

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dirk Bromberg 2003-02-19 18:05:44 Priority of Statments / Connections
Previous Message Jason S. Friedman 2003-02-19 17:42:23 Re: ANY subquery and PreparedStatements