LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 01-08-2011, 05:04 AM   #1
smritisingh03
Member
 
Registered: Nov 2010
Posts: 43

Rep: Reputation: 0
unix script that executes sql query in a while loop


Hi All.I have a problem here.

I have a logfile which looks like:

tableabc 1786rows
tabledfg 8374rows
tablefhd 726rows
......
....
.....

Now i have to writ a script which takes this log as input and quesries the DB for each table.
So,I want to:
  • input the logfile
  • start a while loop.So,while read LINE
  • This variable LINE would store the table name by CUT using a delimiter
  • once the table name is ready it makes a connection to DB and queries the table for its rowcount
  • writes the rowcount to an O/P file

Please help as I am new to IT industry and very new to scripting.
 
Old 01-08-2011, 05:52 AM   #2
carltm
Member
 
Registered: Jan 2007
Location: Canton, MI
Distribution: CentOS, SuSE, Red Hat, Debian, etc.
Posts: 703

Rep: Reputation: 97
Have you started to work on this? My suggestion would be to start
writing the script and then ask for assistance when you need help
with a specific part.
 
Old 01-08-2011, 10:16 AM   #3
smritisingh03
Member
 
Registered: Nov 2010
Posts: 43

Original Poster
Rep: Reputation: 0
yes i have written a script but this script prints only the table name but does not query that table for its rowcount.

#!/bin/ksh

SQL_CONN_CODE="user/pass@Instance"

while read LINE

do

table_name=`echo $LINE|cut -d "-" -f1`

sqlplus -s "user/pass@Instance" <<EOF>> DBcountOP


set heading off feedback off pagesize 0 linesize 30000 trimout on;

row_count=`echo "select count(*) from $table_name;"

print $table_name $row_count >> DBcountOP

done < logcountOP

EOF
~

Last edited by smritisingh03; 01-08-2011 at 10:49 PM.
 
Old 01-08-2011, 10:46 AM   #4
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387Reputation: 2387
Hi,

You are trying to mix and match a here document and normal shell commands, which is not possible (the shell/sql won't be able to figure out which is which).

Have a look at the following:
Code:
#!/bin/bash

cat logcountOP | \
while read TblName RCDummy
do
  echo -n "${TblName} : "
  sqlplus -s sa/sa@conCRM5 <<SQLScript
     set heading off feedback off pagesize 0 linesize 30000 trimout on;
     select count(*) from ${TblName};
SQLScript
done > DBcountOP
I'm assuming that the set heading off .... line is needed and doesn't generate output.

If you use 2 variables after the while read, those are filled with the first and second field from the logcountOP file. This way there is no need for a cut command.
The echo -n "${TblName} : " echo's the table name, the -n makes sure there is no carriage return.
Everything in the here document (all between <<SQLScript and SQLScript) is given to sql.

Hope this helps.

EDIT:
If you have a very large number of tables that need to be checked, this approach might not be what you want. The above example will open and close a sql session for every table that needs to be checked.

Creating a sql file which holds all the sql commands and giving this to sql would be a better solution, but also a bit harder to implement.

Last edited by druuna; 01-08-2011 at 10:51 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Bash script: Need some help on how to parse a simple SQL query newbie_0404 Linux - Newbie 10 06-02-2010 01:31 AM
unix shell script loop concept badrinath_tcs Linux - Software 3 10-01-2009 08:11 PM
Loop or check SQlite3 database continuously, when query found run script for first 3 Techno Guy Linux - Newbie 3 09-27-2009 08:46 PM
Speeding up the script, or the SQL Query? knickers Programming 1 04-13-2004 12:57 PM
ColdFusion - Using a script to build and execute SQL query when a button is clicked Locura Programming 1 02-25-2004 10:59 PM


All times are GMT -5. The time now is 07:35 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration