Re: Customizing SQL generation

[Jakob Braeuchi]

hi stephane,

you can already do this by adding the function to the attribute name.
ie: upper(firstname).
jakob

LAURENT Stephane wrote:

Hi,
I think it's a good idea to be able to call function under SQL query, don't
you ?
Why don't define appendCriteria method taking 2 more string parameters to
define functions call on SQL Attribute and on tested value ?
to do : SELECT ... WHERE FUNCTION1(name) LIKE FUNCTION2('alex%')
or : SELECT ... WHERE FUNCTION1(name)=FUNCTION2('alex%')
Regards.

----- Original Message ----- 
From: "Alexey Kamenchuk" <[EMAIL PROTECTED]>
To: "'OJB Users List'" <[EMAIL PROTECTED]>
Sent: Friday, August 29, 2003 7:24 AM
Subject: RE: Customizing SQL generation

 

Hi Jakob,

Unfortunately, I don't have access to the source code constructing
   

criteria.
 

It was written for MS SQL Server, which is case-insensitive. Now the DB2
   

is
 

used and its LIKE clause behaves differently. Deployer should be able to
declaratively switch case-sensitivity.
Alexey.

-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 28, 2003 7:02 PM
To: OJB Users List
Subject: Re: Customizing SQL generation
hi alexey,

why don't you use

       crit.addLike("upper(name)","ALEX%");

jakob

Alexey Kamenchuk wrote:

   

Hi,

Is there the good way to customize generated SQL statements?
The solution I found requires adding my custom classes into the
org.apache.ojb.broker.accesslayer.sql package what I particularly
     

dislike.
 

I need to make query with case-insensitive LIKE-clause, that's something
like this:
SELECT ... WHERE UPPER(name) LIKE UPPER('alex%')
Here is my solution:

I created subclass of SqlSelectStatement and overrode appendCriteria:

protected void appendCriteria(TableAlias alias, PathInfo pathInfo,
SelectionCriteria c, StringBuffer buf)
{
if (c instanceof LikeCriteria)
appendLikeCriteria(alias, pathInfo,(LikeCriteria) c, buf);
else
super.appendCriteria(alias, pathInfo, c, buf);
}
and added new method

private void appendLikeCriteria(TableAlias alias, PathInfo pathInfo,
LikeCriteria c, StringBuffer buf)
{
buf.append(" UPPER(");
appendColName(alias, pathInfo, c.isTranslateAttribute(), buf);
buf.append(") ");
buf.append(c.getClause());
buf.append(" UPPER(?) ");
}
These two methods reference to TableAlias class, which is declared as
package-private in
     

org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.
 

That's why I have to place my class into the same package.

This subclass is instantiated in overridden
SqlGeneratorDefaultImpl#getPreparedSelectStatement
Implementing it similarly to the parent I need access to SqlCacheKey
     

class,
 

which is also not public; hence, the ancestor of SqlGeneratorDefaultImpl
     

is
 

placed to org.apache.ojb.broker.accesslayer.sql package too.

Best regards,
Alexey.


     

---------------------------------------------------------------------
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]