Mailing-List: contact user-help@geronimo.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@geronimo.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: AW: AW: AW: Geronimo CMP update statements
Date: Mon, 30 Jan 2006 12:52:36 +0100
Message-ID: <C40B585EBE11B54CBF47550F01D5AC890625A1@exchange.dbh.de>
Thread-Topic: AW: AW: Geronimo CMP update statements
Thread-Index: AcYkNiaxYmHRA5rHRqmvJjn82LI7HQBT2o0w
From: "Ueberbach, Michael" <michael.ueberbach@dbh.de>
To: <user@geronimo.apache.org>

Matt,

sorry for the delay, but I've just found your mail this morning (and all =
the other ones following in the discussion).
To answer your question:
In our last projects we really made extensive use of CMP (and CMR), =
although we know (in fact we had to learn it this way) that there can be =
many problems with it(particularly concerning the performance of the =
application).
On the other hand there is a great advantage in delegating standard work =
to the application server as far as there is no demand for special =
treatments. To my understanding this is one benefit of using an app =
server.
I assume the way geronimo generates update statements can be understand =
as similar to the idea of using static finders declared in the dd, so =
there is one update statement for every entity bean and this will be =
used for every situation where an entity has to be updated (in fact =
there will be only one prepared statement looking at the JDBC driver).
To my experience this (I mean the use of static finders) is not =
realistic from the point of view of application development. If you have =
a lot of different entities with many different attributes it seems =
impossible to me to describe all ever wanted search strategies in form =
of static finder methods. So some app servers (to my knowing JBoss and =
BEA Weblogic) offer the possibility of using something like dynamic =
queries inside CMP, that means defining the wanted SELECT statement at =
runtime. In the same way I think the app server should be flexible =
enough to react on different update requirements in generating suitable =
statements.
Im very interested in the oncoming steps geronimo will do concerning =
this point.

Regards
Michael


-----Urspr=FCngliche Nachricht-----
Von: Matt Hogstrom [mailto:matt@hogstrom.org]
Gesendet: Samstag, 28. Januar 2006 19:10
An: user@geronimo.apache.org
Betreff: Re: AW: AW: Geronimo CMP update statements


Michael,

IMO the current SQL generation is not consistent with what I've seen in =
other=20
AppServers.  I don't have any specific data yet but I know for at least =
DB2 the=20
DB2 developers I've talked to have not seen this particular pattern.  As =
a=20
consequence, I expect Oracle would probably have the same comment.  As =
it stands=20
right now I think we need to go back and invest some serious time =
rewriting the=20
SyntaxGenerators to generate more efficient SQL (at least for DB2).

One of the feedback items I've heard is that CMP is not that prevalent =
in the=20
world in terms of adoption.  I'd be curious to get your feedback on how =
you use=20
CMPs and their ubiquity in your environment.

Thanks

Matt

Ueberbach, Michael wrote:
> Hello Matt,
>=20
> yes, this works. Thanks for the hint.
> I think geronimo sends a statement like this one to the database
>=20
> UPDATE table SET col1 =3D CASE WHEN false THEN null ELSE col1 END, =
col2 =3D CASE WHEN true THEN newValue ELSE col1 END, ...
>=20
> and Oracle does not know how to handle the boolean values true and =
false, so they have to be replaced by some expressions like 1=3D1 or =
1=3D0. (This way I can reproduce the situation)
>=20
> Nevertheless I think this behaviour should be modified not only for =
the reason of wrongly fired triggers but also to reduce the =
transportation load.
>=20
> regards
> Michael
>=20
>=20
>=20
> -----Urspr=FCngliche Nachricht-----
> Von: Matt Hogstrom [mailto:matt@hogstrom.org]
> Gesendet: Donnerstag, 26. Januar 2006 17:22
> An: user@geronimo.apache.org
> Betreff: Re: AW: Geronimo CMP update statements
>=20
>=20
> Michael,
>=20
> Add the following lines in your plan:
>=20
> <!--  For Oracle database users uncomment the following line.
> =20
> =
<ejb-ql-compiler-factory>org.tranql.ejbqlcompiler.OracleQLCompilerFactory=
</ejb-ql-compiler-factory>
> =20
> =
<db-syntax-factory>org.tranql.sql.oracle.OracleDBSyntaxFactory</db-syntax=
-factory>
> -->
>=20
> These go after the cmp-connection factory.  Let me know if this fixes =
the problem.
>=20
> Ueberbach, Michael wrote:
>=20
>>Hello,
>>
>>I think there is another problem concerning this issue.
>>Using an Oracle database (9i) I get this error when updating a cmr- =
field:
>>
>>(...)
>>Caused by: org.tranql.ql.QueryException: Error executing statement: =
UPDATE konto SET erzeugt =3D CASE WHEN ? THEN ? ELSE erzeugt END, =
bankname =3D CASE WHEN ? THEN ? ELSE bankname END, bankleitzahl =3D CASE =
WHEN ? THEN ? ELSE bankleitzahl END, kontonummer =3D CASE WHEN ? THEN ? =
ELSE kontonummer END, kontostand =3D CASE WHEN ? THEN ? ELSE kontostand =
END, fk_person =3D CASE WHEN ? THEN ? ELSE fk_person END WHERE guid =3D =
?
>>(...)
>>Caused by: java.sql.SQLException: ORA-00920: invalid relational =
operator=20
>>
>>This not the case when using MySQL.
>>
>>regards
>>Michael
>>
>>
>>-----Urspr=FCngliche Nachricht-----
>>Von: Gianny Damour [mailto:gianny.damour@optusnet.com.au]
>>Gesendet: Donnerstag, 26. Januar 2006 13:16
>>An: user@geronimo.apache.org
>>Betreff: Re: Geronimo CMP update statements
>>
>>
>>Hi Dan,
>>
>>At the beginning, I was not seeing this as an issue. Based on your=20
>>remark that triggers are wrongly fired, I now see this as an issue =
that=20
>>needs to be fixed.
>>
>>Could you please raise a JIRA for this problem?
>>
>>Thanks,
>>Gianny
>>
>>Daniel John Debrunner wrote:
>>
>>
>>
>>>At ApacheConUS 2005 I talked with Matt Hogstrom about the SQL UPDATE
>>>statements Geronimo was issuing against Derby for DayTrader.
>>>
>>>A single UPDATE statement is generated for a table that updates all
>>>columns using a CASE statement to ensure un-modified columns are not
>>>changed, or in reality changed to the same value. An example is
>>>described in GERONIMO-1080, the syntax may be a little different for =
Derby.
>>>
>>>http://issues.apache.org/jira/browse/GERONIMO-1080
>>>
>>>I see two issues with this approach, one is that for Derby this is
>>>inefficient, and two, and probably more important, all SQL update
>>>triggers will fire due to this modification of all columns. Thus if =
an
>>>application defines a trigger on update of the address column of a
>>>customer table, then when using Geronimo this trigger will fire, even =
if
>>>the CMP application is only updating the customer's balance. This =
just
>>>seems the wrong semantics to me.
>>>
>>>Matt had said this was a known issue, and that it was going to be =
fixed.
>>>The comments in GERONIMO-1080 seem to indicate that this may not be =
seen
>>>as an issue, though those comments are dated before ApacheCon.
>>>
>>>I searched Jira and couldn't see any bug for changing this, are there
>>>any plans to address this?
>>>
>>>Thanks,
>>>Dan.
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>>
>=20
>=20
>=20