#! /usr/local/bin/perl
#$Id: remotetables,v 1.3 2003-11-28 22:22:36 kiesling Exp $
require 5.004;
use strict;
require RPC::PlClient;
use UnixODBC qw (:all);
use UnixODBC::BridgeServer;
use Getopt::Long;
my $Host = ''; # Remote host name or address.
my $DSN = ''; # DSN on the remote host.
my $UserName = ''; # Remote host login name.
my $PassWord = ''; # Remote host login password.
my $Verbose = ''; # Print the driver names as well as the DSNs.
my $help; # Print help and exit.
my $usage=<<EOH;
Usage:
remotetables [--help] | [--verbose] [--user=<username>] [--password=<password>] --dsn=<remotedsn> --host=<remotehost>
--help Print this help and exit.
--host Remote host name or address.
--dsn Remote DSN.
--user Remote host login name.
--password Remote host login password.
--verbose Print driver names as well as DSNs.
EOH
GetOptions ('help' => \$help,
'host=s' => \$Host,
'dsn=s' => \$DSN,
'user=s' => \$UserName,
'verbose' => \$Verbose,
'password=s' => \$PassWord);
if ($help || (not length ($Host)) || (not length ($DSN)))
{
print $usage;
exit 0;
}
my $client =
eval { RPC::PlClient->new('peeraddr' => $Host,
'peerport' => 9999,
'application' => 'RPC::PlServer',
'version' => $UnixODBC::VERSION,
'user' => $UserName,
'password' => $PassWord) }
or print "Failed to make first connection: $@\n";
my $c = $client -> ClientObject ('BridgeAPI', 'new');
my $evh = 0;
my $cnh = 0;
my $sth = 0;
my ($r, $sqlstate, $native, $text, $textlen);
my $nrows = 0;
my $ncols = 0;
$evh = $c -> sql_alloc_handle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE);
if (defined $evh) {
$r = $c ->
sql_set_env_attr ($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);
} else {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
print "\nsql_alloc_handle: $r, $text, $textlen\n";
exit 1;
}
$cnh = $c -> sql_alloc_handle ($SQL_HANDLE_DBC, $evh);
$r = $c -> sql_connect ($cnh, $DSN, length($DSN),
$UserName, length($UserName),
$PassWord, length($PassWord));
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
print "\nconnect: $r, $text, $textlen\n";
}
$sth = $c -> sql_alloc_handle ($SQL_HANDLE_STMT, $cnh);
if (! defined $sth) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
print "\nsql_alloc_handle sth: $r, $text, $textlen\n";
}
$r = $c -> sql_tables ($sth, '', 0, '', 0, '', 0, '', 0);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
print "\nsql_tables: $r, $text, $textlen\n";
}
($r, $ncols) = $c -> sql_num_result_columns ($sth);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
print "\nsql_num_result_columns: $r, $text, $textlen\n";
}
print "\n$ncols columns\n" if $Verbose;
($r, $nrows) = $c -> sql_row_count ($sth);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_STMT, $sth, 1, 255);
print "\nsql_row_count: $r, $text, $textlen\n";
}
print "$nrows rows\n" if $Verbose;
while (1) {
$r = $c -> sql_fetch ($sth);
last if $r == $SQL_NO_DATA;
# Catalog name.
($r, $text, $textlen) = $c -> sql_get_data ($sth, 1, $SQL_C_CHAR, 255);
print "$text\t" if $Verbose;
# Schema name
($r, $text, $textlen) = $c -> sql_get_data ($sth, 2, $SQL_C_CHAR, 255);
print "$text\t" if $Verbose;
# Table name - always print.
($r, $text, $textlen) = $c -> sql_get_data ($sth, 3, $SQL_C_CHAR, 255);
print "$text\t";
# Table type.
($r, $text, $textlen) = $c -> sql_get_data ($sth, 4, $SQL_C_CHAR, 255);
print "$text\t" if $Verbose;
# Remarks
($r, $text, $textlen) = $c -> sql_get_data ($sth, 5, $SQL_C_CHAR, 255);
print "$text\t" if $Verbose;
print "\n";
}
$r = $c -> sql_free_handle ($SQL_HANDLE_STMT, $sth);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
print "\nfree_handle sth: $r, $text, $textlen\n";
}
$r = $c -> sql_disconnect ($cnh);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_DBC, $cnh, 1, 255);
print "\nconnect: $r, $text, $textlen\n";
}
$r = $c -> sql_free_connect ($cnh);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
print "\nfree_connect: $r, $text, $textlen\n";
}
$r = $c -> sql_free_handle ($SQL_HANDLE_ENV, $evh);
if ($r != 0) {
($r, $sqlstate, $native, $text, $textlen) =
$c -> sql_get_diag_rec ($SQL_HANDLE_ENV, $evh, 1, 255);
print "\nfree_connect: $r, $text, $textlen\n";
}
=head1 NAME
remotetables - List the tables of a remote data source.
=head1 SYNOPSYS
remotetables [--help] | [--verbose] [--user=<username>] [--password=<password>] --dsn=<remotedsn> --host=<remotehost>
=head1 DESCRIPTION
Remotetables lists the tables for a remote data source and host given
as command line arguments, and optionally lists the tables' catalog,
schema, type, and any remarks provided by the DBMS server.
=head1 OPTIONS
=head2 --help
Print help message and exit.
=head2 --host
Name or network address of remote host.
=head2 --dsn
Name of data source on the remote host.
=head2 --user
User login name on the remote host.
=head2 --password
User login password on theremote host.
=head2 --verbose
List result set size, and tables' catalog, schema, type, and remarks
in addition to the table names.
=head1 VERSION INFORMATION AND CREDITS
Revision: $Revision: 1.3 $
Written by: Robert Allan Kiesling, rkies@cpan.org.
Licensed under the same terms as Perl. Please refer to the
file "Artistic" for details.
=head1 SEE ALSO
perl(1), UnixODBC(3), UnixODBC::BridgeServer(3), bridgeserver(1).
=cut