any possible to run sql result and output to file

Shell Programming and Scripting
1

Hi,

I search all post...and no soluation about..if i would like to run a sql statement and output the result to txt file.

for example,
i usually run "sql" to logon the database and run select statement. Then I need to copy the output into the result.txt. Can I run the script to do this easiler?

Thx!

2

I am not sure which sql you are trying to connect. just for an example i am using mysql

  echo "SELECT *  FROM employee WHERE \
     emp_name="XXXXX" \
	   |$MYSQL_PATH/mysql dbname --user='whoever' --password='whatever'  -v -v -v >$DIRNAME/$outputfile 2>&1
3

thank..how can i check which sql I used?

4

I am not sure how can we check which database is installed in the machine.Might be can find it from the bin directory...Guys , any idea?

Below is another approch to run an sql from unix prompt besides the above method..where new.sql is a text file containing the sql command.This approch will be useful if you have more than one query to run...

sqlplus username/password <new.sql

5

There could be many database's present on the machine but what's the use when you dont know which sql you are going to use and incase, if you might not have the user id and password you cant even login to it. So, first you need to ask your dba which database you have to access and its right credentials!!!!

6

I tried sqlplus, but my existing sql database username and password...cannot work on sqlplus....so I think..I cannot use sqlplus command...

any advise?

7

no one can advise you when you dont have proper credentials, its only the DBA who can grant you the login.

8

Adding to the previous posts, You may also try co-process method

#Start the sql-plus as a co-process from a shell
sqlplus -s user/passwd |&

#Construct the command to be executed
CMD='select * from EMP'

#Write the constructed command to the co-process stdin
print -p $CMD

#Read the stdout the coprocess and print / output to a file
while read -p line ;
do
print $line
done