Re: SQL Query Generation
Re: SQL Query Generation
- Subject: Re: SQL Query Generation
- From: Ken Anderson <email@hidden>
- Date: Sat, 11 Aug 2007 11:12:14 -0400
Personally, I would use the CountFetcher class from Jerry Walker...I
use it all the time. You give it a fetch specification, and it comes
back with the number of rows that would be returned:
http://wocode.com/WOCode/Files/CountFetcher.java
Ken
On Aug 11, 2007, at 6:44 AM, shaun wrote:
Michael Xenakis wrote:
All -
I'm trying to solve a lil' problem here. The semi-short story is
that prior to doing a fetch, I'm going to do a raw SQL fetch to
determine how many rows would be returned - i.e., I'll do a
"select count(*)" based on the search parameters. If the count
is greater than a predetermined fetch limit, then I respond w/ a
message to further restrict the query.
To do this, I build my qualifiers as usual create a fetch spec
based on the qualifiers and then pass them into a
JDBCExpression. The code looks like this:
EOFetchSpecification fetchSpec = new EOFetchSpecification
(entityName, searchQualifier, null);
JDBCExpression sqlExpression = new JDBCExpression(entity);
sqlExpression.prepareSelectExpressionWithAttributes
(entity.attributes(), false, fetchSpec);
String rawSQLQuery = "SELECT COUNT(*) FROM " +
entity.externalName() + " WHERE " + sqlExpression.whereClauseString
();
if (!sqlExpression.joinClauseString().equals("")) {
rawSQLQuery = rawSQLQuery + " AND " +
sqlExpression.joinClauseString();
}
Unfortunately, the where clause being generated
by .whereClauseString () is parameterized, e.g.,
SELECT COUNT(*) FROM PLAYLIST t0, CONTENT_PROVIDER T1 WHERE
(((t0.FIRST_PRODUCTION_RELEASE is not ? AND
t0.LAST_PRODUCTION_RELEASE is ?) AND T1.CONTENT_PROVIDER_ID = ?)
AND UPPER(t0.NAME) LIKE UPPER(?) ESCAPE '\')
What I can't seem to figure is how to get the string returned w/
the parameters replaced w/ the actual bound values.
So, first, am I heading down the right path or is there a better way?
If I am headed in the right direction, what am I missing?
You can use EOSQLExpression and bindVariables to substitute the
keys and values. Something along the lines of the follwoing:
Note: there are numerous problems with this code as it stands,
. BigDecimal formatting might not be right anymore Java 5 changes
need to call method other than toString(), should be toPlainString
()? I think.
. Date formatting, Oracle specific?
EOSQLExpression expression...///// assume it exists.
String sql = expression.statement();
NSArray bindVariables = expression.bindVariableDictionaries();
if( (bindVariables == null) || (bindVariables.count
()==0) ) return true;
for(int i=0; i<bindVariables.count(); i++ ) {
NSDictionary bindVariable = (NSDictionary)
bindVariables.objectAtIndex(i);
String name = (String) bindVariable.valueForKey
("BindVariableName");
Object value = bindVariable.valueForKey
("BindVariableValue");
EOAttribute attribute = (EOAttribute)
bindVariable.valueForKey("BindVariableAttribute");
//TODO check for data type.
String formattedValue = "";
if( value == null )
formattedValue = "NULL";
else if( attribute.externalType().equals("DATE") )
formattedValue = "TO_DATE
('"+DateUtils.toStringUsingFormatter((NSTimestamp)value,"%Y-%m-%d %
H:%M:%S")+"','YYYY-MM-DD HH24:MI:SS')";
else if( attribute.externalType().equals
("NUMBER") )
formattedValue = value.toString();
else formattedValue = "'"+value.toString()+"'";
sql = StringUtils.replaceStringFirstMatch(sql,
"?", formattedValue);
}
HTH.
cheers,
- shaun
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40anderhome.com
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden