Thread: in(...) clause and PreparedStatement

in(...) clause and PreparedStatement

From

Sergey Pariev

Date:

13 June 2005, 10:09:16

Hi all.
    It may be stupid question, but is there way to use PreparedStatement
with queries like
SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
    I've googled but haven't found nothing explicitly said on this topic.
    I've tried the following
    PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable
WHERE t_id IN ( ? )");
    st.setObject(1,"1,2,3");

    and get error complaining on type mismatch.

    Thanks in advance, Sergey.

timestamp & graph time scale

From

santosh dwivedi

Date:

13 June 2005, 10:35:21

Hi All,
This is my very basic question.
I have follwing table in database.
        result_time         | epoch | nodeid | light |
parent | voltage
----------------------------+-------+--------+-------+--------+---------
 2004-12-09 16:44:43.501375 |     1 |        |       |
       |
 2004-12-09 16:44:43.564375 |     2 |     10 |   362 |
     0 |     479
 2004-12-09 16:44:43.626375 |     3 |     10 |   408 |
     0 |     478
 2004-12-09 16:44:43.689375 |     4 |     10 |   429 |
     0 |     478
 2004-12-09 16:44:43.751375 |     5 |     10 |   438 |


I need to plot the light Vs Time.
 For plotiing the data I am using G2Dint java API. To
plot time on x axis I need to convert time to some
integer value (suppose I  choose  x-axis scale in
minute)
How can I convert Timestamp time to an integer minute.
As my database is having data over months of period,
actually I need to plot values Vs time. Is there any
Idea so that I can plot light Vs time ( and can show
date along with time). IFAIK G2Dint takes float or
integer values to plot them along x axis and y axis.
How can I show the time on X axis (with date).
Thanks for help.
Regards:
santosh dwivedi

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: in(...) clause and PreparedStatement

From

Dave Cramer

Date:

13 June 2005, 13:07:24

Yes, because it thinks "1,2,3" is a string

you would have to do

IN(?,?,?)

then

setObject(1, 1);
setObject(2, 2);
setObject(3, 3);

Dave

On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:

