How to get column names of a table using the bcp utility in Sybase?

BCP program or the bulk copy program utility is used to extract data from a table into a flat file but can we have the column names of a table along with the table's data in the file?

The bcp utility does not offer a switch to extract column name so we need to have a workaround to achieve the same and what could that be? So, to tackle this situation here is Views to the rescue. To explain this concept we will be creating a table Employee with the following definition -

create table Employee
(
   EmpId int,
   EmpName varchar(100),
   Age int,
   Salary float
)
go

Now, we will create the following view -

create view V as
select
  "EmpId",
  "EmpName",
  "Age",
  "Salary"
from
  Employee
where 1=1
union
select
  convert(varchar, EmpId),
  EmpName,
  convert(varchar, Age),
  convert(varchar, Salary)
from
  Employee
go

As we can see here that we have created a view V which will do a union of column names and columns data. On executing this view we get the column name and the data both in a single resultset.

Now what remains is running the bcp command to extract the data to a flat file and the command to accomplish this is as follows -

bcp Database.Owner.ViewName out FilePath -SSERVERNAME -UUSERNAME -PPASSWORD -c -t,

and thats it.. We will get the column name and the data in a flat file simply by creating a view. Isn't it simple?

Types of Temporary tables in Sybase and what is the difference between them?

How many types of temporary tables are there in Sybase? How often have you encountered this question? Pretty much... Right? Yup! And here is the answer to much liked question related to Sybase ASE.

Sybase Adaptive Server Enterprise allows developers to create two different types of temporary tables namely -

1. Session Temporary Table
2. Tempdb temporary table

In general both of these table are created in tempdb database however there are few differences between the two.

Will addition or removal of columns in a table effect the view created on the same table in Sybase ASE?

Wonder what will happen when a view is created on a table but later on the table column list gets altered using the Alter Table command. Will the view automatically get updated with the new/deleted columns in the table?

Well the answer to this is "OfCourse No". Why? The answer to this is much simple than what might appear. We all know that a view is a simple database object which has its definition stored in the syscomments system table and does not store any of the information anywhere in the database. This is opposite to what a table does. Now when a table gets altered and a column which was earlier present in the table has been dropped then on envoking the view the Sybase ASE server will try to check the view definition which will still be holding the column which had been deleted from the table and hence will give us an error that the required column does not exists.

Lets demonstrate the above concept with a simple example -

How to create a temporary table with variable name in Sybase ASE?

If someone asks you if we can create a table with variable name or with a runtime generated name, then what would be your answer?

Ofcourse as you are reading this article so the answer should be - "Yes ofcourse". The rest of the article will tell you how to do the same. First let me tell you that ofcourse this approach has a short coming of its own but its fun to learn how we will achieve this thing and the shortcoming is that in case we run same script or stored procedure from two different sessions at nearly the same time then one of the session might get error that the table already exists in the system catalogue. Okay, so lets get back to the approach and you will yourself be able to understand the short coming that I have listed here.

So, coming back to work, the situation with us is as follows -

Situation - We want to create a table which will created and dropped on each session but we do not want to use the session temp table.

With the above problem statement we are sure about one thing and that is - We need to create table uniquely.

Using waitfor time command in Sybase ASE or How to run a stored procedures from different sessions simultaneously at the same time in Sybase ASE?

Sometimes while working with stored procedures it becomes important for us to test some scenario's where the same stored procedure needs to be run from two different sessions at the same time. But what to do in this case? We wont be able to open two different sessions and manually run the same stored procedure at the same time. So what to do in this case?

Well, nothing to fear in this scenario as Sybase already provides a command to enable a wait or make the session sleep for sometime. The waiting command is very simple and can be used in two versions -

1. waitfor time <<At what time should session run>>
2. waitfor delay <<Till what time should the session wait>>

As we can see the waitfor time command when encountered in the process flow the session is halted until the given time is reached. Example, Lets say we run a script like this at '10:20 am'.

use MyDb
go

select * from MyTab

waitfor time '10:25'

update MyTab set Id = 10 where Id = 5

select * from MyTab
go

Now when we ran the script at '10:20 am' then the script runs through all the parts of the script until it reaches the command -

waitfor time '10:25'

When the control reaches this statement the session is put on hold till system time is equal to '10:25 am' and the rest of the session executes exactly (with minor delay) at the given time. So, if we open two sessions at the same time and write waitfor time as the first line of the script then we will be able to run same stored procedure from two sessions at the same time.

Create table within if exists block in Sybase ASE?

So, how is it that you try to check the existance of a sybase table. Well the answer is pretty simple. Fire the following query and you are right into what you want -

if exists(select 1 from sysobjects where name = <<Table Name>> and type = 'U')
begin
  <<SQL statements>>
end
go

Okay, so we know how to check the existance of a table, but what if we say that we want to check if a table exists in system catalogue or not and if not then create the table. What to do in this case? Well the obvious syntax that comes o our mind as first thought is as follows -

