Can I use mysqlimport / LOAD DATA LOCAL INFILE inside stored procedure

Question

I am using mysqlimport to get rows from a text file to a table easily like so:

mysqlimport -u root -p --local mytest employee.txt

Can I do this from a stored procedure? All attempts so far have failed I'm afraid this may only be available using command line? The objective is to have a job call a SP, this SP extracts rows from a text file (tab delimited), then the SP compares each row with an existing table, if found it will update, else insert.

Thanking you

score 1 · Answer 1 · answered May 01 '13 at 05:31

1

Stored procedures execute on the server and do not have access to remote filesystems.

A rough equivalent of this is a call LOAD DATA INFILE:

LOAD DATA INFILE 'employee.txt' INTO TABLE mytest

This requires employee.txt to reside on your server. The --local option requires the mysqlimport client to bridge that data in.

answered May 01 '13 at 05:31

tadman

208,517
23
234
262

thank you for your time. I dont fully understand what you have said about the --local option, what do you mean by this? Also are you saying the 'LOAD DATA INFILE 'employee.txt' INTO TABLE mytest' can be run from a stored proc providing the txt file is on the server? Lastly, does mysqlimport also require the file to be on the server? Thank you – Mat41 May 01 '13 at 22:40
The `--local` option allows you to import a local file to the server as the server cannot load files from your machine without assistance. A stored procedure would be limited to loading files on the server only. – tadman May 02 '13 at 15:24

score 0 · Answer 2 · answered Oct 07 '13 at 08:25

0

Load Data INFILE doesnt work with stored proc. you would like to use perl script or else to do this

answered Oct 07 '13 at 08:25

Spark-Beginner

1,334
5
17
24

How would you go about that? – Robert Oct 07 '13 at 08:42
REFER THIS: http://stackoverflow.com/questions/10737974/load-data-local-infile-gives-the-error-the-used-command-is-not-allowed-with-this – Spark-Beginner Oct 14 '13 at 18:22

Can I use mysqlimport / LOAD DATA LOCAL INFILE inside stored procedure

2 Answers2