> Hi all.
>    It may be stupid question, but is there way to use
> PreparedStatement with queries like
> SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
>    I've googled but haven't found nothing explicitly said on this
> topic.
>    I've tried the following
>    PreparedStatement st = conn.prepareStatement("SELECT * FROM
> mytable WHERE t_id IN ( ? )");
>    st.setObject(1,"1,2,3");
>
>    and get error complaining on type mismatch.
>
>    Thanks in advance, Sergey.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>

Re: in(...) clause and PreparedStatement

From

Csaba Nagy

Date:

13 June 2005, 13:31:01

Sergey,

Additionally to what Dave wrote you: if you want to use the prepared
statement with variable number of parameters, you can sometimes use a
query with a fixed number of parameters, and if you have more
parameters, execute it chunk-wise, if you have less parameters then set
the additional ones to null. This variant complicates your code
considerably though, but the query is prepared and reusable. I would
think it only matters if you reuse the prepared statement for a large
number of executions.

This only works if you don't have to have all the parameters processed
in one statement, i.e. you can chunk your query and the final cumulated
results will be the same.

In any other case you will not be able to use prepared statements, i.e.
you'll need to build your query each time. It is still advisable to do
it via JDBC prepared statements, and not build it directly by hand,
because that will take care for all the escaping necessary for your
parameter values. This means you should build a query with as many ?
signs as many parameters you have, and then set them in a loop or so.

HTH,
Csaba.

On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
> Yes, because it thinks "1,2,3" is a string
>
> you would have to do
>
> IN(?,?,?)
>
> then
>
> setObject(1, 1);
> setObject(2, 2);
> setObject(3, 3);
>
> Dave
>
> On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>
> > Hi all.
> >    It may be stupid question, but is there way to use
> > PreparedStatement with queries like
> > SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
> >    I've googled but haven't found nothing explicitly said on this
> > topic.
> >    I've tried the following
> >    PreparedStatement st = conn.prepareStatement("SELECT * FROM
> > mytable WHERE t_id IN ( ? )");
> >    st.setObject(1,"1,2,3");
> >
> >    and get error complaining on type mismatch.
> >
> >    Thanks in advance, Sergey.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >    (send "unregister YourEmailAddressHere" to
> > majordomo@postgresql.org)
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

Re: timestamp & graph time scale

From

Tom Lane

Date:

13 June 2005, 14:34:45

santosh dwivedi <mwanaag@yahoo.com> writes:
>  For plotiing the data I am using G2Dint java API. To
> plot time on x axis I need to convert time to some
> integer value

I'd suggest "EXTRACT(EPOCH FROM result_time)"

            regards, tom lane

Re: in(...) clause and PreparedStatement

From

Sergey Pariev

Date:

14 June 2005, 07:20:53

Thanks to all for the instant and detailed replies.

I have to say I posted simplified example of the query I need to run, in
reality I don't know how many items will be in the IN clause. I have to
do 3 queries (update, then insert ... select from this table and delete
) on particular table given the set of keys which I retrieve in the
other query, which it complex so I can't dublicate it 3 times - so I
can't just write

SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where
...).

Currently I retrieve keys at first and concatenate them into string like
1,2,3,... , then I issue my queries like

st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;

and so on. It is working as for now, but is really ugly, so I desided to
improve things and was just wandering if there a way to use variable
number of parameters.

Considering the thing you guys wrote I'm propably will rewrite my code
to use fixed number of parameters - it wouldn' t be too hard since I'm
processing it in chunks anyway. It will be only in the next version of
my app though - don't want to fix the thing which are working :).

Thanks again, Sergey.

Csaba Nagy пишет:

>Sergey,
>
>Additionally to what Dave wrote you: if you want to use the prepared
>statement with variable number of parameters, you can sometimes use a
>query with a fixed number of parameters, and if you have more
>parameters, execute it chunk-wise, if you have less parameters then set
>the additional ones to null. This variant complicates your code
>considerably though, but the query is prepared and reusable. I would
>think it only matters if you reuse the prepared statement for a large
>number of executions.
>
>This only works if you don't have to have all the parameters processed
>in one statement, i.e. you can chunk your query and the final cumulated
>results will be the same.
>
>In any other case you will not be able to use prepared statements, i.e.
>you'll need to build your query each time. It is still advisable to do
>it via JDBC prepared statements, and not build it directly by hand,
>because that will take care for all the escaping necessary for your
>parameter values. This means you should build a query with as many ?
>signs as many parameters you have, and then set them in a loop or so.
>
>HTH,
>Csaba.
>
>
>On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
>
>
>>Yes, because it thinks "1,2,3" is a string
>>
>>you would have to do
>>
>>IN(?,?,?)
>>
>>then
>>
>>setObject(1, 1);
>>setObject(2, 2);
>>setObject(3, 3);
>>
>>Dave
>>
>>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>>
>>
>>
>>>Hi all.
>>>   It may be stupid question, but is there way to use
>>>PreparedStatement with queries like
>>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
>>>   I've googled but haven't found nothing explicitly said on this
>>>topic.
>>>   I've tried the following
>>>   PreparedStatement st = conn.prepareStatement("SELECT * FROM
>>>mytable WHERE t_id IN ( ? )");
>>>   st.setObject(1,"1,2,3");
>>>
>>>   and get error complaining on type mismatch.
>>>
>>>   Thanks in advance, Sergey.
>>>
>>>---------------------------(end of
>>>broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>>   (send "unregister YourEmailAddressHere" to
>>>majordomo@postgresql.org)
>>>
>>>
>>>
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>>
>>
>
>
>

Re: in(...) clause and PreparedStatement

From

Csaba Nagy

Date:

14 June 2005, 08:30:32

Sergey,

Doing big processing in chunks is a good thing to do. It will avoid long
running transactions, and allow you to interrupt the operation if
needed.
However, there might be a better way to do it then retrieving the id's
and using "IN": use a temporary table to store the id's, and then use
the subselect query on that table. It should be fast, cause it will only
contain the id's you want to process. The "temporary" table could easily
be a permanent table, and contain more than one set of processing ids,
and a special "transactionid" field you can select on. After processing
you can delete the unnecessary rows to keep the table small, and
probably you want to vacuum it often.
The big advantage is that with this solution you can use prepared
statements, the code will be simpler, chunking is easily achieved by
only selecting so many ids to the temp table, and it is probably also
the fastest way you can process the data, as you don't have to move any
data back and forth between the server and the client.

HTH,
Csaba.


On Tue, 2005-06-14 at 12:20, Sergey Pariev wrote:
> Thanks to all for the instant and detailed replies.
>
> I have to say I posted simplified example of the query I need to run, in
> reality I don't know how many items will be in the IN clause. I have to
> do 3 queries (update, then insert ... select from this table and delete
> ) on particular table given the set of keys which I retrieve in the
> other query, which it complex so I can't dublicate it 3 times - so I
> can't just write
>
> SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where
> ...).
>
> Currently I retrieve keys at first and concatenate them into string like
> 1,2,3,... , then I issue my queries like
>
> st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;
>
> and so on. It is working as for now, but is really ugly, so I desided to
> improve things and was just wandering if there a way to use variable
> number of parameters.
>
> Considering the thing you guys wrote I'm propably will rewrite my code
> to use fixed number of parameters - it wouldn' t be too hard since I'm
> processing it in chunks anyway. It will be only in the next version of
> my app though - don't want to fix the thing which are working :).
>
> Thanks again, Sergey.
>
> Csaba Nagy пишет:
>
> >Sergey,
> >
> >Additionally to what Dave wrote you: if you want to use the prepared
> >statement with variable number of parameters, you can sometimes use a
> >query with a fixed number of parameters, and if you have more
> >parameters, execute it chunk-wise, if you have less parameters then set
> >the additional ones to null. This variant complicates your code
> >considerably though, but the query is prepared and reusable. I would
> >think it only matters if you reuse the prepared statement for a large
> >number of executions.
> >
> >This only works if you don't have to have all the parameters processed
> >in one statement, i.e. you can chunk your query and the final cumulated
> >results will be the same.
> >
> >In any other case you will not be able to use prepared statements, i.e.
> >you'll need to build your query each time. It is still advisable to do
> >it via JDBC prepared statements, and not build it directly by hand,
> >because that will take care for all the escaping necessary for your
> >parameter values. This means you should build a query with as many ?
> >signs as many parameters you have, and then set them in a loop or so.
> >
> >HTH,
> >Csaba.
> >
> >
> >On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
> >
> >
> >>Yes, because it thinks "1,2,3" is a string
> >>
> >>you would have to do
> >>
> >>IN(?,?,?)
> >>
> >>then
> >>
> >>setObject(1, 1);
> >>setObject(2, 2);
> >>setObject(3, 3);
> >>
> >>Dave
> >>
> >>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
> >>
> >>
> >>
> >>>Hi all.
> >>>   It may be stupid question, but is there way to use
> >>>PreparedStatement with queries like
> >>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
> >>>   I've googled but haven't found nothing explicitly said on this
> >>>topic.
> >>>   I've tried the following
> >>>   PreparedStatement st = conn.prepareStatement("SELECT * FROM
> >>>mytable WHERE t_id IN ( ? )");
> >>>   st.setObject(1,"1,2,3");
> >>>
> >>>   and get error complaining on type mismatch.
> >>>
> >>>   Thanks in advance, Sergey.
> >>>
> >>>---------------------------(end of
> >>>broadcast)---------------------------
> >>>TIP 2: you can get off all lists at once with the unregister command
> >>>   (send "unregister YourEmailAddressHere" to
> >>>majordomo@postgresql.org)
> >>>
> >>>
> >>>
> >>>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 6: Have you searched our list archives?
> >>
> >>               http://archives.postgresql.org
> >>
> >>
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

Re: in(...) clause and PreparedStatement

From

Sergey Pariev

Date:

14 June 2005, 10:37:18

Thanks a lot for the insightful advice !!!
I can't understand how I haven't get such design by myself - it seems so
clear now :). It will definitely improve my code and overall design a lot.

Thanks again and best regards, Sergey.

Csaba Nagy пишет:

>Sergey,
>
>Doing big processing in chunks is a good thing to do. It will avoid long
>running transactions, and allow you to interrupt the operation if
>needed.
>However, there might be a better way to do it then retrieving the id's
>and using "IN": use a temporary table to store the id's, and then use
>the subselect query on that table. It should be fast, cause it will only
>contain the id's you want to process. The "temporary" table could easily
>be a permanent table, and contain more than one set of processing ids,
>and a special "transactionid" field you can select on. After processing
>you can delete the unnecessary rows to keep the table small, and
>probably you want to vacuum it often.
>The big advantage is that with this solution you can use prepared
>statements, the code will be simpler, chunking is easily achieved by
>only selecting so many ids to the temp table, and it is probably also
>the fastest way you can process the data, as you don't have to move any
>data back and forth between the server and the client.
>
>HTH,
>Csaba.
>
>
>On Tue, 2005-06-14 at 12:20, Sergey Pariev wrote:
>
>
>>Thanks to all for the instant and detailed replies.
>>
>>I have to say I posted simplified example of the query I need to run, in
>>reality I don't know how many items will be in the IN clause. I have to
>>do 3 queries (update, then insert ... select from this table and delete
>>) on particular table given the set of keys which I retrieve in the
>>other query, which it complex so I can't dublicate it 3 times - so I
>>can't just write
>>
>>SELECT * FROM mytable WHERE t_id IN (select t_id from other_table where
>>...).
>>
>>Currently I retrieve keys at first and concatenate them into string like
>>1,2,3,... , then I issue my queries like
>>
>>st.executeUpdate("update mytable set a_field = 1 where t_id IN ("+keys+")" ;
>>
>>and so on. It is working as for now, but is really ugly, so I desided to
>>improve things and was just wandering if there a way to use variable
>>number of parameters.
>>
>>Considering the thing you guys wrote I'm propably will rewrite my code
>>to use fixed number of parameters - it wouldn' t be too hard since I'm
>>processing it in chunks anyway. It will be only in the next version of
>>my app though - don't want to fix the thing which are working :).
>>
>>Thanks again, Sergey.
>>
>>Csaba Nagy пишет:
>>
>>
>>
>>>Sergey,
>>>
>>>Additionally to what Dave wrote you: if you want to use the prepared
>>>statement with variable number of parameters, you can sometimes use a
>>>query with a fixed number of parameters, and if you have more
>>>parameters, execute it chunk-wise, if you have less parameters then set
>>>the additional ones to null. This variant complicates your code
>>>considerably though, but the query is prepared and reusable. I would
>>>think it only matters if you reuse the prepared statement for a large
>>>number of executions.
>>>
>>>This only works if you don't have to have all the parameters processed
>>>in one statement, i.e. you can chunk your query and the final cumulated
>>>results will be the same.
>>>
>>>In any other case you will not be able to use prepared statements, i.e.
>>>you'll need to build your query each time. It is still advisable to do
>>>it via JDBC prepared statements, and not build it directly by hand,
>>>because that will take care for all the escaping necessary for your
>>>parameter values. This means you should build a query with as many ?
>>>signs as many parameters you have, and then set them in a loop or so.
>>>
>>>HTH,
>>>Csaba.
>>>
>>>
>>>On Mon, 2005-06-13 at 15:07, Dave Cramer wrote:
>>>
>>>
>>>
>>>
>>>>Yes, because it thinks "1,2,3" is a string
>>>>
>>>>you would have to do
>>>>
>>>>IN(?,?,?)
>>>>
>>>>then
>>>>
>>>>setObject(1, 1);
>>>>setObject(2, 2);
>>>>setObject(3, 3);
>>>>
>>>>Dave
>>>>
>>>>On 13-Jun-05, at 9:08 AM, Sergey Pariev wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>Hi all.
>>>>>  It may be stupid question, but is there way to use
>>>>>PreparedStatement with queries like
>>>>>SELECT * FROM mytable WHERE t_id IN (1,2,3) ?
>>>>>  I've googled but haven't found nothing explicitly said on this
>>>>>topic.
>>>>>  I've tried the following
>>>>>  PreparedStatement st = conn.prepareStatement("SELECT * FROM
>>>>>mytable WHERE t_id IN ( ? )");
>>>>>  st.setObject(1,"1,2,3");
>>>>>
>>>>>  and get error complaining on type mismatch.
>>>>>
>>>>>  Thanks in advance, Sergey.
>>>>>
>>>>>---------------------------(end of
>>>>>broadcast)---------------------------
>>>>>TIP 2: you can get off all lists at once with the unregister command
>>>>>  (send "unregister YourEmailAddressHere" to
>>>>>majordomo@postgresql.org)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 6: Have you searched our list archives?
>>>>
>>>>              http://archives.postgresql.org
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose an index scan if your
>>      joining column's datatypes do not match
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>