Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

Hi Evgeny,

Thanks for explaining it in detail. I think now I am clear on the  
proposed scope of the feature and the motivation behind it. While this  
didn't change my initial assessment of the redundancy of the  
#bind($bind) option, still I see no harm in the extra functionality of  
the #bind directive, especially considering that you will be  
submitting a patch for it (so I agree to disagree :-)). So let's go  
for it.


I suggest that we don't limit it to just #bind($bind), but support it  
for all flavors of the bind directive. Also like CAY-1282, this is  
something that will go to 3.0 only. As we've contemplated doing a 3.0  
beta release soon, keep us posted on the progress, as beta would also  
mean a complete feature freeze for 3.0. And on the upside, it may mean  
that your company can start using 3.0 as an API-stable release in your  
work.


Thanks,
Andrus


On Oct 4, 2009, at 6:56 PM, Evgeny Ryabitskiy wrote:

So this argument is based on the fact that people are using #bind()  
already.

With the new approach they'd just need to optionally change the Java
parameter code, and the alternative would be to change both  
template code as

well as Java code.


Yes! My argument is based on that people are using #bind already in
our NamedQueries in our xml files... And no one want to change
something that was written once and still working...
Maybe one thing that I forgot to tell... Any our NamedQuery can be
used in lot's of places... Yeah... huge bank systems
And I can't change it one place from Java and one from queries. I
should find every use of this query in Java and change it to use
BindObject it's too painful!
So that is why Queries should be independent from Java-code, that is
why I need queries in one stile and can't just delete somewhere #bind
and somewhere not.
So again I have only 2 options.. no compromises: 1)everywhere #bind;
2) nowhere #bind and special BindObject everywhere in Java Code.

I am not going to choose second option... :) So without overwriting
this API tool is useless for us.
I know Cayenne is not just for me and my project (even my project is
great!)... but I also don't think that my case is so unic.
I'm not along who wish to have queries in one style!



I am still not convinced that the second option is any
harder or more confusing. To me the first option is confusing  
actually -
when I pass a binding object, and the directive already has some  
properties,
it is not immediately obvious which set of properties will take  
effect.


For me and my team it's absolutely not confusing. Look.. we have some
NamedQuery.. it's used in several places.
So in one place we are passing null and I wish to pass type with this
null to do some hint for JDBC driver to use right type mapping.
So I am overwriting default mapping from #bind($..) by BindValue
object and... few lines later.. I am expecting right type mapping in
result. Don't think it should be confusing to developer.

Just like you in this case, I always look at the new API from the  
point of
view of whether it is internally consistent and easy to explain to  
the end
users. In this case I think having "$xyzSpecialObject" as an  
alternative to
an "#XYZ" directive seems like a straightforward symmetrical  
replacement. At

the same time using "#XYZ($xyzSpecialObject $p1 $p2 $p3)" as a way to
override some parameters appears indeterministic to an uninitiated  
person.


When I was talking about overwriting I had in mind just #bind($...)
directive, without explicit set of JDBC type.
The point of "my" overwriting is to control implicit binding (where is
no JDBC type parameter in SQLTemplate).
So maybe in case of #bind($param 'VARCHAR') it can be confusing to
overwrite type from API.

So... I can suggest one more compromise to overwrite #bind($..) only
where is no explicit JDBC type. Less confusing... more safe from
developer point


Evgeny Ryabitskiy.

Re: Passing null values in parameters, part 2 )

[Evgeny Ryabitskiy]

> So this argument is based on the fact that people are using #bind() already.
> With the new approach they'd just need to optionally change the Java
> parameter code, and the alternative would be to change both template code as
> well as Java code.

Yes! My argument is based on that people are using #bind already in
our NamedQueries in our xml files... And no one want to change
something that was written once and still working...
Maybe one thing that I forgot to tell... Any our NamedQuery can be
used in lot's of places... Yeah... huge bank systems
And I can't change it one place from Java and one from queries. I
should find every use of this query in Java and change it to use
BindObject it's too painful!
So that is why Queries should be independent from Java-code, that is
why I need queries in one stile and can't just delete somewhere #bind
and somewhere not.
So again I have only 2 options.. no compromises: 1)everywhere #bind;
2) nowhere #bind and special BindObject everywhere in Java Code.

