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(); 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 <[email protected]> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
