MySQLdb and MySQL stored functions

[kurt.forrester.fec]

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

 

[kurt.forrester.fec]

        Show the code!

        At a rough guess, given the lack of details...

        You forgot to .fetch() the result and are looking at the status code
from the .execute()
--
        Wulfraed        Dennis Lee Bieber               KD6MOG
        (e-mail address removed)              (e-mail address removed)
                HTTP://wlfraed.home.netcom.com/
        (Bestiaria Support Staff:               (e-mail address removed))
                HTTP://www.bestiaria.com/

Correct diagnosis.

Thanks.

Any ideas on how to suppress the warning output:
__main__:1: Warning: No data - zero rows fetched, selected, or
processed

 

[Steve Holden]

Correct diagnosis.

Thanks.

Any ideas on how to suppress the warning output:
__main__:1: Warning: No data - zero rows fetched, selected, or
processed
--

try:
...
except MySQLdb.Warning:
pass

There is probably some setting you can establish with MySQL so it
doesn't raise these warnings: I have never seen one, and zero rows is a
perfectly valid retrieval result.

regards
Steve