Showing posts with label sybase. Show all posts

Row width and Column separator in Sybase isql

Many of you either donot know or perhaps you are not aware of these since there is very less documentation available in google search for these

In general I use:
isql -U<user> -P<passsword> -D<dbname>

There are two ioptions in isql.
which are 
 -s Column separator.This wil separate columns with what ever character is followed by this option another option is (and probably very important): -w This will set the row width.By default is set to 80 characters and if the row length exceeds 80 characters, a new line is added in the console output.
for eg:
 isql -U<user> -P<passsword> -D<dbname> -s ',' -w 65535
Read More

List of columns in a table from SYBASE

I recently came across a need to create a file which has the columns of a table in sybase.Then I thought it would be better that we write a script which will fetch the column details by taking table name as an argument and store the output in a text file.Below is a simple perl script to do it.

As you can see help text is also there.So i guess i don't need to explain it.

#!/usr/bin/perl
######################################
#This is the script to list all the
#columns in the table.table names
#should be given as arguments to the
#script.
#example:
#script_name table_name_1 table_name_2 ...so on
######################################
use strict;
use warnings;
my $result;
unlink("output.txt");
foreach(@ARGV)
{
$result = qx{isql -U<user_name> -P<password> -D<dbname> <<EOF
set nocount on
SELECT sc.name FROM syscolumns sc INNER JOIN sysobjects so ON sc.id = so.id WHERE so.name = '$_'
go
exit
EOF
};
my @lines = split /\s+\n/, $result;
splice @lines,0,2;
$_=~s/ //g foreach @lines;
my $outfile  = "output.txt";
open (OUTFILE, ">>$outfile") || die "ERROR: opening $outfile\n";
print OUTFILE "$_\n------------\n".join "\n",@lines;
print OUTFILE "\n\n";
}
close OUTFILE;
################################################################################ 
Read More

Executing Sql query in a Perl script

Below is the example. Here we can execute a sql query or a sybase query in a perl script. Please also note that I am not utilizing the DBI module provided by perl. 
 #!/usr/bin/perl use strict; use warnings; my $result = qx { isql -Uxx -Pxxxxxxx -Dxxxx <<EOF set nocount on select count(*) from XXX go exit EOF }; print $result;
Above example show connecting to the sybase database. If you are using Oracle DB, then you can change the connection string: 
 isql -Uxx -Pxxxxxxx -Dxxxx

to 
 sqlplus....
Note:
One strange thing over here never add spaces at the begining of the line after the statement isql.....<<EOF
the query will not be executed in such case you will go nuts trying to figure out why!
Read More
Subscribe to: Comments (Atom)