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

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

 ooooo that thing looks cool, but does it really work? If It did, that would be a great time saver. Any chance the licenses would not be happy together?
  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 
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

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