Execute LOAD DATA INFILE Remotely

Hello
I need to Execute the LOAD DATA INFILE Command on a MySql dase remotely.
I have tried using
sqlstring = "LOAD DATA INFILE '/home/toolsfor/tmp/x_web.txt' REPLACE INTO TABLE x_web;"
WebSQLEx.Execute sqlstring

From VBA and get the following error
[MySql][odbc 5.1 Driver][mysqld-5.0.92-community-log]Access denied for user'user@ipaddress'(using password: YES)

I have tried to put the LOAD DATA INFILE in a stored Procedure but get
ERROR 1314: LOAD DATA is not allowed in stored procedures

Any Idea's?
p-platerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FrankoHCommented:
The error suggests that the user you are trying to access the server with doesn't have this permission granted, or that LOAD DATA INFILE might be disabled altogether. It is usually disabled as it is security concern.

You will need to check a few things,
1. does the server allow remote connections,
2. is LODA DATA INFILE disabled,
3. Does your MySQL user have the relevant permissions granted.

0
p-platerAuthor Commented:
If I login to the database with PhpMyAdmin with the same user I can run the "LOAD DATA INFILE"
I can execute Select and Update Scripts from VBA using the same (WebSQLEx.Execute sqlstring) command.
0
FrankoHCommented:
Sounds like the mysql server doesnt allow remote connections.

Most likely phpmyadmin runs on the same server as the mysql server, so it can access it locally or connections are allowed to the mysql server from specific IPs.

Do you have access to the mysl servers conf file? or access to the server it sits on?

If not, then best contact the system administrator for that server.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

p-platerAuthor Commented:
"I can execute Select and Update Scripts from VBA using the same (WebSQLEx.Execute sqlstring) command. "

This is from my Local machine in VBA so the server must be set up for remote connections
0
johanntagleCommented:
Clicked submit too soon.  Basically For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
0
NoiSCommented:
The trouble is the permission for the specific user that you are using.

Try creating another user with this command

GRANT SELECT, INSERT, UPDATE, DELETE, FILE ON YOU_DB_NAME.* TO 'another_username'@'%' IDENTIFIED BY 'SOME_PASSWORD';


This will create a user with FILE permission. You must do it with a user with GRANT Privilege (such root).

keep in mind that the file must EXIST ON REMOTE SERVER, not in you local computer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
If there is no permission issue you should do:

LOAD DATA LOCAL INFILE '/home/toolsfor/tmp/x_web.txt' REPLACE INTO TABLE x_web;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.