On Friday 27 February 2009 01:58:37 pm Ricardo Aráoz wrote:
> Paul McNett wrote:
> > Ed Leafe wrote:
> >> On Feb 26, 2009, at 8:20 PM, johnf wrote:
> >>> I noted the Ed got MsSQL working recently. Maybe he'll discover the
> >>> root
> >>> cause.
> >>
> >> No, that was PostgreSQL on OS X. I already had that working on
> >> Windows and Linux.
> >>
> >> I don't have a MS SQL Server to test with, so I won't be able to help
> >> much. Plus, with PyCon a month away, I'm going to need to spend a lot
> >> of my time polishing my talk for that.
> >
> > So Ed and I are both stretched pretty thin right now. We are aware of the
> > open issues and will get to them when we can. Thanks to everyone using
> > Dabo and especially to the seasoned Dabo users willing to spend time
> > helping to support the new users!
> >
> > Remember to review the open trac tickets and if your issue isn't there,
> > please make a new ticket for it. We get notified on the dev list with any
> > ticket changes, and putting it in trac ensures it doesn't get lost in the
> > shuffle.
> >
> > The trac url is: http://trac.dabodev.com
> >
> > Cheers!
> > Paul
>
> Hi Paul, so I got some spare time today at work and looked for the error
> message I was getting when using SqlServer. It would seem the problem
> lies in pymssql.py.
>
> The _quote function in pymssql.py goes :
> ---------------------------------------------------------------------------
>---- def _quote(x):
> if type(x) == types.StringType:
> x = "'" + string.replace(str(x), "'", "''") + "'"
> elif type(x) in (types.IntType, types.LongType, types.FloatType):
> pass
> elif x is None:
> x = 'NULL'
> # datetime quoting (thanks Jan Finell <[email protected]>)
> # described under "Writing International Transact-SQL Statements" in
> BOL # beware the order: isinstance(x,datetime.date)=True if x is
> # datetime.datetime ! Also round x.microsecond to milliseconds,
> # otherwise we get Msg 241, Level 16, State 1: Syntax error
> elif isinstance(x, datetime.datetime):
> x = "{ts '%04d-%02d-%02d %02d:%02d:%02d.%s'}" % \
> (x.year,x.month, x.day,
> x.hour, x.minute, x.second, x.microsecond / 1000)
> elif isinstance(x, datetime.date):
> x = "{d '%04d-%02d-%02d'}" % (x.year, x.month, x.day)
> # alternative quoting by Luciano Pacheco <[email protected]>
> #elif hasattr(x, 'timetuple'):
> # x = time.strftime('\'%Y%m%d %H:%M:%S\'', x.timetuple())
> else:
> #print "didn't like " + x + " " + str(type(x))
> raise InterfaceError, 'do not know how to handle type %s' % type(x)
>
> return x
> ---------------------------------------------------------------------------
>---------------------
>
> We can see that if "x" (which for dbmssql.py's getTables() would be
> "dbName", a unicode string) is unicode the code raises an
> InterfaceError. This is probably because my python and wx are both
> unicode. The other people that tested this to reproduce the error
> wouldn't seem to have unicode python. Fail to see why using my first
> suggestion (str(dbName)) would raise a KeyError, it didn't raise it in
> my tests and I didn't see where it could be raised in the code. But it
> was lucky because that forced me to trace the error to the final culprit.
> Mi suggested solution would be to patch pymssql.py in this way (just the
> first two lines have been commented and replaced) :
> ---------------------------------------------------------------------------
>--------------------- def _quote(x):
> ## if type(x) == types.StringType:
> ## x = "'" + string.replace(str(x), "'", "''") + "'"
> if type(x) in (types.StringType, types.UnicodeType):
> x = "'" + x.replace("'", "''") + "'"
> elif type(x) in (types.IntType, types.LongType, types.FloatType):
> pass
> elif x is None:
> x = 'NULL'
> # datetime quoting (thanks Jan Finell <[email protected]>)
> # described under "Writing International Transact-SQL Statements" in
> BOL # beware the order: isinstance(x,datetime.date)=True if x is
> # datetime.datetime ! Also round x.microsecond to milliseconds,
> # otherwise we get Msg 241, Level 16, State 1: Syntax error
> elif isinstance(x, datetime.datetime):
> x = "{ts '%04d-%02d-%02d %02d:%02d:%02d.%s'}" % \
> (x.year,x.month, x.day,
> x.hour, x.minute, x.second, x.microsecond / 1000)
> elif isinstance(x, datetime.date):
> x = "{d '%04d-%02d-%02d'}" % (x.year, x.month, x.day)
> # alternative quoting by Luciano Pacheco <[email protected]>
> #elif hasattr(x, 'timetuple'):
> # x = time.strftime('\'%Y%m%d %H:%M:%S\'', x.timetuple())
> else:
> #print "didn't like " + x + " " + str(type(x))
> raise InterfaceError, 'do not know how to handle type %s' % type(x)
>
> return x
> ---------------------------------------------------------------------------
>---------------------
>
> As you can see by using "x.replace()" instead of "string.replace()" if
> "x" is unicode it will stay unicode and if it is string it will stay
> string, so that would seem to address any objection from people using
> strings nowadays. Have tested this in my system and works ok.
> As we are distributing dabo's own version of pymssql.py it could be
> patched there straight away.
>
> Would this note be enough, or should I do something different for this
> request?
> TIA
>
> Ricardo.
Ricardo, looks like you have discovered at least one of the problems. You are
not using the same pymssql that I'm using. Below is what I have.
def _quote(x):
#jfcs 01/12/07 added "or type(x) == types.UnicodeType" to the
# if statement to handle unicode.
#if type(x) == types.StringType or type(x) == types.UnicodeType:
if isinstance(x,basestring):
x = "'" + string.replace(str(x), "'", "''") + "'"
#elif type(x) in (types.IntType, types.LongType, types.FloatType):
elif isinstance(x, (int, long, float)):
pass
elif x is None:
x = 'NULL'
# datetime quoting (thanks Jan Finell <[email protected]>)
# described under "Writing International Transact-SQL Statements" in BOL
# beware the order: isinstance(x,datetime.date)=True if x is
# datetime.datetime ! Also round x.microsecond to milliseconds,
# otherwise we get Msg 241, Level 16, State 1: Syntax error
elif isinstance(x, datetime.datetime):
x = "{ts '%04d-%02d-%02d %02d:%02d:%02d.%s'}" % \
(x.year,x.month, x.day,
x.hour, x.minute, x.second, x.microsecond / 1000)
elif isinstance(x, datetime.date):
x = "{d '%04d-%02d-%02d'}" % (x.year, x.month, x.day)
# alternative quoting by Luciano Pacheco <[email protected]>
#elif hasattr(x, 'timetuple'):
# x = time.strftime('\'%Y%m%d %H:%M:%S\'', x.timetuple())
else:
#print "didn't like " + x + " " + str(type(x))
raise InterfaceError, 'do not know how to handle type %s' %
type(x)
return x
--
John Fabiani
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message:
http://leafe.com/archives/byMID/[email protected]
