FrancescaL wrote:
> Hi group,
>
> I have a question about Oracle CLOBs.
>
> I expect to be able to manage all 3 different LOB column states:
> (a) Populated
> (b) Empty
> (c) NULL
> First of all: is it my expectation correct?
possibly not. my experience with oracle CLOB is that a "blank" ends up
being a single blank string.
SQLAlchemy doesn't do anything here, data is passed directly from
cx_oracle and back, my advise would be to create a cx_oracle-only test
case (see the docs on cx_oracle's site, I can't copy and paste a URL on
this terminal unfortunately) and email the cx_oracle list if you believe
the DBAPI's direct behavior is erroneous, however this is sort of how it
goes with Oracle and there might not be a workaround.
>
> I'm in process of upgrading to SqlAlchemy 0.6.9 and this seems (to me)
> no more possible.
>
> Here it is how to build 3 examples (one for each column state).
>
> # ------------------------------
> # SQL for manual table creation:
> DROP TABLE t_tmp_sqla_upgrade;
> CREATE TABLE t_tmp_sqla_upgrade (
> id NUMBER (20) not null,
> text CLOB
> );
>
> # ------------------------------
> # Python code:
> from sqlalchemy import MetaData, Table, Column, Integer, Text,
> create_engine
> from sqlalchemy.orm import mapper, sessionmaker
>
> metadata = MetaData()
> table = Table('t_tmp_sqla_upgrade', metadata,
> Column('id', Integer, primary_key=True),
> Column('text', Text())
> )
>
> class TmpTableEntity(object):
> pass
>
> mapper(TmpTableEntity, table)
>
> kwargs = {}
> kwargs['encoding'] = 'utf-8'
> engine = create_engine(u"oracle+cx_oracle://user:pwd@db", **kwargs)
> Session = sessionmaker(bind=engine, autoflush=False)
>
> def createItem(session, text):
> e = TmpTableEntity()
> e.id = 1
> e.text = text
> session.add(e)
> session.commit()
> session.expunge_all()
> return e
>
> def readItem(session, id):
> return
> session.query(TmpTableEntity).filter(TmpTableEntity.id==id).one()
>
> session = Session()
>
> # 3 cases (setup and teardown is needed for each case)
>
> # Case (a): some text (OK)
> text = u'some plain ascii text'
> item = createItem(session, text)
> rItem = readItem(session, 1)
> assert rItem.text == text
>
> # Case (b): empty text (KO)
> text = u''
> item = createItem(session, text)
> rItem = readItem(session, 1)
> assert rItem.text == None
> # But I expect: assert rItem.text == u''
> # since this is a CLOB field and not a VARCHAR2 one.
>
> # Case (c): null text (OK)
> text = None
> item = createItem(session, text)
> rItem = readItem(session, 1)
> assert rItem.text == None
>
> Thank you for your time,
> Francesca
>
> --
> 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.
>
>
--
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.
