1.79.2 -> 1.91.6 migration error (SQL Server BIT field seems to break)

[Roman Bataev]

The following code works fine in 1.79.2 but gives an error in 1.91.6. Any ideas?

tntdb = SQLDB(tnt_connection_string) #this connects to MS SQL Server

tntdb.define_table('Employees',

    Field('employeeID', 'id'),

    Field('firstName', length=20),

    Field('networkUsername'),

    Field('isManager', 'boolean'), #isManager has type BIT in the database

    migrate=False)

def _find_user_by_name(username):

    return tntdb((tntdb.Employees.networkUsername == username) & (tntdb.Employees.isManager == True)).select()

Calling _find_user_by_name() works fine in 1.79.2 but gives the following error in 1.91.6:

Thread-11 2011-01-03 17:34:32,289 ERROR login() Traceback (most recent call last):

  File "c:\web2py\applications\scandabamc/controllers/default.py", line 129, in login

    user = _find_user_by_name(request.vars.username)

  File "c:\web2py\applications\scandabamc/controllers/default.py", line 164, in _find_user_by_name

    return tntdb((tntdb.Employees.networkUsername == username) & (tntdb.Employees.isManager == True)).select()

  File "c:\web2py\gluon\dal.py", line 4507, in select

    return self.db._adapter.select(self.query,fields,attributes)

  File "c:\web2py\gluon\dal.py", line 1003, in select

    rows = response(sql)

  File "c:\web2py\gluon\dal.py", line 994, in response

    self.execute(sql)

  File "c:\web2py\gluon\dal.py", line 1067, in execute

    return self.log_execute(*a, **b)

  File "c:\web2py\gluon\dal.py", line 1064, in log_execute

    return self.cursor.execute(*a,**b)

DataError: ('22018', "[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value 'T' to data type bit. (245) (SQLExecDirectW)")

[mdipierro]

Try replace DAL('mssql://...') with DAL('mssql2://...')

[Roman Bataev]

With mssql2:// it gives another error:

Thread-7 2011-01-04 13:34:58,770 ERROR login() Traceback (most recent call last):

  File "c:\web2py\applications\scandabamc/controllers/default.py", line 129, in login

    user = _find_user_by_name(request.vars.username)

  File "c:\web2py\applications\scandabamc/controllers/default.py", line 164, in _find_user_by_name

    return tntdb((tntdb.Employees.networkUsername == username) & (tntdb.Employees.isManager == True)).select()

  File "c:\web2py\gluon\dal.py", line 4507, in select

    return self.db._adapter.select(self.query,fields,attributes)

  File "c:\web2py\gluon\dal.py", line 1003, in select

    rows = response(sql)

  File "c:\web2py\gluon\dal.py", line 994, in response

    self.execute(sql)

  File "c:\web2py\gluon\dal.py", line 1851, in execute

    return self.log_execute(a,'utf8')

  File "c:\web2py\gluon\dal.py", line 1064, in log_execute

    return self.cursor.execute(*a,**b)

ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

[mdipierro]

I am going to need your help debugging this. Can you edit gluon/dal.py
and replace

value = 'N'+value

with

value = 'N'+value.encode('utf8')

Does the problem go away? If not, can you

print tntdb((tntdb.Employees.networkUsername == username) &
(tntdb.Employees.isManager == True))._select()

Massimo

[Roman Bataev]

The change you suggested did not make any difference - I get the same errors for both mssql:// and mssql2://. (and also, SQL Sever doesn't use utf8, it uses UCS-2).

Here is what print says:

1.79.2:

Both mssql2 and mssql:

SELECT Employees.employeeID, Employees.firstName, Employees.networkUsername, Employees.isManager FROM Employees WHERE (Employees.networkUsername='rbataev' AND Employees.isManager=1);

1.91.6:

mssql2:

SELECT  Employees.employeeID, Employees.firstName, Employees.networkUsername, Employees.isManager FROM Employees WHERE ((Employees.networkUsername = N'rbataev') AND (Employees.isManager = 'T'));

mssql:

SELECT  Employees.employeeID, Employees.firstName, Employees.networkUsername, Employees.isManager FROM Employees WHERE ((Employees.networkUsername = 'rbataev') AND (Employees.isManager = 'T'));

[mdipierro]

This helps. I can fix it by tomorrow. Sorry for the inconvenience.

Massimo

[mdipierro]

I believe this is now fixed in trunk. Please revert to mssql:// if
that is what you had before.
This was a new dal compatibility issues with BIT types.

Massimo

On Jan 5, 9:39 am, Roman Bataev <roman.bat...@gmail.com> wrote:

[Roman Bataev]

It works. Thank you very much!

-Roman

[Massimo Di Pierro]

This will go in stable probably tomorrow. Thanks for checking.

Massimo

[Nico de Groot]

I can confirm that the fix works for mssql. Discovered the problem
today, didn't read this discussion until now, so I thought I could
provide the fix for the line in dal.py:

# near line 1087 in 1.91.6
+ r = self.represent_exceptions(obj,fieldtype)
- r = BaseAdapter.represent_exceptions(self,obj,fieldtype)

I know now that it already in trunk, but the solution maybe helpful
until it is in stable. Teaches me to use trunk for testing purposes in
the future!

Nico de Groot

On 6 jan, 19:18, Massimo Di Pierro <massimo.dipie...@gmail.com> wrote:
> This will go in stable probably tomorrow. Thanks for checking.
>
> Massimo
>
> On Jan 6, 12:33 pm, Roman Bataev <roman.bat...@gmail.com> wrote:
>
>
>
>
>
>
>
> > It works. Thank you very much!
>
> > -Roman
>
> > On Wed, Jan 5, 2011 at 11:41 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:

> > > I believe this is now fixed in trunk. Please revert tomssql:// if

> > > that is what you had before.
> > > This was a new dal compatibility issues withBITtypes.
>
> > > Massimo
>
> > > On Jan 5, 9:39 am, Roman Bataev <roman.bat...@gmail.com> wrote:
> > > > The change you suggested did not make any difference - I get the same
> > > errors

> > > > for bothmssql:// and mssql2://. (and also, SQL Sever doesn't use utf8,