Allegro.cc - Online Community

Allegro.cc Forums » Off-Topic Ordeals » KSH SQL query to variables

This thread is locked; no one can reply to it. rss feed Print
KSH SQL query to variables
Steve Terry
Member #1,989
March 2002
avatar

I'm trying to do a simple SQL query in a Korn Shell script on UNIX where I need to assign the column output to three variables. The problem comes when the columns can have spaces.

For example:
my_var=`db2 -x "select column1, column2, column3 from foo where whatever"`

I was going to parse my_var using cut to cut out the column data, the problem comes from the fact that the SQL command is executed on the shell and then assigned to the variable with all extra spaces removed.

What I mean is that

COLUMN1    COLUMN2    COLUMN3
-------    -------    ----------------
foo        bar        this is column 3

becomes just "foo bar this is column 3"

this is pretty much impossible to parse without the column spacing.

Do any of you know a way around this or a better way other than redirecting the output to a file and parsing it?

___________________________________
[ Facebook ]
Microsoft is not the Borg collective. The Borg collective has got proper networking. - planetspace.de
Bill Gates is in fact Shawn Hargreaves' ßî+çh. - Gideon Weems

kazzmir
Member #1,786
December 2001
avatar

Will the first two columns contain data without spaces in them?

$ echo 'foo bar this is column 3' | awk -F" " '{print $1}'
foo
$ echo 'foo bar this is column 3' | awk -F" " '{print $2}'
bar
$ echo 'foo bar this is column 3' | awk -F" " '{print $3}'
this

Oops that last one didn't work.. ugh time to read up on awk.

Steve Terry
Member #1,989
March 2002
avatar

;D I tried awk already :P

___________________________________
[ Facebook ]
Microsoft is not the Borg collective. The Borg collective has got proper networking. - planetspace.de
Bill Gates is in fact Shawn Hargreaves' ßî+çh. - Gideon Weems

gnolam
Member #2,030
March 2002
avatar

Concatenate delimiters of your own choosing to the columns in the SELECT?

--
Move to the Democratic People's Republic of Vivendi Universal (formerly known as Sweden) - officially democracy- and privacy-free since 2008-06-18!

Crazy Photon
Member #2,588
July 2002
avatar

gnolam said:

Concatenate delimiters of your own choosing to the columns in the SELECT?

^ This, been there already with this problem and this is how I solved it. Just be careful which delimiter you use (don't pick one that might appear in the column values), and then you can tell awk to use it as a delimiter instead of whitespace.

-----
Resistance is NEVER futile...

Steve Terry
Member #1,989
March 2002
avatar

How to do that?

___________________________________
[ Facebook ]
Microsoft is not the Borg collective. The Borg collective has got proper networking. - planetspace.de
Bill Gates is in fact Shawn Hargreaves' ßî+çh. - Gideon Weems

Matthew Leverton
Supreme Loser
January 1999
avatar

select column1, 'X', column2 from foo where whatever
Give the string an alias if your DB requires that.

Crazy Photon
Member #2,588
July 2002
avatar

You can also use CONCAT() to produce just one column with the concatenation of the columns and delimiters, in case there are extra spaces between the column and the delimiter on the result of running the query.

-----
Resistance is NEVER futile...

Go to: