History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: CASTOR-930
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Werner Guttmann
Reporter: sylvie
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
castor

Reading, in the same transaction, a data just created (reading via oql query)

Created: 15/Nov/04 02:15 AM   Updated: 19/Apr/05 10:53 AM
Component/s: JDO
Affects Version/s: 0.9.5.3
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. Java Source File Account.java (0.8 kb)
2. Text File castor-1793.patch (4 kb)
3. File init.sql (0.4 kb)
4. XML File jdo_conf.xml (0.4 kb)
5. XML File mapping.xml (0.6 kb)
6. Java Source File TestXXX.java (8 kb)

Environment:
Operating System: Windows XP
Platform: PC

Bugzilla Id: 1793


 Description  « Hide
While using a database which supports rollback (mysql Innodb or Oracle), I can
do this in the same
transaction (autocommit is set to false):
. create an object A
. read back the object A with a pass-thru OQL query
. read back the object A with a db.load(...,objetA.getId())
. apply the rollback

I can't (before the rollback) :
. read back the object A with a OQL query like this
"select distinct dossier.idDossier from
cnam.scapin.metier.donnees.dossiers.Dossier dossier where nomPatronymique like
$1";

And If I have two different transactions before the rollback, I can't read
(anyway I use) in my transaction #2 the object being created in my transaction
#1. So the transaction isolation is ok.

I think there's really something wrong with the way the OQL query is executed
in this case. Is there something new on this point with the future version?

The same tests made using direct access by jdbc are ok.



 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
sylvie - 16/Nov/04 05:17 AM
Created an attachment (id=774)
Persistent object

File for JUnit test


sylvie - 16/Nov/04 05:18 AM
Created an attachment (id=775)
Create Table

JUnit Test


sylvie - 16/Nov/04 05:19 AM
Created an attachment (id=776)
Database configuration

JUnit Test


sylvie - 16/Nov/04 05:19 AM
Created an attachment (id=777)
Mapping file

JUnit test


sylvie - 16/Nov/04 05:20 AM
Created an attachment (id=778)
Test

JUnit test


Werner Guttmann - 16/Nov/04 05:24 AM
Sylvie, just to confirm this (again), you are using Castor 0.9.5.3 ? If that's
the case, can you please try to download the 0.9.6. release candidate, and let
me know whether your problem still persists ? So far, I have not been able to
reproduce your problem with the 0.9.6 RC.

Werner Guttmann - 16/Nov/04 05:37 AM
If you really considered running your test case with 0.9.6, please bear in mind
that you'll have to amend your database.xml file to cater for a new grammar.

What used to be

<database ...>
...
</database>

now becomes

<jdo-conf>
<database ...>
...
</database>
<transaction-demarcation mode="local" />
</jdo-conf>


sylvie - 16/Nov/04 10:07 AM
I've reproduced the problem with 0.9.6 RC.

This query is ok :
OQLQuery query = db.getOQLQuery("select account from test.testJdo.Account
account where description = $1 ");

This query seems to retrieve nothing :
OQLQuery query = db.getOQLQuery("select account.ref from test.testJdo.Account
account where description = $1 ");

You can try with the test case I've post.


Werner Guttmann - 19/Nov/04 01:00 PM
>This query seems to retrieve nothing :
>OQLQuery query = db.getOQLQuery("select account.ref from test.testJdo.Account
>account where description = $1 ");
Sylvie, I'd actually consider this OQL query as invalid. Castor is an O/R
mapping tool, and as such does not support projection to object attributes.

sylvie - 21/Nov/04 11:37 PM
I hope I did'nt understand you well ?!?

Castor supports this kind of Oql query :
"SELECT max(ligne.numeroLigne) FROM
fr.cnam.scapin.metier.donnees.tablesParam.LigneTableParametres ligne WHERE
ligne.tableParam=$1 ";
or this kind of query as well :
"SELECT distinct ligne.id FROM
fr.cnam.scapin.metier.donnees.tablesParam.LigneTableParametres ligne WHERE
ligne.tableParam=$1 ";

But these queries doesn't work on a just created object ...


Werner Guttmann - 22/Nov/04 02:33 PM
Sorry, Sylvie, my fault. Just forget my last comment.

Werner Guttmann - 22/Nov/04 02:39 PM
It actually looks like we really have a problem here. As Sylvie uses a
projection (i.e. restricts her query to just one field of the complete object),
SimpleQueryExecutor is used rather than the normal procedure (that uses the
complete persistence framework). As such, we just execute a SQL query against
the database without checking whether we have an object already associated with
the current transaction context.

To be honest, as it stands right now, I have no suggestions to make with regards
to a solution.

Sylvie, can't you simply load the complete object in question (without selecting
just one field), and then assess the value in question after the object has been
loaded ?


Werner Guttmann - 22/Nov/04 02:42 PM
Iow, if you changed

OQLQuery query = db.getOQLQuery("select account.ref from test.testJdo.Account
account where description = $1 ");

to

OQLQuery query = db.getOQLQuery("select account from test.testJdo.Account
account where description = $1 ");

things should work as designed. After having loaded the Account object, you
could then access the ref attribute via

account.getRef();


sylvie - 22/Nov/04 11:41 PM
In fact, at the beginning of the problem, here's the query I need to execute :

"SELECT max(ligne.numeroLigne) FROM
fr.cnam.scapin.metier.donnees.tablesParam.LigneTableParametres ligne WHERE
ligne.tableParam=$1 ";

And it's to try to find the real problem that I made this query :
OQLQuery query = db.getOQLQuery("select account.ref from test.testJdo.Account
account where description = $1 ");

I solved the first problem by an OQL pass-thru query
chaine.append("CALL SQL ");
chaine.append("select max(LIG_NUM_LIGNE) from SCA_PAR_LIGNES where TAB_ID = " +
table.getIdTableParametre());
chaine.append(" AS fr.cnam.scapin.metier.donnees.dummy.ScapinDummy");
oql = db.getOQLQuery(chaine.toString());

But I can't be sure the developpers will use this query or another ...
So I need a global solution, to be able to tell to the developpers that there
is no problem, and that they can use the same queries on just created object
than on objects which are yet on the database ...


Werner Guttmann - 26/Nov/04 03:50 AM
What does everybody else think about this ? To be honest, I am tempted to
provide a 'indicator' method on OQLQuery to indicate that e.g. the
SimpleQueryExecutor should be executed rather than using the full persistence
framework. Of course, things would be packaged and sold differently ... .
I've seen a similar feature on JDO, so why not educate the user that (s)he has
a choice here ?

Martin Fuchs - 28/Nov/04 07:49 AM
What about an additional method Database.getSimpleOQLQuery(String oql) nearby
the existing Database.getOQLQuery(String oql) ?
This alternate method would allow to create queries, which are executed using
SimpleQueryExecutor.

Werner Guttmann - 28/Nov/04 09:01 AM
One option, but I'd rather provide a boolean flag on an existing method, i.e.

getOQLQuery (String oql, boolean simple)

and make

getOQLQuery (String oql) call getOQLQuery (String oql, boolean simple) with a
value of 'false'. Similar, but sufficiently different .. . This way, users
who don't want to use the new feature do not have to distinguish between the old
method and a new one, asking themselves awkward questions.


Werner Guttmann - 28/Nov/04 12:03 PM
I am just trying to refactor the code to accommodate the new method described
above. Problem is that when forcing OQLQuery to execute in 'complete' mode, one
will have to alter the OQL query to return full object instances. I just wonder
whether this is the right road to follow ?

Ralf Joachim - 29/Nov/04 11:59 AM
I don't like the idea of adding another method to Database or OQLQuery that we
may have to deprecate again or need to support further with bug 1181 getting
solved. As far as I can see at the moment, the value returned from
OQLQuery.execute() have to change from QueryResults to Object because a query
can also return e.g Integer or a List of Strings. Ok, this needs not to be
discussed here.

The main problem for sylvie seams to be, that SimpleQueryExecuter doesn't
execute within the current transaction. Why not extend SimpleQueryExecuter to
execute within the current transaction. This also has the advantage that the
user/programmer don't need to decide which getOQLQuery() method to use.


Martin Fuchs - 04/Dec/04 07:15 AM
What I still don't understand about this whole thread:
Sylvie claims in Comment #14 to have worked around his problem using an "OQL
pass-thru query". This queries directly the current database content, just as
using a OQL query with projection (and this way using SimpleQueryExecuter).
Neither of both queries looks into the Castor object cache containing not-yet
committed JDO objects. So how could extending SimpleQueryExecuter to
execute within the current transaction be a solution for Sylvie's problem?
Sylvie: Are you really sure, using the "CALL SQL" queries does what you
expected and you can see just created objects of the current Castor transaction?

Ralf Joachim - 06/Dec/04 02:58 AM
Martin, I only had a quick look into OQLQuery and SimpleQueryExecuter. It
seams to me as if OQLQuery executes both OQL and CALL SQL queries within the
transaction and SimpleQueryExecuter executes without taking care on
transactions.

I further assumed that a change to an object has been passed to the database
by e.g. UPDATE... inside the transaction but not have been commited until now
(transaction is still open). With this assumtions a CALL SQL will return the
changed object while a query with SimpleQueryExecuter will return the
unchanged object.


Martin Fuchs - 06/Dec/04 03:13 PM
OK, the problem is that SimpleQueryExecutor creates another database session to
execute the query: It's the DatabaseRegistry.createConnection() call in
SimpleQueryExecutor.execute(). I wrote a small test program and debugged the
SimpleQueryExecutor. What do you think about the following patch? It moves the
SimpleQueryExecutor execution into the current database session and transaction.

Martin Fuchs - 06/Dec/04 03:15 PM
Created an attachment (id=811)
proposed patch

Martin Fuchs - 06/Dec/04 03:39 PM
Changing JDO attributes is even another case: If you change objects in memory,
you don't see them in the database until you call commit(). Neither CALL SQL
queries, nor simple queries will see them (with or without this patch). Only OQL
queries will return the already loaded objects with their changes.
What's addressed by this big is only the case about newly create()ed objects.
Those are INSERTed immediately into the database while executing the create() call.

Ralf Joachim - 07/Dec/04 12:13 AM
+1

Werner Guttmann - 12/Dec/04 01:24 PM
> Neither CALL SQL queries, nor simple queries will see them (with
> or without this patch). Only OQL queries will return the already
> loaded objects with their changes.
And that's by design, unless I am wrong. Looking at this, I think this is ready
to be committed. Objections ?

Werner Guttmann - 13/Dec/04 02:33 AM
Patch applied. Sylvie, can you please get the latest sources from the CVS
repository, build Castor yourself and see whether it resolves your problem ? If
that's not an option, you might want to download the 'daily build'.

sylvie - 23/Dec/04 12:18 AM
Hi,

Everything is ok with my JUnit test.

Thanks for your help.

Merry Christmas!