Re: [OJB-developers] bug in sql generation
Brought to you by:
thma
Menu
▾
▴
Re: [OJB-developers] bug in sql generation
|
From: Oleg N. <on...@ib...> - 2002-05-30 16:29:18
|
Hi Jakob,
Jakob Braeuchi wrote:
> i made stmtFromPos an instvar again, otherwise it can be 0 in some cases and
> the result will be (((SELECT as matthew described.
Now I see. Thanks to Matthew!
Oleg
> 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
>>
|
