On May 21, 2010, at 2:56 AM, Tobias wrote:
> Actually I am not manually constructing the insert statement,
> SQLAlchemy is doing that for me. I am just committing my session:
>
> spot_null = Spot(spot_height=None, spot_location=None)
> session.add(spot_null)
> session.commit();
a bindparam will be generated for spot_location, etc. and it will have the
type_ of spot_location.
If you want to hardwire some kind of SQL expression instead of NULL, assign it
to your mapped object:
Spot(spot_location=text('some_null_token'))
the commit will embed that in the INSERT.
>
> Note that spot_location is a GeoAlchemy geometry column. And if the
> value of this attribute is None, GeoAlchemy just returns None in its
> bind_processor.process() method.
>
> How is the type information sent to cx_Oracle. Using
> Cursor.inputtypehandler (http://cx-oracle.sourceforge.net/html/
> cursor.html#Cursor.inputtypehandler)?
>
>
> On May 20, 7:04 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On May 20, 2010, at 5:25 AM, Tobias wrote:
>>
>>
>>
>>> Hi,
>>
>>> I am working on Oracle support for GeoAlchemy and having problems when
>>> trying to insert NULL values into geometry columns.
>>
>>> spot_null = Spot(spot_height=None, spot_location=None)
>>> session.add(spot_null)
>>> session.commit();
>>
>>> DatabaseError: (DatabaseError) ORA-00932: inconsistent datatypes:
>>> expected MDSYS.SDO_GEOMETRY got CHAR
>>> 'INSERT INTO spots (spot_id, spot_height, spot_location) VALUES
>>> (spots_id_seq.nextval, :spot_height, :spot_location) RETURNING
>>> spots.spot_id INTO :ret_0' {'spot_location': None, 'spot_height':
>>> None, 'ret_0': <cx_Oracle.NUMBER with value None>}
>>
>>> The problem is that Oracle requires a type for bind parameters, and
>>> cx_Oracle -when using None- choses CHAR, because 'CHAR seems the most
>>> likely to cause the least difficulties', see [1]. I hope cx_Oracle
>>> will provide a proper way to use NULL for Oracle objects in near
>>> future.
>>
>>> But right now I am wondering if there is still a way to insert NULL
>>> values into geometry columns. Does SQLAlchemy maybe have a column
>>> flag, so that the column is not used as bind parameter? Or something
>>> else?
>>
>>> At the moment as workaround I set the attribute of my object to a
>>> select that queries NULL. It works but that is not a solution:
>>
>>> spot_null = Spot(spot_height=None,
>>> spot_location=select([text('NULL')], from_obj=['dual']))
>>
>> bindparam() supports a type_ attribute, and we send cx_oracle typing
>> information for all binds except for strings (which for some reason seem to
>> make things work more poorly). Not sure what the context here is though,
>> i.e. at what point are the bindparams being generated etc.
>>
>> if you really didn't want to bind them, then the insert() statement has to
>> be constructed that way. I.e. it can't be execute(stmt, {'foo':None}),
>> that's too late in the process.
>>
>> need more context here.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group
>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
