You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by sailorhero <sa...@hotmail.com> on 2003/08/01 15:03:02 UTC

Question with "addDate" and "andDate"!

I use addDate() and andDate() to query some line .

first:
I want to know where is the diffirent between the two methods.

When i use twice the addDate() to a field ,just the last is used ,Why?
how to create a Criteria which like "book.TIME<='20030801000000' and book.TIME>='20030801000000'"?
example code:
   Criteria crit1 = new Criteria();
   crit1.addDate(BookPeer.TIME,2003,7,1,Criteria.GREATER_EQUAL);
   crit1.andDate(BookPeer.TIME,2003,7,1,Criteria.LESS_EQUAL);
   results = BookPeer.doSelect(crit1); 

but i find just the last method is used.
log4j output:
DEBUG [main] util.BasePeer (createQueryString:994) - SELECT book.BOOK_ID, book.TITLE, book.ISBN, book.TIME, book.PUBLISHER_ID, book.AUTHOR_ID FROM book WHERE book.TIME<='20030801000000'
DEBUG [main] util.BasePeer (executeQuery:1532) - Elapsed time=10 ms

Second:
when i use andDate(),there is error 
code:
   crit1.andDate(BookPeer.TIME,2003,7,1,Criteria.LESS_EQUAL);
   results = BookPeer.doSelect(crit1);
log4j output:
DEBUG [main] util.BasePeer (createQueryString:994) - SELECT book.BOOK_ID, book.TITLE, book.ISBN, book.TIME, book.PUBLISHER_ID, book.AUTHOR_ID FROM book WHERE (book.TIME>='20030801000000' AND book.TIME<=java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Asia/Shanghai",offset=28800000,dstSavings=0,useDaylight=false,transitions=19,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2003,MONTH=7,WEEK_OF_YEAR=?,WEEK_OF_MONTH=?,DAY_OF_MONTH=1,DAY_OF_YEAR=?,DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=?,HOUR=?,HOUR_OF_DAY=?,MINUTE=?,SECOND=?,MILLISECOND=?,ZONE_OFFSET=?,DST_OFFSET=?])
java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:60)
rethrown as org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1541)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:60)
Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 ... 5 more
rethrown as org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1373)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:60)
Caused by: org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1541)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 ... 4 more
Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 ... 5 more

different internal variable names

Posted by Geoff Fortytwo <g4...@ign.com>.
This topic was talked about on this list recently, but no one seemed to 
suggest a solution.

If I have a table column named "short" then that'll screw things up because 
that's a reserved java keyword, so a variable can't have that name. 
Assuming that the column name can't be changed (due to lots of other apps 
already using the table) then is the only solution to modify the velocity 
code so that, for instance, all internal variables have an underscore 
appended to them? That solves the problem for all possible java reserved 
name conflicts.

But is there a way to fix this without modifying velocity? The javaName 
column only fixes the names of the functions, it doesn't alter the internal 
variable names.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Question with "addDate" and "andDate"!

Posted by Murvai-Buzogány László <la...@t-sys.hu>.
Hi,

I thought it would give the same error. Though, it was my mistake thinking
that this was like a ¨bug¨. After having a look at the docs, I found this
in the Criteria-howto:
Criteria criteria = new Criteria();
criteria.add(InvoicePeer.COST, 1000, Criteria.GREATER_EQUAL);
Criteria.Criterion criterion = criteria.getCriterion(InvoicePeer.COST);
criterion.and(
               criteria.getNewCriterion(
                             criterion.getTable(),
                             criterion.getColumn(),
                             new Integer(5000),
                             Criteria.LESS_EQUAL )
               );

There is also an explanation available, which clearly says why it has to be
done like this.

	L

On Fri, Aug 01, 2003 at 11:59:32PM +0800, sailorhero wrote:
> I understand the diffrent .
> But i use the method which you said,It is also wrong.
> Just like I used andDate().
> I use Torque3.0.2.
> Which version has fixed this error?
> code:
>    crit1.clear();
>    crit1.addDate(BookPeer.TIME,2003,7,1,Criteria.GREATER_EQUAL);
>    crit1.and(BookPeer.TIME, new GregorianCalendar(2003, 7, 1),Criteria.LESS_EQUAL);
> 

