sql output into a variable

Shell Programming and Scripting
1

i have to do a check in my UNIX script to see whats saved in the database. depending on whats there will fork data to certain functions. However i do not know how to capture SQL output into a UNIX variable.
Below is what i have tried, but i get an error:
Error 3706 Failure 3706 Syntax error: expected something between the beginning of the request and the word 'var1'

#!/bin/ksh
##########################################################################
###
### - Poll_Form.ksh
###
##########################################################################
. /bto/CommonUtils.ksh
export TempFile="/export/TempFile.txt" ;
#################################################################################
CLLI=LENRNCHA75F
function IP_Check
{
echo "Checking to see if CLLI has a direct IP in SWIFT 6"
}
#a function saved in another script (CommonUtils.ksh) called here.
BTEQ ${LOADACCT} <<EOF
var1='SELECT IP_NUMBER FROM Table6_A WHERE SMGR_CLLI = '${CLLI}';'
EOF
#EXIT is another saved function in CommonUtils.ksh
.EXIT
##########################################################################
###
### Main
###
IP_Check
if [${var1} -eq ""]; then
echo "${var1}"
else
echo "try again"
fi
##########################################################################
EXIT 0

2

You haven't mentioned anything about the database. Is it Oracle, MySQL, DB2 ?
The technique of saving a database value in a Unix variable may vary according to the database involved.

Also, it's not clear what's there in "CommonUtils.ksh" or "BTEQ".

So I shall assume you have Oracle, you want to run a query passing a value to it and capture the output in a Unix shell variable.

A bash script for the same could be:

$
$ cat dbvalue.sh
#!/usr/bin/bash
num=7369
name=`sqlplus -s test/test << EOF
set heading off pages 0 feedback off
select ename from emp where empno = $num;
exit
EOF`
echo "num  = ${num}"
echo "name = ${name}"
$
$ . dbvalue.sh
num  = 7369
name = SMITH
$
$

tyler_durden

3

opps sorry,
ok the database is Teradata,
CommonUtils is another script that houses many function that we use all over the place. it allows us to keep others scripts shorter and if something changes only have to alter script in one place.
BTEQ is the login in function used to get into the database. it lives in Common utils and called here
i found one method late last night that worked.
i exported the results into a text file then used the following:

X=0
if [ -s ${TEMPFILE} ]; then cat ${TEMPFILE} | wc -l | read X; fi
....

i have some if statments after that to handle some login i need to do.
i'm happy with the result, i don't know if there is a more direct way to handle my question. if so i'd be happy to know.

4

Sorry, I have absolutely no idea about Teradata.
However, by looking at this:

X=0
if [ -s ${TEMPFILE} ]; then cat ${TEMPFILE} | wc -l | read X; fi
....

if all your TEMPFILE contains is a number/string with no newlines etc., then you may be able to just use accent-graves to run your method and assign the result to a shell variable:

X=`<the-method-that-worked-i.e.-everything-until-the-redirection-to-text-file>`

tyler_durden