{"id":1384,"date":"2010-11-30T06:58:36","date_gmt":"2010-11-30T06:58:36","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=1384"},"modified":"2023-04-30T17:23:06","modified_gmt":"2023-04-30T17:23:06","slug":"using-load-data-infile-with-stored-procedure-workaround-mysql","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/using-load-data-infile-with-stored-procedure-workaround-mysql","title":{"rendered":"MySQL Load Data Infile with Stored Procedure"},"content":{"rendered":"\n

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

Download MySQL UDF<\/strong><\/h2>\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\n
Update:<\/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\n
Extract 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\n
Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully<\/code><\/pre>\n\n\n\n
Create function sys_exec<\/h2>\n\n\n\n
CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';<\/code><\/pre>\n\n\n\n
sys_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\n
How to load text file to MySQL using Stored Procedure & Load Data Infile syntax:<\/strong><\/h2>\n\n\n\n
Step-1. Creating table:<\/strong><\/p>\n\n\n\n
CREATE TABLE `t` ( `id` int(2) default NULL ) ENGINE=MyISAM<\/code><\/pre>\n\n\n\n
Step-2. Create a sample file to load:<\/strong><\/p>\n\n\n\n
vi loadtest.txt
1
2
3<\/code><\/pre>\n\n\n\n
Step-3. Create a shell script:<\/strong><\/p>\n\n\n\n
vi \/tmp\/load.sh
mysql -u mysql_user -p mysql_password -e \"load data local infile \\\"$1\\\" into table $2;\"<\/code><\/pre>\n\n\n\n
Step-4. Create a Stored Procedure:<\/strong><\/p>\n\n\n\n
DELIMITER $$
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\n
Step 5. Execute:<\/strong><\/p>\n\n\n\n
CALL `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":["post-1384","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-code","tag-load-data-infile","tag-mysql","tag-mysql_udf","tag-stored-procedure","tag-sys_exec"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t