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.

see the below code might help u:-

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.

Thanks for the reply.
But I still want to know how i can export it in a spreadsheet (.csv file)

change the output file name into output.csv that should work for u

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.

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

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

"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"

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.

What part of the example I provided you with do you not understand? I showed you how to output an ISQL query in CSV format. If you want to put the output in a file, just redirect the output to a file.

Hi,

Can anyone help me out on this?
I have a sybase stored procedure (say x), when I write isql to execute this procedure within the script, it generates output and put in a .txt file as I have mentioned.
I want to know two things here:
1.) How I can convert this .txt file in .csv format (Please give me elaborated details step by step as I am new to scripting)
2.) How I can ftp this file from one server to another through the same unix script.

Please reply urgently.
Thanks:confused:

---------- Post updated at 09:51 AM ---------- Previous update was at 09:49 AM ----------

For fpmurphy: I had tried to direct it to .csv but the same format of .txt appears in the .csv as well. The data is not saparated by different cells.
Please see my above post for more clarification.

Thanks