On can always do: db=DAL('mssql://...') db._adapter.types = copy.copy(db._adapter.types) db._adapter.types['boolean']='TINYINT(1)'
It should work. Can you please check it? On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote: > > However, web2py maintains the promise of backwards compatibility. One way > is to have a 'tinyint_boolean' datatype for those who want to use tinyints > as booleans. But that looks kind of messy and inelegant. > > An alternative is this: We could add a migration script to /scripts to > convert all boolean data types from CHAR(1) to TINYINT(1), and from 'T' to > 1 and 'F' to 0. Also, when a table model is called in define_table(), it > would check whether its boolean data types are CHAR or INT, and save the > result somewhere (so it wouldn't have to keep checking.) If the server is > restarted, it would once again perform this check. So, a user would run the > migration script and simply restart the server. > > On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote: >> >> I have just come across this exact same issue. >> >> The web2py adapter converts boolean to char(1) but in MySQL the >> specification is that boolean is stored as tinyint with 0 and 1. So web2py >> adapter is incorrect. Not changing it perpetuates the mistake. >> >> >> On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote: >>> >>> I'm connecting to a legacy MySQL database (migrate=False) with a lot >>> of fields declared BOOLEAN, and noticed that attempts to modify these >>> fields with the DAL failed. The DAL issues a query like this: >>> >>> UPDATE sometable SET someflag='T' WHERE ... >>> >>> but this gets rejected by MySQL. >>> >>> Reading through dal.py, I see that the "boolean" type maps to CHAR(1) >>> in MySQLAdapter, and represent() converts to "T" and "F" values. >>> However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, with >>> values 0 or 1, according to: >>> >>> http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html >>> >>> I can trivially change this behavior in dal.py for my purposes, but it >>> would be interested to try to incorporate this into the main web2py >>> distribution. Unfortunately, the trivial change will break backwards >>> compatibility for people who are already depending on the current >>> behavior. Any thoughts on how this could be done in a backwards- >>> compatible way, or is it too much of an edge case to worry about? >>> >>> Cheers, >>> Kevin >> >> --