I am not going to choose second option... :) So without overwriting
this API tool is useless for us.
I know Cayenne is not just for me and my project (even my project is
great!)... but I also don't think that my case is so unic.
I'm not along who wish to have queries in one style!


> I am still not convinced that the second option is any
> harder or more confusing. To me the first option is confusing actually -
> when I pass a binding object, and the directive already has some properties,
> it is not immediately obvious which set of properties will take effect.

For me and my team it's absolutely not confusing. Look.. we have some
NamedQuery.. it's used in several places.
So in one place we are passing null and I wish to pass type with this
null to do some hint for JDBC driver to use right type mapping.
So I am overwriting default mapping from #bind($..) by BindValue
object and... few lines later.. I am expecting right type mapping in
result. Don't think it should be confusing to developer.

> Just like you in this case, I always look at the new API from the point of
> view of whether it is internally consistent and easy to explain to the end
> users. In this case I think having "$xyzSpecialObject" as an alternative to
> an "#XYZ" directive seems like a straightforward symmetrical replacement. At
> the same time using "#XYZ($xyzSpecialObject $p1 $p2 $p3)" as a way to
> override some parameters appears indeterministic to an uninitiated person.

When I was talking about overwriting I had in mind just #bind($...)
directive, without explicit set of JDBC type.
The point of "my" overwriting is to control implicit binding (where is
no JDBC type parameter in SQLTemplate).
So maybe in case of #bind($param 'VARCHAR') it can be confusing to
overwrite type from API.

So... I can suggest one more compromise to overwrite #bind($..) only
where is no explicit JDBC type. Less confusing... more safe from
developer point


Evgeny Ryabitskiy.

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

On Sep 30, 2009, at 6:21 PM, Evgeny Ryabitskiy wrote:

Without this option I have to variants:
1) Change #bind($) to $... everywhere and every time pass our
special mapping objects to parameters (I will die/be killed by
colleges early...).


So this argument is based on the fact that people are using #bind()  
already. With the new approach they'd just need to optionally change  
the Java parameter code, and the alternative would be to change both  
template code as well as Java code. I am still not convinced that the  
second option is any harder or more confusing. To me the first option  
is confusing actually - when I pass a binding object, and the  
directive already has some properties, it is not immediately obvious  
which set of properties will take effect.



2) Use not general style in SQLTemplate, somewhere with #bind,
somewhere not... Firstly it's confusing... Second Is potential errors
when I forget where I should pass simple Object and where is special
bind object.


Our current approach with Velocity templates is by definition error- 
prone, as there's no parameter type checking anywhere. IMO adding the  
new option won't make it better or worse in this respect.


Just like you in this case, I always look at the new API from the  
point of view of whether it is internally consistent and easy to  
explain to the end users. In this case I think having  
"$xyzSpecialObject" as an alternative to an "#XYZ" directive seems  
like a straightforward symmetrical replacement. At the same time using  
"#XYZ($xyzSpecialObject $p1 $p2 $p3)" as a way to override some  
parameters appears indeterministic to an uninitiated person.


Cheers,
Andrus

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

On Sep 30, 2009, at 6:21 PM, Evgeny Ryabitskiy wrote:


P.S. Patch was for CAY-1282 ready in the morning (hours ago) but... I
couldn't login to Apache JIRA :


I can confirm that the problem was on Apache side.

Andrus

Re: Passing null values in parameters, part 2 )

[Evgeny Ryabitskiy]

> Trying to understand whether the second option is redundant. Do you feel
> like option #1 won't be sufficient and we need to mix BindDirective with
> #bind? Maybe you have a good example why this is important?

