[sqlalchemy] Re: More SA, pyodbc, *nux and MSSQL problems
On Jul 23, 8:30 am, Ed Singleton singleto...@gmail.com wrote:
I've managed to get SA (0.6 branch) and pyodbc connecting to anMSSQL
db on Mac OS X, but I've recently been trying to get it working on
linux (Debian Lenny) and have been hitting some problems.
It's definitely working to some degree. Adding TDS_Version = 8.0 to
my odbc.ini fixed some unicode problems, and so now simple pyodbc
stuff seems to work. Querying seems to be fine, but sometimes adding
lots of data using the orm fails with the message: DBAPIError: (Error)
('HY000', 'The driver did not supply an error!')
The same error occurs in both SA 0.5.5 and SA 0.6
I'm going to try a few more things to narrow down a bit more where the
problem is, but if any has any ideas of what it could be or how I
could debug it, I'd be very grateful.
Any luck on this? I'm using both with OSX and Ubuntu without
differences in behavior. Do you have an isolated test case that
duplicates this behavior?
Michael
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql and pyodbc weirdness with data not saving
Hi, On May 29, 2:37 am, Randy Syring ra...@rcs-comp.com wrote: I have been having a weird thing happen when using pyodbc with mssql. The create table statements work, but none of the INSERT statements do. For example, when I run code and echo, I get this: (TCSData) F:\Inetpub\TCSData\src\tcsdata-dist\tcsdatapysmvt broadcast initapp calling: action_pysapp_initapp 2009-05-29 02:22:17,194 INFO sqlalchemy.engine.base.Engine.0x...a350 BEGIN 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 INSERT INTO users_permission (name) VALUES (?); select scope_identity() 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 [u'webapp-c ontrolpanel'] 2009-05-29 02:22:17,210 INFO sqlalchemy.engine.base.Engine.0x...a350 COMMIT But there is nothing in that table when I am done committing. I profiled the server, and here is the code sequence that it shows: set implicit_transactions on exec sp_datatype_info 93, @ODBCVer = 3 SET IMPLICIT_TRANSACTIONS OFF BEGIN TRANSACTION declare @P1 int set @P1=1 exec sp_prepexec @P1 output, N'@P1 nvarchar(19)', N'INSERT INTO users_permission (name) VALUES (@P1); select scope_identity()', N'webapp-controlpanel' select @P1 exec sp_unprepare 1 IF @@TRANCOUNT 0 COMMIT TRAN If is switch to adodbapi, then the insert works just fine. Looks like you're using 0.5.3 I believe which had problems with this. Upgrade to a later release. Thanks, Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
If you change the test case to indicate that supports_unicode and supports_unicode_statements = False, then it runs just fine with the fix. Without the fix it fails as well, which indicates to me the issue is in FreeTDS. I'd like to commit this but I want to have a discussion with Mike Bayer first to be sure he's okay with it. I had a discussion with Mike Bayer and he expressed that he was uncomfortable committing a hack that just hides the problem instead of figuring out and fixing the problem properly. As we got into the code we began to question some of the design choices surrounding that bit of code, specifically the use of the ; select scope_identity() part. I spent quite a bit of time last night digging into the whole issue and here are my findings. First I removed the pyodbc specific code and just tried to use the base dialect code which doesn't do the ; select scope_identity() hack but instead actually calls a separate execute in the post_exec to get the identity value. This resulted in returning None values every time. I thought it was an issue with pyodbc since they indicate so in their documentation, but it turns out a raw pyodbc script produces the correct results. I finally discovered that the reason we're getting None in this case is do to the prepared queries. Basically the prepared query is in a different scope than the post_exec so it can't get the identity value. Changing this to not use scope_identity but to use @@identity works properly. Now clearly that's not the desired solution since that will be affected by triggers, etc..., and likely the reason for the odd implementation we see of ; select scope_identity. This ensured that the identity was retrieved in the same scope, prepared statement, as the initial insert. I say all the above just more for reference documentation and not as a solution to the problem. Once I got passed the identity issue I was able to get back to the initial Invalid Cursor State problem. After lots of traces it's clear that this cursor problem is a result of something that FreeTDS is doing when retrieving the identity, but only in the case of a transaction. The problem is related to the fact that in those cases the cursor is returning more than one result. That particular cursor error occurs when you try to select an identity but have not fully selected al the results from the cursor. The perplexing part is that the return value of the second result is always None which indicates that there are no more results. Here's a breakdown of what I saw: 1. fetchall()[0] - this will cause the problem to go away as indicated above because it's fully selecting all results before the identity is retrieved. 2. fetchone; fetchone() - if I add two fetchone() statements it will also cause the problem to go away. This clearly indicates that there is a second result. 3. session.commit() - Adding a session.commit() following each insert also causes the problem to go away. So clearly it's being influenced by the open transaction. I proved this by writing raw pyodbc outside of a transaction which worked fine. So the end result of all this is that I know the situation under which it's happening, I'm pretty confident the problem is related to FreeTDS and transactions (it doesn't happen on pure pyodbc on Windows at all), but I don't know the actual statement causing it nor the proper solution. I've also been unable to produce a pure pyodbc script that reproduces this problem, but I haven't explored everything there. I did find this thread where Rick Morrison identified the same problem. http://markmail.org/message/z4egbaof35j67dgt#query:+page:1+mid:z4egbaof35j67dgt+state:results If Rick has more information about this I'd love to hear it. That thread then went off in another direction. So at this point we don't have a solution. If we decide to get rid of the ; select scope_identity() business then that opens us up to identity problems where triggers are involved. The work around at this point is to commit following each insert. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On May 6, 7:56 am, Ed Singleton singleto...@gmail.com wrote:
If it helps, I have finally got my system working, now using FreeTDS
0.82, SQLAlchemy 0.5.3, pymssql, Python 2.5, (all on Mac Leopard) and
SQL Server 2005 (on an WinXP vm).
With this setup, your test passes without any problems.
I also tried it out using pyodbc 2.1.5 and the test failed with this
traceback:
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('42000', '[42000] [FreeTDS][SQL
Server]Must declare the scalar variable @u#. (137)
(SQLExecDirectW)') u'SELECT user_name() as user_name;' []
This is a result of passing unicode statements directly to FreeTDS
which doesn't work.
If you change the test case to indicate that supports_unicode and
supports_unicode_statements = False, then it runs just fine with the
fix. Without the fix it fails as well, which indicates to me the
issue is in FreeTDS.
I'd like to commit this but I want to have a discussion with Mike
Bayer first to be sure he's okay with it.
Michael
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 10:08 am, Tom Wood thomas.a.w...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. This change does not affect any of the tests on Windows. So that's good. I'd like to confirm a couple of other things before we commit. Thanks a lot. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 30, 11:04 pm, mtrier mtr...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. Also, FWIW, the original test passes just fine on Windows and pyodbc. So it's definitely a FreeTDS issue. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: pyodbc issues
On Apr 27, 9:10 pm, Michael Mileusnich justmike2...@gmail.com wrote: Id like to thank you for all your help however is my solution adequate to use in that I perform a metadata.create_all() in the function in my db.py python file that returns the session? On Mon, Apr 27, 2009 at 8:07 PM, Michael Mileusnich justmike2...@gmail.comwrote: On Mon, Apr 27, 2009 at 6:08 PM, Michael Trier mtr...@gmail.com wrote: Okay I'll get rid of it. On Apr 27, 2009, at 6:04 PM, Michael Bayer mike...@zzzcomputing.com wrote: I'd love to revert that change and just say MSSQL doesn't support SAVEPOINT for now. or put an enable_savepoint flag in the dialect. With r5930 I've moved all savepoint logic into savepoint oriented routines so it will not step on non-savepoint based code. Savepoint support is still very experimental in mssql. I'd appreciate as many eyes as possible on this changeset, as well as testing it out against problem code to see if this corrects our issues. At this point I'm pretty satisfied with the implementation based on the tests. I only have one failing test and after a couple of hours of comparing profile traces I'm still unable to understand why it's failing. The trace results are exactly the same. All other savepoint related tests are passing just fine. If this changeset still creates a problem (it shouldn't) let me know and we'll just revert all savepoint functionality. As of yet I still have not seen an isolated test case that illustrates the failures, so that makes me a bit uneasy in making code changes. So, if someone could produce an isolated test case that fails on the previous version of trunk that would still be very helpful to understand where the problem is. Thanks for everyone's help. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: pyodbc issues
On Apr 24, 2009, at 4:02 AM, Michael Mileusnich wrote:
I have formated my PC with Vista 32. I also Installed SQL Server 2008
Express. Installed Python 2.6 and pyodbc...SAME ISSUE. I would be willing
to have somebody overlook my python code. With echo on everything looks
like it should be INSERTING.
I've written this script based on the information you have supplied.
It works fine for me:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('mssql://sprint:spr...@localhost/sprint',
echo=True)
metadata = MetaData(engine)
Session = scoped_session(sessionmaker(bind=engine, autoflush=False,
autocommit=True))
action_table = Table(
'ACTIONS', metadata,
Column('ACTIONID', String(48), primary_key=True),
Column('TITLE', String(128)),
Column('CMDLINE', String(512)),
Column('STDIN', Text),
Column('STARTINDIR', String(512)),
Column('PRIO', Integer),
)
class action(object):
def __init__(self, ACTIONID, CMDLINE):
self.ACTIONID = ACTIONID
self.CMDLINE = CMDLINE
def __repr__(self):
return action('%s', '%s') % (self.ACTIONID, self.CMDLINE)
mapper(action, action_table)
metadata.create_all()
session = Session()
new_action = action(ACTIONID = '500', CMDLINE = 'sol')
session.add(new_action)
session.flush()
session.expunge_all()
act = session.query(action).filter_by(ACTIONID='500').one()
assert new_action.ACTIONID == act.ACTIONID
--
Would you please try it and let me know what results you get. The
following is my output.
S:\sqlalchemy.git\libpython msssqlprob.py
2009-04-24 16:10:30,473 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT user_name() as user_name;
2009-04-24 16:10:30,552 INFO sqlalchemy.engine.base.Engine.0x...6110
[]
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT default_schema_name FROM
sys.database_principals
WHERE name = ?
AND type = 'S'
2009-04-24 16:10:30,568 INFO sqlalchemy.engine.base.Engine.0x...6110
[u'dbo']
2009-04-24 16:10:30,582 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TAB
LE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].
[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLU
MN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
['ACTIONS', u'dbo']
2009-04-24 16:10:30,598 INFO sqlalchemy.engine.base.Engine.0x...6110
CREATE TABLE [ACTIONS] (
[ACTIONID] VARCHAR(48) NOT NULL,
[TITLE] VARCHAR(128) NULL,
[CMDLINE] VARCHAR(512) NULL,
[STDIN] TEXT NULL,
[STARTINDIR] VARCHAR(512) NULL,
[PRIO] INTEGER NULL,
PRIMARY KEY ([ACTIONID])
)
2009-04-24 16:10:30,630 INFO sqlalchemy.engine.base.Engine.0x...6110
()
2009-04-24 16:10:30,661 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,693 INFO sqlalchemy.engine.base.Engine.0x...6110
BEGIN
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
INSERT INTO [ACTIONS] ([ACTIONID], [TITLE], [CMDLIN
E], [STDIN], [STARTINDIR], [PRIO]) VALUES (?, ?, ?, ?, ?, ?)
2009-04-24 16:10:30,707 INFO sqlalchemy.engine.base.Engine.0x...6110
['500', None, 'sol', None, None, None]
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
COMMIT
2009-04-24 16:10:30,723 INFO sqlalchemy.engine.base.Engine.0x...6110
SELECT TOP 2 [ACTIONS].[ACTIONID] AS [ACTIONS_ACTIO
NID], [ACTIONS].[TITLE] AS [ACTIONS_TITLE], [ACTIONS].[CMDLINE] AS
[ACTIONS_CMDLINE], [ACTIONS].[STDIN] AS [ACTIONS_STDI
N], [ACTIONS].[STARTINDIR] AS [ACTIONS_STARTINDIR], [ACTIONS].[PRIO]
AS [ACTIONS_PRIO]
FROM [ACTIONS]
WHERE [ACTIONS].[ACTIONID] = ?
2009-04-24 16:10:30,740 INFO sqlalchemy.engine.base.Engine.0x...6110
['500']
Michael Trier
http://michaeltrier.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
