|
|
OQL: allow to use bind variables in combination with "IN" operators |
Query page
Enter new bug
Currently it is only possible to use OQL WHERE statements with "IN" operators
of the following form:
"... WHERE DNAME in list(1, 2, 3)"
"... WHERE DNAME in list(\"SALES\", \"TEST\", \"ABC\")"
"... WHERE DNAME in list(\"ABC\", nil)"
To make it easier executing dynamic queries, it would be good to also allow
using bind variables, for example:
"... WHERE DNAME in list($1, $2)"
------- Additional Comment
#1 From
Martin Fuchs
2004-11-13 03:41 -------
Created an attachment (id=768)
proposed patch
------- Additional Comment
#2 From
Martin Fuchs
2004-11-13 09:26 -------
Created an attachment (id=771)
CTF test cases to test basic OQL queries and the new functionality
------- Additional Comment
#3 From
Werner Guttmann
2004-11-22 02:33 -------
That's really great work, Martin. Just running the JDO test suite, but I cannot
see any problems hitting us .. ;-).
In addition, can I please ask you to have a look at the documentation, amend it
where necessary and do the same for the 'unofficial' release notes at the
CastorWiki site.
------- Additional Comment
#4 From
Werner Guttmann
2004-11-22 02:37 -------
Tests run fine, but a small issue with the last test, where you are apparently
expecting an exception to be thrown:
boolean exc = false;
try {
query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in
list(\"XXX\", \"one 21\", 999)");
tryQuery(query, 1);
} catch(Exception e) {
exc = true;
}
assertTrue("Exception expected", exc);
I'd consider rewriting this to ...
try {
query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in
list(\"XXX\", \"one 21\", 999)");
tryQuery(query, 1);
fail ("blah ...");
} catch(Exception e) {
assertEquals (e.getCLass().getName().equals ("org.exolab. ... ");
assertEquals (e.getMessage().equals ("exception text ..."));
}
Just my 0.02 (European) cents, though.
------- Additional Comment
#5 From
Werner Guttmann
2004-11-22 02:39 -------
Actually, above test does NOT fail on mySQL 4.1.x ... :-(. Hmm ?
------- Additional Comment
#6 From
Martin Fuchs
2004-11-22 12:18 -------
> Tests run fine, but a small issue with the last test, where you are apparently
expecting an exception to be thrown:
...
> I'd consider rewriting this to ...
>
> try {
> query = _db.getOQLQuery("select x from jdo.TestObject x where value1 in
> list(\"XXX\", \"one 21\", 999)");
> tryQuery(query, 1);
> fail ("blah ...");
> } catch(Exception e) {
> assertEquals (e.getCLass().getName().equals ("org.exolab. ... ");
> assertEquals (e.getMessage().equals ("exception text ..."));
> }
Yes, good idea. But there are currently some problems with it.
The following query generates an "ORA-01722: Invalid Number" exception on Oracle:
select *
from test_table
where value1 in ('XXX', 'one 21', 999)
This has been the exception, I was expecting for this last test case.
MySQL instead doesn't generate a error message. In fact it returns the whole
content of TEST_TABLE! IMHO this is a bug, or at least very unexpected
behaviour. There are no datasets with an value1 of "XXX", "one 21" or "999" in
this table.
The best way to resolve this would be to check for common/convertible types in
the "IN" list directly in Castor. Then it would be possible to add your
"assertEquals(e.getCLass().getName().equals("org.exolab. ... ");".
But that's a thing for a new bug report. For the moment I would like to propose
committing this patch, but commenting out the last test case. Just to let the
test suite run without problems on any of the databases.
------- Additional Comment
#7 From
Werner Guttmann
2004-11-22 12:48 -------
> The best way to resolve this would be to check for common/convertible types in
> the "IN" list directly in Castor. Then it would be possible to add your
>"assertEquals(e.getCLass().getName().equals("org.exolab. ... ");".
Any way of moving this to a RDBMS-specific class, similar to all the
limit/clause handling business ?
------- Additional Comment
#8 From
Werner Guttmann
2004-11-22 12:50 -------
> But that's a thing for a new bug report. For the moment I would like
> to propose committing this patch, but commenting out the last test
> case. Just to let the test suite run without problems on any of the databases.
Fine by me. Let's create a new bug report, uncomment this one particular test
again .. and let's address this issue in this new bug report.
------- Additional Comment
#9 From
Werner Guttmann
2004-11-22 13:27 -------
Patch applied (minus the issue mentioned above).
------- Additional Comment
#10 From
Martin Fuchs
2004-11-28 06:33 -------
OK, closed.
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.
|
|