Okay, I had some time to fiddle with this today. I put up an instance of MSDE
server, and arrived at some code that would work on both SS2000 and SS2005,
when running from a Win client. I couldn't find a situation where pymssql.py
would accept the Port number as a part of the Host parameter. (Going back
and re-reading the docs, I now think the instructions regarding Port numbers
only apply to clients using FreeTDS, and not to Win clients.) So, following
John's hint, I split out how a Win client is handled. Here is where I ended
up, making changes ONLY in dbMsSQL.py:
############## dbMsSQL.py ###################
def getConnection(self, connectInfo, **kwargs):
"""The pymssql module requires the connection be created for the FreeTDS
libraries first. Therefore, the
DSN is really the name of the connection for FreeTDS
__init__(self, dsn, user, passwd, database = None, strip = 0)"""
import pymssql
###################
## gek: 04/07/2008 begin changes
import sys
if sys.platform == 'win32':
## gek: 04/12/2008
## if target server is default (un-named) instance of MS-Sql
## then host = <ComputerName>
## If instance is a 'named instance', then
## host = '<ComputerName>\<InstanceName>'
host = connectInfo.Host
else:
port = str(connectInfo.Port)
if not port or port == "None":
port = 1433
host = "%s:%s" % (connectInfo.Host, port)
## gek: end changes
###################
user = connectInfo.User
password = connectInfo.revealPW()
database = connectInfo.Database
# hack to make this work. I am sure there is a better way.
self.database = database
self._connection = pymssql.connect(host=host, user=user,
password=password,
database=database, **kwargs)
return self._connection
############ dbMsSQL.py ################
def getTables(self, cursor, includeSystemTables=False):
dbName = self.database
###################
## gek: 04/07/2008
## this will only work with MS-Sql 2005 !!!!!
## cursor.execute("select NAME"
## " from %s.SYS.TABLES"
## " order by NAME" % (dbName))
## rs = cursor.getDataSet()
## tables = [x["NAME"] for x in rs]
## while this works with MS-Sql 2005 and 2000....
cursor.execute("select TABLE_NAME"
" from %s.INFORMATION_SCHEMA.TABLES"
" where TABLE_CATALOG = '%s' "
" and TABLE_TYPE = 'BASE TABLE'"
" order by TABLE_NAME" % (dbName,dbName))
rs = cursor.getDataSet()
tables = [x["TABLE_NAME"] for x in rs]
## gek: end changes
###################
tables = tuple(tables)
return tables
########### dbMsSQL.py ##############
def getFields(self, tableName, cursor):
""" Returns the list of fields of the passed table
field: ( fieldname, dabo data type, key )
"""
# fairly standard way of getting column settings
# this may be standard enough to put in the super class
dbName = self.database
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))
fieldDefs = cursor.getDataSet()
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) )
pkFields = cursor.getDataSet()
fields = []
for r in fieldDefs:
name = r["COLUMN_NAME"]
ft = self._fieldTypeNativeToDabo(r["DATA_TYPE"])
pk = (name,) in [(p["COLUMN_NAME"], ) for p in pkFields]
fields.append((name, ft, pk))
return tuple(fields)
########## end code segments ###########
For a discussion of the recommended methods to get table names, fields, and
PKs from MS-SS 2005, see
http://technet.microsoft.com/en-us/library/ms345522.aspx
I could not find anything on TechNet regarding MS-SS 2000 compatibility in
the upcoming MS-SS 2008.
Gary
johnf wrote:
>
> Oh wow I'm not sure we should take that on - supporting pymssql that is.
> I
> wonder if we can just setup different connections that depend on the
> platform
> or some other variable.
>
> if sys.platform == 'win32':
> setupWinConnection()
> elif sys.platform == 'linux':
> setupLinuxConnection
> elif sys.platform == 'mac':
> setupMacConnection
>
>
> if MsSqlVerion == 2000:
> ...
>
> I think Larry may have 2005 running and I think he'll allow me to connect
> for
> testing. If he does not - does anyone else have an open 2005 available
> for
> testing????
>
>
> --
> John Fabiani
>
--
View this message in context:
http://www.nabble.com/AppWizard%2C-MS-SQL%2C-and-dbMsSQL.py-tp16539212p16693090.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]
