Updating records from XML
I need to provide 4 MySQL stored procedures for each table in a database. They are designed to get, update, insert and delete.
Get, Delete, and Insert are simple. The problem is with the "update" because I don't know which options will be set and which will not. Some parameters can be set to NULL, while others simply won't change, so they won't be provided.
Since I am already working with XML, after several googling I found that it is possible to use the UpdateXML function, but the examples are too complex and some articles are from 2007. Therefore, I do not know if there is a better technique or something even easier at this moment.
Any comments, documentation, link, article, or anything you've used and you're happy with will be appreciated: D
Greetings.
a source to share
Usually, when you have data from a row in your database in the frontend, you should have all the values that you can use to update that row in the database. You have to pass all of these values to your update, regardless of whether or not they actually changed. Otherwise, your database doesn't know if it gets a NULL value for the column, because it should be, or because you just didn't pass in a valid value.
If you end up with areas of your application where you don't need specific columns from the table, then you can set up additional stored procedures that don't use those columns. It is often easier to just get all of the columns from the database when you populate your outer object. The overhead of additional columns is usually minimal and is worth the stored maintenance of multiple update stored procedures.
Here's an example. This is MS SQL Server syntax, so you might need to change it a bit, but hopefully this illustrates the idea:
CREATE PROCEDURE Update_My_Table
@my_table_id INT,
@name VARCHAR(40),
@description VARCHAR(500),
@some_other_col INT
AS
BEGIN
UPDATE
My_Table
SET
name = @name,
description = @description,
some_other_col = @some_other_col
WHERE
my_table_id = @my_table_id
END
CREATE PROCEDURE Update_My_Table_Limited
@my_table_id INT,
@name VARCHAR(40),
@description VARCHAR(500)
AS
BEGIN
UPDATE
My_Table
SET
name = @name,
description = @description
WHERE
my_table_id = @my_table_id
END
As you can see, just remove those columns that you are not updating from the UPDATE statement. Just don't go overboard and try to store a stored procedure for every possible combination of columns that you can update. It is much easier to just get additional columns from the DB when you select from the table in the first place. You end up passing the same value back, and your server will complete the column update with the same exact value, but this is not very important. You can program your interface to make sure that at least one column has been changed before it actually tries to update anything in the database.
a source to share