Solved

Putty and Command Line (Windows)

Posted on 2011-02-22
9
2,740 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:kellysteevens
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 34956740
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
 

Author Comment

by:kellysteevens
ID: 34956803
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
 
LVL 18

Expert Comment

by:Raheman M. Abdul
ID: 34956872
use plink.exe

or  
use go at the end of the sql statements in a batch file
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:kellysteevens
ID: 34956882
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
 
LVL 8

Expert Comment

by:subhashchy
ID: 34956917
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
 
LVL 8

Expert Comment

by:subhashchy
ID: 34956926
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
 
LVL 9

Accepted Solution

by:
AriMc earned 500 total points
ID: 34956929
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
 
LVL 9

Expert Comment

by:AriMc
ID: 34956945
Sorry, ignore the last 5 lines I forgot to remove them after editing.
0
 

Author Comment

by:kellysteevens
ID: 34958007
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

VALIDATING DATES One method of validating dates is to jam the date into the DATE command and see if it accepts it by examining the system's errorlevel value. A non-zero result indicates failure. A typical example might look something like the fol…
Fine Tune your automatic Updates for Ubuntu / Debian
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question