Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Putty and Command Line (Windows)

Posted on 2011-02-22
9
2,759 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 19

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 19

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Command prompt output to variable? 9 59
ftp to port 21 4 53
windows 7 starter missing password 21 89
How do I setup Wordpress to use Master / Slave MySQL database setup? 4 51
We all know how boring and exhausting it is to transfer huge web projects developed locally to a webserver simply via FTP. The File Transfer Protocol is a really nice solution if you need to transfer small amounts of files, but if you're plannin…
Hello to you all, I hear of many people congratulate AWS (Amazon Web Services) on how easy it is to spin up and create new EC2 (Elastic Compute Cloud) instances, but then fail and struggle to connect to them using simple tools such as SSH (Secure…
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 find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

840 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