In the following procedure I have simple prepared "update" statement. I have some questions about using this.
- Main question: How do I update only the changed columns in a stored procedure? It seems like setting update on all columns will overwrite data if another user changes a different column at the right time. In Zend Framework 2, I typically use TableGateway, which allows me to send in the form array and I think it will only update the columns that I have sent. How do I build a stored procedure to take a variable set to columns to update?
- With the above in mind, I know I can build a statement with concat and skip the "prepare .. using" bound parameters, but if I do that, then I can't protect against SQL Injection, unless I build my own filtering functions. I'd really prefer to keep the bound parameters. How do I keep those, while still only building a statement that updates only changed columns? Because I don't see how to change the USING portion to have dynamic input parameters.
- Also, just a side question. Why is it in Mysql I have to make all those duplicate input parameters? ("set @col1 = v_val1") Because I can't put v_val1 directly in the USING statement I have to make session variables. I think that really odd we can't put them in directly.
- Last little question. Should I unset each session variable at the end of the procedure? "set @col1 = null" Wouldn't it be safer to clear those out? I do not use pooled connections now, but I may use this procedure from more than just PHP web server, and other systems may have pooled connections.
I'm probably missing something obvious in this, and/or I'm just asking too much. I do know there will always be some level of timing with user edits. That's not critical, and if it occurs in my current situation, I'll have a log to show users what and who changes things. But I do wonder what I can do at the Mysql procedure level to work around it. Also it seems like Mysql is really lacking in procedure capabilities compared to PostgreSQL or MSSQL, but I am stuck with Mysql for now because of backend system requirements.
Sorry about all the questions. Having hard time finding answers. Thanks so much for you help.
create procedure sp_update_mytable ( in v_id int, in v_val1 varchar(10), in v_val2 varchar(10), in v_val2 varchar(10) ) begin set @id = v_id; set @col1 = v_val1; set @col2 = v_val2; set @col3 = v_val3; set @sql = 'UPDATE mytable SET col1 = ?, col2 = ?, col3 = ? WHERE id = ?'; prepare stmt from @sql; execute stmt using @col1, @col2, @col3, @id; deallocate prepare stmt; end$$
Edit: A little background. It occurs to me I may be trying to solve this problem the wrong way or wrong place. Here's why I am asking. In ZF2 I have TableGateway,and I can send just an array to update and only the columns I want to update. Its super easy and convenient. Example:
$result = $this->db->update('mytable', array('col2' => 'my new value 2'), array('id' => 2) );
I'd like to make a stored procedure to do this update, but I can't send only the columns I want. In one system I may want to send col1 and col3, in another, col1 only. The columns to update are dynamic.
We are building systems that will integrate more than just a web server, which will include some ODBC, some Node.js maybe, possibly others. I wanted to be open to use other systems by building a Stored Procedure to handle certain table updates.
What's occurring to me now is that maybe I can't do this with Stored Procedures. Maybe I need "Middleware" to solve this. However, the question still stands, is this possible to do in Mysql. (if not, PostgreSQL?)