RE: RE: Re: [OJB-developers] bug in sql generation
Brought to you by:
thma
Menu
▾
▴
RE: RE: Re: [OJB-developers] bug in sql generation
|
From: <tr...@th...> - 2002-05-30 18:17:43
|
Yes it really works. I use it for everything and it's under apache li=
cense so it can be used.
<br>
<br>And since OJB is looking to go towards apache license it would be a nic=
e fit.
<br>
<br>Travis
<br>
<br>---- Original Message ----
<br>From: Matthew baird <<a href=3D"mailto:mat...@ya...">mattbair=
d...@ya...</a>>
<br>Sent: 2002-05-30
<br>To: <a href=3D"mailto:tr...@th...">tr...@th...<=
/a>, Oleg Nitz <<a href=3D"mailto:on...@ib...">on...@ib...</=
a>>
<br>Subject: RE: Re: [OJB-developers] bug in sql generation
<br>
<br><P> ooooo that thing looks cool, but does it really work? If It did, th=
at would be a great time saver. Any chance the licenses would not be happy =
together?
<P> <B><I>tr...@th...</I></B> wrote:=20
<BLOCKQUOTE style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #101=
0ff 2px solid">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 c=
ould 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.<BR><BR>And it will support =
all databases very easily (5 or 6 dbs are supported now), very easy to supp=
ort more and the OJB code would not have to change to support others.<BR><B=
R>An example of how simple a join query would be is:<BR><BR>SelectQuery sq =
=3D factory.getSelectQuery();<BR>sq.addTable("X");<BR>sq.addTable("Y");<BR>=
sq.addWhereCondition("X.col1", WhereCondition.EQUAL_TO, "Y.col2");<BR>// ad=
d more conditions, order bys, group bys, etc if needed<BR><BR>that's it<BR>=
then call sq.execute when ready.<BR><BR>Travis<BR><BR>---- Original Message=
----<BR>From: Oleg Nitz <ON...@IB...><BR>Sent: 2002-05-30<BR>To: obj=
ect...@li...<BR>Subject: Re: [OJB-developers]=
bug in sql generation<BR><BR>Hi Matthew,<BR><BR>I also don't see error in =
the generated SQL.<BR>Probably it's a problem of SQL-92 JOIN syntax support=
in MS SQL Server.<BR>I propose that we change join syntax for MS SQL Serve=
r to<BR>SYBASE_JOIN_SYNTAX. I am quite sure that this will solve the proble=
m. <BR>Also this will make OJB support earlier versions MS SQL Server, whic=
h<BR>didn't support SQL-92 syntax.<BR><BR>Oleg<BR><BR>Matthew baird wrote:<=
BR><BR>> I think the appendTableWithJoins routine has a bug with parenth=
esis. the routine is recursive to build out all the joins (with parens) upo=
n entry, the stmtFromPos ( the position of the FROM<BR>> 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.<BR><BR>> since I h=
ave a complex path, with lots of joins, I end up calling appendTableWithJoi=
ns from within appendTableWithJoins. Now, stmtFromPos is 0 because table is=
not root. Near the bottom of the<BR>> function, you see a buf.insert(st=
mtFromPos, "("); 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 th=
is:<BR><BR>> (((SELECT ....<BR><BR>> obviously those parens should go=
after the FROM clause. <BR><BR>> the solution is to make the stmtFromPo=
s a parameter and pass it along. I wanted to get opinions on this fix, as t=
he SQL being generated for our really complex case is still wrong (parens a=
ren't<BR>> working right) and I wanted to make sure this wasn't a sympto=
m of another problem.<BR><BR>> protected void appendTableWithJoins(Table=
Alias table, StringBuffer where, StringBuffer buf)<BR>> {<BR>> int st=
mtFromPos =3D 0;<BR><BR>> if (joinSyntaxType =3D=3D SQL92_JOIN_SYNTAX &a=
mp;& table =3D=3D root)<BR>> {<BR>> stmtFromPos =3D buf.length();=
// BRJ : point after FROM<BR>> }<BR>> if (!(joinSyntaxType =3D=3D SQ=
L92_NOPAREN_JOIN_SYNTAX && table !=3D root))<BR>> {<BR>> buf.=
append(table.table);<BR>> buf.append(" ");<BR>> buf.append(table.alia=
s);<BR>> }<BR>> if (!table.hasJoins())<BR>> {<BR>> return;<BR>&=
gt; }<BR>> for (Iterator it =3D table.iterateJoins(); it.hasNext();)<BR>=
> {<BR>> Join join =3D (Join) it.next();<BR>> if (joinSyntaxType =
=3D=3D SQL92_JOIN_SYNTAX)<BR>> {<BR>> if (join.isOuter)<BR>> {<BR>=
> buf.append(" LEFT OUTER JOIN ");<BR>> }<BR>> else<BR>> {<BR>&=
gt; buf.append(" INNER JOIN ");<BR>> }<BR>> if (join.right.hasJoins()=
)<BR>> {<BR>> buf.append("(");<BR>> appendTableWithJoins(join.righ=
t, where, buf);<BR>> buf.append(")");<BR>> }<BR>> else<BR>> {<B=
R>> appendTableWithJoins(join.right, where, buf);<BR>> }<BR>> buf.=
append(" ON ");<BR>> join.appendJoinEqualities(buf);<BR>> if (it.hasN=
ext())<BR>> {<BR>> buf.insert(stmtFromPos, "(");<BR>> buf.append("=
)");<BR>> }<BR>> }<BR>> else if (joinSyntaxType =3D=3D SQL92_NOPAR=
EN_JOIN_SYNTAX)<BR>> {<BR>> if (join.isOuter)<BR>> {<BR>> buf.a=
ppend(" LEFT OUTER JOIN ");<BR>> }<BR>> else<BR>> {<BR>> buf.ap=
pend(" INNER JOIN ");<BR>> }<BR>> buf.append(join.right.table);<BR>&g=
t; buf.append(" ");<BR>> buf.append(join.right.alias);<BR>> buf.appen=
d(" ON ");<BR>> join.appendJoinEqualities(buf);<BR>> appendTableWithJ=
oins(join.right, where, buf);<BR>> }<BR>> else<BR>> {<BR>> buf.=
append(",");<BR>> appendTableWithJoins(join.right, where, buf);<BR>> =
if (where.length() > 0)<BR>> {<BR>> where.append(" AND ");<BR>>=
}<BR>> join.appendJoinEqualities(where);<BR>> }<BR>> }<BR>> }<=
BR>> The SQL being generated for my complex case is:<BR>> SELECT A0.m=
atrix_calc_efdt_uuid,A0.begin_effective_date,A0.end_effective_date,A0.activ=
ation_date,<BR>> A0.deactivation_date,A0.matrix_calc_version_uuid FROM <=
BR>> (((MATRIX_CALC_EFDT A0 INNER JOIN (MATRIX_CALC_VERSION A1 INNER JOI=
N (statement_VAR A2 INNER JOIN PARTICIPANT A3 <BR>> ON A2.participant_uu=
id=3DA3.participant_uuid) INNER JOIN HIERARCHY_OBJECT A5 ON A2.organization=
_uuid=3DA5.hierarchy_object_uuid) <BR>> INNER JOIN JOB A4 ON A2.job_uuid=
=3DA4.job_uuid) INNER JOIN (statement A6 INNER JOIN UNIT A7 ON A6.unit_uuid=
=3DA7.unit_uuid) <BR>> ON A2.statement_uuid=3DA6.statement_uuid) ON A1.s=
tatement_var_uuid=3DA2.statement_var_uuid) <BR>> ON A0.matrix_calc_versi=
on_uuid=3DA1.matrix_calc_version_uuid<BR><BR>> WHERE (((( ( ( A0.activat=
ion_date <=3D ? <BR>> AND A0.deactivation_date > ? OR A0.deactivat=
ion_date IS NULL ) <BR>> AND A0.begin_effective_date <=3D ?) <BR>>=
AND A0.end_effective_date > ? OR A0.end_effective_date IS NULL ) <BR>&g=
t; AND A3.participant_uuid IS NULL ) AND A4.job_uuid IS NULL ) <BR>> AND=
A5.hierarchy_object_uuid IS NULL ) AND A7.unit_uuid =3D ?<BR>> 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 ri=
ght after the FROM.<BR>> thoughts?<BR><BR><BR><BR>> -----------------=
----------------<BR>> Do You Yahoo!?<BR>> Yahoo! - Official partner o=
f 2002 FIFA World Cup<BR><BR><BR><BR>______________________________________=
_________________________<BR><BR>Don't miss the 2002 Sprint PCS Application=
Developer's Conference<BR>August 25-28 in Las Vegas -- http://devcon.sprin=
tpcs.com/adp/index.cfm<BR><BR>_____________________________________________=
__<BR>Objectbridge-developers mailing list<BR>Objectbridge-developers@lists=
.sourceforge.net<BR>https://lists.sourceforge.net/lists/listinfo/objectbrid=
ge-developers<BR><BR><BR><BR>______________________________________________=
_________________<BR><BR>Don't miss the 2002 Sprint PCS Application Develop=
er's Conference<BR>August 25-28 in Las Vegas -- http://devcon.sprintpcs.com=
/adp/index.cfm<BR><BR>_______________________________________________<BR>Ob=
jectbridge-developers mailing list<BR>Obj...@li...urcef=
orge.net<BR>https://lists.sourceforge.net/lists/listinfo/objectbridge-devel=
opers</BLOCKQUOTE><p><br><hr size=3D1><b>Do You Yahoo!?</b><br>
<a href=3D"http://rd.yahoo.com/welcome/*http://fifaworldcup.yahoo.com">Yaho=
o!</a> - Official partner of 2002 FIFA World Cup
|
