MySQLdb and MySQL stored functions

[kurt.forrester.fec at googlemail.com]

Hello All,


I am running
- Ubuntu 8.10
- Python 2.5.2
- MySQLdb (1, 2, 2, 'final', 0)
- MySQL Server/Client 5.0.67

I am trying to write an authentication script for a python application
that connects to a MySQL database. The database has a table named
`user` which has the fields `id`, `alias` and `password` as well as a
stored function `authenticate` as detailed below:

CREATE DEFINER=`root`@`localhost` FUNCTION `authenticate`(a TEXT, p
TEXT) RETURNS int(11)
BEGIN
DECLARE STATUS INT DEFAULT -1;
SELECT id INTO STATUS FROM user WHERE alias = a AND password = p;
RETURN STATUS;
END

table: `user`
`id` = 1
`alias` = 'captain'
`password' = 'a'

I have been executing the following query from various connections:
`SELECT authenticate('captain', 'a')` (this is what is in the table
and should return 1)
and
`SELECT authenticate('captain', 'aa')` (this is a incorrect version of
the password and should return -1)

I have tried running this query from the MySQL Query Browser and it
returns results as expected.
I have also tried query from python using the _mysql module and this
also returns results as expected.
However, when I try to use the MySQLdb module it returns an incorrect
value (it returns 1).

I wish to use the DB API 2.0 compliant module for flexibility.
Therefore I am trying to work out why the MySQLdb does not return the
value as expected (that is as it is returned by the Query Browser).

Any help would be greatly appreciated.

Kurt