mssql reflection not working
Issue #1312
resolved
I searched existing open issues tagged with mssql but didn't see this one. Please close it if it's a duplicate.
~Matt
Pylons 0.9.7, SQLAlchemy 0.5.x, OSX 10.5, iodbc, freetds, remote MSSQL Server instance
test.py contains:
engine = create_engine("mssql://mdoar:secretword@hostname.example.com:1433/my_database?driver=TDS&odbc_options='TDS_Version=7.0'")
This breaks with the attached backtrace
meta = schema.MetaData(engine, reflect=True)
but this works fine:
meta = schema.MetaData(engine, reflect=False)
File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 907, in _execute_compiled self.__execute_raw(context) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 916, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters0, context=context) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 960, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.4.7p1-py2.5.egg/sqlalchemy/engine/base.py", line 942, in _handle_dbapi_exception raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000') u'SELECT tables_1.table_name \nFROM information_schema.tables AS tables_1 \nWHERE tables_1.table_schema = ?' 'dbo'
Comments (23)
-
Account Deleted
-
repo owner
- changed watchers to mdoar@pobox.com
this is likely an issue with FreeTDS and how you're connecting to it. the statement does contain a "?" parameter marker and just recently I noticed FreeTDS has all sorts of issues with bind params.
-
Account Deleted
The original traceback was with 0.4.7 but here is a similar one with 0.5.2
Traceback (most recent call last): File "Importer/importer/tests/alchemy_test4.py", line 19, in <module> meta = schema.MetaData(engine, reflect=True) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/schema.py", line 1542, in init self.reflect() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/schema.py", line 1684, in reflect connection=conn)) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1248, in table_names schema = self.dialect.get_default_schema_name(conn) File "<string>", line 1, in <lambda> File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1894, in decorated connection.infokey = val = fn(self, connection) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/databases/mssql.py", line 1069, in get_default_schema_name user_name = connection.scalar(sql.text(query)) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 814, in scalar return self.execute(object, multiparams, *params).scalar() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 824, in execute return Connection.executorsc(self, object, multiparams, params) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 874, in _execute_clauseelement return self.__execute_context(context) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters0, context=context) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "42000 FreeTDSServerIncorrect syntax near '%'. (102) (SQLExecDirectW)") u'SELECT user_name() as user_name;' []
-
Account Deleted
(original author: ram) Hmmm, the query shown in that traceback ("select user_name()...") doesn't use bound parameters at all, and there's no percent sign in the query..
Also notice that the query string itself is being sent in unicode. This could also be a string encoding issue..
can you please turn on odbc logging and post the log to see what's actually being sent?
-
Account Deleted
Sure. Here's the minimal test program, the results and the odbc log output. Interesting how this has a closed cursor error, just like 1313.
~Matt
# easy_install pyodbc # v2.1.4 from sqlalchemy import orm from sqlalchemy.sql import and_, join, select from sqlalchemy import schema, types, Table, Column from sqlalchemy import Integer, String from sqlalchemy.databases.mssql import MSInteger, MSNVarchar from sqlalchemy.engine import create_engine from sqlalchemy.ext.declarative import declarative_base import sys import urllib raw_cs = "DSN=DTV;UID=mdoar;PWD=secret;DRIVER={TDS};TDS_Version=7.0" cs = "mssql:///?odbc_connect=%s" % urllib.quote_plus(raw_cs) cs_default = 'sqlite:///:memory:' engine = create_engine(cs, echo=True) meta = schema.MetaData(engine, reflect=True) (venv_0.9.7)[uji_pylons_0.9.7](mdoar@mdoar2)$ python Importer/importer/tests/alchemy_test6.py 2009-02-11 14:30:46,421 INFO sqlalchemy.engine.base.Engine.0x...3fb0 SELECT user_name() as user_name; 2009-02-11 14:30:46,421 INFO sqlalchemy.engine.base.Engine.0x...3fb0 [14:30:46,446 WARNING sqlalchemy.pool.QueuePool.0x...8e10 Error closing cursor: Attempt to use a closed cursor. 2009-02-11 14:30:46,447 INFO sqlalchemy.engine.base.Engine.0x...3fb0 ROLLBACK Traceback (most recent call last): File "Importer/importer/tests/alchemy_test6.py", line 19, in <module> meta = schema.MetaData(engine, reflect=True) File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/schema.py", line 1542, in __init__ self.reflect() File "/Users/mdoar/atlassian/jira/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/schema.py", line 1684, in reflect connection=conn)) File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1248, in table_names schema = self.dialect.get_default_schema_name(conn) File "<string>", line 1, in <lambda> File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1894, in decorated connection.info[key](] 2009-02-11) = val = fn(self, connection) File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/databases/mssql.py", line 1069, in get_default_schema_name user_name = connection.scalar(sql.text(query)) File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 814, in scalar return self.execute(object, *multiparams, **params).scalar() File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 1676, in scalar self.connection._handle_dbapi_exception(e, None, None, self.cursor, self.context) File "/Users/mdoar/commercial/venv_0.9.7/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.ProgrammingError: (ProgrammingError) Attempt to use a closed cursor. None None ** iODBC Trace file ** Trace started on Wed Feb 11 14:30:46 2009 ** Driver Manager: 03.52.0406.1211 [000000.000156](000000.000156) python A003C720 ENTER SQLAllocHandle SQLSMALLINT 1 (SQL_HANDLE_ENV) SQLHANDLE 0x0 (SQL_NULL_HANDLE) SQLHANDLE * 0xd4c044 [000000.000206](000000.000206) python A003C720 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 1 (SQL_HANDLE_ENV) SQLHANDLE 0x0 (SQL_NULL_HANDLE) SQLHANDLE * 0xd4c044 (0x8de470) [000000.000249](000000.000249) python A003C720 ENTER SQLSetEnvAttr SQLHENV 0x8de470 SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION) SQLPOINTER 0x3 SQLINTEGER * 4 [000000.000308](000000.000308) python A003C720 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS) SQLHENV 0x8de470 SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION) SQLPOINTER 0x3 SQLINTEGER * 4 [000000.000359](000000.000359) python A003C720 ENTER SQLAllocHandle SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHANDLE 0x8de470 SQLHANDLE * 0xbfffc60c [000000.000403](000000.000403) python A003C720 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHANDLE 0x8de470 SQLHANDLE * 0xbfffc60c (0x8e4d00) [000000.000456](000000.000456) python A003C720 ENTER SQLDriverConnectW SQLHDBC 0x8e4d00 SQLPOINTER 0x0 SQLWCHAR * 0xbfffba54 | DSN=DTV;UID=mdoar;PWD=********;DRIVER={T | | DS};TDS_Version=7.0 | SQLSMALLINT -3 (SQL_NTS) SQLWCHAR * 0x0 SQLSMALLINT 0 SQLSMALLINT * 0x0 SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT) [000000.095878](000000.095878) python A003C720 EXIT SQLDriverConnectW with return code 0 (SQL_SUCCESS) SQLHDBC 0x8e4d00 SQLPOINTER 0x0 SQLWCHAR * 0xbfffba54 SQLSMALLINT -3 (SQL_NTS) SQLWCHAR * 0x0 SQLSMALLINT 0 SQLSMALLINT * 0x0 SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT) [000000.095986](000000.095986) python A003C720 ENTER SQLSetConnectAttr SQLHDBC 0x8e4d00 SQLINTEGER 102 (SQL_ATTR_AUTOCOMMIT) SQLPOINTER 0x0 SQLINTEGER * -5 (SQL_IS_UINTEGER) [000000.119627](000000.119627) python A003C720 EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS) SQLHDBC 0x8e4d00 SQLINTEGER 102 (SQL_ATTR_AUTOCOMMIT) SQLPOINTER 0x0 SQLINTEGER * -5 (SQL_IS_UINTEGER) [000000.119798](000000.119798) python A003C720 ENTER SQLGetInfo SQLHDBC 0x8e4d00 SQLUSMALLINT 77 (SQL_DRIVER_ODBC_VER) SQLPOINTER 0xbfffb9d0 SQLSMALLINT 20 SQLSMALLINT * 0xbfffb9ee [000000.119873](000000.119873) python A003C720 EXIT SQLGetInfo with return code 0 (SQL_SUCCESS) SQLHDBC 0x8e4d00 SQLUSMALLINT 77 (SQL_DRIVER_ODBC_VER) SQLPOINTER 0xbfffb9d0 | 03.00 | SQLSMALLINT 20 SQLSMALLINT * 0xbfffb9ee (5) [000000.119931](000000.119931) python A003C720 ENTER SQLGetInfo SQLHDBC 0x8e4d00 SQLUSMALLINT 10002 (SQL_DESCRIBE_PARAMETER) SQLPOINTER 0xbfffb9ec SQLSMALLINT 2 SQLSMALLINT * 0xbfffb9ee [000000.119981](000000.119981) python A003C720 EXIT SQLGetInfo with return code 0 (SQL_SUCCESS) SQLHDBC 0x8e4d00 SQLUSMALLINT 10002 (SQL_DESCRIBE_PARAMETER) SQLPOINTER 0xbfffb9ec | N | SQLSMALLINT 2 SQLSMALLINT * 0xbfffb9ee (1) [000000.120038](000000.120038) python A003C720 ENTER SQLAllocHandle SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHANDLE 0x8e4d00 SQLHANDLE * 0xbfffb9e8 [000000.120099](000000.120099) python A003C720 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHANDLE 0x8e4d00 SQLHANDLE * 0xbfffb9e8 (0x8e5180) [000000.120146](000000.120146) python A003C720 ENTER SQLGetTypeInfo SQLHSTMT 0x8e5180 SQLSMALLINT 93 (SQL_TYPE_TIMESTAMP) [000000.149573](000000.149573) python A003C720 EXIT SQLGetTypeInfo with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e5180 SQLSMALLINT 93 (SQL_TYPE_TIMESTAMP) [000000.149672](000000.149672) python A003C720 ENTER SQLFetch SQLHSTMT 0x8e5180 [000000.149720](000000.149720) python A003C720 EXIT SQLFetch with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e5180 [000000.149753](000000.149753) python A003C720 ENTER SQLGetData SQLHSTMT 0x8e5180 SQLUSMALLINT 3 SQLSMALLINT 4 (SQL_C_LONG) SQLPOINTER 0xbfffb9e4 SQLLEN 4 SQLLEN * 0x0 [000000.149832](000000.149832) python A003C720 EXIT SQLGetData with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e5180 SQLUSMALLINT 3 SQLSMALLINT 4 (SQL_C_LONG) SQLPOINTER 0xbfffb9e4 | 23 | SQLLEN 4 SQLLEN * 0x0 [000000.149896](000000.149896) python A003C720 ENTER SQLFreeStmt SQLHSTMT 0x8e5180 SQLUSMALLINT 0 (SQL_CLOSE) [000000.174456](000000.174456) python A003C720 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e5180 SQLUSMALLINT 0 (SQL_CLOSE) [000000.175127](000000.175127) python A003C720 ENTER SQLAllocHandle SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHANDLE 0x8e4d00 SQLHANDLE * 0xed66f4 [000000.175179](000000.175179) python A003C720 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHANDLE 0x8e4d00 SQLHANDLE * 0xed66f4 (0x8e9a60) [000000.175884](000000.175884) python A003C720 ENTER SQLFreeStmt SQLHSTMT 0x8e9a60 SQLUSMALLINT 0 (SQL_CLOSE) [000000.176010](000000.176010) python A003C720 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e9a60 SQLUSMALLINT 0 (SQL_CLOSE) [000000.176051](000000.176051) python A003C720 ENTER SQLExecDirectW SQLHSTMT 0x8e9a60 SQLWCHAR * 0x8df2e0 | | SQLINTEGER -3 (SQL_NTS) [000000.199977](000000.199977) python A003C720 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e9a60 SQLWCHAR * 0x8df2e0 SQLINTEGER -3 (SQL_NTS) [000000.200086](000000.200086) python A003C720 ENTER SQLRowCount SQLHSTMT 0x8e9a60 SQLLEN * 0xbfffccb8 [000000.200121](000000.200121) python A003C720 EXIT SQLRowCount with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e9a60 SQLLEN * 0xbfffccb8 (-1) [000000.200153](000000.200153) python A003C720 ENTER SQLNumResultCols SQLHSTMT 0x8e9a60 SQLSMALLINT * 0xbfffccbe [000000.200185](000000.200185) python A003C720 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e9a60 SQLSMALLINT * 0xbfffccbe (0) [000000.200304](000000.200304) python A003C720 ENTER SQLFreeStmt SQLHSTMT 0x8e9a60 SQLUSMALLINT 0 (SQL_CLOSE) [000000.200339](000000.200339) python A003C720 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) SQLHSTMT 0x8e9a60 SQLUSMALLINT 0 (SQL_CLOSE) [000000.200372](000000.200372) python A003C720 ENTER SQLFreeHandle SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHSTMT 0x8e9a60 [000000.200407](000000.200407) python A003C720 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 (SQL_HANDLE_STMT) SQLHSTMT 0x8e9a60 [000000.200952](000000.200952) python A003C720 ENTER SQLEndTran SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHDBC 0x8e4d00 SQLSMALLINT 1 (SQL_ROLLBACK) [000000.225007](000000.225007) python A003C720 EXIT SQLEndTran with return code 0 (SQL_SUCCESS) SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHDBC 0x8e4d00 SQLSMALLINT 1 (SQL_ROLLBACK) [000000.243707](000000.243707) python A003C720 ENTER SQLEndTran SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHDBC 0x8e4d00 SQLSMALLINT 1 (SQL_ROLLBACK) [000000.274140](000000.274140) python A003C720 EXIT SQLEndTran with return code 0 (SQL_SUCCESS) SQLSMALLINT 2 (SQL_HANDLE_DBC) SQLHDBC 0x8e4d00 SQLSMALLINT 1 (SQL_ROLLBACK) -
Account Deleted
And just for kicks I sent the same SQL query in with a raw pyodbc script and it worked just fine.
-
Account Deleted
(original author: ram) Eh, that's not as helpful as I hoped: I was hoping to see the actual SQL text being sent.
Interesting how this has a closed cursor error, just like 1313. Well, this is a very different error than the last traceback, which complained about a '%' in the query string. What's different this time?
I sent the same SQL query in with a raw pyodbc script and it worked just fine a) Which query was that? The "select user_name()..." query? b) Did you send the query in unicode as SA is doing, or ascii?
-
Account Deleted
The difference is that I upgrade to 0.5.2
The query I was referring to is "SELECT user_name() as user_name;" I think you're on the right track because with a test script:
import pyodbc connection = pyodbc.connect("DSN=DTV;UID=mdoar;PWD=secret;DRIVER={TDS};TDS_Version=7.0") result = connection.execute('SELECT user_name() as user_name;') print "First result: %s" % result.fetchone() # The exact same query but as unicode this time causes a crash result2 = connection.execute(u'SELECT user_name() as user_name;') print "Second result: %s" % result2.fetchone() connection.close()I see the following output:
[commercial](mdoar@mdoar2)$ python ~/tester4.py First result: ('mdoar', ) Traceback (most recent call last): File "/Users/mdoar/tester4.py", line 8, in <module> result2 = connection.execute(u'SELECT user_name() as user_name;') pyodbc.ProgrammingError: ('24000', '[24000](24000) [FreeTDS](FreeTDS)[Server](SQL)Invalid cursor state (0) (SQLExecDirectW)') -
Account Deleted
I believe that FreeTDS doesn't support unicode
~Matt
-
Account Deleted
(original author: ram) ..so it's probably a string encoding issue.
Read this: http://www.freetds.org/userguide/localization.htm
and set the client character set as appropriate (probably utf-8)
You may also want to use tds v8 instead, tds v7 will cause issues with datetimes being one day off, and tds v8 supports new mssql types in mssql 2000+: http://www.freetds.org/userguide/choosingtdsprotocol.htm
-
Account Deleted
Hmm, according to tsql from FreeTDS I am already using UTF-8. Thanks for the 8.0 suggestion.
freetds-0.82$ tsql -S DTV -U mdoar -P secret -D dtv_tt_data_migration Locale is "en_US.UTF-8" locale charset is "UTF-8"
I'd be interested to know whether anyone else has managed to get reflection working with mssql and 0.5.
-
Account Deleted
(original author: ram) The reflection code that issues the query is:
query = "SELECT user_name() as user_name;" user_name = connection.scalar(sql.text(query))Mike: Does the "sql.text()" call return a unicode string? Maybe we can just use str() on the result and send an ascii query instead?
-
Account Deleted
Looks like there may still be some problem with the parameter substitution?
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000') u'SELECT tables_1.table_name \nFROM information_schema.tables AS tables_1 \nWHERE tables_1.table_schema = ?' 'dbo'
-
Account Deleted
- changed milestone to 0.5.xx
(original author: ram) My suspicion is that the parameter substitution error is a red herring and is an artifact of a mistaken parameter when parsing the unicode sql string. Awaiting a unix+odbc+freedts test environment to verify this.
-
Account Deleted
Yes, the str() call on the result from sql.text() improves things and if I change the actual statement with a str() call, then reflection appears to work. Now perhaps this would all be unnecessary if I could set the character set properly in freetds/odbc but what is below works for me.
Index: lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/databases/mssql.py =================================================================== --- lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/databases/mssql.py (revision 572) +++ lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/databases/mssql.py (working copy) @@ -1066,7 +1066,7 @@ @base.connection_memoize(('dialect', 'default_schema_name')) def get_default_schema_name(self, connection): query = "SELECT user_name() as user_name;" - user_name = connection.scalar(sql.text(query)) + user_name = connection.scalar(str(sql.text(query))) if user_name is not None: # now, get the default schema query = """ @@ -1076,7 +1076,7 @@ AND type = 'S' """ try: - default_schema_name = connection.scalar(sql.text(query), + default_schema_name = connection.scalar(str(sql.text(query)), user_name=user_name) if default_schema_name is not None: return default_schema_name @@ -1169,7 +1169,7 @@ coltype = coltype(**kwargs) colargs = [] if default is not None: - colargs.append(schema.DefaultClause(sql.text(default))) + colargs.append(schema.DefaultClause(str(sql.text(default)))) table.append_column(schema.Column(name, coltype, nullable=nullable, autoincrement=False, *colargs)) if not found_table: Index: lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py =================================================================== --- lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py (revision 572) +++ lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/base.py (working copy) @@ -945,7 +945,7 @@ self.engine.logger.info(statement) self.engine.logger.info(repr(parameters)) try: - self.dialect.do_execute(cursor, statement, parameters, context=context) + self.dialect.do_execute(cursor, str(statement), parameters, context=context) except Exception, e: self._handle_dbapi_exception(e, statement, parameters, cursor, context) raise -
repo owner
Replying to ram:
The reflection code that issues the query is:
{{{ query = "SELECT user_name() as user_name;" user_name = connection.scalar(sql.text(query)) }}}
Mike: Does the "sql.text()" call return a unicode string? Maybe we can just use str() on the result and send an ascii query instead?
in the MSSQL dialect it does, due to this code (assuming supports_unicode comes out as True there):
supports_unicode = sys.maxunicode == 65535 supports_unicode_statements = supports_unicodebut this would break all queries sent to FreeTDS, not just those originating from text(). text() constructs and everything else which is compiled come from the compiler and are sent to DefaultExecutionContext. If that flag is True, the string is converted to a unicode object unconditionally.
-
repo owner
also it should go without saying, but here I am saying it, that the str() call in the above patch is not an option for resolving this issue. the dialect needs to have a grip on whether or not its DBAPI accepts unicode strings or not.
-
Account Deleted
(original author: ram) Replying to zzzeek:
If that flag is True, the string is converted to a unicode object unconditionally.
So "supports_unicode" really means "force_unicode"? That sounds backwards to me: why not instead force unicode strings to str() if "supports_unicode == False" ? That seems to make more sense because if a Dialect supports unicode, it presumably supports ASCII as well, whereas the converse is probably not true.
the str() call in the above patch is not an option for resolving this issue. the dialect needs to have a grip on whether or not its DBAPI accepts unicode strings or not.
For ODBC, at least as it's currently factored in the .4 and .5 series, you can't. ODBC on Windows supports unicode, where as FreeTDS might have some issues with it. We currently don't have a FreeTDS ODBC Dialect separate from a MS ODBC Dialect, and therefore can't know whether our single ODBC dialect supports unicode or not.
I'm not sure it makes a lot of sense to have these two Dialects separate, as it could cause undue administrative headaches for those switching between Windows and Unix connections on a Dialect that should be 99.9% identical.
Switching the behavior of the "supports_unicode" flag as I suggest above would at least allow a workaround for this for now.
-
repo owner
Replying to ram:
Replying to zzzeek:
If that flag is True, the string is converted to a unicode object unconditionally.
So "supports_unicode" really means "force_unicode"? That sounds backwards to me: why not instead force unicode strings to str() if "supports_unicode == False" ? That seems to make more sense because if a Dialect supports unicode, it presumably supports ASCII as well, whereas the converse is probably not true.
we do force them to str() if supports_unicode=False. if supports_unicode=True, we force to unicode since that may be all the dialect supports in the case that the string contains multibyte characters. ASCII can never be assumed since encoding=utf-8 by default. for example, if you send a multibyte column name to sqlite using a raw utf-8 encoded string, you get an error, but as a python unicode, no problem. if the dialect supports unicode, sending a python unicode is the best approach to dealing with multibyte SQL data. we should not in any case be talking to a DBAPI without a definitive value for this flag.
the str() call in the above patch is not an option for resolving this issue. the dialect needs to have a grip on whether or not its DBAPI accepts unicode strings or not.
For ODBC, at least as it's currently factored in the .4 and .5 series, you can't. ODBC on Windows supports unicode, where as FreeTDS might have some issues with it. We currently don't have a FreeTDS ODBC Dialect separate from a MS ODBC Dialect, and therefore can't know whether our single ODBC dialect supports unicode or not.
then it becomes a flag sent to create_engine() for the MSSQL dialect. FreeTDS is a nightmare in all respsects AFAICT so if people are using it, they're going to have to go to the wiki, take down all the magic flags needed to talk to it for their particular situation, and apply.
I'm not sure it makes a lot of sense to have these two Dialects separate, as it could cause undue administrative headaches for those switching between Windows and Unix connections on a Dialect that should be 99.9% identical.
i didnt want to make a separate dialect. PyODBC is PyODBC. it just needs flags to customize its behavior (like ODBC itself does).
-
Account Deleted
- attached add_flag.patch
(original author: ram) Add supports_unicode_statements flag to pyodbc Dialect ctor
-
Account Deleted
(original author: ram) Okay, I've attached a simple patch that will allow users with balky FreeTDS setups to force off unicode statements. Please apply the patch, use it to turn off unicode statements, and see if that fixes the issue.
-
0.6 works fine on FreeTDS with the standard unicode conversion to bytecode code. I am using it on a production system without a problem. If you still have issues provide a test case and we can reinvestigate.
-
repo owner
- removed milestone
Removing milestone: 0.6.0 (automated comment)
- Log in to comment
Email for me is mdoar@pobox.com