Putty and Command Line (Windows)

I would think this is easy but I have been struggling with it all day.

I need to launch a putty window, login, and run two commands (login to mysql and run a SQL command) via an automated script (I was doing this through a batch file).

I cannot figure out how to automate the second two commands.  Every time I run this .bat file, it successfully launches putty and logs me in, but it won't log me in to mysql or run the SQL statement (basically, my .bat file won't run the last two lines).  Any ideas??  FYI, this does work if I manually type it in a putty window.

This is my .bat file:
c:\Putty\putty.exe mysession
mysql -u username -ppassword database_name
LOAD DATA LOCAL INFILE "/home/tmp/load_data.txt" INTO TABLE tablename FIELDS TERMINATED BY "," ENCLOSED BY '"';

I'm on a windows computer, by the way.

Thanks in advance!
Kelly
kellysteevensAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AriMcConnect With a Mentor Commented:
First create a script for MySQL on your remote host (/myhome/dbscript.txt):

   use mydatabase;
   LOAD DATA LOCAL INFILE ...

Then create a script on your local host for PuTTY to execute (C:\test.txt)

   mysql --user=username --password=password </myhome/dbscript.txt

Finally execute PuTTY:

   C:\...\putty.exe YOURSESSION -m C:\test.txt




Write you bat
c:\Putty\putty.exe mysession
mysql -u username -ppassword database_name
LOAD DATA LOCAL INFILE "/home/tmp/load_data.txt" INTO TABLE tablename FIELDS TERMINATED BY "," ENCLOSED BY '"';
0
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
I think you're pasting the query from a text editor
Type the query again into fresh file and dont paste them into the bat file.

Just remove the extra "return" or the white spaces from the end of each line and your query should be fine. It's not the error of putty, but that's how SQLPlus will expect a command to be.
0
 
kellysteevensAuthor Commented:
I don't know if we are on the same page.  I am trying to use a .bat file to do the 3 things (login to server, login to mysql, run sql statement).  I tried taking the SQL statement out of the .bat file and the .bat behaves the same way.  It runs the first statement but not the mysql line.

Kelly
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
use plink.exe

or  
use go at the end of the sql statements in a batch file
0
 
kellysteevensAuthor Commented:
I have also tried pink.  I couldn't find much documentation on plink, so I did this:

c:\Putty\plink.exe mysession
mysql -u username -ppassword database_name
LOAD DATA LOCAL INFILE "/home/tmp/load_data.txt" INTO TABLE tablename FIELDS TERMINATED BY "," ENCLOSED BY '"';

But I get the same result.  It doesn't want to read multiple commands.

Kelly
0
 
subhashchyCommented:
Try this from a batch file.... The idea is to use putty to connect and then send the keys using VBscript instead of autmating.. I dont have mysql so there could be errors in first attempt but it will work..
@echo off
start putty @mysession
echo Set WshShell = WScript.CreateObject("WScript.Shell") >temp.vbs
echo wshshell.AppActivate "Putty" >>temp.vbs
echo wscript.sleep 2000 >>temp.vbs
echo "put your firstline of command here which you want to type in putty window" >>temp.vbs
echo wscrecho wshshell.sendkeys"{enter}" >>temp.vbs
echo wscript.sleep 1000 >>temp.vbs
echo "put your 2nd line of command here which you want to type in putty window" >>temp.vbs
echo wscrecho wshshell.sendkeys"{enter}" >>temp.vbs
cscript /nologo temp.vbs

Open in new window



See if it works..
0
 
subhashchyCommented:
forgot to mention you will have to save your putty session as  "mysession" and replace putty in first line with the actual putty path.
0
 
AriMcCommented:
Sorry, ignore the last 5 lines I forgot to remove them after editing.
0
 
kellysteevensAuthor Commented:
Thank you!  Thank you!  Thank you!  I couldn't get subhashchy to work, but the second solution worked perfectly and now I'm done with my project!

Kelly
0
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.

All Courses

From novice to tech pro — start learning today.