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 ----------
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.
Hi Gurus,
I am very new in Unix,
I have 1 script, in which I am truncating the table , then BCP the data in Sybase table, and then loading the data from sybase table to sybase table.
every thing is working fine, but the problem is with Error.
I made some hanges in my insert statement so... (3 Replies)
Hi all,
I have wrote a script to redirect the output of Sybase query to a file.
Its a single XML message i am fetching with sybase query.
The problem i faced here is the XML message line crosses the normal line limit in unix.
Could some please help me how to get complete xml message... (1 Reply)
Hi,
How do i get result set in perl script using isql while connecting sybase server.
I have a perl script which connected to sybase and get the result set. but i wanted to get the result set. How do i get the result set in perl script not unix shell script..
$server ="ServerName";
open... (1 Reply)
Hello Folks,
I want to get the results from a SQL query which needs to be exported to a .txt file.
My Script is something like
#!/bin/ksh
db2 connect to DATABASE user user_name using pwd;
touch test.txt
isResult=0;
isResult= `db2 -x select 'ABC',COL_B from TABLE_A WHERE COL_B=CONDITION`... (6 Replies)
#!/bin/ksh
file="/pkgs/roots/scripts/ISQL_op.txt"
isql -H 11.11.11.111:1111 -U myUser -P myPwd -o $file << eof
go
select * from Table1
go
eof
my cron entry
00 08 03 11 * /pkgs/roots/scripts/testc.ksh
file permission of the script is correct, i have used absolute path everywhere.
... (2 Replies)
Hello All,
I am trying to remove all tabspaces and all blankspaces from my file using sed & awk, but not getting proper code. Please help me out.
My file is like this (<b> means one blank space, <t> means one tab space)-
$ cat file
NARESH<b><b><b>KUMAR<t><t>PRADHAN... (3 Replies)
Hello,
I am using suse 10.1 and isql from unixodbc to connect to a MS SQL server. I got everything to work fine. What do I need to unload the results from the sql to a file? I attempted to put unload to in my sql statement but got a error. I don't see in the isql help where it has a option to... (0 Replies)
I've got:
isql -U $USERID -S $SERVER -D $DATABASE -i inputfile.sql -o outputfile.txt
in inputfile I have:
go
sql#1
go
sql#2
go
sql#3
go
I also tried without "go" and with";" instead which did not work
SQL statements will work if I paste them directly into the script and use EOF
... (0 Replies)
How to get the data from the sybase database and append the data obtained into the file.
For example,
I will get the filename 'temp' from the database. This filename is associated with the number 1.6... This number 1.6 needs to be copied into the file that matches the filename in the... (1 Reply)
Hello,
I have extracted the first line of the syslogins file and wish to use this as an example but the field change applies to the whole file. I wish to change the second field for all lines from 0 to 2 , the 0 field is constant for all lines. Also I wish to change the encrypted password field... (0 Replies)