Re: ANY subquery and PreparedStatements

If you can reformulate your query in such a way that it will work if you
execute it multiple times with just a chunk of the total parameters, and
then sum up the results, you can group your parameters and thus limit
the max nr. of them, at the expense of having to execute the query
multiple times if the actual nr. of parameters excedes the chunk size
you choose.
Of course this will not work with all queries, but some of them can be
done like this...

Cheers,
Csaba.

On Thu, 2003-02-20 at 15:40, Felipe Schnack wrote:
> I don't think I can understand what you mean... You pass the values to
> what method/constructor?
> Anyway, I know the parameter types (Integer), but I have no idea of
> the maximum number of parameters... that's my biggest problem.
>
> On Thu, 2003-02-20 at 11:15, awc wrote:
> >
> > Hi Felipe,
> >
> > Do you know the number of parameters and types, just before you set the query?
> > I do kind of same thing with xml files, digester. I pass name, value array to
> > the method to
> > construct the prepared statement.
> >
> > .anil
> >
> >
> > Felipe Schnack wrote:
> >
> > > yes... I understood your idea... my problem is that I don't know the
> > > maximum number of parameters I'll have
> > >
> > > 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
> > > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(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
> > >
> > > ---------------------------(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>