Yes, it is based off the mssql code but I made some modifications to
it to take care of situations like, for example, where there is an
identity column but it is not the primary key (and hence not a
'sequence'). This means a read off the catalog to find the identity
column (I believe that only one identity column is permitted per
table). I was wondering if some 'bad thing' happens if you execute a
select on the cursor and retrieve results when you are in the
pre_exec.
I don't know what you are referring to when you say 'throw a pdb' .. I
hope it has something to do with the debugger!
As for being in deep, I am afraid we are only starting: Sybase has
enough 'special' stuff to keep us busy for a long time e.g. cross-
database referential integrity constraints. database-specific default
schemas (e.g. login foo may have schema 'dbo' in database apple but
schema 'guest' in database pear and schema 'prod' in database banana).
Then what does one do about remote objects mapped in via CIS (e.g.
REMOTE.production.dbo.very_important_table) (actually this is a
problem with SQL Server also)
pjjH
On Feb 27, 4:05 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
> 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
>
> ...
>
> read more ยป
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
