Modifying the order of the date field, all works fine.
Thank you, Ralf, I will try to explain the situation
to the client, perhaps it won't be a problem to change
the database. Anyway, thank you for your help.
Sergio Collantes
On Tue, 23 Aug 2005 09:57:56 +0200, Ralf Joachim
<[EMAIL PROTECTED]> wrote:
Sergio,
as you map your dates to strings the database sort them like strings. I
suggested to change the pattern to generate string that are sorted as
you would expect from your dates.
I know that customer is always right but sometimes he don't know what he
causes with his requiremnets. I would give it a try to ask if he can
change that pattern. If that's not possible it gets a bit difficult.
I can see only to possible solutions:
1) Load all the objects and compare dates by your own code.
2) Use castor SQL passthrough query that look similar to:
CALL SQL select <fields> from <table>
where (SUBSTR(mydate,6,4) < year)
or ((SUBSTR(mydate,6,4) = year)
and ((SUBSTR(mydate,3,2) < month)
or ((SUBSTR(mydate,3,2) = month)
and (SUBSTR(mydate,0,2) < day))))
AS MyClass
The <fields> need to be in the same order as if castor translates a OQL
query to SQL. You can view the SQL queries generated by castor if you
switch on debiging.
You need to replace the SUBSTR function calls with an appropriate
function from the SQL syntax of your database.
Ralf
Sergio Collantes schrieb:
Hello!
First of all, I'm sorry if I can't follow the thread,
but sometimes it's impossible for me to do it.
In order to map the dates with a different pattern I
shuld need to change the database strings, wich is
something that i can't do (specifications from the client,
i know is a crazy sort, but "the client is always right" :)
Is there any other way to do it? I expected that once Castor
has made the query to the database, the framework
ordered the objects, not the results as they come from the
database.
Hi Sergio,
my first thought was that you have problems how to include dates in your
query but as you stated now it seams you managed that. Having a second
look on your code I found that you will have the problem that your query
returns the wrong records. This relates to the way you map the dates to
your database with string and the pattern 'dd/MM/yyyy'. With the pattern
you use a date '01/12/2004' comes before '13/01/2004' which is obviously
not what you want. I suggest you to change your mapping to 'yyyy/MM/dd'.
This is also easy to read but has the advantage that you'll be able to
sort your records with that field and your query will return the results
you expect.
Ralf
Sergio Collantes schrieb:
Hello, Ralf.
Thanks for your quick answer, but it does not solve
the problem. I have to say that is my fault, I explined
the problem in a wrong way. What I actually do is what
you suggested, bind the java.util.Date to the query (when
I posted that simple example, I forgot to do so, but in my
real app it is just what I do). Anyway, is does not work either.
Thank again, Ralf.
On Mon, 22 Aug 2005 13:53:15 +0200, Ralf Joachim
<[EMAIL PROTECTED]> wrote:
Hi Sergio,
you should be able to execute such a query with:
OQLQuery query = db.getOQLQuery(
"select a from MyClass a where a.mydate > $1");
query.bind(expirationDate);
QueryResults results = query.execute();
Regards
Ralf
Sergio Collantes schrieb:
Hi all:
I've been having trouble when trying to
build an OQL query using java.util.Date types.
Suppose we have a simple class "MyClass" with
a java.util.Date filed named "myDate".
The query goes like this:
select a from MyClass a where a.mydate > expirationDate
Let's suppose that the object expirationDate is declared
in this way:
Calendar calendar = Calendar.getInstance();
calendar.set (...) // Whe set the expiration date
java.util.Date expirationDate = calendar.getTime();
The field myDate is a varchar in the database, and the
mapping is correct (i.e. the data is converted to Date
correctly in the JDO, i have tried it and it works).
The mapping is declared as follows:
<field name="mydate" type="date">
<sql name="mydate" type="char[dd/MM/yyyy]"/>
</field>
I assume in doing the wrong query, ¿can anybody tell
me what do I have to do?
-------------------------------------------------
If you wish to unsubscribe from this list, please send an empty
message
to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
-------------------------------------------------
If you wish to unsubscribe from this list, please send an empty message
to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