Ok. Example... So now there is all queries with #bind($..) where
parameters are passed.. I tolled everyone to use bind everywhere...
it's not confusing to anyone who writes or fixes(modifies) queries.

So now we have type problem in query from first letter in this chain :
SELECT isnull(#bind($MyNumericColumn), AnotherNumericColumn) AS
MyColumn FROM MyTable
My wish is sometimes overwrite type mapping while passing parameters
especially null parameters via API (that is exactly my  second option
wich is not redundant).

Without this option I have to variants:
1) Change #bind($) to $... everywhere and every time pass our
special mapping objects to parameters (I will die/be killed by
colleges early...).
2) Use not general style in SQLTemplate, somewhere with #bind,
somewhere not... Firstly it's confusing... Second Is potential errors
when I forget where I should pass simple Object and where is special
bind object.


Hope I wrote my thoughts in understandable way... :)

> BTW if we can come up with a sensible implementation of CAY-1282 that you
> just opened, it will be great. This will require some smart matching of
> column names obtained from ResultSetMetadata with explicitly mapped columns.
> Not impossible I guess.

Already post a patch for CAY-1282 ;)   + JUnit. Looks like it's fixing things.
Also about #result directive... I wish to have ResultDirective also
for 2 cases: 1) to explicitly set type. 2) to overwrite type sittings
from #result (back compatibility for old queries is useful thing ).

P.S. Patch was for CAY-1282 ready in the morning (hours ago) but... I
couldn't login to Apache JIRA :
Did you had same problem or I should kick some Sys-admin to
reconfigure our proxy at work?...

Evgeny Ryabitskiy.

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

On Sep 29, 2009, at 5:42 PM, Evgeny Ryabitskiy wrote:


1) When we don't have bind in template:

SELECT * FROM Table1 where PersonName = $MyParamName




2) When we have #bind in template but wish to overwrite it's
parameters (JDBC type and precision) via API.

SELECT * FROM Table1 where PersonID = @bind($MyParamName 'NUMERIC' 2)
/* pass BindDirective with precision 0 here */


Trying to understand whether the second option is redundant. Do you  
feel like option #1 won't be sufficient and we need to mix  
BindDirective with #bind? Maybe you have a good example why this is  
important?




About ResultDirective... it is used for SQL return... so we can pass
name, jdbc type and precision (no value) to parameters and use it's as
hints while mapping to return values.


Yeah, the idea here is to fully emulate #result() directive behavior,  
so the properties will be the same as the #result() parameters.


BTW if we can come up with a sensible implementation of CAY-1282 that  
you just opened, it will be great. This will require some smart  
matching of column names obtained from ResultSetMetadata with  
explicitly mapped columns. Not impossible I guess.


P.S. Sorry for so slow answering, GMail page is banned at my work  
proxy :/


No worries. People here are used to asynchronous communication.

Andrus

Re: Passing null values in parameters, part 2 )

[Evgeny Ryabitskiy]

> It will work exactly like #bind directive if the parameter is BindDirective,
> as #result if a parameter is a ResultDirective, etc. It seems to be equally
> counterintuitive to pass a special wrapper object in a variable inside of
> #bind, as it is in a standalone variable :-) But in the later case we get a
> more concise syntax that can help in case of large queries.

Ok, I like your idea! So I can suggest to use BindDirective in 2 cases:

1) When we don't have bind in template:

SELECT * FROM Table1 where PersonName = $MyParamName

2) When we have #bind in template but wish to overwrite it's
parameters (JDBC type and precision) via API.

SELECT * FROM Table1 where PersonID = @bind($MyParamName 'NUMERIC' 2)
 /* pass BindDirective with precision 0 here */


About ResultDirective... it is used for SQL return... so we can pass
name, jdbc type and precision (no value) to parameters and use it's as
hints while mapping to return values.

Any more ideas? )

Evgeny Ryabitskiy.

P.S. Sorry for so slow answering, GMail page is banned at my work proxy :/

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

On Sep 29, 2009, at 12:43 PM, Evgeny Ryabitskiy wrote:

