I want to automatically set IDENTITY_INSERT for a table if the
identity column is explicitly listed. Likewise, after execution of an
insert on a table with an identity column we want to retrieve the
identity value.
Any idea why the following code would cause the connection to be
checked in between the pre_exec() and the actual execution of the
statement? I have enabled high levels of debugging on the python-
sybase driver and can see that a new connection is made *after* the
'SET IDENTITY_INSERT foo ON' and the actual command runs on that new
connection (and, of course, fails as IDENTITY_INSERT is not enabled on
that table for the new connection).
pjjH
class SybaseSQLExecutionContext(default.DefaultExecutionContext):
def _table_identity_column(self, t):
"""Return the name of the this table's identity column"""
# negative caching
if not hasattr(t, '_identity_column'):
t._identity_column = None
s = r"""SELECT cols.name FROM syscolumns as cols JOIN
sysobjects as o ON (cols.id = o.id) WHERE o.name ='%s' and cols.status
& 0x80 = 0x80""" % (t.name)
self.cursor.execute(s)
r = self.cursor.fetchone()
if r:
t._identity_column = r[0]
return t._identity_column
def pre_exec(self):
self.HAS_IDENTITY = False
self.IDENTITY_INSERT = False
# What about UPDATE statements? Is this even possible in
Sybase?
if self.compiled.isinsert:
if self._table_identity_column
(self.compiled.statement.table):
self.HAS_IDENTITY = True
identity_column = self._table_identity_column
(self.compiled.statement.table)
if identity_column in self.compiled_parameters[0].keys
():
self.IDENTITY_INSERT = True
self.cursor.execute("SET IDENTITY_INSERT %s ON" %
self.dialect.identifier_preparer.format_table
(self.compiled.statement.table))
def post_exec(self):
if self.HAS_IDENTITY:
self.cursor.execute("SELECT @@identity AS lastrowid")
lastrowid = self.cursor.fetchone()[0]
if lastrowid > 0:
if not hasattr(self, '_last_inserted_ids') or
self._last_inserted_ids is None:
self._last_inserted_ids = [lastrowid]
else:
self._last_inserted_ids = [lastrowid] +
self._last_inserted_ids[1:]
if self.IDENTITY_INSERT:
self.cursor.execute("SET IDENTITY_INSERT %s OFF" %
self.dialect.identifier_preparer.format_table
(self.compiled.statement.table))
On Feb 27, 2:02 pm, "phrrn...@googlemail.com"
<phrrn...@googlemail.com> wrote:
> OK. I will do the development work against the 0.6 tree. I may end up
> backporting it to 0.5 as I want to get in into use at work as soon as
> is reasonable (which may be prior to the 0.6 release)
>
> pjjH
>
> On Feb 27, 11:29 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>
> > phrrn...@googlemail.com wrote:
>
> > > How does one deal with driver-specific unit tests? I am running in
> > > difficulties in testing the pyodbc and python-sybase drivers for the
> > > sybase dialect. For example, test_raw_qmark works with the pyodbc
> > > driver (as it supports that style) but not with the python-sybase
> > > driver. Is there some decorator available that can help with skipping
> > > certain tests for a given DBABI driver. Any suggestions on how to
> > > handle this?
>
> > most tests make usage of decorators like @testing.fails_on to mark various
> > databases as unsupported. That test in particular is very specific to
> > certain DBAPIs, i.e. those that support "qmark" bind parameters. For the
> > "lesser" databases like MSSQL and Firebird, i.e. those which have lots of
> > missing features, hundreds of decorators are configured to exclude them.
> > You would have a similar task in the case of sybase.
>
> > But to be specific regarding pyodbc vs. python-sybase, that is exactly
> > what's addressed in SQLA 0.6. If you look there you'll see the decorators
> > can differentiate among multiple DBAPIs for the same dialect, i.e.
> > sybase+pyodbc vs. sybase+python-sybase in this case. There is also a
> > coherent non-guesswork system of using specific drivers.
>
> > just so you know we'd really like SQLA 0.6 to be released soon after
> > pycon. There's not that much work to be done on it for a release. The
> > only reason its a "major" number is because the API for dialects does
> > change considerably.
>
> > > pjjH
>
> > > On Feb 26, 5:31 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > >> we have ticket 785 for this:
>
> > >>http://www.sqlalchemy.org/trac/ticket/785
>
> > >> On Feb 26, 2009, at 4:45 PM, phrrn...@googlemail.com wrote:
>
> > >> > Thanks Michael. I have a sybase.py passing *some* unit tests with both
> > >> > pyodbc and the Sybase driver, both running on Solaris 10 x86 against
> > >> > ASE 15. This is a hack that seems to work for the Sybase DBAPI module.
> > >> > I do have access to lots and lots of different Sybase stuff so I will
> > >> > start from your patched version and reintegrate my schema
> > >> > introspection and other stuff. Do you have a ticket open for the
> > >> > sybase driver yet? Where should I send the patches?
>
> > >> > pjjH
>
> > >> > def do_execute(self, cursor, statement, parameters, context=None,
> > >> > **kwargs):
> > >> > if self.paramstyle == 'named':
> > >> > #prepend the arguments with an '@'
> > >> > hacked_args = dict(("@"+n, v) for n,v in parameters.items
> > >> > ())
> > >> > super(SybaseSQLDialect_Sybase, self).do_execute(cursor,
> > >> > statement, hacked_args, context=context, **kwargs)
> > >> > else:
> > >> > super(SybaseSQLDialect_Sybase, self).do_execute(cursor,
> > >> > statement, parameters, context=context, **kwargs)
>
> > >> > def create_connect_args(self, url):
> > >> > opts = url.translate_connect_args()
> > >> > opts.update(url.query)
>
> > >> > self.autocommit = False
> > >> > if 'autocommit' in opts:
> > >> > self.autocommit = bool(int(opts.pop('autocommit')))
>
> > >> > dictArgs = {
> > >> > 'datetime' : 'python', # Stop the annoying
> > >> > diagnostics from the module
> > >> > 'auto_commit' : self.autocommit, # the named argument is
> > >> > called 'auto_commit' rather than 'autocommit'
> > >> > }
>
> > >> > if 'database' in opts:
> > >> > dictArgs['database'] = opts['database']
>
> > >> > return ([opts['host'], opts['username'], opts['password']],
> > >> > dictArgs)
>
> > >> > On Feb 26, 4:30 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > >> >> On Feb 26, 2009, at 3:55 PM, phrrn...@googlemail.com wrote:
>
> > >> >>> I am doing some work on a SA engine for Sybase Adaptive Server
> > >> >>> Enterprise (ASE) on top of both pyodbc and the Sybase DB-API driver.
> > >> >>> The existing sybase engine for SA only works with Sybase Anywhere
> > >> >>> (ASA).
>
> > >> >> that is correct ; I've recently had to take a look at this driver and
> > >> >> realized that it was not really written for Sybase at all, and the
> > >> >> original author is whereabouts unknown. To that end I would like it
> > >> >> to be replaced with an actual Sybase driver.
>
> > >> >>> There is a problem with named parameters with the Sybase driver in
> > >> >>> that the placeholders are prepended with an '@' *and* the execute
> > >> >>> method expects any dict paramers to have have keys that also have an
> > >> >>> '@'. I was able to get the placeholders generated correctly by
> > >> >>> subclassing the compiler. Any suggestions on how to get the execute
> > >> >>> method to work nicely or do I have to do some much around with
> > >> >>> copying
> > >> >>> parameters or monkeypatching the Sybase module with an
> > >> >>> implementation
> > >> >>> of execute that will work with 'ordinary' dictionaries?
>
> > >> >> the attached patch, which represents my partial progress, addresses
> > >> >> this. Unfortuantely I was not able to continue since I was
> > >> >> developing
> > >> >> from a Mac to a development server, and it turns out that connecting
> > >> >> with the Sybase driver using FreeTDS renders bind parameters
> > >> >> inoperable. After several days of attempting to get the developer
> > >> >> edition of sybase ASE running in a virtual linux environment
> > >> >> (apparently only works on older versions of ubuntu/fedora, but even
> > >> >> after installing those, I was unsuccessful), I gave up.
>
> > >> >> If you have access to a working Sybase ASE environment, you can have
> > >> >> full reign over the sybase.py dialect - anything specific to SQL
> > >> >> Anywhere can be removed, since its an obsolete product and if it were
> > >> >> supported, it would be in its own dialect. The Sybase driver may
> > >> >> be targeted towards the 0.6 release of SQLAlchemy. Version 0.6 is
> > >> >> oriented around a dialect refactor and schema expression refactor
> > >> >> (there are no ORM changes) and would be a much better place to start
> > >> >> building out new drivers - there are some significant differences in
> > >> >> how dialects are constructed between 0.5 versus 0.6.
>
> > >> >> sybase.patch
> > >> >> 12KViewDownload
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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
-~----------~----~----~----~------~----~------~--~---
