Shell Programming and Scripting

Perhaps there is a better way to do this, but right now this is all I can think of. If there is a better way to do this, all suggestions are welcome.

I would like to take a file and perform the following actions on it.

1. Search for CREATE TABLE
2. Copy that line and paste it one line above
3. Change the CREATE to a DROP
4. Change the '(' at the end to a ';'
5. Run until the EOF is reached, then save

I have a script written that goes as follows:

vi gl.ddl << EOF
/CREATE TABLE
yy
k
p
cw DROP
$
cw ;
n
n
ZZ
EOF

It currently does not work at all, and throws the following message when I try to execute it.

ex: 0602-134 An error occurred while reading input.

I initially thought it was a file permission problem, so set the perms to 777, but still receive the same message.

Thanks in advance for any insights.

Schmitty

This'll work for you, unless you really wanted to use vi commands for some reason...

Code:

while read LINE
do
 echo $LINE >> resultFile
 echo $LINE | grep -q "CREATE TABLE"
 if [ $? -eq 0 ]; then
  echo $LINE | sed -e 's/CREATE/DROP/' -e 's/($/;/' >> resultFile
 fi
done << someFile

The only reason I wanted to use vi commands is because that's what I knew how to do. I did not know there was a LINES keyword that would read in a file line-by-line.

How does the script know what file to read with the LINES keyword?

Here's the problem that I'm finding.

1. When I run it like you coded, I receive the message 'Syntax error at line 33: `<' is not matched' which refers to the 'done <<' part of the script.

2. If I take out the '<< fileNAME' after the done, it appears that the file is never created.

Thanks for your help as I do not have experience with a script like this yet.

Actually, the structure

Code:

while read someVariable
do
  stuff
done < fileName

is just a way to step through a file. With each iteration of the "while" loop, the next line in fileName is stored in someVariable.

So LINE is just what I chose to call the variable. If you take out the part you took out, then the script will hang-up because it doesn't have a filename to read from (at that point, it's actually waiting for the user to provide the input).

However, I screwed up - replace done << someFile with done < someFile.

Thank you for the help, the script works. I put the output file name in the done < inputFile part, I didn't realize that was supposed to be in input file.

Thanks for your help, this knowledge will benefit me greatly.

I believe your requirement was to print the altered line (the "DROP" line) before the original, i.e., to drop a table and then re-create it. I think this will work as well. If your source file is thousands of lines long, it will also be appreciably faster.

Code:

awk '/CREATE TABLE/ {
        s = $0
        k = index(s, "CREATE")
        s = substr(s,1,k-1) "DROP" substr(s,k+6)
        k = index(s, "(")
        s = substr(s,1,k-1) " ;"
        print s
    }
    { print }' < infile > outfile # print all lines

This should work in all awks. See below for nawk notes.

This assumes that your "CREATE TABLE" statement is completely contained on one line, which I think is implicit in the vi keystrokes you originally posted.

Re: nawk
nawk (or gawk or /usr/xpg4/bin/awk or ...) has facilities that make dealing with substitution and mixed case input a bit easier:

Code:

nawk 'match(t = toupper($0), /CREATE TABLE/) {
        t = substr(t,1,RSTART-1) "DROP" substr(t,RSTART+6)
        sub(/\(.*/, ";", t)
        print t
    }
    {print}' < infile > outfile

The backslash before the paren in the regexp may not be needed, depending on your version of nawk.

Note that both oombera's solution and mine create a second file, and do not, indeed can not, write the file in place.

Actually, you might be creating far more work for yourself than is necessary.

If you are trying to create an sql to create a sequence of drop commands for tables that have been created you might consider accessing the RDBMS catalog tables to do this.

In Informix I would do somthing like:

select "drop ", tabname, ";"
from systables
where tabid > 99;

The results can be saved to a file and subsequently used as an sql with a minumum of editing. You may further qualify the tables to drop with additional clauses to the where part, or by unioning a sequence of similar sqls together.

This is for Informix, and the solution will differ for other RDBMS, like Oracle or Sql Server.

Here is link to a useful BB for databases: www.dbforums.com.

MBB