Thanks again, Dies.
I may have been inaccurate explaining this. I know I need two trips to the
database, as I first need to update the records and then fetch them. Two
trips are ok, but since I cannot use ORDER BY in a subquery, I obviously
need three trips:
1 select the correct records, based on status and priority
2 update the selected records
3 fetch the records
If ORDER BY was possible, I could combine 1 and 2 in the first trip and then
fetch the messages in the second trip.
Seems it isn't possible to solve this with less than 3 trips then...
//Marius
Dies wrote:
>
> Hi Marius,
>
> I don't know how to explain. It is an UPDATE query, not a SELECT query
> that you are sending to the database, so all records in your subquery will
> be updated, none will be returned. Calling setMaxResults has no effect (it
> will be ignored).
> If you want to both select records and update them, you'll need to break
> it up and make several round trips.
> Another way might be by using a native SQL query that can both update and
> return queries in one operation. (I vaguely remember Oracle introducing
> such feature in Oracle 9 or 10.)
>
> Regards,
> Dies
>
>
>
> MariusW wrote:
>>
>> Thanks for answering, both Michael and Dies.
>>
>> I can't just skip the ORDER BY, as I need to be able to pick just a
>> subset of the messages, and this subset MUST include the messages with
>> the highest priority.
>>
>> Skipping setMaxResults is not an option, as tens of thousands of messages
>> might be placed in this queue, and I need to be sure that each thread
>> dispatching these messages only has a limited number of messages to
>> handle.
>>
>> I could, of course, solve this by contacting the database twice. First do
>> a SELECT with ORDER BY and setMaxResults to get a list of IDs, and then a
>> second trip to the database to execute the UPDATE query, using the list
>> from the SELECT. I was hoping there was a more elegant solution, though.
>> This is the simplified process in my message queue manager:
>>
>> 1. Get the (e.g.) 100 messages (id's) with the highest priority (SELECT)
>> 2. Flag these messages as being in process, and tag them with a unique
>> value (UPDATE)
>> 3. Get the 100 messages (the whole record) that were just updated in a
>> List (SELECT)
>> 4. Create a worker thread (using a thread pool), give it the List of
>> messages and execute it (this worker will do the dispatch and subsequent
>> updates to the records)
>> 5. Continue from 1 until there are no pending messages.
>>
>> I need to tag the records so that the message queue manager does not pick
>> the same messages again while a worker thread is dispatching them. Three
>> roundtrips to the database seems excessive, but maybe there is no other
>> way if I cannot use ORDER BY in a subquery.
>>
>> //Marius
>>
>>
>> Michael Bouschen-2 wrote:
>>>
>>> Hi,
>>>
>>> I agree with Dies. The Java Persistence query language does not support
>>> ORDER BY in a subquery.
>>>
>>> I understand you call Query.setMaxResults in order to limit the result
>>> of the subquery, correct? I think that does not work, because
>>> setMaxResult applies to the entire query which is an UPDATE query in
>>> your case. I propose to skip the ORDER BY clause in the subquery.
>>>
>>> Regards Michael
>>>
>>>> I need it because I use setMaxResults to get only a subset of the
>>>> records.
>>>> And this subset must always be ordered by priority so that the messages
>>>> with
>>>> the highest priority is selected first. In this way, if there are 1000
>>>> messages currently queued, and setMaxResults is 100, I must be sure
>>>> that the
>>>> most important messages are picked up first, no matter how long
>>>> low-priority
>>>> messages have been in the queue.
>>>>
>>>> If you have ideas on other ways to solve this, they are most welcome!
>>>>
>>>> //Marius
>>>>
>>>>
>>>> Dies wrote:
>>>>
>>>>> JPQL does not allow the use of ORDER BY in a subquery:
>>>>>
>>>>> subquery_from_clause ::=
>>>>> FROM subselect_identification_variable_declaration
>>>>> {, subselect_identification_variable_declaration}*
>>>>>
>>>>> Why do you need it?
>>>>>
>>>>>
>>>>>
>>>>> MariusW wrote:
>>>>>
>>>>>> Internal Exception: line 1:273: unexpected token: ORDER
>>>>>>
>>>>>> In a more readable format, the EJB QL looks like this:
>>>>>> UPDATE MobileMessage x
>>>>>> SET x.statusmsg = 'PROCESSING',x.transporttime = :transporttime
>>>>>> WHERE x.id IN (
>>>>>> SELECT y.id FROM MobileMessage y
>>>>>> WHERE y.gateway = :gateway
>>>>>> AND y.statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
>>>>>> AND ((y.timetosend IS NULL) OR (y.timetosend < :cutoff ))
>>>>>> ORDER BY y.priority
>>>>>> )
>>>>>>
>>>>>> If I translate it to standard SQL and execute it, it works fine:
>>>>>> UPDATE bb_core.bb_message
>>>>>> SET statusmsg = 'PROCESSING'
>>>>>> WHERE id IN (
>>>>>> SELECT id FROM bb_core.bb_message
>>>>>> WHERE gateway = 1
>>>>>> AND statusmsg IN ( 'PENDING','RETRY','SCHEDULED')
>>>>>> AND ((timetosend IS NULL) OR (timetosend < now() ))
>>>>>> ORDER BY priority
>>>>>> )
>>>>>>
>>>>>> Any tips would be greatly appreciated!
>>>>>>
>>>>>> // Marius
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> *Michael Bouschen*
>>> *Prokurist*
>>>
>>> akquinet tech_at_spree GmbH
>>> Tempelhofer Ufer 23-24, D-10963 Berlin
>>> Bülowstr. 66, D-10783 Berlin
>>>
>>> Fon: +49 30 235 520-33
>>> Fax: +49 30 217 520-12
>>> Email: michael.bouschen_at_akquinet.de
>>> Url: www.akquinet.de <http://www.akquinet.de>
>>>
>>> akquinet tech_at_spree GmbH, Berlin
>>> Geschäftsführung: Prof. Dr. Christian Roth, Hendrik Saly, Martin Weber
>>> Amtsgericht Berlin-Charlottenburg HRB 86780
>>> USt.-Id. Nr.: DE 225 964 680
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Unexpected-token%3A-ORDER-tp16582059p16584556.html
Sent from the java.net - glassfish persistence mailing list archive at Nabble.com.