04-10-2008
Use SED command to do the need.
First of all i dont think u need any scripts..
U can do with a single line command along with pipes
Here is the command
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile
INPUT FILE : test
OUTPUT FILE : targetfile
Assumptions :
*************
1. The input file doesnt have any blank lines.
2. If it has then use the following sed command
sed '/^$/d' <sourcefile> <targetfile>
Here is my input file
cat test
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
4 ASDFASDFASDFASDFSAD
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
THINGS THAT ARE TAKEN CARE OFF :
*********************************
1. There might be a single SELECT STATEMENT (no sub-queries).
2. There might be sub-queries which are written continiously (without breaking)
3. There might be sub-queries which are written in different statements.
Now the command :
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile
Explanation of the commands :
*****************
***sed '/;/G' test ***
*****************
This inserts a new line after the end of a complete SQL statment ie. when it encounters a ";" it inserts a line after it.
*****************
***grep "SELECT" ***
*****************
The output of the command sed '/;/G' test is piped and only the SELECT queries are grepped from that.
********************************
****sed -e :a -e '$!N;s/\n/~/g;ta'******
********************************
The output of the previous command sed '/;/G' test | grep "SELECT" is sent as input to the above command. This is manily done becoz if there are queries which span more than one line which is not continuous. (they might have pressed enter key and continued)
**************
***tr "~" "\n"***
**************
This will replace all "~" characters to "\n"
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n"
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
Here is the file which u needed....
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
I am working on a code to extract the table names out of a select statement. Is there anybody who has worked on something similar? May be you could provide me with the regular expression for the same.
Regards.
Silas (2 Replies)
Discussion started by: silas.john
2 Replies
2. UNIX for Dummies Questions & Answers
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
3. Shell Programming and Scripting
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
4. Shell Programming and Scripting
count.sh#!/bin/ksh
SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3`
SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3`
LIST="Count Select_All"
select i in $LIST
do
if
then
echo $SQL1
elif
then
echo $SQL2
fi
done (2 Replies)
Discussion started by: killboy
2 Replies
5. Shell Programming and Scripting
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 (7 Replies)
Discussion started by: meva
7 Replies
6. Shell Programming and Scripting
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
7. UNIX for Advanced & Expert Users
Hi,
First query:
I am trying to execute the below command to pull all the record whose length is not of the expected. But this is not giving the expected results.
$2 is the record length passed in the script as second parameter.$filename is the filename on which the awk is executed.It is... (4 Replies)
Discussion started by: devina
4 Replies
8. Shell Programming and Scripting
Hi all,
I have script, which performing sql queries and put output into file.
When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors...
I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error:
# more... (4 Replies)
Discussion started by: nypreH
4 Replies
9. Shell Programming and Scripting
How to run multiple Queries in a ksh Script
I have a KSH script that has one SQL Query and generates and emails output of the query in HTML format. I want to change the script so that it has three SQL queries and the last query generates and emails the HTML output page of just that query.
So far... (5 Replies)
Discussion started by: JolietJake
5 Replies
10. Shell Programming and Scripting
Hi Team,
I am Oracle Databse developer. I am currently working on two databases.
DB1 and DB2.
in DB1 I have a Select query which will return 100 records.
In Db2 I have a Select query which also return 100 records.
In these two tables ( in different Schemas) we have a common column.
... (2 Replies)
Discussion started by: vasuvv
2 Replies
LEARN ABOUT PHP
mysqlnd_qc_set_is_select
MYSQLND_QC_SET_IS_SELECT(3) 1 MYSQLND_QC_SET_IS_SELECT(3)
mysqlnd_qc_set_is_select - Installs a callback which decides whether a statement is cached
SYNOPSIS
mixed mysqlnd_qc_set_is_select (string $callback)
DESCRIPTION
Installs a callback which decides whether a statement is cached.
There are several ways of hinting PELC/mysqlnd_qc to cache a query. By default, PECL/mysqlnd_qc attempts to cache a if caching of all
statements is enabled or the query string begins with a certain SQL hint. The plugin internally calls a function named is_select() to find
out. This internal function can be replaced with a user-defined callback. Then, the user-defined callback is responsible to decide whether
the plugin attempts to cache a statement. Because the internal function is replaced with the callback, the callback gains full control. The
callback is free to ignore the configuration setting mysqlnd_qc.cache_by_default and SQL hints.
The callback is invoked for every statement inspected by the plugin. It is given the statements string as a parameter. The callback
returns FALSE if the statement shall not be cached. It returns TRUE to make the plugin attempt to cache the statements result set, if any.
A so-created cache entry is given the default TTL set with the PHP configuration directive mysqlnd_qc.ttl. If a different TTL shall be
used, the callback returns a numeric value to be used as the TTL.
The internal is_select function is part of the internal cache storage handler interface. Thus, a user-defined storage handler offers the
same capabilities.
PARAMETERS
This function has no parameters.
RETURN VALUES
Returns TRUE on success or FALSE on failure.
EXAMPLES
Example #1
mysqlnd_qc_set_is_select(3) example
<?php
/* callback which decides if query is cached */
function is_select($query) {
static $patterns = array(
/* true - use default from mysqlnd_qc.ttl */
"@SELECTs+.*s+FROMs+test@ismU" => true,
/* 3 - use TTL = 3 seconds */
"@SELECTs+.*s+FROMs+news@ismU" => 3
);
/* check if query does match pattern */
foreach ($patterns as $pattern => $ttl) {
if (preg_match($pattern, $query)) {
printf("is_select(%45s): cache
", $query);
return $ttl;
}
}
printf("is_select(%45s): do not cache
", $query);
return false;
}
mysqlnd_qc_set_is_select("is_select");
/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
/* cache put */
$mysqli->query("SELECT id FROM test WHERE id = 1");
/* cache hit */
$mysqli->query("SELECT id FROM test WHERE id = 1");
/* cache put */
$mysqli->query("SELECT * FROM test");
?>
The above examples will output:
is_select( DROP TABLE IF EXISTS test): do not cache
is_select( CREATE TABLE test(id INT)): do not cache
is_select( INSERT INTO test(id) VALUES (1), (2), (3)): do not cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT id FROM test WHERE id = 1): cache
is_select( SELECT * FROM test): cache
SEE ALSO
Runtime configuration, mysqlnd_qc.ttl, mysqlnd_qc.cache_by_default, mysqlnd_qc_set_user_handlers(3).
PHP Documentation Group MYSQLND_QC_SET_IS_SELECT(3)