Michael,
IMO the current SQL generation is not consistent with what I've seen in other
AppServers. I don't have any specific data yet but I know for at least DB2 the
DB2 developers I've talked to have not seen this particular pattern. As a
consequence, I expect Oracle would probably have the same comment. As it stands
right now I think we need to go back and invest some serious time rewriting the
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
world in terms of adoption. I'd be curious to get your feedback on how you use
CMPs and their ubiquity in your environment.
Thanks
Matt
Ueberbach, Michael wrote:
Hello Matt,
yes, this works. Thanks for the hint.
I think geronimo sends a statement like this one to the database
UPDATE table SET col1 = CASE WHEN false THEN null ELSE col1 END, col2 = CASE
WHEN true THEN newValue ELSE col1 END, ...
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=1 or 1=0. (This way I can
reproduce the situation)
Nevertheless I think this behaviour should be modified not only for the reason
of wrongly fired triggers but also to reduce the transportation load.
regards
Michael
-----Ursprüngliche Nachricht-----
Von: Matt Hogstrom [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 26. Januar 2006 17:22
An: [email protected]
Betreff: Re: AW: Geronimo CMP update statements
Michael,
Add the following lines in your plan:
<!-- For Oracle database users uncomment the following line.
<ejb-ql-compiler-factory>org.tranql.ejbqlcompiler.OracleQLCompilerFactory</ejb-ql-compiler-factory>
<db-syntax-factory>org.tranql.sql.oracle.OracleDBSyntaxFactory</db-syntax-factory>
-->
These go after the cmp-connection factory. Let me know if this fixes the
problem.
Ueberbach, Michael wrote:
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 = CASE WHEN ? THEN ? ELSE erzeugt END, bankname = CASE WHEN ?
THEN ? ELSE bankname END, bankleitzahl = CASE WHEN ? THEN ? ELSE bankleitzahl
END, kontonummer = CASE WHEN ? THEN ? ELSE kontonummer END, kontostand = CASE
WHEN ? THEN ? ELSE kontostand END, fk_person = CASE WHEN ? THEN ? ELSE
fk_person END WHERE guid = ?
(...)
Caused by: java.sql.SQLException: ORA-00920: invalid relational operator
This not the case when using MySQL.
regards
Michael
-----Ursprüngliche Nachricht-----
Von: Gianny Damour [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 26. Januar 2006 13:16
An: [email protected]
Betreff: Re: Geronimo CMP update statements
Hi Dan,
At the beginning, I was not seeing this as an issue. Based on your
remark that triggers are wrongly fired, I now see this as an issue that
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.
