I need to pass a DDL statement into a ksh script & parse the statement.
What is the best way to pass a DDL statement into a KSH script.
---------- Post updated at 09:28 AM ---------- Previous update was at 07:35 AM ----------
if the name of the script is test.ksh
test.ksh "ALTER TABLE emp ADD emp_status varchar2(20);"
this is what I want to do, Can I do like this or any other way to do?
and how to handle each word of the string in the script?
Please suggest.
Yes, you can pass the string in the way you have it in your example. With quotes round the whole string it will be addressable in the script as $1 (first parameter from the command line).
How you parse it really depends on what you need to do with it. A simple mechanism would be to use the set command:
set $1
The result of the set command is that each token from the string in $1 will be assigned to $1 through $n. You then can examine/use the tokens as you need to .
A small example to illustrate the set command:
#!/usr/bin/env ksh
set $1
echo $1
echo $3
echo $5
The results when t22 -- my test script -- is executed are:
$ t22 "Now, is (the) time for UNIX programmers to help;"
Now,
(the)
for
Hope this helps. If you post how you need to parse the string, there might be different/better ways to go.
Thanks for replying.
Let me try to be more specific.
Here is the Scenario / requirement
When we submit the DDL to the DBA, DBA implement those DDLs to the database.
=> We need to automate the process of verifying if all the DDL are implemented properly.
for example:
following are the DDL statement submitted to DBA - (and DBA has already implemented the changes to DB tables)
these are the input string to the script:
ALTER TABLE supplier ADD supplier_name varchar2(50);
ALTER TABLE suppliers RENAME TO vendors;
ALTER TABLE supplier ADD (supplier_name varchar2(50),city varchar2(45) );
ALTER TABLE supplier MODIFY supplier_name varchar2(100) not null;
ALTER TABLE supplier MODIFY (supplier_name varchar2(100) not null, city varchar2(75));
ALTER TABLE supplier DROP COLUMN supplier_name;
ALTER TABLE supplier RENAME COLUMN supplier_name to sname;
Script should parse thr these statement word by word, if first 2 words is ALTER TABLE, pick the table name into a variable(v_tab_name).
if the 4th letter is ADD - pick the 5th word into a variable(v_col_name)
connect to Database and do the following query:
select 1 from USER_TABLES
where table_name = v_tab_name
and column_name = v_col_name
If this query returns one row, then print verification success
Hope this gives a better pic of what I am trying to do.
This should give you enough of an idea of how to write the script. This example assumes Ksh (Korn shell) as bash has issues with piping command output to read into variable names.
#!/usr/bin/env ksh
while [[ -n $1 ]] # for each string on the command line
do
echo "$1" | read cmd1 cmd2 table_name sub_cmd column_name junk
if [[ $1" "$2 == "ALTER TABLE" ]]
then
if [[ $sub_cmd == "ADD" ]]
then
database_command "select 1 from USER_TABLES where table_name = $table_name and column_name = ${column_name/\(/}" | wc -l | read count
# assuming that one record out from database command is a good response and any other amount of output is not
if (( $count != 1 ))
then
echo "error verifying $1"
else
echo "verified: $1"
fi
fi
fi
shift
done
I have no way to actually test this, but it does pass syntax checking by the shell.
Have fun.
Thanks Agama, I can build other conditions using this template.
Could you please share the database_connect function too?
Unfortunately I have no data base magic in my head. I don't work with them and that is why I used "database_command" in place of some real db interface. I'm sure there is some 'send-sql' or similar command line interface depending on the database you are using, but I have no experience to even start to point you in the right direction.
I was thinking if AWK can do this. Please suggest.
ALTER TABLE < > ADD < > < >;
Can I search for this pattern and extract content between the < > into a variable, like:
var1=tab_nm
var2=col_nm
var3=col_type
Hi
# cat a
ALTER TABLE abc ADD empid char
# awk '{if($1=="ALTER" && $4="ADD"){print "var"++i"="$3"\n" "var"++i"="$5"\n" "var"++i"="$6}}' a
var1=abc
var2=empid
var3=char
#
Guru.
Thanks Guru,
just another change needed.
var1, var2, var3 are the variables i need to set from within the awk and use them outside the awk, How can I do that?
Most efficient if you use Ksh:
awk '{if( $1=="ALTER" && $4="ADD" ) print $3, $5, $6 }' file | read table column type
Bash dosen't support piping int read like this so the only way I know is to direct it to a temp file and read from the tmp file, which results in additional overhead that adds up significantly if you must execute this a lot.
If you must use bash:
awk '{ if( $1=="ALTER" && $4="ADD" ) print $3, $5, $6 }' file >/tmp/x.$$
read table column type </tmp/.x$$
rm /tmp/x.$$
var1=$(awk -F "[<>]" '{print $2}' file);var2=$(awk -F "[<>]" '{print $4}' file);var3=$(awk -F "[<>]" '{print $6}' file)
Bash does support piping into read but the read is in a subshell, so that when the read ends the variables are lost... You can do something like this:
awk '$1=="ALTER" && $4="ADD" { print $3, $5, $6 }' infile |
{
read table column type
echo $table
echo $column
echo etcetera...
}
Alternatively, bash supports this construct:
read table column type < <( awk '$1=="ALTER" && $4="ADD" { print $3, $5, $6 }' infile )
Appreciate the responses, as I am new to KSH .. its helping me learn a lot.
So here is what I modified it.
#!/bin/ksh
while read line
do
echo $line | awk '{
if($1=="ALTER" && $2=="TABLE"){
if($4=="ADD"){
sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s and column_type = %s", $3, $5, $6);
print sql_statement
}
if($4=="MODIFY"){
sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s and column_type = %s", $3, $5, $6);
print sql_statement
}
if($4=="RENAME" && $5=="TO" ){
sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s", $6);
print sql_statement
}
if($4=="RENAME" && $5=="COLUMN"){
sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s", $3, $8);
print sql_statement
}
if($4=="DROP" && $5=="COLUMN"){
sql_statement=sprintf("select 1 from USER_TABLES where table_name = %s and column_name = %s", $3, $6);
print sql_statement
}
}
}' | read sql
echo $sql
done < file
Input File: file
1. ALTER TABLE emp ADD status VARCHAR2(20);
2. ALTER TABLE dept MODIFY dept_name varchar2(20) NOT NULL;
3. ALTER TABLE temp RENAME TO temp01;
4. ALTER TABLE dept RENAME COLUMN status TO status_new;
5. ALTER TABLE emp DROP COLUMN status;
6. ALTER TABLE customers ADD (contact_name varchar2(50),last_contacted date );
7. ALTER TABLE customers MODIFY ( customer_name varchar2(50) not null, state varchar2(2));
With all of yours help, I was able to handle first 5 conditions. What is the best way to handle 6th and 7th?
BTW I am using KSH shell scripting.