Updating array data in stored procedure

Question

So here is my problem. I know there is a fair bit of literature on this site about this type of issue but I am confused about how several of these issues intertwine for my problem. First I have an array of row data that needs to be updated or inserted based on a remote id value within that array, in this case value_c. This array corresponds to a row instance from table foo. Basically, if a record with a matching value_c exists in the database then update that record otherwise insert the new record payload. The data structure of the array corresponds to the row schema of table foo in our db. This is the schema (obfuscated for safety):

CREATE TABLE IF NOT EXISTS `foos` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `value_a` varchar(13) DEFAULT NULL,
     `value_b` int(11) DEFAULT NULL,
     `value_c` int(11) DEFAULT NULL,
     .
     .
     .
     .
     `value_x` enum('enum_a','enum_b','enum_c','enum_d') DEFAULT NULL,
     `value_y` text,
     `value_z` enum('daily','monthly','weekly') DEFAULT NULL,
     `value_aa` tinyint(4) NOT NULL,
     `value_bb` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=829762 ;

There is a lot of data, and the plan in as followed. String-ify this data send it to a stored procedure that would then update or insert as needed. Something like the following (note that this will be happening in a model within codeigniter):

public function update_or_insert($event_array_payload)
{
    // string-ify the data
    $mod_payload = implode('<delimiter>', $event_array_payload)

    //deal with NULLs in array
    $mod_payload = $this->deal_with_nulls($mod_payload);

    $this->stored_procedure_lib->update_or_insert_payload($mod_payload);
}

// then elsewhere in the stored procedure library
public function update_or_insert_payload($foo)
{
    $this->_CI->db->query('CALL update_or_insert_foo(\'$foo\')');
}

My issue is as followed. A single string value is passed into the stored procedure. Then it needs to be parsed apart and places into either a single update or a single insert statement. I could create a variable for each column of the foo table and a loop to populate each variable and update/insert that way, but the foo table is very likely to be extended, and I do not want to create bugs further down the line. Is there a way to dynamically place the parsed apart contents of a string representation of an array into a single update or insert statement. I'm not sure if that is even possible, but I feel like a work around I do not know about might exist. Thank you for the help.

Do You mean that You would like to have a procedure update_or_insert(listOfValuesAsString) instead of using INSERT/UPDATE because in the future You may have more columns? — Gustek
– Gustek, Commented Dec 11, 2012 at 3:27
@Gustek Well its more that I want to be able to inert or update in the same operation block. This is because it will save additional calls to the database server, which will (in the long term) save a lot of processing power and money. — usumoio
– usumoio, Commented Dec 12, 2012 at 17:27
So maybe You simply need insert on duplicate or if not provide a pseudo code of procedure You trying to make. — Gustek
– Gustek, Commented Dec 12, 2012 at 19:27

alditis · Accepted Answer · 2012-12-11 03:17:26Z

2

It is not a definitive answer but it would be an option to try.

If you want to avoid sending many parameters the procedure, you can create a table called foos_tmp with the same structure foos but with one field aditional id_foos_tmp (pk and autoincrement) and enter the array in table foos_tmp. Then the procedure you send only the id_foos_tmp of the table foos_tmp generated and internally the procedure to do a SELECT foos_tmp table and get the data that you had before in the array.

I hope it helps somewhat.

Greetings.

answered Dec 11, 2012 at 3:17

alditis

4,8473 gold badges53 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating array data in stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related