Visit Our UNIX and Linux User Community


sql output into a variable


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sql output into a variable
# 1  
Old 09-08-2009
sql output into a variable

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  
Old 09-08-2009
Quote:
Originally Posted by purplebirky
...
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'

...
. /bto/CommonUtils.ksh
...
#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
...
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:

Code:
$
$ 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  
Old 09-09-2009
Bug

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  
Old 09-09-2009
Sorry, I have absolutely no idea about Teradata.
However, by looking at this:

Code:
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:

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

tyler_durden

Previous Thread | Next Thread
Test Your Knowledge in Computers #484
Difficulty: Medium
A compiler transforms source code into object code, a floating point number format that machines understand.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to Assign the Output of an SQL Query to a Variable?

Hi iam new to shell scripting how to declare variables as redshift query and I have to compare two counts by using if condition . ex:count=select count(*) from prd; select count(*) from prd; select count(*) from tag; can any one help me . Please use CODE tags when displaying... (1 Reply)
Discussion started by: sam526
1 Replies

2. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

3. UNIX for Advanced & Expert Users

Passing variable as input & storing output in other variable

I have a below syntax its working fine... var12=$(ps -ef | grep apache | awk '{print $2,$4}') Im getting expected output as below: printf "%b\n" "${VAR12}" dell 123 dell 456 dell 457 Now I wrote a while loop.. the output of VAR12 should be passed as input parameters to while loop and results... (5 Replies)
Discussion started by: sam@sam
5 Replies

4. Shell Programming and Scripting

SQL output to UNIX variable

I have a sql statement , i need to assign to a variable in Unix sel count(*) AS num_files from TABLE_A; i need to use "num_files" in unix statements. let me know how to assign unix variable to above num_files (1 Reply)
Discussion started by: nani1984
1 Replies

5. Shell Programming and Scripting

Redirect output from SQL to unix variable

Hi, I have a requirement to store oracle sqlplus output to some unix variable, count the records and then print the output on the screen. Can you please point me to any sample program for reference purpose. Thanks a lot for your time. (0 Replies)
Discussion started by: bhupinder08
0 Replies

6. Shell Programming and Scripting

Awk script to run a sql and print the output to an output file

Hi All, I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file. Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
Discussion started by: adept
4 Replies

7. Shell Programming and Scripting

sql output to shell variable

Hi I tried searching but I used to see , this has been answered may times. But I could not find. I am looking a SQLPLUS output to be assigned to shell variable. This is what I coded. ##/bin/ksh sqlplus dmishr/mishra#09@ps004d.world <<ENDOFSQL spool abc.log SET ECHO OFF NEWP... (4 Replies)
Discussion started by: dgmm
4 Replies

8. Shell Programming and Scripting

Assigning value of SQL output to a variable in shell scripting

I am trying to assgn the output of the select statement to a variable, like this "VARIABLE_NAME=$ db2 "select COLUMN_NAME_1 from TABLE_NAME where COLUMN_NAME_2='VALUE_TO_CHECK'"; " but the value that is getting into VARIABLE_NAME is "COLUMN_NAME_1 ----------------- VALUE 1... (3 Replies)
Discussion started by: sgmini
3 Replies

9. Shell Programming and Scripting

How to store the sql query's output in a variable

Hi, My requirement is : We are calling an sql statement from a UNIX session, and fetching data into some variables from a table .. now we are unable to access these variables from outside the SQL part. Please let me know how can I achieve this. Can you please share a code snippet which... (4 Replies)
Discussion started by: venkatesh_sasi
4 Replies

10. Shell Programming and Scripting

how to assign sql output data to shell script variable

Hi Guys ! I am new to unix and want to find out how we can make sql statement data to shell script variable? Any help/suggestion is greatly appreciated -Chandra (1 Reply)
Discussion started by: kattics
1 Replies

Featured Tech Videos