phrrn...@googlemail.com wrote:
>
> 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).
Assuming you took that code from the MSSQL dialect, it should be fine.
that looks like an older version of it, though...in 0.6 take a look in
mssql/base.py for the latest version of the IDENTITY_INSERT dance.
but no there's nothing in there in any case that would cause a second
connection to be checked out. throw a pdb into the Connection constructor,
or perhaps in pool.connect(), to track where that's coming from.
sorry you're in deep .... :)
>
> 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
-~----------~----~----~----~------~----~------~--~---
