johnf wrote:
> 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
>
>
Hey! Where did you get that from? That's neither official 8 version, nor
the recommended modified pymssql (as per Ed's site).
Anyway, in your version I'd change :
x = "'" + string.replace(str(x), "'", "''") + "'"
to :
x = "'" + x.replace("'", "''") + "'"
so you use proper x's replace method (if x is unicode unicode.replace should be
used).
I would update Dabo's modified pymssql with all these changes first.
Next week I'll check version 1.0.1 and see if I can post something similar.
Ricardo.
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
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]