-- 
Murvai-Buzogány László
T-Sys Kft.
Software developer
Tel.: (06)-1 436-8725 
Laszlo.Murvai-Buzogany@t-sys.hu

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

Re: Question with "addDate" and "andDate"!

Posted by sailorhero <sa...@hotmail.com>.
I understand the diffrent .
But i use the method which you said,It is also wrong.
Just like I used andDate().
I use Torque3.0.2.
Which version has fixed this error?
code:
   crit1.clear();
   crit1.addDate(BookPeer.TIME,2003,7,1,Criteria.GREATER_EQUAL);
   crit1.and(BookPeer.TIME, new GregorianCalendar(2003, 7, 1),Criteria.LESS_EQUAL);

log4j output:
DEBUG [main] util.BasePeer (createQueryString:994) - SELECT book.BOOK_ID, book.TITLE, book.ISBN, book.TIME, book.PUBLISHER_ID, book.AUTHOR_ID FROM book WHERE (book.TIME>='20030801000000' AND book.TIME<=java.util.GregorianCalendar[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Asia/Shanghai",offset=28800000,dstSavings=0,useDaylight=false,transitions=19,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2003,MONTH=7,WEEK_OF_YEAR=?,WEEK_OF_MONTH=?,DAY_OF_MONTH=1,DAY_OF_YEAR=?,DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=?,HOUR=?,HOUR_OF_DAY=?,MINUTE=?,SECOND=?,MILLISECOND=?,ZONE_OFFSET=?,DST_OFFSET=?])
java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:56)
rethrown as org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1541)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:56)
Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 ... 5 more
rethrown as org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1373)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:385)
 at com.kazmier.om.BaseBookPeer.doSelectVillageRecords(BaseBookPeer.java:353)
 at com.kazmier.om.BaseBookPeer.doSelect(BaseBookPeer.java:321)
 at com.kazmier.mytest.main(mytest.java:56)
Caused by: org.apache.torque.TorqueException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1541)
 at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:1360)
 ... 4 more
Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '[time=?,areFieldsSet=false,areAllFieldsSet=false,lenient=true,z"
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
 at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
 at com.mysql.jdbc.Statement.executeQuery(Statement.java:1143)
 at com.workingdogs.village.QueryDataSet.<init>(Unknown Source)
 at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:1531)
 ... 5 more

Re: Question with "addDate" and "andDate"!

Posted by Murvai-Buzogány László <la...@t-sys.hu>.
Hi there,

just as a fast advice: conforming to Torque Javadocs, try using 
crit1.and(BookPeer.TIME, new GregorianCalendar(2003, 7, 1), 
Criteria.LESS_EQUAL) instead of andDate. Since addDate and andDate show 
handle dates in a similar way, there may be a problem in the implementation 
of the date-related and() function, which does not generate a mysql- 
compliant date-string from GregorianCalendar (that's why the serialized 
form og the GregorianCalendar object appears in the query).

What version of Torque are you using? It may be an issue already fixed in 
the latest version/s.

On Fri, 1 Aug 2003 21:03:02 +0800, sailorhero <sa...@hotmail.com> 
wrote:
> I use addDate() and andDate() to query some line .
>
> first:
> I want to know where is the diffirent between the two methods.

It differs in a very trivial manner: add* adds only one criterion, and the 
second time you call it, it replaces the first one. Add* functions are 
useful for adding one criterion to a query, or adding the first one. And* 
adds additional criterion, which then will be separated by 'and's in the 
query.


Best regards,
	Laszlo
-- 
Murvai-Buzogány László
T-Sys Kft.
Software developer
Tel.: (06)-1 436-8724
Laszlo.Murvai-Buzogany@t-sys.hu

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org