On 12/05/2012 05:28 PM, John Fabiani wrote:
I realize that this bit of info is late and I'm sorry - have you
considered using pyODBC? I have used it in the past and others have
posted special setups to allow the use of ODBC. It requires hand
coding a connection file and creating a new dbODBC.py in dabo/db. I
have posted my solution and I believe two others posted their ODBC
solutions on this list. Unfortunately I can't find my solution or I
would post it. But I know it has been posted and I believe you should
be able to find it in the archives.
I used pyodbc to import 54 gigs of data into Postgres without any issues.
Johnf
On 12/05/2012 05:01 PM, Paul McNett wrote:
Unfortunately Dabo ate the original exception so it's impossible to
know what the
callstack is making it hard to pinpoint. Do this:
1) edit dabo/dabo/db/dCursorMixin.py
2) search for "error fetching records", there should be 1 occurence
at about line 414.
3) go up to about line 407 (the 'except Exception, e:' line)
4) right after that line, indent 3 tabs and type "raise" without the
quotes
5) save
6) now run your script and you should see a full stacktrace now. Post
that.
Thanks
Paul
On 12/5/12 4:17 PM, Carey Gagnon wrote:
Bump
Anyone? This doesn't seem to be a pymssql problem as it works fine
on it's
own. Once Dabo is involved I get the error:
Error fetching records: (<type 'exceptions.UnboundLocalError'>, local
variable 'row' referenced before assignment)
There are several references to the variable row (row = blahblah..) in
dCursorMixin.py, 14 to be exact, but I don't know enough about
python to do
much about it.
Can anyone help please?
Thanks
Carey
On Mon, Dec 3, 2012 at 7:20 PM, Carey Gagnon
<[email protected]>wrote:
That was pretty much the full traceback Ed, but here's the entire
enchilada:
C:\src\ide\wizards\AppWizard>AppWizard.py
C:\src\dabo\lib\SimpleCrypt.py:52: UserWarning: WARNING:
SimpleCrypt is
not secu
re. Please see http://wiki.dabodev.com/SimpleCrypt for more
information
warnings.warn("WARNING: SimpleCrypt is not secure. Please see
http://wiki.dabo
dev.com/SimpleCrypt for more information")
2012-12-03 19:14:37 - ERROR - Error fetching records: (<type
'exceptions.UnboundLocalError'>, local variable 'row' referenced
before
assignment)
Thanks
Carey
On Mon, Dec 3, 2012 at 7:04 PM, Ed Leafe <[email protected]>
wrote:
On Dec 3, 2012, at 6:35 PM, Carey Gagnon <[email protected]>
wrote:
Then I run the appwizard (all installed Dabo components at revision
7288).
It connects to the database but doesn't show tables or anything
throwing
the following error:
Error fetching records: (<type 'exceptions.UnboundLocalError'>,
local
variable 'row' referenced before assignment)
Is this a Dabo error or a pymssql error?
Could you post the full traceback? That will show where
the error
originated.
-- Ed Leafe
Ok I found an early version of the required file. Of course had to hard
code the connection DSN but beyond that I believe it's straight
forward. I'm going out of town so I won't be around to help in a timely
manner. Beyond the files below I haven't used it for about 6 months and
do not recall all the in's and outs. But I do recall that it worked
very well and access was fast.
This is how to call a connection file:
def createBizobjs(self):
self.Application.addConnectFile("dean_mssql.cnxml")
self.ms_conn = self.Application.getConnectionByName("conn_odbc")
self.Application.addConnectFile("pes.cnxml")
self.pg_conn = self.Application.getConnectionByName("apiconn")
Here is a sample connection file:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<connectiondefs xmlns="http://www.dabodev.com";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.dabodev.com conn.xsd"
xsi:noNamespaceSchemaLocation = "http://dabodev.com/schema/conn.xsd";>
<connection dbtype="ODBC">
<name>conn_odbc</name>
<host>192.168.1.113</host>
<database>dean</database>
<user>sa</user>
<password>G44WBBX5FI92E3DV10</password>
<port>1433</port>
</connection>
</connectiondefs>
below is "dbOdbc.py"
# -*- coding: utf-8 -*-
import datetime
from dabo.dLocalize import _
from dBackend import dBackend
from dabo.lib.utils import ustr
class ODBC(dBackend):
"""Class providing Microsoft SQL Server connectivity. Uses pyodbc."""
def __init__(self):
dBackend.__init__(self)
self.dbModuleName = "pyodbc"
self.useTransactions = True # this does not appear to be required
import pyodbc
def getConnection(self, connectInfo, forceCreate=False, **kwargs):
"""
The pyodbc 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 pyodbc
port = ustr(connectInfo.Port)
if not port or port == "None":
port = 1433
host = "%s:%s" % (connectInfo.Host, port)
user = connectInfo.User
password = connectInfo.revealPW()
database = connectInfo.Database
connect_str = '''DRIVER={SQL Server}; SERVER=192.168.1.113;
DATABASE=dean; UID=sa; PWD=master'''
# hack to make this work. I am sure there is a better way.
self.database = database
self._connection = pyodbc.connect(connect_str, **kwargs)
class Cursor:
#Class property /not/ per instance.
connection = self._connection
def __init__(self,*arg,**kwargs):
self._realcursor = self.__class__.connection.cursor()
self.setLimitClause(None)
#Handle exceute different -as must be a member of the class
# as well as the instance.
def execute(self,sql,*params):
return self._realcursor.execute(sql,params)
def fetchone(self):
return list(self._realcursor.fetchone())
def fetchall(self):
return map(list,self._realcursor.fetchall())
def fetachmany(self):
return map(list,self._realcursor.fetchmany())
#Forward items and attribute to our real obj.
def __getattr__(self,attrnm):
#Shouldn't happen but prevent infinte recursion if it does.
if attrnm == "_realcursor":
return None
return getattr(self._realcursor,attrnm)
def __setattr__(self,attrnm,v):
if attrnm == "_realcursor":
return
setattr(self._realcursor,attrnm,v)
def __delattr__(self,attrnm):
return delattr(self._realcursor,attrnm)
self._cursorclass = Cursor
return self._connection
def getDictCursorClass(self):
"""Currently this is not working completely"""
return self._cursorclass
#if pyodbc.__version__ >= "2.0.0":
#class ConCursor(pyodbc.Cursor):
#def __init__(self, *args, **kwargs):
## pyodbc requires an additional param to its
__init__() method
#kwargs["as_dict"] = True
#super(ConCursor, self).__init__(*args, **kwargs)
#else:
#class ConCursor(pyodbc.pyodbcCursor):
#def __init__(self, *args, **kwargs):
## pyodbc requires an additional param to its
__init__() method
#kwargs["as_dict"] = True
#super(ConCursor, self).__init__(*args, **kwargs)
#if not hasattr(pyodbc.pyodbcCursor, "connection"):
#def _getconn(self):
#return self._source
## pyodbc doesn't supply this optional dbapi attribute,
so create it here.
#connection = property(_getconn, None, None)
#return ConCursor
def escQuote(self, val):
# escape backslashes and single quotes, and
# wrap the result in single quotes
sl = "\\"
qt = "\'"
return qt + val.replace(sl, sl+sl).replace(qt, sl+qt) + qt
def formatDateTime(self, val):
"""We need to wrap the value in quotes."""
sqt = "'" # single quote
val = ustr(val)
return "%s%s%s" % (sqt, val, sqt)
def getTables(self, cursor, includeSystemTables=False):
dbName = self.database
sql = """
select table_name
from INFORMATION_SCHEMA.TABLES
where table_catalog = '%(db)s'
and table_type = 'BASE TABLE'
order by table_name """
cursor.execute(sql % {'db':dbName})
rs = cursor.getDataSet()
tables = [x["table_name"] for x in rs]
tables = tuple(tables)
return tables
def getTableRecordCount(self, tableName, cursor):
cursor.execute("select count(*) as ncount from '%(tablename)'"
% tableName)
return cursor.getDataSet()[0]["ncount"]
def _fieldTypeNativeToDabo(self, nativeType):
"""
converts the results of
select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
to a dabo datatype.
"""
# todo: break out the dict into a constant defined somewhere
# todo: make a formal definition of the dabo datatypes.
# (at least document them)
try:
ret = {
"BINARY": "I",
"BIT": "I",
"BIGINT": "G",
"BLOB": "M",
"CHAR": "C",
"DATE": "D",
"DATETIME": "T",
"DECIMAL": "N",
"DOUBLE": "G", ## G maps to Long (INT), but this could
be wrong if it is supposed to be a double float.
"ENUM": "C",
"FLOAT": "F",
"GEOMETRY": "?",
"INT": "I",
"IMAGE": "?",
"INTERVAL": "?",
"LONG": "G",
"LONGBLOB": "M",
"LONGTEXT": "M",
"MEDIUMBLOB": "M",
"MEDIUMINT": "I",
"MEDIUMTEXT": "M",
"MONEY": "F",
"NEWDATE": "?",
"NCHAR": "C",
"NTEXT": "M",
"NUMERIC": "N",
"NVARCHAR": "C",
"NULL": "?",
"SET": "?",
"SHORT": "I",
"SMALLINT": "I",
"STRING": "C",
"TEXT": "M",
"TIME": "?",
"TIMESTAMP": "T",
"TINY": "I",
"TINYINT": "I",
"TINYBLOB": "M",
"TINYTEXT": "M",
"UNIQUEIDENTIFIER": "?",
"VARBINARY": "I",
"VARCHAR": "C",
"VAR_STRING": "C",
"YEAR": "?"}[nativeType.upper()]
except KeyError:
print 'KeyError:', nativeType
ret = '?'
return ret
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
sql = """
select COLUMN_NAME,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where table_catalog = '%(db)s'
and table_name = '%(table)s'
order by ORDINAL_POSITION """
cursor.execute(sql % {'table': tableName, 'db': dbName})
fieldDefs = cursor.getDataSet()
sql = """
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' """
cursor.execute(sql % {'table': tableName, 'db': dbName})
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)
def noResultsOnSave(self):
"""
Most backends will return a non-zero number if there are updates.
Some do not, so this will have to be customized in those cases.
"""
return
def noResultsOnDelete(self):
"""
Most backends will return a non-zero number if there are deletions.
Some do not, so this will have to be customized in those cases.
"""
#raise dException.dException(_("No records deleted"))
return
def flush(self, cursor):
self.commitTransaction(cursor)
def getLimitWord(self):
return "TOP"
def formSQL(self, fieldClause, fromClause, joinClause,
whereClause, groupByClause, orderByClause, limitClause):
"""MS SQL wants the limit clause before the field clause."""
clauses = (limitClause, fieldClause, fromClause, joinClause,
whereClause, groupByClause, orderByClause)
sql = "SELECT " + "\n".join( [clause for clause in clauses if
clause] )
return sql
def getLastInsertID(self, cursor):
"""
pyodbc does not populate the 'lastrowid' attribute of the
cursor, so we
need to get the newly-inserted PK ourselves.
"""
# Use the AuxCursor so as not to disturb the contents of the
primary data cursor.
try:
idVal = self.lastrowid
except AttributeError:
crs = cursor.AuxCursor
crs.execute("select @@IDENTITY as newid")
idVal = crs.getFieldVal("newid")
# Some interface versions return PK constraint values as
Decimal type
# what isn't well tolerated by Dabo.
if "Decimal" in str(type(idVal)):
idVal = int(idVal)
return idVal
def beginTransaction(self, cursor):
pass
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message: http://leafe.com/archives/byMID/[email protected]
