[storm] What's the best way to handle Python Boolean values with Oracle?

[Vernon Cole]

Yes, the test should be skipped, IMHO.  In the adodbapi test suite, it was
necessary to do this in many cases, so a function to determine the
underlying dialect of SQL at the moment was added to the test suite. (I
often wish there were a way to add it to the connection object.) For
example:
<Python>
    def testDataTypeBinary(self):
        if self.getEngine() == 'MySQL':
            pass
#self.helpTestDataType("BLOB",'BINARY',adodbapi.Binary('\x00\x01\xE2\x40'))
        else:
            binfld = str2bytes('\x00\x01\xE2\x40')  # works in Python 2 or 3

self.helpTestDataType("binary(4)",'BINARY',adodbapi.Binary(binfld))

self.helpTestDataType("varbinary(100)",'BINARY',adodbapi.Binary(binfld))
            self.helpTestDataType("image",'BINARY',adodbapi.Binary(binfld))
<\Python>

Question for the group:

  I think it would be handy if such a function (or perhaps an attribute)
were added to the database object. It could contain the "scheme" as a
string, perhaps, or perhaps something more definitive.  Such a feature would
be very helpful in an ADO implementation.

Would the group entertain such a thing?

Could it/how should it be done?
--
Vernon Cole


On Tue, Jun 23, 2009 at 8:30 AM, Drew Smathers <drew.smathers at gmail.com>wrote:

> On Tue, Jun 23, 2009 at 5:16 AM, James Henstridge<james at jamesh.id.au>
> wrote:
> > On Mon, Jun 22, 2009 at 11:44 PM, Jason Baker<jbaker at zeomega.com> wrote:
> >> There are two tests left that are giving me trouble:
> >>
> >>    def test_is_in_empty_result_set(self):
> >>        result1 = self.store.find(Foo, Foo.id < 10)
> >>        result2 = self.store.find(Foo, Or(Foo.id > 20,
> Foo.id.is_in(result1)))
> >>        self.assertEquals(result2.count(), 1)
> >>
> >>    def test_is_in_empty_list(self):
> >>        result2 = self.store.find(Foo, Eq(False, And(True,
> Foo.id.is_in([]))))
> >>        self.assertEquals(result2.count(), 3)
> >>
> >> Both of these tests pass a Python Boolean to the backend.  The problem
> >> is that these both pass a boolean value in the WHERE clause, which
> >> there's really not any direct translation for in Oracle.  I tried it
> >> with this compile function:
> >>
> >> @compile.when(bool)
> >> def compile_bool(compile, expr, state):
> >>    return compile_eq(compile, Eq(1, int(expr)), state)
> >>
> >> This essentially converts a bool to a 1=1 or 1=0 expression.  This
> >> works with the first test, but the second one gives me this:
> >>
> >> [10:27:38.064000] EXECUTE: 'SELECT COUNT(*) FROM foo WHERE 1 = 0 = (1
> >> = 1 AND 1 = 0)', (1, 0, 1, 1, 1, 0)
> >> [10:27:38.111000] ERROR: ORA-00933: SQL command not properly ended
> >>
> >> Which is of course gibberish in Oracle.  I can run the test without a
> >> WHERE clause and have it work, but will anything break if the backend
> >> can't handle an expression of the form FALSE = (TRUE AND FALSE)?  Or
> >> is there a better way to do this that I'm missing?
> >
> > If this is just a case of parentheses, it might be easiest to do the
> following:
> >
> > @compile.when(bool)
> > def compile_bool(compile, expr, state):
> >    if expr:
> >        return "(1=1)"
> >    else:
> >        return "(1=0)"
> >
> > That should avoid the operator precedence problems.
> >
> > James.
> >
>
> The problem is more than operator precendence with Oracle; Oracle
> simply doesn't have a boolean type (outside of PL/SQL) and can't
> understand a boolean comparison: (1=0) = (1=1).  This would result in
> an ORA-00933.  In that respect, I don't see how it's possible to make
> test_is_in_empty_list pass since it involves a boolean comparison.
> Given that boolean data type is optional in the SQL spec, would it not
> be sufficient to skip this test for oracle?
>
> -Drew
>
> --
> storm mailing list
> storm at lists.canonical.com
> Modify settings or unsubscribe at:
> https://lists.ubuntu.com/mailman/listinfo/storm
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/storm/attachments/20090623/742162a2/attachment.htm