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

Key: CASTOR-925
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Werner Guttmann
Reporter: Martin Fuchs
Votes: 0
Watchers: 0
Operations

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

OQL: allow to use bind variables in combination with "IN" operators

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

Time Tracking:
Not Specified

File Attachments: 1. Text File castor-1788.patch (5 kb)
2. Text File castor-oqltests1.patch (9 kb)

Environment:
Operating System: All
Platform: All

Bugzilla Id: 1788


 Description  « Hide
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)"



 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
Martin Fuchs - 13/Nov/04 03:41 AM
Created an attachment (id=768)
proposed patch

Martin Fuchs - 13/Nov/04 09:26 AM
Created an attachment (id=771)
CTF test cases to test basic OQL queries and the new functionality

Werner Guttmann - 22/Nov/04 02:33 AM
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.


Werner Guttmann - 22/Nov/04 02:37 AM
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.


Werner Guttmann - 22/Nov/04 02:39 AM
Actually, above test does NOT fail on mySQL 4.1.x ... . Hmm ?

Martin Fuchs - 22/Nov/04 12:18 PM
> 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.


Werner Guttmann - 22/Nov/04 12:48 PM
> 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 ?

Werner Guttmann - 22/Nov/04 12:50 PM
> 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.

Werner Guttmann - 22/Nov/04 01:27 PM
Patch applied (minus the issue mentioned above).

Martin Fuchs - 28/Nov/04 06:33 AM
OK, closed.