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 Brendan Miller <bm...@dotster.com> on 2008/03/07 00:53:37 UTC
Criteria.addIn on List or Object[] doesn't generate quoted strings?
I am trying to use the addIn() method of the Criteria class.
I have a table with various states. I would like to query objects whose
state is in a list I define.
I have defined my states thusly:
public enum State { NEW, ACTIVE, SUSPENDED, CANCELED };
I have used both
crit.addIn(ServicePeer.State, new ArrayList<State> {{
add(State.NEW);
add(State.ACTIVE);
}});
and
crit.addIn(ServicePeer.State, { State.NEW, State.ACTIVE });
Both generate SQL as
SELECT SERVICE.ID, ... FROM SERVICE WHERE SERVICE.STATE IN (NEW,ACTIVE)
Obviously, NEW and ACTIVE need to be quoted. If I pass a List or array
of Strings, of course it works just fine. (I know I can get a list or
array of Strings by calling .toString() on my enum values.)
But the documentation says:
Adds an 'IN' clause with the criteria supplied as an Object array. For
example:
FOO.NAME IN ('FOO', 'BAR', 'ZOW')
where 'values' contains three objects that evaluate to the respective
strings above when .toString() is called.
This led me to believe that .toString() would be called internally. I
guess it is (as I get the string literals in the SQL), but the strings
are not quoted. Shouldn't they be?
Is this a bug, or am I misreading the documentation? If it's a bug,
I'd be happy to enter a JIRA and attempt a patch.
Brendan
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Criteria.addIn on List or Object[] doesn't generate quoted strings?
Posted by Greg Monroe <Gr...@DukeCE.com>.
> From: Brendan Miller [mailto:bmiller@dotster.com]
>
> Out of curiosity, what kind of performance implications do you think
> any of this (or your DataMap idea) would incur--is it really that
huge?
Well, my first thought was that the look up shouldn't occur in the
processInValue method. This gets called for each element in the
array which could be hundreds of times.
IMHO, the "ideal" solution would be that once Criteria are being
processed into SQL, there should be one lookup that converts the
column info into a Column object. This would have to deal with
"pseudo" columns (E.g. aliased or function items). This object
would have a method to get the ColumnMap object if it's a "real"
column.
This object is what is passed to all the build methods instead
of a plain String "column" object. This would limit the
lookup overhead to once per each criteria.
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Criteria.addIn on List or Object[] doesn't generate quoted strings?
Posted by Brendan Miller <bm...@dotster.com>.
Greg:
Thanks for the quick redux. I hadn't had a chance to trace it myself.
In order to Get Things Done, I just passed a list/array of Strings
(called .toString() on my enums myself).
I'm not familiar with DataMap, but I have seen some ocde "in there"
to implement particular rules based on SQL type.
It does seem like simply testing for getClass().equals(String.class)
is a bit short of complete. I totally get what you're saying about
Floats, Integers, etc. The enum I was using is a bit of a special
case. I use enums in lieu of string literals to provide better
typing on values for things like entity states as in my example, but
I can see where someone doing an addIn() might want the in-list
constructed using element.ordinal() as opposed to element.toString()...
And yet, the documentation alludes to .toString() being called--it just
surprised me that it didn't quote them. In Oracle, quoting the toString
result of a Integer would probably cause a ORA-xxxx "yo SQL is bad"
error, but I think MySQL would swallow it just fine. I don't know what
SQL-92 or somesuch says. So I think it should not always quote. But
if it's going to call toString()... <sigh>
Out of curiosity, what kind of performance implications do you think
any of this (or your DataMap idea) would incur--is it really that huge?
If you lean toward anything worthwhile, I'm more than happy to tinker
on it.
Brendan
On Thu, Mar 06, 2008 at 08:11:18PM -0500, Greg Monroe wrote:
> I traced out the code and found that the applicable
> section of code is the SQLExpression.processInValue
> method. This is called with each member of the list
> or array specified.
>
> This tests if the object is a String or not. If it's
> a string, it calls quoteAndEscapeText. However, if
> it's not a String, it just calls the object's toString()
> method.
>
> The problem is that there is no clean way to determine
> if this type of object is supposed to be a quoted string
> or an unquoted string. Suppose that it's a list of
> Integer or Float objects? To wrap these in quotes would
> be wrong in this case.
>
> Hmm, I suppose the code could use the DataMap structure
> to look up the SQL field type and then decide. But that
> assumes that the "Column" part of the IN is a true
> table column. Also, some thought would need to go into
> how to make this not be a performance issue.
>
>
> > -----Original Message-----
> > From: Brendan Miller [mailto:bmiller@dotster.com]
> > Sent: Thursday, March 06, 2008 6:54 PM
> > To: torque-user@db.apache.org
> > Subject: Criteria.addIn on List or Object[] doesn't generate quoted
> > strings?
> >
> >
> > I am trying to use the addIn() method of the Criteria class.
> >
> > I have a table with various states. I would like to query objects
> > whose
> > state is in a list I define.
> >
> > I have defined my states thusly:
> >
> > public enum State { NEW, ACTIVE, SUSPENDED, CANCELED };
> >
> > I have used both
> >
> > crit.addIn(ServicePeer.State, new ArrayList<State> {{
> > add(State.NEW);
> > add(State.ACTIVE);
> > }});
> >
> > and
> >
> > crit.addIn(ServicePeer.State, { State.NEW, State.ACTIVE });
> >
> > Both generate SQL as
> >
> > SELECT SERVICE.ID, ... FROM SERVICE WHERE SERVICE.STATE IN
> (NEW,ACTIVE)
> >
> > Obviously, NEW and ACTIVE need to be quoted. If I pass a List or
> array
> > of Strings, of course it works just fine. (I know I can get a list or
> > array of Strings by calling .toString() on my enum values.)
> >
> > But the documentation says:
> >
> > Adds an 'IN' clause with the criteria supplied as an Object array. For
> > example:
> >
> > FOO.NAME IN ('FOO', 'BAR', 'ZOW')
> >
> > where 'values' contains three objects that evaluate to the respective
> > strings above when .toString() is called.
> >
> >
> > This led me to believe that .toString() would be called internally. I
> > guess it is (as I get the string literals in the SQL), but the strings
> > are not quoted. Shouldn't they be?
> >
> > Is this a bug, or am I misreading the documentation? If it's a bug,
> > I'd be happy to enter a JIRA and attempt a patch.
> >
> > Brendan
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Criteria.addIn on List or Object[] doesn't generate quoted strings?
Posted by Raul Acevedo <ra...@cantara.com>.
Apparently (and unfortunately) true. Unless someone speaks up and
says there is a way to get Torque to do prepared statements for
addIn, I'm going to have to convert our code to use OR instead of IN.
Raul
On Mar 7, 2008, at 7:06 AM, Brendan Miller wrote:
> Also, on the topic of addIn() and in-lists, is it true that
> Torque does not generate prepared statements with bind variables
> for the in list elements?
>
> Brendan
>
> On Thu, Mar 06, 2008 at 08:11:18PM -0500, Greg Monroe wrote:
>> I traced out the code and found that the applicable
>> section of code is the SQLExpression.processInValue
>> method. This is called with each member of the list
>> or array specified.
>>
>> This tests if the object is a String or not. If it's
>> a string, it calls quoteAndEscapeText. However, if
>> it's not a String, it just calls the object's toString()
>> method.
>>
>> The problem is that there is no clean way to determine
>> if this type of object is supposed to be a quoted string
>> or an unquoted string. Suppose that it's a list of
>> Integer or Float objects? To wrap these in quotes would
>> be wrong in this case.
>>
>> Hmm, I suppose the code could use the DataMap structure
>> to look up the SQL field type and then decide. But that
>> assumes that the "Column" part of the IN is a true
>> table column. Also, some thought would need to go into
>> how to make this not be a performance issue.
>>
>>
>>> -----Original Message-----
>>> From: Brendan Miller [mailto:bmiller@dotster.com]
>>> Sent: Thursday, March 06, 2008 6:54 PM
>>> To: torque-user@db.apache.org
>>> Subject: Criteria.addIn on List or Object[] doesn't generate quoted
>>> strings?
>>>
>>>
>>> I am trying to use the addIn() method of the Criteria class.
>>>
>>> I have a table with various states. I would like to query objects
>>> whose
>>> state is in a list I define.
>>>
>>> I have defined my states thusly:
>>>
>>> public enum State { NEW, ACTIVE, SUSPENDED, CANCELED };
>>>
>>> I have used both
>>>
>>> crit.addIn(ServicePeer.State, new ArrayList<State> {{
>>> add(State.NEW);
>>> add(State.ACTIVE);
>>> }});
>>>
>>> and
>>>
>>> crit.addIn(ServicePeer.State, { State.NEW, State.ACTIVE });
>>>
>>> Both generate SQL as
>>>
>>> SELECT SERVICE.ID, ... FROM SERVICE WHERE SERVICE.STATE IN
>> (NEW,ACTIVE)
>>>
>>> Obviously, NEW and ACTIVE need to be quoted. If I pass a List or
>> array
>>> of Strings, of course it works just fine. (I know I can get a
>>> list or
>>> array of Strings by calling .toString() on my enum values.)
>>>
>>> But the documentation says:
>>>
>>> Adds an 'IN' clause with the criteria supplied as an Object
>>> array. For
>>> example:
>>>
>>> FOO.NAME IN ('FOO', 'BAR', 'ZOW')
>>>
>>> where 'values' contains three objects that evaluate to the
>>> respective
>>> strings above when .toString() is called.
>>>
>>>
>>> This led me to believe that .toString() would be called
>>> internally. I
>>> guess it is (as I get the string literals in the SQL), but the
>>> strings
>>> are not quoted. Shouldn't they be?
>>>
>>> Is this a bug, or am I misreading the documentation? If it's a bug,
>>> I'd be happy to enter a JIRA and attempt a patch.
>>>
>>> Brendan
>>>
>>>
>>> --------------------------------------------------------------------
>>> -
>>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: torque-user-help@db.apache.org
>>
>> DukeCE Privacy Statement:
>> Please be advised that this e-mail and any files transmitted with
>> it are confidential communication or may otherwise be privileged or
>> confidential and are intended solely for the individual or entity
>> to whom they are addressed. If you are not the intended recipient
>> you may not rely on the contents of this email or any attachments,
>> and we ask that you please not read, copy or retransmit this
>> communication, but reply to the sender and destroy the email, its
>> contents, and all copies thereof immediately. Any unauthorized
>> dissemination, distribution or copying of this communication is
>> strictly prohibited.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-user-help@db.apache.org
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Criteria.addIn on List or Object[] doesn't generate quoted strings?
Posted by Brendan Miller <bm...@dotster.com>.
Also, on the topic of addIn() and in-lists, is it true that
Torque does not generate prepared statements with bind variables
for the in list elements?
Brendan
On Thu, Mar 06, 2008 at 08:11:18PM -0500, Greg Monroe wrote:
> I traced out the code and found that the applicable
> section of code is the SQLExpression.processInValue
> method. This is called with each member of the list
> or array specified.
>
> This tests if the object is a String or not. If it's
> a string, it calls quoteAndEscapeText. However, if
> it's not a String, it just calls the object's toString()
> method.
>
> The problem is that there is no clean way to determine
> if this type of object is supposed to be a quoted string
> or an unquoted string. Suppose that it's a list of
> Integer or Float objects? To wrap these in quotes would
> be wrong in this case.
>
> Hmm, I suppose the code could use the DataMap structure
> to look up the SQL field type and then decide. But that
> assumes that the "Column" part of the IN is a true
> table column. Also, some thought would need to go into
> how to make this not be a performance issue.
>
>
> > -----Original Message-----
> > From: Brendan Miller [mailto:bmiller@dotster.com]
> > Sent: Thursday, March 06, 2008 6:54 PM
> > To: torque-user@db.apache.org
> > Subject: Criteria.addIn on List or Object[] doesn't generate quoted
> > strings?
> >
> >
> > I am trying to use the addIn() method of the Criteria class.
> >
> > I have a table with various states. I would like to query objects
> > whose
> > state is in a list I define.
> >
> > I have defined my states thusly:
> >
> > public enum State { NEW, ACTIVE, SUSPENDED, CANCELED };
> >
> > I have used both
> >
> > crit.addIn(ServicePeer.State, new ArrayList<State> {{
> > add(State.NEW);
> > add(State.ACTIVE);
> > }});
> >
> > and
> >
> > crit.addIn(ServicePeer.State, { State.NEW, State.ACTIVE });
> >
> > Both generate SQL as
> >
> > SELECT SERVICE.ID, ... FROM SERVICE WHERE SERVICE.STATE IN
> (NEW,ACTIVE)
> >
> > Obviously, NEW and ACTIVE need to be quoted. If I pass a List or
> array
> > of Strings, of course it works just fine. (I know I can get a list or
> > array of Strings by calling .toString() on my enum values.)
> >
> > But the documentation says:
> >
> > Adds an 'IN' clause with the criteria supplied as an Object array. For
> > example:
> >
> > FOO.NAME IN ('FOO', 'BAR', 'ZOW')
> >
> > where 'values' contains three objects that evaluate to the respective
> > strings above when .toString() is called.
> >
> >
> > This led me to believe that .toString() would be called internally. I
> > guess it is (as I get the string literals in the SQL), but the strings
> > are not quoted. Shouldn't they be?
> >
> > Is this a bug, or am I misreading the documentation? If it's a bug,
> > I'd be happy to enter a JIRA and attempt a patch.
> >
> > Brendan
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
>
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Criteria.addIn on List or Object[] doesn't generate quoted strings?
Posted by Greg Monroe <Gr...@DukeCE.com>.
I traced out the code and found that the applicable
section of code is the SQLExpression.processInValue
method. This is called with each member of the list
or array specified.
This tests if the object is a String or not. If it's
a string, it calls quoteAndEscapeText. However, if
it's not a String, it just calls the object's toString()
method.
The problem is that there is no clean way to determine
if this type of object is supposed to be a quoted string
or an unquoted string. Suppose that it's a list of
Integer or Float objects? To wrap these in quotes would
be wrong in this case.
Hmm, I suppose the code could use the DataMap structure
to look up the SQL field type and then decide. But that
assumes that the "Column" part of the IN is a true
table column. Also, some thought would need to go into
how to make this not be a performance issue.
> -----Original Message-----
> From: Brendan Miller [mailto:bmiller@dotster.com]
> Sent: Thursday, March 06, 2008 6:54 PM
> To: torque-user@db.apache.org
> Subject: Criteria.addIn on List or Object[] doesn't generate quoted
> strings?
>
>
> I am trying to use the addIn() method of the Criteria class.
>
> I have a table with various states. I would like to query objects
> whose
> state is in a list I define.
>
> I have defined my states thusly:
>
> public enum State { NEW, ACTIVE, SUSPENDED, CANCELED };
>
> I have used both
>
> crit.addIn(ServicePeer.State, new ArrayList<State> {{
> add(State.NEW);
> add(State.ACTIVE);
> }});
>
> and
>
> crit.addIn(ServicePeer.State, { State.NEW, State.ACTIVE });
>
> Both generate SQL as
>
> SELECT SERVICE.ID, ... FROM SERVICE WHERE SERVICE.STATE IN
(NEW,ACTIVE)
>
> Obviously, NEW and ACTIVE need to be quoted. If I pass a List or
array
> of Strings, of course it works just fine. (I know I can get a list or
> array of Strings by calling .toString() on my enum values.)
>
> But the documentation says:
>
> Adds an 'IN' clause with the criteria supplied as an Object array. For
> example:
>
> FOO.NAME IN ('FOO', 'BAR', 'ZOW')
>
> where 'values' contains three objects that evaluate to the respective
> strings above when .toString() is called.
>
>
> This led me to believe that .toString() would be called internally. I
> guess it is (as I get the string literals in the SQL), but the strings
> are not quoted. Shouldn't they be?
>
> Is this a bug, or am I misreading the documentation? If it's a bug,
> I'd be happy to enter a JIRA and attempt a patch.
>
> Brendan
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org