if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
  create table MyTab
  (
     Id int,
     Name varchar(100),
     Age int
  )
end
else
begin
  print 'Table already exists'
end
go

Well, is it correct? I dont think so. Why? Because if in case MyTab table was already existing in the sysobjects table and we run this query then we will get the error that table MyTab already exists in the system catalogue. So what is wrong with the statement?

To understand this we need to look deeper into how Adaptive Server Enterprise tries to execute the above query. Well in above case when we fire the if exists clause and define the create table syntax after it, the server first tries to check the presence of the MyTab table in the system catalogue and once it is found the server further finds that the next statement tries to create a table with a name that is already present in the system catalogue and hence throws the error.

In case if we would have defined a drop table command within the if exists block then the command would have run successfully as in that case the server checks if the required table is already in the system catalogue and if so tries to drop it which is prefectly valid. The drop command is as follows -

if exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
  drop table MyTab
end
else
begin
  print 'MyTab does not exists.'
end
go

So, coming back to the question - What to do in case we require to create table within the if exists block in Sybase ASE? Is there any way by which we can achieve this?

Well the answer is - Yes, ofcourse. How? Lets figure it out...

How to change a User Defined Data Type?

User defined data types or UDT's allow a database developer to define their own user defined data types over the base data types available in Sybase ASE. Adaptive Server Enterprise the enterprise version of RDBMS offered from Sybase Inc took care of developers requirement to define new data types via the user defined data types. Though a developer cannot completely define a new data type in Sybase but the developer can still define a customized version of data types to meet their customer requirements.

Lets say we use 250 character length too frequently to meet our customer requirements then we can define a user defined data type 'TEXT' to meet this requirement and use 'TEXT' instead of varchar(250) data type. A new data type can be defined via the following syntax -

sp_addtype TEXT, varchar(250), null

Apart from customizing the data type UDT's basic requirement is to provide a wrapper to the base data types so that in case the below mentioned data type gets changed the UDT remains the same. However, this statement has a major catch -

The catch is that once we define a column specific to a UDT Sybase does not provide any option to make amendments to this UDT and hence there is nothing like - sp_modifytype which will help us change the UDT - What if we need to amend our TEXT (varchar(250)) to TEXT (i.e. varchar(255))?

So, what to do in this scenario?

How to get table data in a file in Sybase ASE?

BCP or the Bulk Copy Program is a utility that is provided in Sybase ASE to help the developer get a table's data as output to a file. With the use of bcp utility we can get the data from a table into a file and restore this data back into the table at any later stage.

The bcp command can be used to input and output data from a file to a Sybase table or vice-versa. This is suggested via the use of two keywords - in and out for input and output respectively. The syntax for bcp utility is as follows -

For native mode -

bcp DATABASENAME.OWNERNAME.TABLENAME out “File Path with File Name within quotes” –SSERVERNAME –ULOGINNAME –PPASSWORD –t, -n


For character mode –

bcp DATABASENAME.OWNERNAME.TABLENAME out “File Path with File Name within quotes” –SSERVERNAME –ULOGINNAME –PPASSWORD –t, -c

As mentioned above in case we need to output the result in character mode then we need to use -c option in the bcp command however if we need to output the data in native mode then we use the -n option. Apart from this basic syntax we can also define format file for the data transfer using the -f option.

How to get a SQL query output in a file, Sybase ASE?

While working in support projects for sometime I often realized that it is good to have results of a SQL Query as output in a file for later reference. So, if you do a data fix today and someone later asks you to confirm that the issue was infact fixed by you then you can show them the output file with SQL query output and tell them - "Here it is..."

So now as we have defined one of the purpose of getting SQL query output into a file lets show you how to do the same.

Lets say there is an Employee table with the following structure -

create table Employee
(
  EmpId int,
  FirstName varchar(100),
  LastName varchar(100),
  Age int,
  Salary float,
  ManagerId int,
  DeptId int
)
go

Now lets say this table is having around 10,000 records and recently our Department 'Accounts' with DeptId as 1 has a new manager 'TOM' with ManagerId as 2. So we try to update the table Employee using the following query -

update table Employee
set ManagerId = 2
where
  DeptId = 1
go

Now after running this script we check our data fix and write the following query -

select * from Employee where DeptId = 1
go

After running this SQL query we see that the output is as expected and the new manager 'TOM' has been set correctly for Department 'Accounts' but we do want to keep a proof of this and so we open the isql in the command prompt (for Windows) and write the following to get the output of this result directly to the file, so we write the following command -

isql -S<<SERVER NAME>> -D<<DATABASE NAME>> -U<<LOGIN NAME>> -P<<PASSWORD>> -i<<INPUT SQL FILE PATH>> -o<<OUTPUT RESULT FILE PATH>> -w1000 -n

and we are done with it. The above statement must be written in the command prompt or UNIX prompt and not within an isql session. Also, in the above statement remove the <<>> markers to execute the statement with actual parameters.