Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Execute stored procedure through script in sybase database and store the output in a .csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Execute stored procedure through script in sybase database and store the output in a .csv file
# 1  
Old 07-01-2010
Bug Execute stored procedure through script in sybase database and store the output in a .csv file
Hi,

I have a sybase stored procedure which takes two input parameters (start_date and end_date) and when it get executed, it gives few records as an output.
I want to write a unix script (ksh) which login to the sybase database, then execute this stored procedure (takes the input parameter as end_date = getdate and start_date = getdate - 7 days), and export the output of this stored procedure in a .csv file.
I am very new to shell programming and having good knowlwdge of unix commands only. Can anyone please help me out with this kind of detailed unix script?

---------- Post updated 07-01-10 at 01:52 AM ---------- Previous update was 06-30-10 at 03:01 PM ----------

Can any one please help me out with above issue?

Thanks.
# 2  
Old 07-01-2010
see the below code might help u:-
Code:
while getopts S: E: PARA
do
case ${PARA} in
S) START_DATE=$OPTARG;;
E)END_DATE=$OPTARG;;
*) ERR="please pass correct filed"

(then check for valid dates if u want to) and also for empty dates
if [[ -z ${start_date} ]]
then
echo "dates not populated"
fi

call to isql for taking data.
(
isql -S${SERVER} -D${DB} -U${C_USER} -P${PWD} -b0 <<END > output_file
(the values of SERVER,DB, USER  and PWD - you should read from some file or mention above in your script.)
set nocount on
go
select (whatever your queries are)
go SQL
)

this will help you in achieving what you looking for.
# 3  
Old 07-01-2010
Thanks for the reply.
But I still want to know how i can export it in a spreadsheet (.csv file)
# 4  
Old 07-01-2010
change the output file name into output.csv that should work for u
# 5  
Old 07-07-2010
Question
hi.

I have tried this but no luck because I need to pass the parameters also to the stored procedure from the unix script.
For more details, below are the details, I need to do:
1.) Connect to the database by loginid and username
2.) Call the procedure and pass the input parameter that is $end_date = getdate and $start_date = getdate - 7 days
3.) Procedure has to execute based on the above parameters
4.) Output should store in a .csv file.

Please assist.
# 6  
Old 07-07-2010
Sybase does not have build-in support for outputting queries in CSV format. BCP has support for CSV but you probably do not want to down that route.

Here is one way of outputting the result of your query in CSV format. I will use the Sybase pubs2 example as the database. (the pubs2 example comes with all versions of Sybase).

Here is the contents of my shell script
Code:
isql -Uname -Ppassword -Sserver -Dpubs2 -b -h -w200  <<EOF | sed -e 's/ //g'
select '"',au_id,'","',au_fname,'","',au_lname,'","',phone,'"' from authors
go
EOF

Here is the output generated by the shell script
Code:
"172-32-1176","Johnson","White","408496-7223"
"213-46-8915","Marjorie","Green","415986-7020"
"238-95-7766","Cheryl","Carson","415548-7723"
"267-41-2394","Michael","O'Leary","408286-2428"
"274-80-9391","Dick","Straight","415834-2919"
"341-22-1782","Meander","Smith","913843-0462"
"409-56-7008","Abraham","Bennet","415658-9932"
"427-17-2319","Ann","Dull","415836-7128"
"472-27-2349","Burt","Gringlesby","707938-6445"
"486-29-1786","Chastity","Locksley","415585-4620"
"527-72-3246","Morningstar","Greene","615297-2723"
"648-92-1872","Reginald","Blotchet-Halls","503745-6402"
"672-71-3249","Akiko","Yokomoto","415935-4228"
"712-45-1867","Innes","delCastillo","615996-8275"
"722-51-5454","Michel","DeFrance","219547-9982"
"724-08-9931","Dirk","Stringer","415843-2991"
"724-80-9391","Stearns","MacFeather","415354-7128"
"756-30-7391","Livia","Karsen","415534-9219"
"807-91-6654","Sylvia","Panteley","301946-8853"
"846-92-7186","Sheryl","Hunter","415836-7128"
"893-72-1158","Heather","McBadden","707448-4982"
"899-46-2035","Anne","Ringer","801826-0752"
"998-72-3567","Albert","Ringer","801826-0752"

# 7  
Old 07-07-2010
Question
Hi.
Thanks for your reply but I donot have a single statement rather I need to execute the procedure and need to put output into a .csv file.
Also, let me know how I can pass the parameters also through unix script in for sybase database.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. How to Post in the The UNIX and Linux Forums

Calling a Sybase Stored procedure from a UNIX Script

Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File.somehow i try to find a solution but whwn i try to run the script i am not getting the output file with... (1 Reply)
Discussion started by: Arun619
1 Replies

2. Shell Programming and Scripting

ksh and Oracle stored procedure output in logfile

Friends, I pass some runtime arguments (date, number) through ksh script to Oracle procedure, use input value and pass it on to procedure. Oracle procedure gets input value, run query and logs everything in the logfile. I'm facing with couple of challenges 1. Even though I pass all... (5 Replies)
Discussion started by: homer4all
5 Replies

3. Shell Programming and Scripting

Unable to execute Stored Procedure from CRON

Hi, I am very new to this environment - I hope this is the right platform to discuss my issue: I created a CRON job to run a Stored Procedure from our database - Sybase. Within the Stored Procedure there is a TRUNCATE table and CREATE table function. the CRON job fails to run with... (2 Replies)
Discussion started by: pizzazzz
2 Replies

4. Shell Programming and Scripting

Shell Linux to connect to a database and execute store procedure

HI, i want to write a script (Linux) that: 1) connect to a database oracle 2) execute some store procedure. Can anybody help me, please? Thanks a lot Andrew (3 Replies)
Discussion started by: manichino74
3 Replies

5. Shell Programming and Scripting

How to execute the stored procedure from shell script

How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies

6. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak (0 Replies)
Discussion started by: jakSun8
0 Replies

7. Shell Programming and Scripting

how to store the return values of stored procedure in unix shell script.

hi i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh). the called stored procedure returns some values through OUT variables i want to assign the return values of stored procedure in to unix shell script variable. can you provide me the code. ... (1 Reply)
Discussion started by: barani75
1 Replies

8. Shell Programming and Scripting

How to compile a stored procedure that is there with in a script file(.sql) in unix

Hi, How can i compile the procedure code that is there in a script file (.sql) in unix. (0 Replies)
Discussion started by: krishna_gnv
0 Replies

9. UNIX for Dummies Questions & Answers

execute store procedure

Hi, can someone point out the tutorial or syntax for how to execute store procedure through shell scripting . thanks. (7 Replies)
Discussion started by: epall
7 Replies

10. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies
Login or Register to Ask a Question