I have posted several times a simple 1 line patch that fixes the
CALL SQL select parenthesis bug. The most recent was last week.
Here is is again, this patch was generated for the version in CVS
from last week.
And even though I have submitted this patch numerous times, it
still isn't in CVS. :-(
Regards,
Glenn
Jon Burford wrote:
>
> I hate to be a pest, but I have tried and tried to get Castor to pass an SQL
> query through that contains parentheses without removing all text in the
> parens, but it simply just does not happen. Has no one else experienced
> this problem? If not, any suggestions at all as to what might be going on?
> All my other SQL statements work just fine when I use the "CALL SQL" method.
> Again, my example is:
>
> LQuery oql = db.getOQLQuery("CALL SQL select * from a where a.b = (select
> MAX(x) from y) AS classA");
> OQLQueryImpl oqli = (OQLQueryImpl)oql;
> System.out.println("resulting SQL for Sybase is <"+oqli.getSQL()+">");
> QueryResults results = oql.execute();
>
> And I get the following output:
>
> resulting SQL for Sybase is <SQL select * from a where a.b = ()>
> com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near ')'.
> at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2535)
> at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1916)
> at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
> at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1455)
> at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1440)
> at
> com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement
> .java:70)
> at
> org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute(SQLEngine.java:1584)
> at
> org.exolab.castor.persist.TransactionContext.query(TransactionContext.java:6
> 68)
> at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:458)
> at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:405)
> at AppFrameworkTest.main(AppFrameworkTest.java:35)
>
> I am not sure whether OQLQueryImpl.getSQL() returns exactly what is being
> sent to the RDBMS (Sybase) or not, but if I take the SQL I print out above
> and execute it in a Sybase ISQL shell, I get the same error. So, it seems
> like my raw SQL is getting parsed and modified before it gets sent to
> Sybase. In particular, ANYTHING inside a pair of parentheses is removed
> before the query is sent to Sybase. Is there a work around for this or a
> reason why this might be happening? Is it possibly specific to the Sybase
> implementation? Has anyone else experienced this problem? Basically, we
> use the raw SQL interface quite a lot, and it is very limiting not to be
> able to use parentheses in any of our raw SQL queries. I appreciate the
> link to the SQL example in the FAQ, but we have been using the "CALL SQL"
> method of sending raw SQL for some time successfully. The only problems we
> have had is the parentheses issue. Any help or insight on this problem
> would be much appreciated. Does SQLEngine.createQuery or
> ParseTreeWalker.getQueryExpression modify the SQL when we use "CALL SQL"?
>
> Thanks in advance, I love your work!
> Jon
>
> -----------------------------------------------------------
> If you wish to unsubscribe from this mailing, send mail to
> [EMAIL PROTECTED] with a subject of:
> unsubscribe castor-dev
--
----------------------------------------------------------------------
Glenn Nielsen [EMAIL PROTECTED] | /* Spelin donut madder |
MOREnet System Programming | * if iz ina coment. |
Missouri Research and Education Network | */ |
----------------------------------------------------------------------
Index: src/main/org/exolab/castor/jdo/engine/OQLQueryImpl.java
===================================================================
RCS file: /cvs/castor/castor/src/main/org/exolab/castor/jdo/engine/OQLQueryImpl.java,v
retrieving revision 1.67
diff -u -r1.67 OQLQueryImpl.java
--- src/main/org/exolab/castor/jdo/engine/OQLQueryImpl.java 11 Dec 2001 09:38:49
-0000 1.67
+++ src/main/org/exolab/castor/jdo/engine/OQLQueryImpl.java 9 Feb 2002 22:14:48
+-0000
@@ -332,7 +332,7 @@
sql = new StringBuffer();
paramCnt = 0;
_paramInfo = new Hashtable();
- if ( leftParen < 0 && rightParen < 0 ) {
+ if ( oql.startsWith("CALL SQL") || (leftParen < 0 && rightParen < 0) ) {
sql.append( oql.substring( 5, as ) );
} else {
if ( ( leftParen < 0 && rightParen >= 0 )
