How to ignore * (asterisk) in a variable

bradtri2 · #1 (**permalink**) 06-05-2008

I am using a shell script to read SQL statements stored in a DB2 table and write them out to a file. The problem I have is that some SQL statements have an "*" in them which gets resolved as the list of files in the current directory when I run the script. How can I prevent the "*" from being resolved??

Actual SQL: SELECT * FROM RTDS.TSPSTOPS

Result SQL: SELECT gen.out gen.sql gen2.sql getsql.sh getsql2.sh sqlrow.out sqltxt.out test.sh FROM RTDS.TSPSTOPS

Code Snippet:

sqlrow=$(db2 +c -x fetch from c1 )
fetchrc=$?
echo fetchrc = $rc

echo ${sqlrow}

fabtagon · #2 (**permalink**) 06-05-2008

Quote:

sqlrow=$(db2 +c -x fetch from c1 )

No variable in here => no expansion will take place. Maybe your example is too short? Do you in reality have something like "x=$(db2 $var fetch from c1)"? In this case surround $var with quotes.

joeyg · #3 (**permalink**) 06-05-2008

Code:

> line1="ls *"
> echo $line1
ls 50964hdr.txt bin cbin dead.letter greetings junk.tmp masters orictl sample tmp

this seems to be what you were referring to

Code:

> echo "$line1"
ls *

this 2nd example is what I think you are trying to get

bradtri2 · #4 (**permalink**) 06-05-2008

My apologies for my example not being very clear. The "Actual SQL" shown in my OP is the value that the fetch will return into the shell variable "sqlrow". Since the Actual SQL has an "*" in it, when I echo $sqlrow, the * gets resolved and substitutes in the list of files from the current directory.

So, duh, I was able to resolve that by putting double quotes around the variable on the echo like : echo "$sqlrow"

Now, the plot thickens a bit. The sql is stored in a varchar(32000) on the source table. When I put the double quotes on the echo, I now get a record echo'd out that is 32000 bytes long. (The actual SQL statements will range from 50 - 10000 bytes). So, I don't want to write out all that extra space on the end of each record.

I can get the length of the sql, and I'm now trying to substr it in awk as follows where $rowlen contains the length of the sql:

sqltxt=`echo "$sqlrow"|awk '{print substr($0,1,$rl)}' rl=$rowlen`

However, I'm now getting the message "awk: record ` SELECT * F...' too long"

What is the best way for me to either suppress the extra spaces on the end of each record or to substr out the actual SQL??

joeyg · #5 (**permalink**) 06-05-2008

tr -s " "
will suppress extra " " space charcters

Code:

> line2="This  2spaces   3spaces    4and5after     *"
> echo "$line2"
This  2spaces   3spaces    4and5after     *
> echo "$line2" | tr -s " "
This 2spaces 3spaces 4and5after *

shew01 · #6 (**permalink**) 06-05-2008

Quote:

The sql is stored in a varchar(32000) on the source table. When I put the double quotes on the echo, I now get a record echo'd out that is 32000 bytes long.

If the column is defined as varchar(32000), I don't understand why you are retrieving what appears to be full width columns. I'm using Oracle instead of DB2, but the principle should be the same.

Code:

create table my_table
	(sql_statement varchar(1000));

insert into my_table values ('select * from table2');

select sql_statement || '#' from my_table;

Here is my output:

Code:

SQL_STATEMENT||'#'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from table2#

1 row selected.

Have you tried trimming the output as you select it from the DB2 database?

Code:

select trim(sql_statement) || '#' from my_table;

The output is the same (i.e., no trailing spaces for the data), unless the long line of dashes is giving you a problem:

Code:

TRIM(SQL_STATEMENT)||'#'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select * from table2#

1 row selected.

shew01 · #7 (**permalink**) 06-05-2008

It's been a while since I worked with DB2, but, the db2 command line verb may have a switch that would suppress the output of padded spaces, if it (i.e., the db2 command line verb) is the culprit. Do you get padded spaces when you run the query interactively? Say, from within Toad?

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Asterisk on IPCop Asterisk 1.4.20 for IPCop 1.4.18 (Default branch)	iBot	Software Releases - RSS News	0	05-21-2008 08:40 PM
Asterisk on IPCop Asterisk 1.4.19.2 for IPCop 1.4.18 (Default branch)	iBot	Software Releases - RSS News	0	05-14-2008 07:20 AM
Asterisk on IPCop IPCop 1.4.18 with Asterisk 1.4.19.1 (Asterisk IPCop ISO branch)	iBot	Software Releases - RSS News	0	04-26-2008 04:10 AM
Asterisk on IPCop Asterisk 1.4.19.1 for IPCop 1.4.18 (Default branch)	iBot	Software Releases - RSS News	0	04-25-2008 04:10 PM
passing asterisk to a script as variable	GKnight	Shell Programming and Scripting	9	04-02-2008 10:32 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: