Bugzilla Version 2.16.7
Bugzilla Bug 1793
  Reading, in the same transaction, a data just created (reading via oql query)
     Query page      Enter new bug
Bug#: 1793   Platform:   Reporter: sylvie dot palluel at cnamts dot fr (sylvie)
Product:   OS:   Add CC:
Component:   Version:   CC:
Remove selected CCs
Status: CLOSED   Priority:  
Resolution: FIXED   Severity:  
Assigned To: werner dot guttmann at gmx dot net (Werner Guttmann)   Target Milestone:  
URL:
Summary:

Attachment Type Modified Status Actions
Persistent object application/octet-stream 2004-11-16 05:17 none Edit
Create Table application/octet-stream 2004-11-16 05:18 none Edit
Database configuration text/xml 2004-11-16 05:19 none Edit
Mapping file text/xml 2004-11-16 05:19 none Edit
Test application/octet-stream 2004-11-16 05:20 none Edit
proposed patch patch 2004-12-06 15:15 none Edit
Create a New Attachment (proposed patch, testcase, etc.) View All

Bug 1793 depends on: Show dependency tree
Show dependency graph
Bug 1793 blocks:
Votes: 0    Show votes for this bug    Vote for this bug

Additional Comments:


Leave as CLOSED FIXED
Reopen bug

View Bug Activity   |   Format For Printing

Description: Opened: 2004-11-15 02:15

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.

------- Additional Comment #1 From sylvie 2004-11-16 05:17 -------
Created an attachment (id=774)
Persistent object

File for JUnit test

------- Additional Comment #2 From sylvie 2004-11-16 05:18 -------
Created an attachment (id=775)
Create Table

JUnit Test

------- Additional Comment #3 From sylvie 2004-11-16 05:19 -------
Created an attachment (id=776)
Database configuration

JUnit Test

------- Additional Comment #4 From sylvie 2004-11-16 05:19 -------
Created an attachment (id=777)
Mapping file

JUnit test

------- Additional Comment #5 From sylvie 2004-11-16 05:20 -------
Created an attachment (id=778)
Test

JUnit test

------- Additional Comment #6 From Werner Guttmann 2004-11-16 05:24 -------
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.

------- Additional Comment #7 From Werner Guttmann 2004-11-16 05:37 -------
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>

------- Additional Comment #8 From sylvie 2004-11-16 10:07 -------
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.

------- Additional Comment #9 From Werner Guttmann 2004-11-19 13:00 -------
>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.

------- Additional Comment #10 From sylvie 2004-11-21 23:37 -------
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 ...




------- Additional Comment #11 From Werner Guttmann 2004-11-22 14:33 -------
Sorry, Sylvie, my fault. Just forget my last comment.

------- Additional Comment #12 From Werner Guttmann 2004-11-22 14:39 -------
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 ?

------- Additional Comment #13 From Werner Guttmann 2004-11-22 14:42 -------
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();

------- Additional Comment #14 From sylvie 2004-11-22 23:41 -------
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 ...



------- Additional Comment #15 From Werner Guttmann 2004-11-26 03:50 -------
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 ?

------- Additional Comment #16 From Martin Fuchs 2004-11-28 07:49 -------
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.

------- Additional Comment #17 From Werner Guttmann 2004-11-28 09:01 -------
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.

------- Additional Comment #18 From Werner Guttmann 2004-11-28 12:03 -------
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 ?

------- Additional Comment #19 From ralf joachim 2004-11-29 11:59 -------
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.

------- Additional Comment #20 From Martin Fuchs 2004-12-04 07:15 -------
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?

------- Additional Comment #21 From ralf joachim 2004-12-06 02:58 -------
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.

------- Additional Comment #22 From Martin Fuchs 2004-12-06 15:13 -------
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.

------- Additional Comment #23 From Martin Fuchs 2004-12-06 15:15 -------
Created an attachment (id=811)
proposed patch

------- Additional Comment #24 From Martin Fuchs 2004-12-06 15:39 -------
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.

------- Additional Comment #25 From ralf joachim 2004-12-07 00:13 -------
+1

------- Additional Comment #26 From Werner Guttmann 2004-12-12 13:24 -------
> 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 ?


------- Additional Comment #27 From Werner Guttmann 2004-12-13 02:33 -------
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'.

------- Additional Comment #28 From sylvie 2004-12-23 00:18 -------
Hi,

Everything is ok with my JUnit test.

Thanks for your help.

Merry Christmas!

     Query page      Enter new bug
This is Bugzilla: the Mozilla bug system. For more information about what Bugzilla is and what it can do, see bugzilla.org.
Actions: New | Query | bug # | Reports   New Account | Log In