Thread: ANY subquery and PreparedStatements
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@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
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?
>
>
>
Hello,
You can use a helper variable to store all your values as a string, and
then pass it
to prepared statement, it works for me with IN( ) and I think will work
as well for ANY
example code:
String categories = "";
for( int i = 0; i < someCategoryIDs.length; i++ ) {
if( i > 0 ) { categories = categories + ","; }
categories = categories + " " + someCategoryIDs[i];
}
pstmt = connection.prepareStatement( SELECT * FROM catalog WHERE cat_id
IN (" + categories + ") "
Hope it helps
Best Regards,
Pavel Fokin,
Software Engineer,
www.openvillage.info
Yes, this would work... but it's not very pretty :-)
JDBC's preparedstatements should have *some way* to handle that...
simply makeing impossible to use such an useful keyword is quite bad...
Or jdbc doesnt have it?
On Wed, 2003-02-19 at 09:49, Pavel Fokin wrote:
> 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?
> >
> >
> >
> Hello,
>
> You can use a helper variable to store all your values as a string, and
> then pass it
> to prepared statement, it works for me with IN( ) and I think will work
> as well for ANY
>
> example code:
>
> String categories = "";
> for( int i = 0; i < someCategoryIDs.length; i++ ) {
> if( i > 0 ) { categories = categories + ","; }
> categories = categories + " " + someCategoryIDs[i];
> }
>
> pstmt = connection.prepareStatement( SELECT * FROM catalog WHERE cat_id
> IN (" + categories + ") "
>
> Hope it helps
>
> Best Regards,
> Pavel Fokin,
>
> Software Engineer,
> www.openvillage.info
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893
Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341
how about
public String setCommand(String table,String filedName1)
{
StringBuffer sb = new StringBuffer("Select * from table ");
sb.append(table);
sb.append(" where ");
sb.append(filedName1);
sb.append(" any (?)");
return sb.toSting();
}
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@ritterdosreis.br
> Cel.: (51)91287530
> Linux Counter #281893
>
> Centro Universitário Ritter dos Reis
> http://www.ritterdosreis.br
> ritter@ritterdosreis.br
> Fone/Fax.: (51)32303341
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
My two cents: it would be great if Java had a built-in (JDK) method that did the same thing as the Perl join command: @array = ( 'apple', 'banana', 'orange' ); $fruit = join( ',', @array ); # result is 'apple,banana,orange' On Wed, Feb 19, 2003 at 09:56:47AM -0300, Felipe Schnack wrote: > Yes, this would work... but it's not very pretty :-) > JDBC's preparedstatements should have *some way* to handle that... > simply makeing impossible to use such an useful keyword is quite bad... > Or jdbc doesnt have it? > > On Wed, 2003-02-19 at 09:49, Pavel Fokin wrote: > > 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?
I agree, and you can find it in lots of languages... even Visual Basic. Anyway, wouldn't help my problem, unfortunately :-))) On Wed, 2003-02-19 at 11:57, Jason S. Friedman wrote: > My two cents: it would be great if Java had a built-in (JDK) method that did the same thing as the Perl join command: > > @array = ( 'apple', 'banana', 'orange' ); > $fruit = join( ',', @array ); > # result is 'apple,banana,orange' > > On Wed, Feb 19, 2003 at 09:56:47AM -0300, Felipe Schnack wrote: > > Yes, this would work... but it's not very pretty :-) > > JDBC's preparedstatements should have *some way* to handle that... > > simply makeing impossible to use such an useful keyword is quite bad... > > Or jdbc doesnt have it? > > > > On Wed, 2003-02-19 at 09:49, Pavel Fokin wrote: > > > 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? > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
I Guess you don't understand my problem.. my problem is that I would
like to use preparedstatements with this query, but I would like to set
a list of values for the any parameter... In other words I would like to
execute, in the end a query like that:
select * from <table> where <field> any (1,2,3)
But I don't think I can do that using prep. statements... am i wrong?
On Wed, 2003-02-19 at 11:56, awc wrote:
> how about
>
> public String setCommand(String table,String filedName1)
> {
> StringBuffer sb = new StringBuffer("Select * from table ");
> sb.append(table);
> sb.append(" where ");
> sb.append(filedName1);
> sb.append(" any (?)");
> return sb.toSting();
> }
>
> 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@ritterdosreis.br
> > Cel.: (51)91287530
> > Linux Counter #281893
> >
> > Centro Universitário Ritter dos Reis
> > http://www.ritterdosreis.br
> > ritter@ritterdosreis.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@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893
Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone/Fax.: (51)32303341
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@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Centro Universitário Ritter dos Reis > > http://www.ritterdosreis.br > > ritter@ritterdosreis.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@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
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@ritterdosreis.br
> > > Cel.: (51)91287530
> > > Linux Counter #281893
> > >
> > > Centro Universitário Ritter dos Reis
> > > http://www.ritterdosreis.br
> > > ritter@ritterdosreis.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@ritterdosreis.br
> Cel.: (51)91287530
> Linux Counter #281893
>
> Centro Universitário Ritter dos Reis
> http://www.ritterdosreis.br
> ritter@ritterdosreis.br
> Fone/Fax.: (51)32303341
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Regards,
Jason S. Friedman
mailto:jason@powerpull.net
http://www.powerpull.net
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 EXISTSclause: > > 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@ritterdosreis.br > > > > Cel.: (51)91287530 > > > > Linux Counter #281893 > > > > > > > > Centro Universitário Ritter dos Reis > > > > http://www.ritterdosreis.br > > > > ritter@ritterdosreis.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@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Centro Universitário Ritter dos Reis > > http://www.ritterdosreis.br > > ritter@ritterdosreis.br > > Fone/Fax.: (51)32303341 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > -- > > Regards, > > Jason S. Friedman > mailto:jason@powerpull.net > http://www.powerpull.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
> 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 :-( Why can't you simply build the prepared statement on the fly based on the number of values to be placed in the IN list? If you have 3 items, you just append three '?' and if you have 20 items, then you append 20 '?' to your list. This way the number of '?' will match the number of PreparedStatement.setXXX() calls and you don't have to rely on the database handling all the nulls. Since the list is fairly dynamic anyway, it's overall performance will be about the same (and it's much better than doing a series of queries instead). Of course, there are limits on how long a query can be for most databases, so you may find that if the list is too long, you'll run into query processor problems. David
This is impossible because of the framework we use here... The SQLs aren't stored in java code, but in a separate XML file On Wed, 2003-02-19 at 15:12, David Wall wrote: > > 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 :-( > > Why can't you simply build the prepared statement on the fly based on the > number of values to be placed in the IN list? If you have 3 items, you just > append three '?' and if you have 20 items, then you append 20 '?' to your > list. This way the number of '?' will match the number of > PreparedStatement.setXXX() calls and you don't have to rely on the database > handling all the nulls. Since the list is fairly dynamic anyway, it's > overall performance will be about the same (and it's much better than doing > a series of queries instead). > > Of course, there are limits on how long a query can be for most databases, > so you may find that if the list is too long, you'll run into query > processor problems. > > David > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
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@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Centro Universitário Ritter dos Reis > http://www.ritterdosreis.br > ritter@ritterdosreis.br > Fone/Fax.: (51)32303341 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
The reason we didn't do it this way is that our queries were part of a batch processing, and the input data was chunked anyway so most of the times the exact nr. of parameters (the maximum one) was set. the other advantage was easier audit of the query execution log, having just 1 query for this operation instead of 100s. That being said, dynamic queries are more elegant. Cheers, Csaba. On Wed, 2003-02-19 at 19:12, David Wall wrote: > > 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 :-( > > Why can't you simply build the prepared statement on the fly based on the > number of values to be placed in the IN list? If you have 3 items, you just > append three '?' and if you have 20 items, then you append 20 '?' to your > list. This way the number of '?' will match the number of > PreparedStatement.setXXX() calls and you don't have to rely on the database > handling all the nulls. Since the list is fairly dynamic anyway, it's > overall performance will be about the same (and it's much better than doing > a series of queries instead). > > Of course, there are limits on how long a query can be for most databases, > so you may find that if the list is too long, you'll run into query > processor problems. > > David > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
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@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Centro Universitário Ritter dos Reis > > http://www.ritterdosreis.br > > ritter@ritterdosreis.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@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
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@ritterdosreis.br > > > Cel.: (51)91287530 > > > Linux Counter #281893 > > > > > > Centro Universitário Ritter dos Reis > > > http://www.ritterdosreis.br > > > ritter@ritterdosreis.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@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Centro Universitário Ritter dos Reis > http://www.ritterdosreis.br > ritter@ritterdosreis.br > Fone/Fax.: (51)32303341 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
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@ritterdosreis.br > > > > Cel.: (51)91287530 > > > > Linux Counter #281893 > > > > > > > > Centro Universitário Ritter dos Reis > > > > http://www.ritterdosreis.br > > > > ritter@ritterdosreis.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@ritterdosreis.br > > Cel.: (51)91287530 > > Linux Counter #281893 > > > > Centro Universitário Ritter dos Reis > > http://www.ritterdosreis.br > > ritter@ritterdosreis.br > > Fone/Fax.: (51)32303341 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > -- Felipe Schnack Analista de Sistemas felipes@ritterdosreis.br Cel.: (51)91287530 Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br ritter@ritterdosreis.br Fone/Fax.: (51)32303341
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@ritterdosreis.br > > > > > Cel.: (51)91287530 > > > > > Linux Counter #281893 > > > > > > > > > > Centro Universitário Ritter dos Reis > > > > > http://www.ritterdosreis.br > > > > > ritter@ritterdosreis.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@ritterdosreis.br > > > Cel.: (51)91287530 > > > Linux Counter #281893 > > > > > > Centro Universitário Ritter dos Reis > > > http://www.ritterdosreis.br > > > ritter@ritterdosreis.br > > > Fone/Fax.: (51)32303341 > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > -- > > Felipe Schnack > Analista de Sistemas > felipes@ritterdosreis.br > Cel.: (51)91287530 > Linux Counter #281893 > > Centro Universitário Ritter dos Reis > http://www.ritterdosreis.br > ritter@ritterdosreis.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 >