Hope you needed sybase
#!/usr/local/bin/perl5
################################################################################
#
# NAME
# generic.pl
#
# SYNOPSIS
# generic.pl [-S server | -H hostname -p port] [-d database] [-U user]
# [-P password] [-l log]
#
# generic.pl -h
#
# DESCRIPTION
# This is a generic program for accessing Sybase databases (hence the
# name). Use this as a template for writing scripts that actually do
# something useful.
#
# OPTIONS
#
# d database The name of the database from sysdatabases.
#
# H hostname The hostname (when connecting via hostname
# and port number).
#
# h Print the usage message. (That's all the help
# you get!)
#
# l log The pathname to a log file or directory in
# which to save it.
#
# P password The Sybase password.
#
# p port The TCP port number (when connecting via host-
# name and port number).
#
# S server The SQL server name from the interfaces file.
#
# U user The Sybase login name from syslogins.
#
# NOTES
# The script is written to be executed on both Solaris and NT systems
# without modification. Be wary of modifications/additions that could
# break this feature (such as system calls and pathnames).
#
# HISTORY
# 06-25-02 Code Wrote it.
#
################################################################################
################################################################################
#
# BEGIN
#
# Before loading the Sybase module, sets up the environment. Sets globals
# according to which operating system is running (NT or Solaris).
#
################################################################################
BEGIN {
# Fill in the Config associative array to find out which OS the script
# is running on.
#
use Config;
# Set OS related global (yech) parameters.
#
if ($Config{osname} eq 'MSWin32') {
$g_os = 'MSWin32';
# Set the directory delimiter to a back slash.
#
$g_slash = '\\';
# Set the program name to the "base" of the command string.
#
($g_program = $0) =~ s#.*\\([^\\]+)$#$1#;
# If it's not defined, set the SYBASE environment variable to
# d:\SYBASE.
#
$ENV{SYBASE} = $ENV{SYBASE} ? $ENV{SYBASE} : 'd:\SYBASE';
$g_hostname = $ENV{COMPUTERNAME};
# Set the directory in which to save temporary interfaces files.
#
$g_tempdir = 'c:\TEMP';
# Set the path to the file containing the 'sa' password.
#
$g_sybpasswd_file = "$ENV{SYBASE}\\.SYBPASSWD";
}
elsif ($Config{osname} eq 'solaris') {
$g_os = 'solaris';
# Set the directory delimiter to a proper forward slash.
#
$g_slash = '/';
# Set the program name to the "base" of the command string.
#
($g_program = $0) =~ s#.*/([^/]+)$#$1#;
# If it's not defined, set the SYBASE environment variable to
# the sybase user's home or /usr/local/sybase (in that order).
#
($name, $passwd, $uid, $gid, $quota, $comment, $GCOS, $home, $shell)
= getpwnam('sybase');
$ENV{SYBASE} = $ENV{SYBASE} ? $ENV{SYBASE} :
$home ? $home : '/usr/local/bin/sybase';
# Get the hostname from the operating system.
#
open(NODENAME, '/bin/uname -n |');
chop($g_hostname = <NODENAME>);
# Set the directory in which to save temporary interfaces files.
#
$g_tempdir = '/tmp';
# Set the path to the file containing the 'sa' password.
#
$g_sybpasswd_file = "$ENV{SYBASE}/.sybpasswd";
}
else {
die("FATAL ERROR: Can't determine OS type.");
}
}
# Load external modules.
#
use Sybase::CTlib;
use Getopt::Std;
# Trap interrupt and quit signals for (relatively) graceful exiting.
#
$SIG{INT} = \&abort;
$SIG{QUIT} = \&abort;
# Set up the message handler routine for SQL Server errors.
#
ct_callback(CS_SERVERMSG_CB, \&syb_msg_handler);
ct_callback(CS_CLIENTMSG_CB, \&client_msg_handler);
################################################################################
#
# abort(message)
#
# Prints an error message and exits.
#
################################################################################
sub abort {
my($message) = @_;
# Make trapped signals a little more descriptive.
#
$message = 'Interrupted by user.' if $message eq 'INT';
$message = 'Terminated.' if $message eq 'TERM';
# Get rid of temporary files.
#
unlink "$g_tempdir${g_slash}interfaces.$$"
if -f "$g_tempdir${g_slash}interfaces.$$";
# Exit gracefully if it's just a usage message.
#
if ($message =~ /^Usage: /) {
print STDERR "$message\n";
exit 0;
}
# Set errno to -1, and die! die! die!
#
$! = -1;
die "FATAL ERROR: $g_program: $message\n";
}
################################################################################
#
# complain(message)
#
# Prints an error message, but doesn't exit.
#
################################################################################
sub complain {
my($message) = @_;
print STDERR "WARNING: $g_program: $message\n";
}
################################################################################
#
# init_params()
#
# Sets user-definable parameters. For parameters that aren't set from the
# command line, try to make a reasonable assumption.
#
################################################################################
sub init_params {
my($server, $hostname, $port, $database, $user, $password, $log);
# These are the accepted switches. All take an argument except 'h'.
#
getopts('S:d:U
:H
:l:h');
# If asked for help, print the usage message and exit.
#
abort("Usage: $g_program [-S server | -H hostname -p port] [-d database] [-U user] [-P password] [-l log]")
if $opt_h;
# If the SQL server name is not set, use the DSQUERY environment
# variable. If that isn't set, assume SYBASE (not likely to work).
#
$server = $opt_S ? $opt_S :
$ENV{DSQUERY} ? $ENV{DSQUERY} : 'SYBASE';
# If the hostname isn't provided, then assume the local host.
#
$hostname = $opt_H ? $opt_H : $g_hostname;
# If a port number isn't supplied, set it to zero. It's likely it won't
# be used to establish a connection to the SQL server.
#
$port = $opt_p ? $opt_p : 0;
# If the database isn't set, use the default database for that user.
#
$database = $opt_d ? $opt_d : 'default';
# If the user name isn't set, assume 'sa'.
#
$user = $opt_U ? $opt_U : 'sa';
# If the password isn't set, look for the file that contains it. This
# file should not be accessible to anyone but root and sybase.
#
$password = $opt_P ? $opt_P :
open(PASSWD, $g_sybpasswd_file) ? <PASSWD> : '';
# If the password is still not set, prompt for it.
#
if (! $password) {
# For Solaris, make sure the script is running from a terminal.
#
if ($g_os eq 'solaris' && -t) {
print STDOUT "Password: ";
system('stty -echo'); # Turn echoing off.
$password = <STDIN>;
print STDOUT "\n";
system('stty echo'); # Turn echoing on.
}
# If anyone reading this code knows how to turn off echoing
# in NT, please let me know. I'll buy you a Coke.
#
elsif ($g_os eq 'MSWin32') {
print STDOUT "Password: ";
$password = <STDIN>;
}
else {
$password = 'secret'; # (Won't work.)
}
}
chomp($password);
# If no log file is set, use standard out (indicated by '-').
#
$log = $opt_l ? $opt_l : '-';
($server, $hostname, $port, $database, $user, $password, $log);
}
################################################################################
#
# syb_msg_handler(dbh, number, severity, state, line, server, proc, msg)
#
# Prints error messages from the SQL server. This subroutine is called asynch-
# ronously. It replaces the standard default callback routine.
#
################################################################################
sub syb_msg_handler {
my($dbh, $number, $severity, $state, $line, $server, $proc, $msg) = @_;
# Ignore informational messages (serverity = 10).
#
if ($severity > 10) {
chomp($msg);
print STDERR "Message #$number from $server: \"$msg\"\n";
}
1; # Return true (required).
}
################################################################################
#
# client_msg_handler(layer, origin, severity, number, msg, osmsg, dbh)
#
# Prints error messages from Open Client. This subroutine is called asynchron-
# ously. It replaces the standard default callback routine.
#
################################################################################
sub client_msg_handler {
my($layer, $origin, $severity, $number, $msg, $osmsg, $dbh) = @_;
chomp($msg);
print STDERR "\nOpen Client Message: $msg\n";
if (defined($osmsg)) {
chomp($osmsg);
print STDERR "Operating System Error: $osmsg\n";
}
1; # Return true (required).
}
################################################################################
#
# db_connect(user, password, server, database)
#
# Logs into the SQL server (using the standard interfaces file) and returns
# a database handle. Attempts to use the given database.
#
################################################################################
sub db_connect {
my($user, $password, $server, $database) = @_;
# Log in. Set the network packet size to 2048, and turn on password
# encryption to annoy snoopers.
#
my $dbh = new Sybase::CTlib $user, $password, $server, $g_program, {
CON_PROPS => {
CS_HOSTNAME => "$g_hostname",
CS_PACKETSIZE => 2048,
CS_SEC_ENCRYPTION => CS_TRUE
}
};
# Complain if it failed. Leave it to the calling routine to decide if
# it's worth aborting over.
#
if (! $dbh) {
complain("db_connect: Unable to connect to $server as $user.");
return 0;
}
# Attempt to use the database if one has been given.
#
if ($database && $database ne 'default') {
$dbh->ct_sql("use $database");
if ($dbh->{RC} == CS_FAIL) {
complain("db_connect: Couldn't use $database.");
return 0;
}
}
# Return the database handle.
#
$dbh;
}
################################################################################
#
# tli_addr(hostname, port)
#
# Given a hostname and port number, returns the tli address as used in Solaris
# interfaces files. This requires a reverse lookup, which may hit the DNS.
#
################################################################################
sub tli_addr {
my($hostname, $port) = @_;
# Get the hostent structure. Complain and return 0 if the entry wasn't
# found.
#
($name, $aliases, $addrtype, $length, @addrs) = gethostbyname($hostname);
if (! defined $name) {
complain("tli_addr: Unable to get address for $hostname: $!");
return 0;
}
# Extract the IP octets.
#
($a, $b, $c, $d) = unpack('C4', $addrs[0]);
# Convert them and the port number to embedded hex digits.
#
sprintf("\\x0002%.4x%.2x%.2x%.2x%.2x%.16x", $port, $a, $b, $c, $d, 0);
}
################################################################################
#
# db_connect2(user, password, hostname, port, database)
#
# Logs into the SQL server (using hostname and port number, instead of SQL
# server name) and returns a database handle. Creates a temporary interfaces
# file to hold a dummy entry for the server. Attempts to use the database.
#
################################################################################
sub db_connect2 {
my($user, $password, $hostname, $port, $database) = @_;
# Open a temporary interfaces file. Use the PID to avoid stepping
# on concurrent Sybperl scripts.
#
my $interfaces = "$g_tempdir${g_slash}interfaces.$$";
if (! open(INTERFACES, "> $interfaces")) {
complain("db_connect2: Unable to open $interfaces: $!");
return 0;
}
# The contents of interfaces is OS dependent.
#
if ($g_os eq 'MSWin32') {
print INTERFACES "[SYBASE]\nquery=NLWNSCK,$hostname,$port\n";
}
elsif ($g_os eq 'solaris') {
my $hex = tli_addr($hostname, $port);
print INTERFACES "SYBASE\n\tquery tli tcp /dev/tcp $hex\n\n\n";
}
else {
abort("db_connect2: Unknown operating system: \"$g_os\"\n");
}
close(INTERFACES); # (Closing it now flushes output for immediate use.)
# Save the original interfaces pathname.
#
my $orig_interfaces;
$ret = ct_config(CS_GET, CS_IFILE, $orig_interfaces, CS_CHAR_TYPE);
if ($ret == CS_FAIL) {
complain("db_connect2: Unable to get original interfaces pathname.");
}
# Use the temporary interfaces file.
#
$ret = ct_config(CS_SET, CS_IFILE, $interfaces, CS_CHAR_TYPE);
if ($ret == CS_FAIL) {
complain("db_connect2: Unable to set interfaces to $interfaces.");
return 0;
}
# Log in. Set the network packet size to 2048, and turn on password
# encryption to annoy snoopers.
#
my $dbh = new Sybase::CTlib $user, $password, 'SYBASE', $g_program, {
CON_PROPS => {
CS_HOSTNAME => "$g_hostname",
CS_PACKETSIZE => 2048,
CS_SEC_ENCRYPTION => CS_TRUE
}
};
# Restore the original interfaces file.
#
ct_config(CS_SET, CS_IFILE, $orig_interfaces, CS_CHAR_TYPE);
if ($ret == CS_FAIL) {
complain("$db_connect2: Unable to reset interfaces to $orig_interfaces.");
return 0;
}
# Remove the temporary interfaces file.
#
unlink $interfaces if -f $interfaces;
# Complain if the login failed. Leave it to the calling routine to decide if
# it's worth aborting over.
#
if (! $dbh) {
complain("db_connect2: Unable to connect to $hostname (port $port) as $user.");
return 0;
}
# Attempt to use the database if one has been given.
#
if ($database && $database ne 'default') {
$dbh->ct_sql("use $database");
if ($dbh->{RC} == CS_FAIL) {
complain("db_connect2: Couldn't use $database.");
return 0;
}
}
# Return the database handle.
#
$dbh;
}
################################################################################
#
# get_util_logon(loc_dbh, hostname, port, database)
#
# For a given hostname, port, and database, returns a database handle using a
# utility logon. Uses the existing locations database handle to find a valid
# name and password.
#
################################################################################
sub get_util_logon {
my($loc_dbh, $hostname, $port, $database) = @_;
my($user, $password, $dbh, $num_found);
# Query the locations database for all utility logons that match the
# given host, port, and database.
#
$query =<<EOF;
select
x = l.loginname,
y = l.password
from
logins l,
databases d
where
l.utilitylogin = 1 and
d.dbname = "$database" and
d.server = "$hostname" and
d.portnumber = $port and
l.databaseid = d.id
union
select
l.highresloginname,
l.highrespassword
from
logins l,
databases d
where
l.utilitylogin = 1 and
d.dbname = "$database" and
d.server = "$hostname" and
d.portnumber = $port and
l.highresdatabaseid = d.id
EOF
$loc_dbh->ct_execute($query);
$num_found = 0;
while ($loc_dbh->ct_results($res_type) == CS_SUCCEED) {
next unless $loc_dbh->ct_fetchable($res_type);
while (($user, $password) = $loc_dbh->ct_fetch) {
$num_found++;
# Check candidate user names and passwords by
# attempting to log in as that user.
#
$dbh = db_connect2($user, $password, $hostname, $port, $database);
# If it worked, cancel the remaining results set and
# bail out of the loop.
#
$loc_dbh->ct_cancel(CS_CANCEL_CURRENT) && last if $dbh;
}
}
# Complain if no candidate utility logins were even found.
#
complain("get_util_login: Couldn't find a utility login for \"$database\" on $hostname (port $port).")
if $num_found == 0;
# Return the database handle.
#
$dbh;
}
################################################################################
#
# main
#
# Not a function really, just a good place to jump into the code.
#
################################################################################
MAIN:
# Set up database parameters (usually from the command line).
#
($server, $hostname, $port, $database, $user, $password, $log) = init_params();
# Open the log file. If the pathname is a directory, append a file name that's
# based on the program name minus "pl" extension.
#
($program = $g_program) =~ s/(.*)(\.pl|\.PL)$/$1/;
$log = "$log$g_slash$program.log" if -d $log;
if (! open(LOG, ">$log")) {
complain("main: Unable to open $log ($!), using standard out.");
open(LOG, ">&STDOUT");
}
# Log into the SQL server.
#
if ($hostname && $port) {
$dbh = db_connect2($user, $password, $hostname, $port, $database);
abort("main: Failed connecting to $hostname (port $port).") if ! $dbh;
}
else {
$dbh = db_connect($user, $password, $server, $database);
abort("main: Failed connecting to $server.") if ! $dbh;
}
# Print a fancy starting banner.
#
$now = localtime;
$banner = sprintf("%s starting at %s.", $g_program, $now);
$border = "=" x (length($banner) + 2);
print LOG "$border\n $banner\n$border\n\n";
################################################################################
#
# Put your own stuff here.
#
################################################################################
$dbh->ct_execute('select name, uid from sysusers order by name');
while ($dbh->ct_results($res_type) == CS_SUCCEED) {
next unless $dbh->ct_fetchable($res_type);
while (($name, $uid) = $dbh->ct_fetch) {
print LOG "$name\t$uid\n";
}
}
################################################################################
#
# Okay, stop your stuff here.
#
################################################################################
# Print an ending banner.
#
$now = localtime;
$banner = sprintf("%s finished at %s.", $g_program, $now);
$border = "=" x (length($banner) + 2);
print LOG "\n$border\n $banner\n$border\n";
Please Scroll Down to See Forums Below 
