using lastrowid with callproc

[mperpick]

lastrowid works fine to return a row inserted with cursor.execute('insert into sometable ....')
but I am struggling to return the id of a row inserted with callproc ...

for example ...

cur.callproc('insert_experiment', [name, desc])
id = cur.lastrowid

id is always zero.

can anyone help? here is my mysql procedure. thanks so much.

DELIMITER //

CREATE PROCEDURE insert_experiment
(
_name varchar(50),
_description varchar(500)
)
BEGIN
INSERT INTO experiment
(name, description, create_date)
VALUES
(_name, _description, now());
SELECT LAST_INSERT_ID();
END;
//
DELIMETER;

[Andy Dustman]

DO you actually have an auto_increment column in there somewhere? I bet you don't. But I do:

CREATE TABLE experiment (
name varchar(50) default NULL,
description varchar(500) default NULL,
create_date date default NULL,
id int(11) NOT NULL auto_increment,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> call insert_experiment('foo','bar');
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.06 sec)

However, you will also need to change your code to:

cur.callproc('insert_experiment', [name, desc])
rowid = cur.fetchone()[0]

because the row id is returned as a result set, and lastrowid is not going to be set. If it were, you wouldn't need select last_insert_id() in your procedure.

[mperpick]

thanks for the quick reply.

as for the bet ... i had auto incremenet in there. it worked. i made sure of that with cur.execute('insert into ... ') and then checking lastrowid.

now ... when I do this ...

cur.callproc('insert_experiment', [name, desc])
rowid = cur.fetchone()[0]

i get this error here: (ProgrammingError: execute() first)

[Andy Dustman]

That's a known bug that's fixed in the SVN tree. As a quickie workaround, do this:

cur.callproc('insert_experiment', [name, desc])
cur._executed = True
rowid = cur.fetchone()[0]

or patch cursors.py so that callproc() sets self._executed to the query.

Also, using execute() prior to callproc() will work around it as well.

[mperpick]

ah ... i was banging my head for an hour. thanks so much.

just out of curiosity .. when is the next release coming out?

[Andy Dustman]

Real Soon Now.

I did a test today with Python-2.5 (but only a 32-bit platform) and it seems fine. Some minor patches are needed for 64-bit platforms, which is what my main development system is.