• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

pull data from sybase

I have a query in sybase (sql)  and i want to pull the data from that sql to a flat file using a shell script.

Just as we use sqlplus to pull oracle data and spool it to a file.

How to do this?
1 Solution
Jan FranekCommented:
You may use isql or bcp.

isql lets you run any query or procedure, but output will contain some clutter (names of columns, horizontal dividing lines, number of returned records etc)

bcp cen give you clear data, but you can output only tables or views (so you have to store your query as a view and then you can use bcp).

See http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/title.htm for detailed explanation of both these utilities.
I'm not as familiar with Sybase as with Oracle, but I do use dbisql just a bit, here is what I know

I have a script tables.sql to list the tables in my Sybase database

select * from sysobjects where type = 'U'

dbisql -c "UID=dba;PWD=sql" tables.sql > results.txt

Open in new window

vishali_vishuAuthor Commented:
is it possible to pull to pull few columns from a table (sybase table).

ex: Table_x (col_1,col_2,col_3,col_4,col_5).

can i use the bcp to pull only col_2 and col_5 ?

Bcp is a very flexible tool. You can use bcp with a format file and specify what columns to be included, format of them and delimiters. Basic syntax of bcp:

bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
        [-m maxerrors] [-f formatfile] [-e errfile]
        [-F firstrow] [-L lastrow] [-b batchsize]
        [-n] [-c] [-t field_terminator] [-r row_terminator]
        [-U username] [-P password] [-I interfaces_file] [-S server]

You can use bcp with "out" option and without -f -c or -n options to create a sample format file. Bcp asks you the datatype, data length etc. for each column. Then you can save the format file at the end. After then you can edit format file by removing undesired columns. Be careful that it inlcudes number of columns at the beginning. You need to change it as well.

Or you can create a format file by checking Sybase manuals.
By the way, if you want a quicker solution without more flexilbility, creating a view including only these columns would be easier.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now