Visit Our UNIX and Linux User Community


How to extract queries using UNIX shell script?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to extract queries using UNIX shell script?
# 1  
Old 10-26-2009
How to extract queries using UNIX shell script?

Hi,

I have an input file which have many lines,from which i need to extract only the complete sql statements and write this alone to an output file.

please help us in this.

Regards
Meva
# 2  
Old 10-26-2009
Post an example of your input file and the desired output between code tags.
# 3  
Old 10-26-2009
the sample input
Quote:
## Operator options
-data_source '[SRC_TNSNAME]'
-user '[SRC_UNAME]'
-password [SRC_PASSWD]
-upsertmode update_insert
-insert 'INSERT
INTO
TABLEA
(VALUE X, VALUE Y, VALUE Z)
VALUES
(NEXTVAL FOR X, Y,Z)'
#some statements
-update 'UPDATE
TABLEA
SET
VALUE A = A, VALUE B = B
WHERE
(ID = 100)'
The Expected output should be in a seperate output file with the sql statements alone.
Quote:
'INSERT
INTO
TABLEA
(VALUE X, VALUE Y, VALUE Z)
VALUES
(NEXTVAL FOR X, Y,Z)'

-update 'UPDATE
TABLEA
SET
VALUE A = A, VALUE B = B
WHERE
(ID = 100)'
Any suggestions?
# 4  
Old 10-26-2009
You want the insert statement to begin 'INSERT and the update statement to begin -update, I assume this is a typo?I have never seen SQL that begins and ends all statements with a single quote, are you sure this is the output you require? SQL statements that contain textual data generally delimit it with single quotes within the statement. Will you require this as well as containing the whole statement within single quotes?
# 5  
Old 10-26-2009
Works only according to the data you posted :
Code:
awk '$0 ~ /INSERT/ {flag=1;} /UPDATE/ {flag=1;} $0 ~ /)/ { flag=0 ;va=va"\n"$0;next} {if(flag==1) {va=va"\n"$0} } END {print va }' input_file.txt

# 6  
Old 10-26-2009
Expected output

Quote:
INSERT
INTO
TABLEA
(VALUE X, VALUE Y, VALUE Z)
VALUES
(NEXTVAL FOR X, Y,Z)

UPDATE
TABLEA
SET
VALUE A = A, VALUE B = B
WHERE
(ID = 100)


---------- Post updated at 05:47 AM ---------- Previous update was at 05:34 AM ----------

Hi Panyam

I tried the code you have posted, but i do get other lines as well other than the SQL statements.

the input file will have the SQL statements as well as some other lines. We need to extract only the SQL.

Thanks in Advance.
Meva

---------- Post updated at 06:02 AM ---------- Previous update was at 05:47 AM ----------

Hi Panyam

If you could explain me the command you have given me for this, i could also try to modify it further to get the desired result.

Thanks
Meva
# 7  
Old 10-27-2009
Try this :

Code:
awk 'BEGIN{va=" "} $0 ~ /INSERT/ {flag=1;va=va"\n\n INSERT";next} $0 ~ /UPDATE/ {flag=1;va=va"\n\n UPDATE";next} $0 ~ /)'"'"'/   { flag=0 ;va=va"\n"substr($0,1,index($0,")"));next} {if(flag==1) {va=va"\n"$0} } END {print va }' file_name.txt

try to put the script in a file and execute it.

Previous Thread | Next Thread
Test Your Knowledge in Computers #540
Difficulty: Medium
In dynamically typed programming languages. instead of declaring a variable to have a particular type, the type of a variable is determined by an A.I. in the operating system.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Issue on executing db2 queries through shell script

hi i am trying to execute db2 queries through shell script. it's working fine but for few queries is not working ( those queries are taking time so the script is not waiting to get the complete the execution of that query ) could you please any one help me on this is there any wait... (1 Reply)
Discussion started by: bhaskar v
1 Replies

2. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused: (1 Reply)
Discussion started by: 24ajay
1 Replies

3. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

4. Shell Programming and Scripting

Executing set of sql queries from shell script

Hi All, I tried executing set of queries from shell script but not able to capture the input query in the log file. The code looks something similar to below sqlplus user/pwd@dbname << EOF > output.log $(<inputfile.txt) EOF The above code is capturing the output of queries into... (9 Replies)
Discussion started by: loggedin.ksh
9 Replies

5. Shell Programming and Scripting

Nested SQL queries within Shell script

Hi, Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows: my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g" connect... (2 Replies)
Discussion started by: shrutihardas
2 Replies

6. Shell Programming and Scripting

Multiple MySql queries in shell script?

Hi guys, i know how to run a single query using mysql embedded in a shell script as follows: `mysql -umyuser -pmypass --host myhost database<<SQL ${query}; quit SQL` However, how would i be able to run several queries within the same connection? The reason for this is i am creating... (3 Replies)
Discussion started by: muay_tb
3 Replies

7. HP-UX

extract field of characters after a specific pattern - using UNIX shell script

Hello, Below is my input file's content ( in HP-UX platform ): ABCD120672-B21 1 ABCD142257-002 1 ABCD142257-003 1 ABCD142257-006 1 From the above, I just want to get the field of 13 characters that comes after 'ABCD' i.e '120672-B21'... . Could... (2 Replies)
Discussion started by: jansat
2 Replies

8. UNIX for Dummies Questions & Answers

shell script for sql queries

Hi All, I have written 4 sql queries . Now I want to write one SHELL SCRIPTING program for all these queries... i.e 1.select * from head; 2. select * from detail; 3. delete from head; 4. delete from detail; Please let me know how to write a shell script... Thank you (1 Reply)
Discussion started by: user71408
1 Replies

9. Shell Programming and Scripting

How to extract data using UNIX shell script?

Hello All, I am starting with UNIX. Any help is highly appreciated. How to extract data using UNIX shell script? And how do you export data using UNIX shell scripts into Microsoft Excel format? Thank you. (3 Replies)
Discussion started by: desiondarun
3 Replies

10. Shell Programming and Scripting

shell script queries: $home; broadcast ping

Dear all, This is the Bionic Fysh again. I have two quick questions: 1- when writing shell scripts, how does one allow the tilda ~ into the script ? e.g ls ~; ls ~me; user=you; ls ~$user (N.B I think that for this one you need: ls `~$user`) 2- In FreeBSD 4.0, I would like for a... (6 Replies)
Discussion started by: bionicfysh
6 Replies

Featured Tech Videos