Shell Programming and Scripting

I'm running isql in sybase on a Linux box to read a table, and write the contents to a file. It needs to be a nice comma delimited file for input into another system.

It reads the DB fine and writes the file.
However, I have two issues.

1) I don't want the line width to be limited in any way. Apparently the default is to 80 chars... I'm using the -w switch to set the width to 99999999999, but really I don't want any limit. We have a comment field that can get very lengthy. Is there any way to just turn off the line width and allow it to write a line no matter how long it is? Then it can line break (or carriage return or whatever) for the next record.

2) For some reason when I write the record to the file, it seems to be writing the length of each field to be twice as long as it really is. Example... the first field is a char(20) field, but it's writing to the file as 40 characters with a bunch of white space after the first 20 chars. Playing with it a bit, if I do a select on the first field with:
SELECT substring(REC_CREATED_DATE,1,5) FROM table
It writes the first 5 characters to the file, but it also writes enough white spaces to fill in that field to 10 chars. I don't know why it's doing this and I don't want that. If I use substring(REC_CREATED_DATE,1,10), it writes 10 chars and 10 white spaces for 20 characters. If I just use the field with no substring, it writes 40 characters.

If I try to use trim(), it gives me an error, so apparently Sybase isql doesn't have trim as a function. I try ltrim and rtrim, but they don't do anything, I still have the white space on the right of the output.

I would love some help before I pull all my hair out.
Thanks!
Cynthia

---------- Post updated at 09:39 AM ---------- Previous update was at 09:31 AM ----------

Here's my isql string if anyone is interested:

Code:

isql -U$user -w9999999999999 -P$pass -S$serv -b -s, -oXFER.txt < SelectXFER.sql

Welcome to the forum.

It is best to use bcp for playing with file in sybase. isql lakes some useful features which I felt, useful for dealing with files (or may be I am not aware of that). Most of the time I need to format the resulting file externally with tools like awk,sed etc..

If I want the output of a query (or a .sql file), I would first export the data in a view then use bcp to export to file.

Okay thanks, I will look into using BCP instead. That might work better for me.

---------- Post updated at 01:52 PM ---------- Previous update was at 01:33 PM ----------

I have some comment fields in the table, and when I extract them (either with BCP or ISQL), the carriage returns show as carriage returns in the output file. I don't want them to have carriage returns in the output file. I only want a line feed/ carriage return at the end of each record/line.

Is it possible to remove the LF CR in the comments field with BCP, or will I have to pipe it through awk or grep, neither of which I'm good at at all?

And if I use awk or grep, I'm not sure how I'd leave some of them in, and remove some of them... hrmph.
Thanks.

---------- Post updated 05-31-12 at 10:59 AM ---------- Previous update was 05-30-12 at 01:52 PM ----------

I'm trying BCP... however, when I try to use selection criteria in the SQL select statement, and use queryout, I get an error. So... is it required that i use a view? I am not able to get the exec command to work, either... but I haven't tried using a view. I'd prefer to just not use a view but maybe that doesn't work.

This line gives me the queryout syntax error:

Code:

#bcp "select * from DBname..TableName" queryout out1.txt -S$RETserv -U$RETuser -P$RETpass -c -t!~ -r"0x0d"