You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tuscany.apache.org by Kevin Williams <ke...@qwest.net> on 2006/06/13 22:41:32 UTC
DAS Needs a new parameter marker (T-406)
DAS has a problem with its attempt to support named parameters in SQL as in:
command = Factory.createCommand ("select * from Customer where
Customer.id = :id")
command.setParm("id", 500);
We have been using a colon as the parameter marker and this works well
since we can do a simple scan of the select statement to find the named
parameter markers and replace them with '?'. Unfortunately, a scan is
not sufficient when the SQL uses colons as part of the statement itself,
like in an embedded timestamp. To pluck out the parameter markers in
these more complicated cases requires a parser and, IMO, the DAS has no
business parsing SQL.
So, what I propose is to remove support for named parameters altogether
and leave clients with indexed parameter access like this:
command = Factory.createCommand ("select * from Customer where
Customer.id = ?")
command.setParm(1, 500);
Any opinions?
Thanks,
--Kevin
---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org
Re: DAS Needs a new parameter marker (T-406)
Posted by Kevin Williams <ke...@qwest.net>.
I believe it is best for the DAS to not manipulate the provided SQL and
also to require that clients provide SQL that conforms to the standard.
I would like to remove support for named parameters if there is no
objection.
Thanks,
--Kevin
Kevin Williams wrote:
> The problem is that our marker ':' can be used in SQL although, as far
> as I can tell, only within embedded single or double quotes like this:
>
> Select "fif:fi'ef" from 'sefti:iend' where "sdsd:ske" =:id and age
> >:age and addr = :addr
>
> Which we need to convert to
>
> Select "fif:fi'ef" from 'sefti:iend' where "sdsd:ske" =? and age >?
> and addr = ?
>
> The following algorithm was suggested by Don Clare and seems to work:
>
> final static String Q_MARK = "?";
> final static String SQL_QUOTED_SEQUENCE_PATTERN = "(['\\\"]).*?\\1";
> final static String SQL_HOST_VARIABLE_PATTERN = "(\\W):(\\w*)";
>
> public static String replaceHostVariables(String inStr) {
> ArrayList<Integer> indices = new ArrayList<Integer>();
> indices.add(0);
> StringBuffer sb = new StringBuffer();
> Pattern p =
> Pattern.compile(SQL_QUOTED_SEQUENCE_PATTERN,Pattern.DOTALL);
> Matcher m = p.matcher(inStr);
> while (m.find()) {
> indices.add(m.start());
> indices.add(m.end());
> }
> indices.add(inStr.length());
> Iterator<Integer> i = indices.iterator();
> p = Pattern.compile(SQL_HOST_VARIABLE_PATTERN,Pattern.DOTALL);
> while (i.hasNext()) {
> m.usePattern(p);
> m.region(i.next(), i.next());
> while (m.find()) {
> m.appendReplacement(sb, m.group(1) + Q_MARK);
> }
> }
> m.appendTail(sb);
> return sb.toString();
> }
>
> EJB supports named parameters as part of EJBQL but only positional
> parameters for native queries.
>
> I am really torn here. I see the value in named parms but wonder if
> the DAS should have to work so hard to provide it when this is not yet
> part of the SQL standard.
>
> Thanks,
>
> --Kevin
>
>
>
>
> Jeremy Boynes wrote:
>
>> There are a lot of benefits to named parameters and users seem to like
>> them - it would be a shame to see them go.
>>
>> How complex would a parser be? Wouldn't it just need to handle the
>> distinction between string values/comments etc. and SQL text? That
>> would be a lot simpler than parsing the actual SQL.
>>
>> What does EJB3 do for native queries? Can we do something like that
>> (or better)?
>> --
>> Jeremy
>>
>> On 6/13/06, Kevin Williams <ke...@qwest.net> wrote:
>>
>>> DAS has a problem with its attempt to support named parameters in
>>> SQL as in:
>>>
>>> command = Factory.createCommand ("select * from Customer where
>>> Customer.id = :id")
>>> command.setParm("id", 500);
>>>
>>> We have been using a colon as the parameter marker and this works well
>>> since we can do a simple scan of the select statement to find the named
>>> parameter markers and replace them with '?'. Unfortunately, a scan is
>>> not sufficient when the SQL uses colons as part of the statement
>>> itself,
>>> like in an embedded timestamp. To pluck out the parameter markers in
>>> these more complicated cases requires a parser and, IMO, the DAS has no
>>> business parsing SQL.
>>>
>>> So, what I propose is to remove support for named parameters altogether
>>> and leave clients with indexed parameter access like this:
>>>
>>> command = Factory.createCommand ("select * from Customer where
>>> Customer.id = ?")
>>> command.setParm(1, 500);
>>>
>>> Any opinions?
>>>
>>> Thanks,
>>>
>>> --Kevin
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
>>> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
>> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>>
>>
>>
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org
Re: DAS Needs a new parameter marker (T-406)
Posted by Kevin Williams <ke...@qwest.net>.
The problem is that our marker ':' can be used in SQL although, as far
as I can tell, only within embedded single or double quotes like this:
Select "fif:fi'ef" from 'sefti:iend' where "sdsd:ske" =:id and age
>:age and addr = :addr
Which we need to convert to
Select "fif:fi'ef" from 'sefti:iend' where "sdsd:ske" =? and age >?
and addr = ?
The following algorithm was suggested by Don Clare and seems to work:
final static String Q_MARK = "?";
final static String SQL_QUOTED_SEQUENCE_PATTERN = "(['\\\"]).*?\\1";
final static String SQL_HOST_VARIABLE_PATTERN = "(\\W):(\\w*)";
public static String replaceHostVariables(String inStr) {
ArrayList<Integer> indices = new ArrayList<Integer>();
indices.add(0);
StringBuffer sb = new StringBuffer();
Pattern p =
Pattern.compile(SQL_QUOTED_SEQUENCE_PATTERN,Pattern.DOTALL);
Matcher m = p.matcher(inStr);
while (m.find()) {
indices.add(m.start());
indices.add(m.end());
}
indices.add(inStr.length());
Iterator<Integer> i = indices.iterator();
p = Pattern.compile(SQL_HOST_VARIABLE_PATTERN,Pattern.DOTALL);
while (i.hasNext()) {
m.usePattern(p);
m.region(i.next(), i.next());
while (m.find()) {
m.appendReplacement(sb, m.group(1) + Q_MARK);
}
}
m.appendTail(sb);
return sb.toString();
}
EJB supports named parameters as part of EJBQL but only positional
parameters for native queries.
I am really torn here. I see the value in named parms but wonder if the
DAS should have to work so hard to provide it when this is not yet part
of the SQL standard.
Thanks,
--Kevin
Jeremy Boynes wrote:
> There are a lot of benefits to named parameters and users seem to like
> them - it would be a shame to see them go.
>
> How complex would a parser be? Wouldn't it just need to handle the
> distinction between string values/comments etc. and SQL text? That
> would be a lot simpler than parsing the actual SQL.
>
> What does EJB3 do for native queries? Can we do something like that
> (or better)?
> --
> Jeremy
>
> On 6/13/06, Kevin Williams <ke...@qwest.net> wrote:
>
>> DAS has a problem with its attempt to support named parameters in SQL
>> as in:
>>
>> command = Factory.createCommand ("select * from Customer where
>> Customer.id = :id")
>> command.setParm("id", 500);
>>
>> We have been using a colon as the parameter marker and this works well
>> since we can do a simple scan of the select statement to find the named
>> parameter markers and replace them with '?'. Unfortunately, a scan is
>> not sufficient when the SQL uses colons as part of the statement itself,
>> like in an embedded timestamp. To pluck out the parameter markers in
>> these more complicated cases requires a parser and, IMO, the DAS has no
>> business parsing SQL.
>>
>> So, what I propose is to remove support for named parameters altogether
>> and leave clients with indexed parameter access like this:
>>
>> command = Factory.createCommand ("select * from Customer where
>> Customer.id = ?")
>> command.setParm(1, 500);
>>
>> Any opinions?
>>
>> Thanks,
>>
>> --Kevin
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
>> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org
Re: DAS Needs a new parameter marker (T-406)
Posted by Jeremy Boynes <jb...@apache.org>.
There are a lot of benefits to named parameters and users seem to like
them - it would be a shame to see them go.
How complex would a parser be? Wouldn't it just need to handle the
distinction between string values/comments etc. and SQL text? That
would be a lot simpler than parsing the actual SQL.
What does EJB3 do for native queries? Can we do something like that (or better)?
--
Jeremy
On 6/13/06, Kevin Williams <ke...@qwest.net> wrote:
> DAS has a problem with its attempt to support named parameters in SQL as in:
>
> command = Factory.createCommand ("select * from Customer where
> Customer.id = :id")
> command.setParm("id", 500);
>
> We have been using a colon as the parameter marker and this works well
> since we can do a simple scan of the select statement to find the named
> parameter markers and replace them with '?'. Unfortunately, a scan is
> not sufficient when the SQL uses colons as part of the statement itself,
> like in an embedded timestamp. To pluck out the parameter markers in
> these more complicated cases requires a parser and, IMO, the DAS has no
> business parsing SQL.
>
> So, what I propose is to remove support for named parameters altogether
> and leave clients with indexed parameter access like this:
>
> command = Factory.createCommand ("select * from Customer where
> Customer.id = ?")
> command.setParm(1, 500);
>
> Any opinions?
>
> Thanks,
>
> --Kevin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org