CODEWITHSUNDEEP
sqlshelldb2
springpress
springpress

Reputation: 126

Capture db2 sql results in shell script

I have a shell script which will connect to database and get the results. My script is like

#!/bin/bash
getResults()
{
    db2 "connect to ${1} user ${2} using ${3}"
    db2 "set schema ${4}"
    status=`db2 -x "select status from results where id=1"`
    echo $status
}
#MAIN STARS HERE
getResults dbname foo bar test

Now I wanted to get multiple columns from results tables using 

select status,timestamp from results where id=1

How can I run the above query and capture both status and timestamp into two different shell variables using single query instead of running 2 differnt queries like

#!/bin/bash
getResults()
{
    db2 "connect to ${1} user ${2} using ${3}"
    db2 "set schema ${4}"
    status=`db2 -x "select status from results where id=1"`
    echo $status
     timestamp=`db2 -x "select timestamp from results where id=1"`
    echo $timestamp

}
#MAIN STARS HERE
getResults dbname foo bar test

My results table is like:

create table (id number, status char(1), timestamp datetime);

And data is like

1 P <some ts>
2 F <some ts>

Thanks in advance!

Upvotes: 1

Views: 20220

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

The problem is that the database connection you make in the getResults function is not visible to the subshell (i.e. when you call db2 -x). Using backticks invokes a new shell.

To make this work you need to keep your query in the same shell:

db2 "connect to ${1} user ${2} using ${3}"
db2 "set schema ${4}"

db2 -x "select status,timestamp from results where id = 1" | while read status timestamp ; do
    echo $status
    echo $timestamp
done

Note that with the while loop here you will output multiple lines if your query returns more than 1 row. It's easy to modify the SQL to return only 1 row.

Upvotes: 2

Related Questions