I create CAY-1280 and attached draft patch. Maybe I should export  
this mail conversion there?


Yeah I saw that. I suggest we keep the discussion on the list for now,  
until we come to some conclusion. Then we can simply add a thread  
archive URL to the Jira for reference.



in theory we can do
intelligent analyze of SQL query and parameter to determine if we
should do bind or put it in template (like table name) but it's not
cool!


Yes, this is what I had in mind. E.g. making special parameters to  
implement "Directive" interface of some sort. Why do you think this is  
a bad idea?


It will work exactly like #bind directive if the parameter is  
BindDirective, as #result if a parameter is a ResultDirective, etc. It  
seems to be equally counterintuitive to pass a special wrapper object  
in a variable inside of #bind, as it is in a standalone variable :-)  
But in the later case we get a more concise syntax that can help in  
case of large queries.


Andrus

Re: Passing null values in parameters, part 2 )

[Evgeny Ryabitskiy]

> i.e. from the user standpoint, extra #bind(..) wrapper seems redundant. Now 
> wonder if we can extend that to other directives,
> such as #result?

Not sure that #bind(..) directive is redundant. It can seems redundant
like it was from my first user standpoint, but now I understand how
Velocity is working :)

Practically for me is not hart to tell every "SQL-Query-Writer" to use
#bind($Param) where he expects some parameters.
But it is huge work if we should set JDBC types it's much easy to
do some hints about type from Java Code, sometimes real expected type
is known only at runtime.

That is my Idea. I should think a little about #result directive.

I create CAY-1280 and attached draft patch. Maybe I should export this
mail conversion there?


> I like the idea, but maybe we can take this even further, and provide an API 
> substitute for the entire directive, e.g. instead of
>
>   #bind($bindDirective)
>
> we'd have
>
>   $bindDirective

I think bind is necessary course we can use parameters not only for Binding.

Like passing table name to template:
    1) SELECT * FROM $MyParamTable

And we should separate such queries from binding:
    2)  SELECT * FROM Table1 where PersonName = #bind($MyParamName)

We can't use just PersonName = $MyParamName course string in SQL
should be in ' '
Another one very useful thing is that while we use bind we are sending
precompiled queries which is very cool.


So I don't think we could do it without bind... in theory we can do
intelligent analyze of SQL query and parameter to determine if we
should do bind or put it in template (like table name) but it's not
cool! or we can rewrite common rendering of simple template variable
in Velocity... which is also not cool (IMHO).

Maybe I have missed something what is your idea?

 Best regards,
 Evgeny Ryabitskiy.

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

On Sep 28, 2009, at 11:09 PM, Evgeny Ryabitskiy wrote:

If I explicitly set JDBC type in bind directive, I get right type  
(the one

that I have set explicitly).


Good to know.

Set whole result is a solution but also not so flexible. If I have  
select
from 10 columns and want change type of only one column I should  
pass all 10

types (where 9 is same that returns by default).


Agree, this is painful, done for purely implementation reasons and is  
something we should handle better.


I wish to have ability to optionally set result type for several  
columns

from API.
I have very simple idea: To have standard Object-wrapper (name like
"BindValue" or something like that) that contains reference to  
Object (or

null) and it's JDBC type and precision.
After we touch BindDirective to work with this "BindValue", we can  
anytime

pass BindValue and explicitly set JDBC type through API.


I like the idea, but maybe we can take this even further, and provide  
an API substitute for the entire directive, e.g. instead of


   #bind($bindDirective)

we'd have

   $bindDirective

i.e. from the user standpoint, extra #bind(..) wrapper seems  
redundant. Now wonder if we can extend that to other directives, such  
as #result?


What do you think?

Andrus

Re: Passing null values in parameters, part 2 )

[Evgeny Ryabitskiy]

If I explicitly set JDBC type in bind directive, I get right type (the one
that I have set explicitly).
But the point is to not set JDBC type. :)
I believe that Cayenne is able to solve this problem.

