CODEWITHSUNDEEP
mysqldjangostored-procedures
RuSs
RuSs

Reputation: 789

django mysql: stored procedure won't execute

I'm trying to execute the following very simple stored procedure from django. I'm actually gonna be running a more complex one, but I'm using this one as a test to get it running. The SP works if I execute it from mysql directly, but when I try to execute it from django, it does nothing. No errors, nothing. At first I thought maybe it's permissions, but site can read and write to the database fine. I tried executing it as raw sql in django, also, nothing happens. I've tried to introduce a syntax error into the SP to see if it even connects. It detects the syntax error so django connects to the SP. But it simply won't execute. Here is the django code:

def priceupdate(request, store_id):

    cur = connection.cursor()
    try:
        cur.callproc('liquorDB.Test_scheduler')

    finally:
        cur.close()

    return HttpResponseRedirect(request.META.get('HTTP_REFERER'))

And here is the stored procedure:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Test_scheduler`()
BEGIN
update liquorDB.store_store set Address = '45 Test' where StoreID = 1;

END

Upvotes: 0

Views: 1576

Answers (1)

RuSs
RuSs

Reputation: 789

For those of you also looking for this answer, I've figured it out. The stored procedure code you'll find in Google is based around retrieving data not modifying it. My stored procedure is designed to modify. I found my answer looking at the django documentation for raw sql queries. https://docs.djangoproject.com/en/1.5/topics/db/sql/ There it stated that if you want your sql to modify something on the database you must add a transaction.commit, like so: 

cur.callproc('liquorDB.Test_scheduler')
transaction.commit_unless_managed()

Upvotes: 1

Related Questions