python-sybase-misc

[Python-sybase-misc] Can't drop table without first closing cursor and connection

[<sk...@po...>]

I have a simple test script which creates a table, inserts a row, selects
the contents then deletes the table:

    import Sybase

    print Sybase.__version__

    db = Sybase.connect('TEST', 'skip', 'xxxxxx')
    curs = db.cursor()
    curs.execute("CREATE TABLE tempdb..skip (symbol CHAR(5))")

    dbvalues = (["A%d" % i] for i in range(10)) # this could be a recarray
    curs.executemany("INSERT into tempdb..skip VALUES (@1)" , dbvalues)
    curs.execute("SELECT * from tempdb..skip")
    print curs.fetchall()

    # I'm not sure why I have to open a new cursor to drop the table. A bug?
    #curs.execute("DROP TABLE tempdb..skip")

    curs.close()
    db.close()
    db = Sybase.connect('TEST', 'skip', 'xxxxxx')
    curs = db.cursor()
    curs.execute("DROP TABLE tempdb..skip")
    curs.close()
    db.close()

I've tried several variants of this code which would allow me to drop the
table with the original cursor and database connection.  All fail in one way
or another.  For example, if I comment out the db.close() and "db = ..."
statements near the end I get this error executing the DROP TABLE statement:

    Sybase.DatabaseError: Msg 2762, Level 16, State 4, Line 1
    The 'DROP TABLE' command is not allowed within a multi-statement
    transaction in the 'tempdb' database.

(I'd display the traceback, but apparently because I've installed 0.39x
using eggs it can't find the correct line/file information.  What I see
references our antiquated 0.36 install (done pre-egg).)

Is this a feature of Sybase, and bug in the adapter or something else?

Thanks,

Skip

Re: [Python-sybase-misc] Can't drop table without first closing cursor and connection

[Sébastien S. <sa...@us...>]

Hi Skip,

you need to either connect in auto_commit mode by using:

db = Sybase.connect('TEST', 'skip', 'xxxxxx', auto_commit=True)

or to manually commit after each execute or fetchall, using:

db.commit()

Your test should then work.

regards

--
Sébastien Sablé

sk...@po... a écrit :
> I have a simple test script which creates a table, inserts a row, selects
> the contents then deletes the table:
> 
>     import Sybase
> 
>     print Sybase.__version__
> 
>     db = Sybase.connect('TEST', 'skip', 'xxxxxx')
>     curs = db.cursor()
>     curs.execute("CREATE TABLE tempdb..skip (symbol CHAR(5))")
> 
>     dbvalues = (["A%d" % i] for i in range(10)) # this could be a recarray
>     curs.executemany("INSERT into tempdb..skip VALUES (@1)" , dbvalues)
>     curs.execute("SELECT * from tempdb..skip")
>     print curs.fetchall()
> 
>     # I'm not sure why I have to open a new cursor to drop the table. A bug?
>     #curs.execute("DROP TABLE tempdb..skip")
> 
>     curs.close()
>     db.close()
>     db = Sybase.connect('TEST', 'skip', 'xxxxxx')
>     curs = db.cursor()
>     curs.execute("DROP TABLE tempdb..skip")
>     curs.close()
>     db.close()
> 
> I've tried several variants of this code which would allow me to drop the
> table with the original cursor and database connection.  All fail in one way
> or another.  For example, if I comment out the db.close() and "db = ..."
> statements near the end I get this error executing the DROP TABLE statement:
> 
>     Sybase.DatabaseError: Msg 2762, Level 16, State 4, Line 1
>     The 'DROP TABLE' command is not allowed within a multi-statement
>     transaction in the 'tempdb' database.
> 
> (I'd display the traceback, but apparently because I've installed 0.39x
> using eggs it can't find the correct line/file information.  What I see
> references our antiquated 0.36 install (done pre-egg).)
> 
> Is this a feature of Sybase, and bug in the adapter or something else?
> 
> Thanks,
> 
> Skip
> 
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://sourceforge.net/services/buy/index.php
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc