Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

FAQ
It is currently Mon Mar 08, 2021 9:31 am


Username:
Subject:
Message body:
Enter your message here, it may contain no more than 60000 characters. 

Smilies
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :arrow: :| :mrgreen: :geek: :ugeek:
Font size:
 
Font colour
Options:
BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON
Disable BBCode
Disable smilies
Do not automatically parse URLs
Confirmation code
Confirmation code:
In an effort to prevent automatic submissions, we require that you enter both of the words displayed into the text field underneath.
     

Topic review - Mysql> stored procedure for inserting in multiple tables \w common key
Author Message
Post subject: Re: Mysql> stored procedure > inserting in multiple files \w commond key  |  Post Posted: Tue Nov 22, 2011 5:17 pm
To load the above stored procedure in your mysql database, save it to a file, i.e.: insert_multiple_tables.sql and load it via input redirection:
Code:
# mysql -u user -ppassword db_name <insert_multiple_tables.sql
Post subject: Mysql> stored procedure for inserting in multiple tables \w common key  |  Post Posted: Tue Nov 22, 2011 5:15 pm
Below is an example of a mysql stored procedure that:
1) drops the procedure if it already exists
2) creates procedure and defines the fields that it accepts
3) declares an internal variable
4) inserts a row in multiple tables having a common key all tables using last_insert_id() from the first table as their first column's value
5) sets the internal variable to the last_insert_id()
6) selects the internal variable, returning it to the calling handler

Code:
DELIMITER //
DROP PROCEDURE IF EXISTS insert_multiple_tables
//
CREATE PROCEDURE insert_multiple_tables(product_hash char(32), user_id smallint, product_ip char(10), product_dir ENUM('p1','p2','p3','p4'), product_name varchar(100), product_type varchar(30), product_ext varchar(10), product_size bigint)
BEGIN
   DECLARE RETURN_VAL INT UNSIGNED DEFAULT 0;
START TRANSACTION;

   INSERT INTO f (product_id, product_hash, user_id)
     VALUES('', product_hash, user_id);

   INSERT INTO product_info (product_id, product_upload_date, product_ip )
     VALUES(LAST_INSERT_ID(), NOW(), product_ip);

   INSERT INTO product_location (product_id, product_dir, product_node)
     VALUES(LAST_INSERT_ID(), product_dir, '0');

   INSERT INTO product_properties(product_id, product_name, product_type, product_ext, product_size)
     VALUES (LAST_INSERT_ID(), product_name, product_type, product_ext, product_size);

   INSERT INTO product_status(product_id, product_private)
     VALUES (LAST_INSERT_ID(), '0');

   INSERT INTO product_download(product_id, product_d_date, product_d_ip)
     VALUES (LAST_INSERT_ID(), CURDATE(), '0');

   SET RETURN_VAL = LAST_INSERT_ID();

   SELECT RETURN_VAL;

COMMIT;
END//
DELIMITER ;
Jump to:  
cronNews News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]



phpBB SEO