CMP Dates and EJB QL
I'm writing an EJB system that requires a query similar to the following SQL:
SELECT * FROM programmes p WHERE NOW() BETWEEN p.start AND p.stop;
I've read that Date types are partially supported, some reports say they work for some types of queries and not others.
I can see several solutions, at least I think I can.
1. Store dates in Unix long int format and only use java.util.Date for presentation and non-CMP related calculations.
2. Store dates in litte-endian String format yyyyMMddHHmmss which, when converted to a Long is also numerically comparable and sortable. This would require returning a collection of ALL programmes and doing a manual iteration scan over all the dates, converting them individually before comparison.
Without significant redesign this query will be run at least once every 10 seconds. There aren't likely to be more than a few dozen records, but still, I've found that returning an entire table, only to loop through it, do conversions and compare things can make an operation like this take several orders of magnitude longer than a simple SQL query and substantially more memory and CPU time.
Both make dealing with daylight saying time a little more challenging than I'd like. Then again I haven't really solved that in the normal SQL query either yet.
Are there are normal ways to deal with this scenario.
I'm using JBoss 3.2 and MySQL 5.
Paul
[1456 byte] By [
PCampbella] at [2007-11-27 4:56:06]
# 2
The clients are PCs that have TV recorder cards. I have an existing system that has a simple MySQL table, a PHP web front end and Java JDBC clients that poll the DB directly and do the recordings.
I'm not quite sure what you mean by using a timer. I haven't fully designed the client server protocol/conversation for the EJB system, but I expect something like.
ClientN -> Server : "Hi I'm ready for work"
Server -> Client : "Grand"
[Every 10 seconds of so]
ClientN -> Server : "Got any work for me?"
Server->ClientN : "Nope"
[Until]
Server->ClientN : "Yes, do this!"
ClientN->Server : "Certainly! I'll get right to that"
[Every 10 seconds of so]
ClientN->Server : "I'm doing this thing you asked, okay?"
Server->ClientN : "Yep, keep going."
[OR]
ClientN->Server : "Look, I've messed up somehow, I've given up."
Server->ClientN : "Fine, I'll get someone else who can do it."
[OR]
ClientN->Server : "I'm doing this thing you asked, okay?"
Server->ClientN : "No, stop right there!"
This allows 'tasks' aka recordings to be given to pooled devices and for recordings to switch device mid recording even.
All I need is a method query to query between date ranges and determine which programmes should be running and then figure out "Programme->Ready Device" priorities.
I did consider an event based system. Where the clients listen on a UDP port for commands from the server, but it didn't seem to fit with the request/response/session nature of EJB conversational flow.
I'm still very new to EJBs and J2EE in general, maybe getting in too deep too soon with this project.
Paul
