Hello everyone,
I am running Castor 0.9.5 with MySQL 3.23.52 on a RedHat linux machine.
I perform a query that I know will only retrieve one object of the class
MEBioSample, change a field of the returned object and then commit the
transaction. The field is called 'name' and it belongs to the top class
of the hierarchy and not the class itself. As far as I know, the change
should be updated in the database, upon committing the transaction.
Instead, I get the following exception:
org.exolab.castor.jdo.TransactionAbortedException: Nested error:
org.exolab.castor.jdo.PersistenceException: Nested error:
java.sql.SQLException: Syntax error or access violation,
message from server: "You have an error in your SQL syntax near 'WHERE
ID=9' at line 1"
It seems that the server did not like the SQL command castor produced.
Upon inspection of the MySQL server log, I see the following (last part
of the log shown):
13 Connect [EMAIL PROTECTED] on
13 Init DB ae
13 Query SHOW VARIABLES
13 Query SET autocommit=1
13 Query SET autocommit=0
13 Query SELECT
TT_MEBIOSAMPLE.ID,TT_NAMEVALUETYPE.ID,TT_AUDIT.ID,TT_DESCRIPTION.ID,TT_DESCRIBABLE.SECURITY_ID,TT_IDENTIFIABLE.IDENTIFIER,TT_IDENTIFIABLE.NAME,TT_CHARACTERIS_T_BIOMATERI.CHARACTERISTICS_ID,TT_NAMEVALUETYPE_f7.ID,TT_TREATMENT.ID,TT_BIOMATERIAL.MATERIALTYPE_ID,TT_BIOSAMPLE.TYPE_ID
FROM TT_MEBIOSAMPLE LEFT OUTER JOIN TT_NAMEVALUETYPE ON
TT_MEBIOSAMPLE.ID=TT_NAMEVALUETYPE.T_EXTENDABLE_ID LEFT OUTER JOIN TT_AUDIT ON
TT_MEBIOSAMPLE.ID=TT_AUDIT.T_DESCRIBABLE_ID LEFT OUTER JOIN TT_DESCRIPTION ON
TT_MEBIOSAMPLE.ID=TT_DESCRIPTION.T_DESCRIBABLE_ID LEFT OUTER JOIN
TT_CHARACTERIS_T_BIOMATERI ON
TT_MEBIOSAMPLE.ID=TT_CHARACTERIS_T_BIOMATERI.T_BIOMATERIAL_ID LEFT OUTER JOIN
TT_NAMEVALUETYPE TT_NAMEVALUETYPE_f7 ON
TT_MEBIOSAMPLE.ID=TT_NAMEVALUETYPE_f7.T_BIOMATERIAL_ID LEFT OUTER JOIN TT_TREATMENT ON
TT_MEBIOSAMPLE.ID=TT_TREATMENT.T_BIOMATERIAL_ID,TT_IDENTIFIABLE,TT_DESCRIBABLE,TT_BIOSAMPLE,TT_EXTENDABLE,TT_BIOMATERIAL
WHERE TT_MEBIOSAMPLE.ID=TT_BIOSAMPLE.ID AND TT_BIOSAMPLE.ID=TT_BIOMATERIAL!
.ID AND TT_BIOMATERIAL.ID=TT_IDENTIFIABLE.ID AND
TT_IDENTIFIABLE.ID=TT_DESCRIBABLE.ID AND
TT_DESCRIBABLE.ID=TT_EXTENDABLE.ID AND (TT_IDENTIFIABLE.NAME = 'XUXUXU')
*** 13 Query UPDATE TT_EXTENDABLE SET WHERE ID=9
13 Query rollback
13 Query rollback
13 Quit
What seems to be happening is that castor connects, retrieves the object
(the select statement has so many joins because of the many levels of
hierarchy), and then an incomplete UPDATE query is sent to the server
(marked with *** by me). So it seems that indeed Castor does not produce
the correct SQL in that case. TT_EXTENDABLE is the top class in the
hierachy and it seems that Castor started the update from there (but
didn't get far!) What could be wrong?
I am including part of the mapping at the end of this email. The mapping
for the parents of the class is not included. Also, the relevant java
code is included.
Hopefully someone will be able to help.
Thanks,
Stathis
--- Java code -----------
JDO jdo = env.getAEJDO(); //the jdo object is initialised elsewhere
Database db = jdo.getDatabase();
db.begin();
OQLQuery oql = db.getOQLQuery(
"SELECT MEBioSample FROM MEBioSample WHERE name = $1"
);
oql.bind( "XUXUXU" );
QueryResults results = oql.execute();
MEBioSample entry = null;
if(results != null) {
while ( results.hasMore() ) {
entry = (MEBioSample) results.next();
}
} else {
System.out.println("Query did not return results.");
}
entry.setName(entry.getName()+"333");
db.commit();
db.close();
--- mapping -------------
<class name="org.biomage.BioMaterial.BioSample" identity="id"
key-generator="IDENTITY" extends="org.biomage.BioMaterial.BioMaterial" >
<map-to table="TT_BIOSAMPLE" />
<field name="id" type="big-decimal">
<sql name="ID" type="numeric" />
</field>
<field name="type" type="org.biomage.Description.OntologyEntry">
<sql name="TYPE_ID" />
</field>
</class>
-----------------------------------------------------------
If you wish to unsubscribe from this mailing, send mail to
[EMAIL PROTECTED] with a subject of:
unsubscribe castor-dev
