Batch script to issue commands to mySQL db?

Question

I am trying to create a batch script that would connect to a mySQL database and issue a delete command:

@echo off
echo Resetting all assessments...
mysql -hlocalhost -urdfdev -p%1 rdf_feedback
delete from competency_question_answer;

I will run this script providing the password as a command-line argument, but all this script does is, connects to the database, and the mysql> prompt will be shown. After I exit from mysql, the rest of the batch commands get to execute (and fail, no surprise).

How can I pass the SQL commands from the batch script to the mysql console? Is this even possible?

Ólafur Waage · Accepted Answer · 2009-04-16 12:44:16Z

4

You need to use command line tools. I don't know if there exists any for MySQL but for SQL there is SQLCMD and for Oracle there is OSQL.

What you can also do is something like this.

mysql -uuser -ppass < foo.sql

Where foo.sql is the commands you want to execute.

answered Apr 16, 2009 at 12:44

Ólafur Waage

70.2k22 gold badges147 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Perháč Over a year ago

thanks, tried it this way and after adding an empty line at the end of the foo.sql and removing the last semicolon (seemed to cause problems), all went through successfully.

paxdiablo · Accepted Answer · 2009-04-16 12:46:12Z

3

You may need to connect multiple times:

@echo off
echo Resetting all assessments...
mysql -hlocalhost -urdfdev -p%1 rdf_feedback -e delete from competency_question_answer;

Alternatively, you should be able to put all your commands in a separate file such as input.sql and use:

mysql -hlocalhost -urdfdev -p%1 rdf_feedback <input.sql

answered Apr 16, 2009 at 12:46

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Comments

一二三 · Accepted Answer · 2012-01-01 13:16:18Z

2

echo "delete from competency_question_answer;" | mysql -hlocalhost -ur... etc.

edited Jan 1, 2012 at 13:16

一二三

21.3k11 gold badges70 silver badges77 bronze badges

answered Apr 16, 2009 at 12:42

pjz

43.4k6 gold badges54 silver badges60 bronze badges

4 Comments

Peter Perháč Over a year ago

seems to be going in the right direction, but I get: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"delete from competency_question_answer;"' at line 1

Peter Perháč Over a year ago

the command is okay when i type it into the mysql console manually... also, how would I pass more commands? There seems to be an issue with all the double quotes

paxdiablo Over a year ago

In Windows, <echo "x"> echos <"x">, not <x>, so you probably should remove the double quotes.

Peter Perháč Over a year ago

Thanks, I decided to use a separate file for the sql commands, but your solution is also nice.

Tony · Accepted Answer · 2011-05-11 10:51:40Z

1

Putting multiple sets of commands into .sql batch files works best, and you can execute multiples of these in the .bat file.

answered May 11, 2011 at 10:51

Tony

112 bronze badges

Collectives™ on Stack Overflow

Batch script to issue commands to mySQL db?

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related