1

I'm calling a stored procedure that inserts a new row, but if the unique name exists, just updates a numerator and SHOULD return the updated id.

CREATE PROCEDURE bear_crm.insert_subject(in_subject_name varchar(100), OUT out_subject_id int)
BEGIN
    DECLARE  t_update_id int;
    set t_update_id := 0;
    INSERT INTO t_subject (subject_name) VALUES (in_subject_name) ON DUPLICATE KEY
    UPDATE pop_index = COALESCE(pop_index,0)+1, subject_id=LAST_INSERT_ID(subject_id);
    set out_subject_id :=  LAST_INSERT_ID();
    
    insert into testing(aa) values(out_subject_id); # just a test to see values
END//

The numerator is updated after the second attempt to insert the same value. the "testing" table get the expected updated subject id, yet my out parameter in the Python side is not effected:

v_id = -999
v_args = ['test subject', v_id]
mycursor.callproc('insert_subject', v_args)
print('id is:', v_args[1])

v_id remains -999

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
Eba
  • 29
  • 4
  • Similar https://stackoverflow.com/q/15320265/5320906 – snakecharmerb Aug 16 '20 at 07:10
  • thanks, don't get it why v_id = mycursor.callproc('insert_subject', v_args) works while the other ways did not. – Eba Aug 16 '20 at 07:42
  • There's no (reasonable) way to update `v_id` from within `callproc`. The out parameter in `v_args` _could_ be replaced with the new value, but [PEP-249](https://www.python.org/dev/peps/pep-0249/#callproc) says "The result of the call is returned as modified copy of the input sequence", so modifying the input sequence itself would be outside the spec. So the only option is to put the new value in the return value of `callproc`, I would guess. – snakecharmerb Aug 16 '20 at 08:33
  • I've closed this question as being the same as the one I linked to - if you think that isn't so @-me here and I'll reopen (you would need to [edit] your question to explain what additional information you require). – snakecharmerb Aug 16 '20 at 08:34

0 Answers0