Re: [OJB-developers] bug in sql generation
Brought to you by:
thma
Menu
▾
▴
Re: [OJB-developers] bug in sql generation
|
From: Jakob B. <jbr...@ho...> - 2002-05-30 16:21:21
|
hi oleg,
i made stmtFromPos an instvar again, otherwise it can be 0 in some cases and
the result will be (((SELECT as matthew described.
jakob
----- Original Message -----
From: "Oleg Nitz" <on...@ib...>
To: <obj...@li...>
Sent: Thursday, May 30, 2002 6:08 PM
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.ac
tivation_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
>
|
