Did you ever need to run LOAD DATA INFILE in a procedure? May be to automate or dynamically perform the large data file load to the MySQL database.<\/p>\n\n\n\n
In this blog post, we will walk you through how to use a stored procedure and Load Data Infile syntax to efficiently load data into MySQL. This will also provide step-by-step instructions on how to create a table, a sample file to load, a shell script, and a stored procedure. By following these instructions, you will be able to effortlessly load data into MySQL.<\/p>\n\n\n\n
So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround.<\/p>\n\n\n\n
[root@localhost kedar]# wget http:\/\/www.mysqludf.org\/lib_mysqludf_sys\/lib_mysqludf_sys_0.0.3.tar.gz<\/code><\/pre>\n\n\n\n[refer: http:\/\/www.mysqludf.org\/]<\/em><\/p>\n\n\n\nUpdate:<\/strong> https:\/\/web.archive.org\/web\/20111016004142\/http:\/\/www.mysqludf.org\/lib_mysqludf_sys\/lib_mysqludf_sys_0.0.3.tar.gz<\/p>\n\n\n\nExtract and Install MySQL UDF<\/h2>\n\n\n\n[root@localhost kedar]# tar -xzvf lib_mysqludf_sys_0.0.3.tar.gz
install.sh
lib_mysqludf_sys.c
lib_mysqludf_sys.html
lib_mysqludf_sys.so
lib_mysqludf_sys.sql
Makefile<\/code><\/pre>\n\n\n\n[root@localhost kedar]# sh install.sh
Compiling the MySQL UDF
gcc -Wall -I\/usr\/include\/mysql -I. -shared lib_mysqludf_sys.c -o \/usr\/lib\/lib_mysqludf_sys.so
MySQL UDF compiled successfully<\/code><\/pre>\n\n\n\nPlease provide your MySQL root password
Enter password:
MySQL UDF installed successfully<\/code><\/pre>\n\n\n\nCreate function sys_exec<\/h2>\n\n\n\nCREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';<\/code><\/pre>\n\n\n\nsys_exec – executes an arbitrary command, and returns it’s exit code.<\/p>\n\n\n\n
You also can similarly create functions:<\/strong>
sys_eval – executes an arbitrary command, and returns it’s output.
sys_get – gets the value of an environment variable.
sys_set – create an environment variable, or update the value of an existing environment variable.<\/p>\n\n\n\nHow to load text file to MySQL using Stored Procedure & Load Data Infile syntax:<\/strong><\/h2>\n\n\n\nStep-1. Creating table:<\/strong><\/p>\n\n\n\nCREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM<\/code><\/pre>\n\n\n\nStep-2. Create a sample file to load:<\/strong><\/p>\n\n\n\nvi loadtest.txt
1
2
3<\/code><\/pre>\n\n\n\nStep-3. Create a shell script:<\/strong><\/p>\n\n\n\nvi \/tmp\/load.sh
mysql -u mysql_user -p mysql_password -e \"load data local infile \\\"$1\\\" into table $2;\"<\/code><\/pre>\n\n\n\nStep-4. Create a Stored Procedure:<\/strong><\/p>\n\n\n\nDELIMITER $$
DROP PROCEDURE IF EXISTS `load_data_SP` $$
CREATE PROCEDURE `load_data_SP` (in_filepath varchar(100),in_db_table varchar(100))
BEGIN
declare exec_str varchar(500);
declare ret_val int;
set exec_str=concat(\"sh \/tmp\/load.sh \",in_filepath,\" \", in_db_table);
set ret_val=sys_exec(exec_str);
if ret_val=0 then
select \"Success\" as Result;
else
select \"Please check file permissions and paths\" as Result;
end if;
END $$
DELIMITER ;<\/code><\/pre>\n\n\n\nStep 5. Execute:<\/strong><\/p>\n\n\n\nCALL `load_data_SP`('\/tmp\/loadtest.txt' , 'test.t');<\/code><\/pre>\n\n\n\n…and that’s it Stored Procedure will return Success or Failure accordingly.<\/p>\n\n\n\n
Make sure you’re having file permissions well set and MySQL can access the files.
Here I’ve kept the files under \/tmp directory with chmod 777 & chown mysql:mysql to remove the permission-issue possibility.<\/p>\n\n\n\n
Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"Did you ever need to run LOAD DATA INFILE in a procedure? May be to automate or dynamically perform the large data file load to the MySQL database. In this…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[24,364,427,196,108,195],"class_list":{"0":"post-1384","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-code","9":"tag-load-data-infile","10":"tag-mysql","11":"tag-mysql_udf","12":"tag-stored-procedure","13":"tag-sys_exec"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=1384"}],"version-history":[{"count":10,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1384\/revisions"}],"predecessor-version":[{"id":2862,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/1384\/revisions\/2862"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=1384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=1384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=1384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}