I'm new to Dabo and Python. My first project using this environment requires
using an existing MS-sql Server 2005 database. (I'm on WinXP client. Details
at end of post.) First step in my initiation: get ConnectionEditor and
AppWizard working against this server. Neither worked at the outset.
ConnectionEditor failed "Test..." after a long delay, without error trace.
AppWizard failed leaving the connection info panel with this traceback:
******************
File
"c:\python25\lib\site-packages\Dabo-0.8.3-py2.5.egg\dabo\db\dConnectInfo.py",
line 98, in get
Connection
return self._backendObject.getConnection(self, **kwargs)
File
"C:\Python25\lib\site-packages\dabo-0.8.3-py2.5.egg\dabo\db\dbMsSQL.py",
line 59, in getConne
ction
database=database, **kwargs)
File "C:\Python25\lib\site-packages\pymssql.py", line 350, in connect
con = _mssql.connect(dbhost, dbuser, dbpasswd)
_mssql.error: DB-Lib error message 10004, severity 9:
Unable to connect: SQL Server is unavailable or does not exist. Invalid
connection.
Net-Lib error during ConnectionOpen (ParseConnectParams()).
Error 14 - Not enough storage is available to complete this operation.
******************
Traced problems to dbMsSQL.py and made these code changes:
#### In getConnection()
Removed:
## port = str(connectInfo.Port)
## if not port or port == "None":
## port = 1433
## host = "%s:%s" % (connectInfo.Host, port)
Added:
## host = connectInfo.Host
Comments:
getConnection changes the host name that it gets from the connectInfo object
by appending the port, if one exists, or "1433" as a default, if none is
passed. As I understand it, specifying the port is not necessary, at least
in SS2005, unless you are running a non-default (named) instance, OR running
a default (un-named) instance on a non-standard port AND not running MS SQL
Server Browser. My instance is un-named, using the default (1433) port. (The
above code works whether the Browser is running or stopped.) The pymssql FAQ
mentions handling named instances, but dbMsSQL does not appear to allow for
this case.
Now, no errors in ConnectionEditor. AppWizard still raised an error, this
one in getTables():
*********************
0.8.3-py2.5.egg\dabo\db\dCursorMixin.
py", line 1716, in getTables
return self.BackendObject.getTables(self.AuxCursor, includeSystemTables)
File "c:\python25\lib\site-packages\dabo-
0.8.3-py2.5.egg\dabo\db\dbMsSQL.py",
line 79, in getTables
{'db':dbName} )
File "C:\Python25\lib\site-packages\dabo-
0.8.3-py2.5.egg\dabo\db\dCursorMixin.
py", line 314, in execute
raise dException.DBQueryException(e, sql)
dabo.dException.DBQueryException: pymssqlCnx instance has no attribute
'errmsg'
SQL: select table_name from INFORMATION_SCHEMA.TABLES where table_catalog =
%(db
)s and table_type = 'BASE TABLE' order by table_name
*********************
#### In getTables()
Removed:
## # jfcs 11/01/06 assumed public schema
## # cfk: this worries me: how does it know what db is being used?
##
## dbName = self.database
## cursor.execute("select table_name"
## " from INFORMATION_SCHEMA.TABLES"
## " where table_catalog = %(db)s"
## " and table_type = 'BASE TABLE'"
## " order by table_name",
## {'db':dbName} )
## rs = cursor.getDataSet()
## tables = [x["table_name"] for x in rs]
Added:
## dbName = self.database
## cursor.execute("select NAME"
## " from %s.SYS.TABLES"
## " order by NAME" % (dbName))
## rs = cursor.getDataSet()
## tables = [x["NAME"] for x in rs]
Comments:
There may have been two problems here: 1) the table was not properly
addressed (it needs the fully qualified name, which includes the database
prefix) and 2) CursorMixin.execute() seems to have a problem handling the
params passed to it.
1) Table list can be from either of the views SYS.TABLES or
INFORMATION_SCHEMA.TABLES. I chose the former only because it is the MS
recommendation in MS docs. Simplier query, too.
2) I could not see a reason why the query could not be expanded BEFORE we
sent it to the cursor object. So that's what I did. I applied a similar
solution in getFields().
#### In getFields()
Deleted:
## cursor.execute(
## "select COLUMN_NAME, DATA_TYPE"
## " from INFORMATION_SCHEMA.COLUMNS"
## " where table_catalog = %(db)s"
## " and table_name = %(table)s"
## " order by ORDINAL_POSITION",
## {'table':tableName, 'db':dbName} )
Added:
## cursor.execute("select COLUMN_NAME, DATA_TYPE"
## " from %s.INFORMATION_SCHEMA.COLUMNS"
## " where table_catalog = '%s'"
## " and table_name = '%s'"
## " order by ORDINAL_POSITION" %
(dbName,dbName,tableName))
And...
Deleted:
## cursor.execute(
## "select COLUMN_NAME "
## " from information_schema.Constraint_Column_Usage CCU"
## " join information_schema.TABLE_CONSTRAINTS TC"
## " on CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME"
## " where"
## " CONSTRAINT_TYPE = 'PRIMARY KEY'"
## " and TC.CONSTRAINT_CATALOG = %(db)s"
## " and TC.Table_Name = %(table)s",
## {'table':tableName, 'db':dbName} )
Added:
## cursor.execute(
## "select COLUMN_NAME "
## " from %s.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
CCU"
## " join %s.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC"
## " on CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME"
## " where"
## " CONSTRAINT_TYPE = 'PRIMARY KEY'"
## " and TC.CONSTRAINT_CATALOG = '%s'"
## " and TC.Table_Name = '%s'" %
## (dbName,dbName,dbName,tableName) )
Can someone test these same changes against MSSql Server 2000/2005? My
changes work on both the (2005) server installs I have access to, but I'm
not sure about 2000. In the mean time, if anyone encounters these problems,
I will be glad to offer what assistance I can.
I apologize for the length, but I wanted it to be fairly complete.
Gary
Here is my environment:
## Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
## Feb 9 2007 22:47:07
## Copyright (c) 1988-2005 Microsoft Corporation
## Express Edition with Advanced Services on Windows NT 5.1
## (Build 2600: Service Pack 2)
## Testing using Dabo install made 3/17/2008:
## Platform: Win
## Python Version: 2.5.2 on win32
## Dabo Version: Version 0.8.3; Revision ~3920
## UI Version: 2.8.7.1 on wxMSW
## Running against the AdventureWorks sample database.
Gary_Krell wrote:
>
> An earlier post on the dabo-users list described some problems with
> MS-SQL. Since then, I have made changes to dbMsSQL.py that work, and put
> together my notes on the subject. Since I am new to Dabo, and the
> description is lengthy, I thought I would ask before sending it to the
> list.
>
> Uhmmm... is that the thing to do?
>
> Gary
>
>
--
View this message in context:
http://www.nabble.com/AppWizard%2C-MS-SQL%2C-and-dbMsSQL.py-tp16539212p16578674.html
Sent from the dabo-dev mailing list archive at Nabble.com.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/[EMAIL PROTECTED]
