You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Borut Bolčina <bo...@gmail.com> on 2008/05/30 15:27:14 UTC
delete from ... where ... in (...)
Hi,
what is the C3M4 prefered way of doing this SQL equivalent: DELETE FROM
Artist WHERE Oid IN (1,4,8,9);
Thanks,
Borut
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
On May 30, 2008, at 4:51 PM, Michael Gentry wrote:
> Ah, cool. I've really not looked at the EJBQL stuff at all. What are
> the semantics of using EJBQLQuery to delete records with regard to the
> Cayenne cache/object graph/etc?
Same as SQLTemplate... Although I hope we can implement targeted
invalidation of the cached objects over time.
Andrus
Re: delete from ... where ... in (...)
Posted by Michael Gentry <bl...@gmail.com>.
Ah, cool. I've really not looked at the EJBQL stuff at all. What are
the semantics of using EJBQLQuery to delete records with regard to the
Cayenne cache/object graph/etc?
/dev/mrg
On Fri, May 30, 2008 at 9:46 AM, Andrus Adamchik <an...@objectstyle.org> wrote:
> See my other reply - there is actually an EJBQL delete.
>
>
> On May 30, 2008, at 4:27 PM, Borut Bolčina wrote:
>
>> Hi,
>>
>> what is the C3M4 prefered way of doing this SQL equivalent: DELETE FROM
>> Artist WHERE Oid IN (1,4,8,9);
>>
>> Thanks,
>> Borut
>
>
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
Scratch that. It was a bug [1]. We couldn't reproduce it on HSQLDB,
but when I started testing unrelated things on SQLServer, our unit
test blew up. Anyways, it is fixed on trunk already.
Thanks,
Andrus
[1] https://issues.apache.org/cayenne/browse/CAY-1121
On Sep 26, 2008, at 2:38 PM, Andrus Adamchik wrote:
> Replying a little late... We just tested this case and it works. So
> there maybe a user error involved on your end.
>
> Andrus
>
>
> On Jul 24, 2008, at 12:27 PM, Borut Bolčina wrote:
>
>> Hi,
>>
>> the database is MySQL 5.0.45, the driver is jConnector 5.1.6.
>>
>>
>> 2008/7/24 Andrus Adamchik <an...@objectstyle.org>:
>>
>>> The only difference in generated SQL syntax is using alias in the
>>> WHERE
>>> clause. The most weird thing is the deletion of 10 rows in the
>>> second case.
>>> This could be a bug in EJBQL processing I guess, resulting in
>>> incorrect
>>> value bindings (String vs. numeric or something like that). Which
>>> DB is
>>> this?
>>>
>>> Andrus
>>>
>>>
>>> On Jul 24, 2008, at 10:10 AM, Borut Bolčina wrote:
>>>
>>> Hi,
>>>>
>>>> I tried removing with
>>>>
>>>> Expression qual = ExpressionFactory.inExp("id", eventIds);
>>>> DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); //
>>>> DEPRECATED
>>>>
>>>> which gives:
>>>> INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
>>>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>>>> t0.url_provider, t0.description, t0.subcateg
>>>> ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event
>>>> t0 WHERE
>>>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/'
>>>> ]
>>>> INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102
>>>> ms.
>>>> INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
>>>> INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous
>>>> events...
>>>> INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
>>>> INFO [24 Jul 2008 08:52:01.442] --- transaction started.
>>>> INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN
>>>> (?, ... ?)
>>>>
>>> ...
>>>
>>>>
>>>> INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
>>>> INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
>>>>
>>>> and is correct,
>>>>
>>>>
>>>> where as with
>>>> EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e
>>>> where e.idin
>>>> (:eventIds)");
>>>> deleteQuery.setParameter("eventIds", eventIds);
>>>>
>>>> gives me:
>>>>
>>>> INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
>>>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>>>> t0.url_provider, t0.description, t0.subcatego
>>>> ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event
>>>> t0 WHERE
>>>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/'
>>>> ]
>>>> INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95
>>>> ms.
>>>> INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
>>>> INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous
>>>> events...
>>>> INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
>>>> INFO [24 Jul 2008 08:39:00.591] --- transaction started.
>>>> INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id
>>>> IN (
>>>> ?,?...,?)
>>>> INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
>>>>
>>>
>
>
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
Replying a little late... We just tested this case and it works. So
there maybe a user error involved on your end.
Andrus
On Jul 24, 2008, at 12:27 PM, Borut Bolčina wrote:
> Hi,
>
> the database is MySQL 5.0.45, the driver is jConnector 5.1.6.
>
>
> 2008/7/24 Andrus Adamchik <an...@objectstyle.org>:
>
>> The only difference in generated SQL syntax is using alias in the
>> WHERE
>> clause. The most weird thing is the deletion of 10 rows in the
>> second case.
>> This could be a bug in EJBQL processing I guess, resulting in
>> incorrect
>> value bindings (String vs. numeric or something like that). Which
>> DB is
>> this?
>>
>> Andrus
>>
>>
>> On Jul 24, 2008, at 10:10 AM, Borut Bolčina wrote:
>>
>> Hi,
>>>
>>> I tried removing with
>>>
>>> Expression qual = ExpressionFactory.inExp("id", eventIds);
>>> DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); //
>>> DEPRECATED
>>>
>>> which gives:
>>> INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
>>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>>> t0.url_provider, t0.description, t0.subcateg
>>> ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event
>>> t0 WHERE
>>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://
>>> www.acme.com/']
>>> INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102
>>> ms.
>>> INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
>>> INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous events...
>>> INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
>>> INFO [24 Jul 2008 08:52:01.442] --- transaction started.
>>> INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN
>>> (?, ... ?)
>>>
>> ...
>>
>>>
>>> INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
>>> INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
>>>
>>> and is correct,
>>>
>>>
>>> where as with
>>> EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e where
>>> e.idin
>>> (:eventIds)");
>>> deleteQuery.setParameter("eventIds", eventIds);
>>>
>>> gives me:
>>>
>>> INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
>>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>>> t0.url_provider, t0.description, t0.subcatego
>>> ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event
>>> t0 WHERE
>>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://
>>> www.acme.com/']
>>> INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95 ms.
>>> INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
>>> INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous events...
>>> INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
>>> INFO [24 Jul 2008 08:39:00.591] --- transaction started.
>>> INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id
>>> IN (
>>> ?,?...,?)
>>> INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
>>>
>>
Re: delete from ... where ... in (...)
Posted by Borut Bolčina <bo...@gmail.com>.
Hi,
the database is MySQL 5.0.45, the driver is jConnector 5.1.6.
2008/7/24 Andrus Adamchik <an...@objectstyle.org>:
> The only difference in generated SQL syntax is using alias in the WHERE
> clause. The most weird thing is the deletion of 10 rows in the second case.
> This could be a bug in EJBQL processing I guess, resulting in incorrect
> value bindings (String vs. numeric or something like that). Which DB is
> this?
>
> Andrus
>
>
> On Jul 24, 2008, at 10:10 AM, Borut Bolčina wrote:
>
> Hi,
>>
>> I tried removing with
>>
>> Expression qual = ExpressionFactory.inExp("id", eventIds);
>> DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); //
>> DEPRECATED
>>
>> which gives:
>> INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>> t0.url_provider, t0.description, t0.subcateg
>> ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
>> INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102 ms.
>> INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
>> INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous events...
>> INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
>> INFO [24 Jul 2008 08:52:01.442] --- transaction started.
>> INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN (?, ... ?)
>>
> ...
>
>>
>> INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
>> INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
>>
>> and is correct,
>>
>>
>> where as with
>> EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e where e.idin
>> (:eventIds)");
>> deleteQuery.setParameter("eventIds", eventIds);
>>
>> gives me:
>>
>> INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
>> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
>> t0.url_provider, t0.description, t0.subcatego
>> ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
>> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
>> INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95 ms.
>> INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
>> INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous events...
>> INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
>> INFO [24 Jul 2008 08:39:00.591] --- transaction started.
>> INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id IN (
>> ?,?...,?)
>> INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
>>
>
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
The only difference in generated SQL syntax is using alias in the
WHERE clause. The most weird thing is the deletion of 10 rows in the
second case. This could be a bug in EJBQL processing I guess,
resulting in incorrect value bindings (String vs. numeric or something
like that). Which DB is this?
Andrus
On Jul 24, 2008, at 10:10 AM, Borut Bolčina wrote:
> Hi,
>
> I tried removing with
>
> Expression qual = ExpressionFactory.inExp("id", eventIds);
> DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); //
> DEPRECATED
>
> which gives:
> INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
> t0.url_provider, t0.description, t0.subcateg
> ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0
> WHERE
> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
> INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102 ms.
> INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
> INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous events...
> INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
> INFO [24 Jul 2008 08:52:01.442] --- transaction started.
> INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN
> (?, ... ?)
...
>
> INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
> INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
>
> and is correct,
>
>
> where as with
> EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e where
> e.id in
> (:eventIds)");
> deleteQuery.setParameter("eventIds", eventIds);
>
> gives me:
>
> INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
> t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
> t0.url_provider, t0.description, t0.subcatego
> ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0
> WHERE
> t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
> INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95 ms.
> INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
> INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous events...
> INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
> INFO [24 Jul 2008 08:39:00.591] --- transaction started.
> INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id IN (
> ?,?...,?)
> INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
Re: delete from ... where ... in (...)
Posted by Borut Bolčina <bo...@gmail.com>.
Hi,
I tried removing with
Expression qual = ExpressionFactory.inExp("id", eventIds);
DeleteQuery deleteQuery = new DeleteQuery(Event.class, qual); // DEPRECATED
which gives:
INFO [24 Jul 2008 08:52:01.319] SELECT t0.location, t0.date_to,
t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
t0.url_provider, t0.description, t0.subcateg
ory, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
INFO [24 Jul 2008 08:52:01.421] === returned 410 rows. - took 102 ms.
INFO [24 Jul 2008 08:52:01.422] +++ transaction committed.
INFO [24 Jul 2008 08:52:01.422] Removing all(410) previous events...
INFO [24 Jul 2008 08:52:01.441] --- will run 1 query.
INFO [24 Jul 2008 08:52:01.442] --- transaction started.
INFO [24 Jul 2008 08:52:01.459] DELETE FROM event WHERE id IN (?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?) [bind: 1->id:0, 2->id:1, 3->id:2, 4->id:3, 5->id:4, 6->id:5,
7->id:6, 8->id:7, 9->
id:8, 10->id:9, 11->id:10, 12->id:11, 13->id:12, 14->id:13, 15->id:14,
16->id:15, 17->id:16, 18->id:17, 19->id:18, 20->id:19, 21->id:20, 22->id:21,
23->id:22, 24->id:23, 25
->id:24, 26->id:25, 27->id:26, 28->id:27, 29->id:28, 30->id:29, 31->id:30,
32->id:31, 33->id:32, 34->id:33, 35->id:34, 36->id:35, 37->id:36, 38->id:37,
39->id:38, 40->id:39
, 41->id:40, 42->id:41, 43->id:42, 44->id:43, 45->id:44, 46->id:45,
47->id:46, 48->id:47, 49->id:48, 50->id:49, 51->id:50, 52->id:51, 53->id:52,
54->id:53, 55->id:54, 56->i
d:55, 57->id:56, 58->id:57, 59->id:58, 60->id:59, 61->id:60, 62->id:61,
63->id:62, 64->id:63, 65->id:64, 66->id:65, 67->id:66, 68->id:67, 69->id:68,
70->id:69, 71->id:70, 7
2->id:71, 73->id:72, 74->id:73, 75->id:74, 76->id:75, 77->id:76, 78->id:77,
79->id:78, 80->id:79, 81->id:80, 82->id:81, 83->id:82, 84->id:83, 85->id:84,
86->id:85, 87->id:8
6, 88->id:87, 89->id:88, 90->id:89, 91->id:90, 92->id:91, 93->id:92,
94->id:93, 95->id:94, 96->id:95, 97->id:96, 98->id:97, 99->id:98,
100->id:99, 101->id:100, 102->id:101,
103->id:102, 104->id:103, 105->id:104, 106->id:105, 107->id:106,
108->id:107, 109->id:108, 110->id:109, 111->id:110, 112->id:111,
113->id:112, 114->id:113, 115->id:114, 11
6->id:115, 117->id:116, 118->id:117, 119->id:118, 120->id:119, 121->id:120,
122->id:121, 123->id:122, 124->id:123, 125->id:124, 126->id:125,
127->id:126, 128->id:127, 129->
id:128, 130->id:129, 131->id:130, 132->id:131, 133->id:132, 134->id:133,
135->id:134, 136->id:135, 137->id:136, 138->id:137, 139->id:138,
140->id:139, 141->id:140, 142->id:
141, 143->id:142, 144->id:143, 145->id:144, 146->id:145, 147->id:146,
148->id:147, 149->id:148, 150->id:149, 151->id:150, 152->id:151,
153->id:152, 154->id:153, 155->id:154
, 156->id:155, 157->id:156, 158->id:157, 159->id:158, 160->id:159,
161->id:160, 162->id:161, 163->id:162, 164->id:163, 165->id:164,
166->id:165, 167->id:166, 168->id:167, 1
69->id:168, 170->id:169, 171->id:170, 172->id:171, 173->id:172, 174->id:173,
175->id:174, 176->id:175, 177->id:176, 178->id:177, 179->id:178,
180->id:179, 181->id:180, 182-
>id:181, 183->id:182, 184->id:183, 185->id:184, 186->id:185, 187->id:186,
188->id:187, 189->id:188, 190->id:189, 191->id:190, 192->id:191,
193->id:192, 194->id:193, 195->id
:194, 196->id:195, 197->id:196, 198->id:197, 199->id:198, 200->id:199,
201->id:200, 202->id:201, 203->id:202, 204->id:203, 205->id:204,
206->id:205, 207->id:206, 208->id:20
7, 209->id:208, 210->id:209, 211->id:210, 212->id:211, 213->id:212,
214->id:213, 215->id:214, 216->id:215, 217->id:216, 218->id:217,
219->id:218, 220->id:219, 221->id:220,
222->id:221, 223->id:222, 224->id:223, 225->id:224, 226->id:225,
227->id:226, 228->id:227, 229->id:228, 230->id:229, 231->id:230,
232->id:231, 233->id:232, 234->id:233, 235
->id:234, 236->id:235, 237->id:236, 238->id:237, 239->id:238, 240->id:239,
241->id:240, 242->id:241, 243->id:242, 244->id:243, 245->id:244,
246->id:245, 247->id:246, 248->i
d:247, 249->id:248, 250->id:249, 251->id:250, 252->id:251, 253->id:252,
254->id:253, 255->id:254, 256->id:255, 257->id:256, 258->id:257,
259->id:258, 260->id:259, 261->id:2
60, 262->id:261, 263->id:262, 264->id:263, 265->id:264, 266->id:265,
267->id:266, 268->id:267, 269->id:268, 270->id:269, 271->id:270,
272->id:271, 273->id:272, 274->id:273,
275->id:274, 276->id:275, 277->id:276, 278->id:277, 279->id:278,
280->id:279, 281->id:280, 282->id:281, 283->id:282, 284->id:283,
285->id:284, 286->id:285, 287->id:286, 28
8->id:287, 289->id:288, 290->id:289, 291->id:290, 292->id:291, 293->id:292,
294->id:293, 295->id:294, 296->id:295, 297->id:296, 298->id:297,
299->id:298, 300->id:299, 301->
id:300, 302->id:301, 303->id:302, 304->id:303, 305->id:304, 306->id:305,
307->id:306, 308->id:307, 309->id:308, 310->id:309, 311->id:310,
312->id:311, 313->id:312, 314->id:
313, 315->id:314, 316->id:315, 317->id:316, 318->id:317, 319->id:318,
320->id:319, 321->id:320, 322->id:321, 323->id:322, 324->id:323,
325->id:324, 326->id:325, 327->id:326
, 328->id:327, 329->id:328, 330->id:329, 331->id:330, 332->id:331,
333->id:332, 334->id:333, 335->id:334, 336->id:335, 337->id:336,
338->id:337, 339->id:338, 340->id:339, 3
41->id:340, 342->id:341, 343->id:342, 344->id:343, 345->id:344, 346->id:345,
347->id:346, 348->id:347, 349->id:348, 350->id:349, 351->id:350,
352->id:351, 353->id:352, 354-
>id:353, 355->id:354, 356->id:355, 357->id:356, 358->id:357, 359->id:358,
360->id:359, 361->id:360, 362->id:361, 363->id:362, 364->id:363,
365->id:364, 366->id:365, 367->id
:366, 368->id:367, 369->id:368, 370->id:369, 371->id:370, 372->id:371,
373->id:372, 374->id:373, 375->id:374, 376->id:375, 377->id:376,
378->id:377, 379->id:378, 380->id:37
9, 381->id:380, 382->id:381, 383->id:382, 384->id:383, 385->id:384,
386->id:385, 387->id:386, 388->id:387, 389->id:388, 390->id:389,
391->id:390, 392->id:391, 393->id:392,
394->id:393, 395->id:394, 396->id:395, 397->id:396, 398->id:397,
399->id:398, 400->id:399, 401->id:400, 402->id:401, 403->id:402,
404->id:403, 405->id:404, 406->id:405, 407
->id:406, 408->id:407, 409->id:408, 410->id:409]
INFO [24 Jul 2008 08:52:01.474] === updated 410 rows.
INFO [24 Jul 2008 08:52:01.474] +++ transaction committed.
and is correct,
where as with
EJBQLQuery deleteQuery = new EJBQLQuery("delete from Event e where e.id in
(:eventIds)");
deleteQuery.setParameter("eventIds", eventIds);
gives me:
INFO [24 Jul 2008 08:39:00.470] SELECT t0.location, t0.date_to,
t0.url_event, t0.type, t0.id, t0.category, t0.title, t0.price,
t0.url_provider, t0.description, t0.subcatego
ry, t0.date_from, t0.name_provider, t0.time_from_list FROM event t0 WHERE
t0.url_provider LIKE ? [bind: 1->url_provider:'http://www.acme.com/']
INFO [24 Jul 2008 08:39:00.565] === returned 410 rows. - took 95 ms.
INFO [24 Jul 2008 08:39:00.566] +++ transaction committed.
INFO [24 Jul 2008 08:39:00.566] Removing all(410) previous events...
INFO [24 Jul 2008 08:39:00.591] --- will run 1 query.
INFO [24 Jul 2008 08:39:00.591] --- transaction started.
INFO [24 Jul 2008 08:39:00.642] DELETE FROM event WHERE event.id IN (
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?) [bind: 1:0, 2:1, 3:2, 4:3, 5:4, 6:5, 7:6, 8:7,
9:8, 10:9, 11:10, 12:11, 13:12, 14:13, 15:14, 16:15, 17:16, 18:17, 19:18,
20:19, 21:20, 22:21, 23:2
2, 24:23, 25:24, 26:25, 27:26, 28:27, 29:28, 30:29, 31:30, 32:31, 33:32,
34:33, 35:34, 36:35, 37:36, 38:37, 39:38, 40:39, 41:40, 42:41, 43:42, 44:43,
45:44, 46:45, 47:46, 48
:47, 49:48, 50:49, 51:50, 52:51, 53:52, 54:53, 55:54, 56:55, 57:56, 58:57,
59:58, 60:59, 61:60, 62:61, 63:62, 64:63, 65:64, 66:65, 67:66, 68:67, 69:68,
70:69, 71:70, 72:71,
73:72, 74:73, 75:74, 76:75, 77:76, 78:77, 79:78, 80:79, 81:80, 82:81, 83:82,
84:83, 85:84, 86:85, 87:86, 88:87, 89:88, 90:89, 91:90, 92:91, 93:92, 94:93,
95:94, 96:95, 97:96
, 98:97, 99:98, 100:99, 101:100, 102:101, 103:102, 104:103, 105:104,
106:105, 107:106, 108:107, 109:108, 110:109, 111:110, 112:111, 113:112,
114:113, 115:114, 116:115, 117:1
16, 118:117, 119:118, 120:119, 121:120, 122:121, 123:122, 124:123, 125:124,
126:125, 127:126, 128:127, 129:128, 130:129, 131:130, 132:131, 133:132,
134:133, 135:134, 136:135
, 137:136, 138:137, 139:138, 140:139, 141:140, 142:141, 143:142, 144:143,
145:144, 146:145, 147:146, 148:147, 149:148, 150:149, 151:150, 152:151,
153:152, 154:153, 155:154,
156:155, 157:156, 158:157, 159:158, 160:159, 161:160, 162:161, 163:162,
164:163, 165:164, 166:165, 167:166, 168:167, 169:168, 170:169, 171:170,
172:171, 173:172, 174:173, 17
5:174, 176:175, 177:176, 178:177, 179:178, 180:179, 181:180, 182:181,
183:182, 184:183, 185:184, 186:185, 187:186, 188:187, 189:188, 190:189,
191:190, 192:191, 193:192, 194:
193, 195:194, 196:195, 197:196, 198:197, 199:198, 200:199, 201:200, 202:201,
203:202, 204:203, 205:204, 206:205, 207:206, 208:207, 209:208, 210:209,
211:210, 212:211, 213:21
2, 214:213, 215:214, 216:215, 217:216, 218:217, 219:218, 220:219, 221:220,
222:221, 223:222, 224:223, 225:224, 226:225, 227:226, 228:227, 229:228,
230:229, 231:230, 232:231,
233:232, 234:233, 235:234, 236:235, 237:236, 238:237, 239:238, 240:239,
241:240, 242:241, 243:242, 244:243, 245:244, 246:245, 247:246, 248:247,
249:248, 250:249, 251:250, 2
52:251, 253:252, 254:253, 255:254, 256:255, 257:256, 258:257, 259:258,
260:259, 261:260, 262:261, 263:262, 264:263, 265:264, 266:265, 267:266,
268:267, 269:268, 270:269, 271
:270, 272:271, 273:272, 274:273, 275:274, 276:275, 277:276, 278:277,
279:278, 280:279, 281:280, 282:281, 283:282, 284:283, 285:284, 286:285,
287:286, 288:287, 289:288, 290:2
89, 291:290, 292:291, 293:292, 294:293, 295:294, 296:295, 297:296, 298:297,
299:298, 300:299, 301:300, 302:301, 303:302, 304:303, 305:304, 306:305,
307:306, 308:307, 309:308
, 310:309, 311:310, 312:311, 313:312, 314:313, 315:314, 316:315, 317:316,
318:317, 319:318, 320:319, 321:320, 322:321, 323:322, 324:323, 325:324,
326:325, 327:326, 328:327,
329:328, 330:329, 331:330, 332:331, 333:332, 334:333, 335:334, 336:335,
337:336, 338:337, 339:338, 340:339, 341:340, 342:341, 343:342, 344:343,
345:344, 346:345, 347:346, 34
8:347, 349:348, 350:349, 351:350, 352:351, 353:352, 354:353, 355:354,
356:355, 357:356, 358:357, 359:358, 360:359, 361:360, 362:361, 363:362,
364:363, 365:364, 366:365, 367:
366, 368:367, 369:368, 370:369, 371:370, 372:371, 373:372, 374:373, 375:374,
376:375, 377:376, 378:377, 379:378, 380:379, 381:380, 382:381, 383:382,
384:383, 385:384, 386:38
5, 387:386, 388:387, 389:388, 390:389, 391:390, 392:391, 393:392, 394:393,
395:394, 396:395, 397:396, 398:397, 399:398, 400:399, 401:400, 402:401,
403:402, 404:403, 405:404,
406:405, 407:406, 408:407, 409:408, 410:409]
INFO [24 Jul 2008 08:39:00.674] === updated 10 rows.
INFO [24 Jul 2008 08:39:00.675] +++ transaction committed.
which is not correct.
Is the syntax for EJBQL wrong?
Thanks,
Borut
2008/5/30 Andrus Adamchik <an...@objectstyle.org>:
> Don't remember off-hand (this was certainly possible with Cayenne 'classic'
> expressions). Could you give it a try?
>
> Andrus
>
>
> On May 30, 2008, at 4:53 PM, Borut Bolčina wrote:
>
> Hi,
>>
>> EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id in
>> (1,
>>
>>> 2, 3)");
>>>
>>
>>
>> this looks more promising. It would be better this way (IMHO):
>>
>> context = // assume this exists
>> List<Integer> myArtistList = // assume this exists
>> EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id in
>> :myArtistList");
>> delete.setParameter("myArtistList", myArtistList);
>> context.performNonSelectingQuery(deleteQuery);
>>
>> Is this possible? Or, would it be?
>>
>> Thanks,
>> Borut
>>
>> 2008/5/30 Andrus Adamchik <an...@objectstyle.org>:
>>
>> See my other reply - there is actually an EJBQL delete.
>>>
>>>
>>>
>>> On May 30, 2008, at 4:27 PM, Borut Bolčina wrote:
>>>
>>> Hi,
>>>
>>>>
>>>> what is the C3M4 prefered way of doing this SQL equivalent: DELETE FROM
>>>> Artist WHERE Oid IN (1,4,8,9);
>>>>
>>>> Thanks,
>>>> Borut
>>>>
>>>>
>>>
>>>
>
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
Don't remember off-hand (this was certainly possible with Cayenne
'classic' expressions). Could you give it a try?
Andrus
On May 30, 2008, at 4:53 PM, Borut Bolčina wrote:
> Hi,
>
> EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id
> in (1,
>> 2, 3)");
>
>
> this looks more promising. It would be better this way (IMHO):
>
> context = // assume this exists
> List<Integer> myArtistList = // assume this exists
> EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id in
> :myArtistList");
> delete.setParameter("myArtistList", myArtistList);
> context.performNonSelectingQuery(deleteQuery);
>
> Is this possible? Or, would it be?
>
> Thanks,
> Borut
>
> 2008/5/30 Andrus Adamchik <an...@objectstyle.org>:
>
>> See my other reply - there is actually an EJBQL delete.
>>
>>
>>
>> On May 30, 2008, at 4:27 PM, Borut Bolčina wrote:
>>
>> Hi,
>>>
>>> what is the C3M4 prefered way of doing this SQL equivalent: DELETE
>>> FROM
>>> Artist WHERE Oid IN (1,4,8,9);
>>>
>>> Thanks,
>>> Borut
>>>
>>
>>
Re: delete from ... where ... in (...)
Posted by Borut Bolčina <bo...@gmail.com>.
Hi,
EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id in (1,
> 2, 3)");
this looks more promising. It would be better this way (IMHO):
context = // assume this exists
List<Integer> myArtistList = // assume this exists
EJBQLQuery delete = new EJBQLQuery("delete from Artist a where a.id in
:myArtistList");
delete.setParameter("myArtistList", myArtistList);
context.performNonSelectingQuery(deleteQuery);
Is this possible? Or, would it be?
Thanks,
Borut
2008/5/30 Andrus Adamchik <an...@objectstyle.org>:
> See my other reply - there is actually an EJBQL delete.
>
>
>
> On May 30, 2008, at 4:27 PM, Borut Bolčina wrote:
>
> Hi,
>>
>> what is the C3M4 prefered way of doing this SQL equivalent: DELETE FROM
>> Artist WHERE Oid IN (1,4,8,9);
>>
>> Thanks,
>> Borut
>>
>
>
Re: delete from ... where ... in (...)
Posted by Andrus Adamchik <an...@objectstyle.org>.
See my other reply - there is actually an EJBQL delete.
On May 30, 2008, at 4:27 PM, Borut Bolčina wrote:
> Hi,
>
> what is the C3M4 prefered way of doing this SQL equivalent: DELETE
> FROM
> Artist WHERE Oid IN (1,4,8,9);
>
> Thanks,
> Borut
Re: delete from ... where ... in (...)
Posted by Michael Gentry <bl...@gmail.com>.
Wow, you just gave me a Star Wars flashback. If we make it to C3PO,
we'll be *golden*! :-)
If you want to execute that exact SQL, you could do it with
SQLTemplate. For example, in one of my demo/test projects, I have:
sqlQuery = new SQLTemplate(Order.class, "DELETE FROM Orders");
dataContext.performNonSelectingQuery(sqlQuery);
If you need it more dynamic, look at inExp() on this page:
http://cayenne.apache.org/doc/expression-factory-utilities.html
You'd then make an Expression and perform query them/fetch them, then
delete them. There might be an easier way to do this, but I know this
approach would keep your Cayenne object graph in sync (the SQLTemplate
can get things out of sync). Something like:
Expression exp = ExpressionFactory.inExp("oid", values);
SelectQuery select = new SelectQuery(Artist.class, exp);
List list = dataContext.performQuery(select);
// loop over list and:
dataContext.deleteObject(item);
// then save
dataContext.commitChanges();
/dev/mrg
PS. The "oid" field needs to be mapped in the above example ...
On Fri, May 30, 2008 at 9:27 AM, Borut Bolčina <bo...@gmail.com> wrote:
> Hi,
>
> what is the C3M4 prefered way of doing this SQL equivalent: DELETE FROM
> Artist WHERE Oid IN (1,4,8,9);
>
> Thanks,
> Borut
>