Re: [OJB-developers] bug in sql generation

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

Hi Travis,

OJB is database abstraction level itself, why introduce one more level?
Actually it is not so difficult to support multiple databases in OJB, 
but SqlStatement.java code was added recently and need some time to stabilize.
And I suppose that OJB already supports more than 6 databases.
Also note that the generated queries may be very complicated: multiple inner 
and outer joins, aliases, unions. Does CrossDB support all these things?

Thanks,
 Oleg

On Thursday 30 May 2002 19:12, tr...@th... wrote:
> 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
>
>
>
> _______________________________________________________________
>
> 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