Thread: RE: Re: [OJB-developers] bug in sql generation

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

You guys might want to consider using CrossDB at www.crossdb.com, then you wouldn't have to deal with the intracacies of each db.  You could cut out half your if/elses, etc and just use one common set of methods.  I'd be happy to help in this area if need be.

And it will support all databases very easily (5 or 6 dbs are supported now), very easy to support more and the OJB code would not have to change to support others.

An example of how simple a join query would be is:

SelectQuery sq = factory.getSelectQuery();
sq.addTable("X");
sq.addTable("Y");
sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2");
// add more conditions, order bys, group bys, etc if needed

that's it
then call sq.execute when ready.

Travis

---- Original Message ----
From: Oleg Nitz <on...@ib...>
Sent: 2002-05-30
To: obj...@li...
Subject: Re: [OJB-developers] bug in sql generation

Hi Matthew,

I also don't see error in the generated SQL.
Probably it's a problem of SQL-92 JOIN syntax support in MS SQL Server.
I propose that we change join syntax for MS SQL Server to
SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the problem. 
Also this will make OJB support earlier versions MS SQL Server, which
didn't support SQL-92 syntax.

Oleg

Matthew baird wrote:

> I think the   appendTableWithJoins routine has a bug with parenthesis. the routine is recursive to build out all the joins (with parens) upon entry, the stmtFromPos ( the position of the FROM
> keyword in the SQL being generated) is set because joinSyntaxType is SQL92_JOIN_SYNTAX and the table is the root. So let's say it's set to 150.

> since I have a complex path, with lots of joins, I end up calling appendTableWithJoins from within appendTableWithJoins. Now, stmtFromPos is 0 because table is not root. Near the bottom of the
> function, you see a buf.insert(stmtFromPos, "("); if iterator has next. In my case it has next, so the ( is inserted at stmtFromPos which is 0, so I end up with SQL that looks like this:

> (((SELECT ....

> obviously those parens should go after the FROM clause. 

> the solution is to make the stmtFromPos a parameter and pass it along. I wanted to get opinions on this fix, as the SQL being generated for our really complex case is still wrong (parens aren't
> working right) and I wanted to make sure this wasn't a symptom of another problem.

> protected void appendTableWithJoins(TableAlias table, StringBuffer where, StringBuffer buf)
>     {
>         int stmtFromPos = 0;

>         if (joinSyntaxType == SQL92_JOIN_SYNTAX && table == root)
>         {
>             stmtFromPos = buf.length(); // BRJ : point after FROM
>         }
>         if (!(joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX && table != root))
>         {
>             buf.append(table.table);
>             buf.append(" ");
>             buf.append(table.alias);
>         }
>         if (!table.hasJoins())
>         {
>             return;
>         }
>         for (Iterator it = table.iterateJoins(); it.hasNext();)
>         {
>             Join join = (Join) it.next();
>             if (joinSyntaxType == SQL92_JOIN_SYNTAX)
>             {
>                 if (join.isOuter)
>                 {
>                     buf.append(" LEFT OUTER JOIN ");
>                 }
>                 else
>                 {
>                     buf.append(" INNER JOIN ");
>                 }
>                 if (join.right.hasJoins())
>                 {
>                     buf.append("(");
>                     appendTableWithJoins(join.right, where, buf);
>                     buf.append(")");
>                 }
>                 else
>                 {
>                     appendTableWithJoins(join.right, where, buf);
>                 }
>                 buf.append(" ON ");
>                 join.appendJoinEqualities(buf);
>                 if (it.hasNext())
>                 {
>                     buf.insert(stmtFromPos, "(");
>                     buf.append(")");
>                 }
>             }
>             else if (joinSyntaxType == SQL92_NOPAREN_JOIN_SYNTAX)
>             {
>                 if (join.isOuter)
>                 {
>                     buf.append(" LEFT OUTER JOIN ");
>                 }
>                 else
>                 {
>                     buf.append(" INNER JOIN ");
>                 }
>                 buf.append(join.right.table);
>                 buf.append(" ");
>                 buf.append(join.right.alias);
>                 buf.append(" ON ");
>                 join.appendJoinEqualities(buf);
>                 appendTableWithJoins(join.right, where, buf);
>             }
>             else
>             {
>                 buf.append(",");
>                 appendTableWithJoins(join.right, where, buf);
>                 if (where.length() > 0)
>                 {
>                     where.append(" AND ");
>                 }
>                 join.appendJoinEqualities(where);
>             }
>         }
>     }
> The SQL being generated for my complex case is:
> SELECT A0.matrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activation_date,
> A0.deactivation_date,A0.matrix_calc_version_uuid FROM 
> (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOIN (statement_VAR A2 INNER JOIN PARTICIPANT A3 
> ON A2.participant_uuid=A3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization_uuid=A5.hierarchy_object_uuid) 
> INNER JOIN JOB A4 ON A2.job_uuid=A4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=A7.unit_uuid) 
> ON A2.statement_uuid=A6.statement_uuid) ON A1.statement_var_uuid=A2.statement_var_uuid) 
> ON A0.matrix_calc_version_uuid=A1.matrix_calc_version_uuid

>  WHERE (((( ( (  A0.activation_date <= ? 
>  AND  A0.deactivation_date > ? OR A0.deactivation_date IS NULL ) 
>  AND  A0.begin_effective_date <= ?) 
>  AND  A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) 
>  AND  A3.participant_uuid IS NULL ) AND  A4.job_uuid IS NULL ) 
>  AND  A5.hierarchy_object_uuid IS NULL ) AND  A7.unit_uuid = ?
> which if you drop it into Query Analyzer or try to execute it against MS SQL Server, you get an error with not a lot of information, but it looks like it's right after the FROM.
> thoughts?

> ---------------------------------
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup

_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference
August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm

_______________________________________________
Objectbridge-developers mailing list
Obj...@li...
https://lists.sourceforge.net/lists/listinfo/objectbridge-developers