Set whole result is a solution but also not so flexible. If I have select
from 10 columns and want change type of only one column I should pass all 10
types (where 9 is same that returns by default).

I wish to have ability to optionally set result type for several columns
from API.
I have very simple idea: To have standard Object-wrapper (name like
"BindValue" or something like that) that contains reference to Object (or
null) and it's JDBC type and precision.
After we touch BindDirective to work with this "BindValue", we can anytime
pass BindValue and explicitly set JDBC type through API.

I think it's very useful. Any new opportunity can't make things worse.

Best regards,
 Evgeny Ryabitskiy.



2009/9/28 Andrus Adamchik 

> Yeah, seems like a JDBC driver issue having hard time guessing the type of
> the result (which Cayenne takes from ResultSetMetadata). So just to
> doublecheck, if you do #bind($MyNumericColumn 'int'), it doesn't make a
> difference?
>
> As for the solution if nothing works, Cayenne 3.0 has an ability to set
> explicit result mapping via API:
>
>
> http://cayenne.apache.org/doc/api/org/apache/cayenne/query/SQLTemplate.html#setResult(org.apache.cayenne.map.SQLResult)
>
> Not sure 100% if it solves this problem, but let's check things one step at
> a time...
>
> Andrus
>
>
> On Sep 28, 2009, at 5:58 PM, Evgeny Ryabitskiy wrote:
>
>> Hello to everyone!
>>
>> After previous fix I can pass null values in parameters to SQLTemplates.
>> It's working and it's cool!
>>
>>
>> Now I have one more issue... :
>>
>> Here is query (very simple.. to focus on this issue, that I can't
>> avoid by changing SQL or add directives like #bind or #result)
>>
>> SELECT isnull(#bind($MyNumericColumn), AnotherNumericColumn) AS
>> MyColumn FROM MyTable
>>
>> If I pass some not null Decimal value it's working properly and
>> returns BigDecimal value.
>>
>> But if I pass null or not pass anything.. it's still working! but
>> return type is java.lang.String .. course null now has special JDBC
>> type for it and JDBC converts my Numeric to String.
>>
>> I have only one idea: pass JDBC type with my null in parameters
>> (like special class "JDBCTypedNull" with constructor and pass there
>> JDBC type or value type and map it to JDBC).
>>
>> Any other suggestions?
>>
>> Best regards,
>> Evgeny Ryabitskiy.
>>
>>
>

Re: Passing null values in parameters, part 2 )

[Andrus Adamchik]

Yeah, seems like a JDBC driver issue having hard time guessing the  
type of the result (which Cayenne takes from ResultSetMetadata). So  
just to doublecheck, if you do #bind($MyNumericColumn 'int'), it  
doesn't make a difference?


As for the solution if nothing works, Cayenne 3.0 has an ability to  
set explicit result mapping via API:


http://cayenne.apache.org/doc/api/org/apache/cayenne/query/SQLTemplate.html#setResult(org.apache.cayenne.map.SQLResult)

Not sure 100% if it solves this problem, but let's check things one  
step at a time...


Andrus

On Sep 28, 2009, at 5:58 PM, Evgeny Ryabitskiy wrote:

Hello to everyone!

After previous fix I can pass null values in parameters to  
SQLTemplates.

It's working and it's cool!


Now I have one more issue... :

Here is query (very simple.. to focus on this issue, that I can't
avoid by changing SQL or add directives like #bind or #result)

SELECT isnull(#bind($MyNumericColumn), AnotherNumericColumn) AS
MyColumn FROM MyTable

If I pass some not null Decimal value it's working properly and
returns BigDecimal value.

But if I pass null or not pass anything.. it's still working! but
return type is java.lang.String .. course null now has special JDBC
type for it and JDBC converts my Numeric to String.

I have only one idea: pass JDBC type with my null in parameters
(like special class "JDBCTypedNull" with constructor and pass there
JDBC type or value type and map it to JDBC).

Any other suggestions?

Best regards,
Evgeny Ryabitskiy.