Re: addLessThan or addBetween with Dates in mySQL

[Jakob Braeuchi]

hi aaron,

p6spy is a jdbc-logger shipped with ojb. it provides a special driver 
(it's actually a wrapper) com.p6spy.engine.spy.P6SpyDriver which you'll 
have to use in the connection-descriptor:

    <jdbc-connection-descriptor
       default-connection="true"
       jcd-alias="default"
          platform="MySQL"
          jdbc-level="2.0"
          driver="com.p6spy.engine.spy.P6SpyDriver"
          protocol="jdbc"
          subprotocol="mysql"
          dbalias="//localhost:3306/brjtest"
          username=""
          password=""
       batch-mode="false"
   >
       <sequence-manager 
className="org.apache.ojb.broker.util.sequence.SequenceManagerHighLowImpl">
           <attribute attribute-name="grabSize" attribute-value="20"/>
       </sequence-manager>
       <connection-pool
           maxActive="5"
           whenExhaustedAction="0"/>
   </jdbc-connection-descriptor>

in the file spy.properties the real driver has to be defined:

# the mysql open source driver
realdriver=org.gjt.mm.mysql.Driver
the appender  let's you select the output destination:

#specifies the appender to use for logging
#appender=com.p6spy.engine.logging.appender.Log4jLogger
appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger
hth
jakob
Aaron Longwell wrote:

I'm not familiar with p6spy.. is that a class within OJB? Or a 
log4j-type logger?

Jakob Braeuchi wrote:

hi aaron,

you can use p6spy to log all the sql.
btw it works with my installation:
       QueryByCriteria query;
       Criteria crit;
       Date d1, d2;
             d1 = new Date(70,0,1);
       d2 = new Date(77,0,1);
             crit = new Criteria();
       crit.addBetween("geburtsDatum", d1, d2);
       query = new QueryByCriteria(Person.class, crit);
       query.addOrderByAscending("geburtsDatum");
       broker.getCollectionByQuery(query);
...
      <field-descriptor
        name="geburtsDatum"
        column="gebDat"
        jdbc-type="DATE"
        
conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlDateFieldConversion" 

     />
...
p6spy output:
SELECT 
A0.isBoss,A0.gebDat,A0.idTest,A0.entryDate,A0.adresse,A0.idPartner,A0.name,A0.test,A0.vorname,A0.id 
FROM tabPerson A0 WHERE A0.gebDat BETWEEN  ? AND ?  ORDER BY 2

p6spy output with filed variables:
SELECT 
A0.isBoss,A0.gebDat,A0.idTest,A0.entryDate,A0.adresse,A0.idPartner,A0.name,A0.test,A0.vorname,A0.id 
FROM tabPerson A0 WHERE A0.gebDat BETWEEN  '1970-01-01' AND 
'1977-01-01'  ORDER BY 2

hth
jakob
Aaron Longwell wrote:

Jakob,

How do I retrieve the raw SQL for a Query object (or criteria)?

The SQL I am using now (working correctly):

SimpleDateFormat fmt = (SimpleDateFormat) 
DateFormat.getDateTimeInstance();
fmt.applyPattern("yyyy-MM-dd HH:mm:ss");

criteria.addSql("StartDate >= '" + fmt.format(startDate) + "' AND 
StartDate < '" +
   fmt.format(endDate) + "'");
Query query = QueryFactory.newQuery(Event.class, criteria);

Notice that I'm using the addSql() method... I cannot get the 
addLessThan to work with identical date values.

Thanks,
Aaron
Jakob Braeuchi wrote:

hi aaron,

afaik ojb does nothing special when using dates with mysql, it 
maybe the driver.
could you please post the sql that caused the problem, so i can 
check it with my mySQL db.

hth
jakob
Aaron Longwell wrote:

Edson,

I think there's something wrong with the way OJB is turning an 
addLessThan to SQL code on the mySQL implementation. I switched my 
code to SimpleDateFormat the submitted dates and turn them into 
mySQL-DateTime format (2003-05-18 00:00:00). I then use 
addSQL("StartDate <= '" + strStartDate + "' AND StartDate < '" + 
strEndDate + "'");

This works just fine... I guess I can deal with it for now.



Edson Carlos Ericksson Richter wrote:

Nop. I'm using SapDB and MS SQL.
Maybe you are having some kind of driver issue here.
Edson Richter

----- Original Message ----- From: "Aaron Longwell" 
<[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Monday, June 16, 2003 10:40 AM
Subject: Re: addLessThan or addBetween with Dates in mySQL

Edison,

I'm actually using the same conversion... were you using mySQL?

Thanks,
Aaron
Edson Carlos Ericksson Richter wrote:

 

Hi!

I've used Dates normally with less than, greater than and 
between with
SapDB.
Unique thing I had todo is put a "conversion" in field like:

conversion="org.apache.ojb.broker.accesslayer.conversions.JavaDate2SqlTimes
  




t
 

ampFieldConversion"

then it works for me.

Edson Richter

----- Original Message ----- From: "Aaron Longwell" 
<[EMAIL PROTECTED]>
To: "OJB User" <[EMAIL PROTECTED]>
Sent: Monday, June 16, 2003 2:42 AM
Subject: addLessThan or addBetween with Dates in mySQL

OJB Experts,

I am using mySQL with OJB to build an event calendar. I need to 
display
1 month's worth of events. At first, I tried code like this:

criteria.addBetween("EventDate", startDate, endDate);

where startDate and endDate are java.util.Dates. I got no 
results, even
though there are events in the database between those two dates. I
retried with a single addGreaterOrEqualThan(). This worked as 
expected,
returning events whose date was on or after the startDate 
submitted. I
then tried the addLessOrEqualThan(). This returned 0 results, even
though there are events before the submitted date in the database.

The Javadocs show examples of these commands being used with 
integers
and not dates. Am I wrong to even attempt this? I would prefer 
not to
implement my method an addSQL() call. I would think 
Date-handling would
be automatic with an OJB tool.

Thanks for the help,
Aaron




--------------------------------------------------------------------- 

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 10/6/2003


--------------------------------------------------------------------- 

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




  






--------------------------------------------------------------------- 

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 10/6/2003
--------------------------------------------------------------------- 

To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


 




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]





---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]