#!/usr/bin/perl

# $Id: sqltables,v 1.6 2004-03-06 05:57:56 kiesling Exp $
$VERSION=1.0;

use UnixODBC qw(:all);
use Getopt::Long;

# ODBC Handles

my $env;
my $cnh;
my $sth;

# Function Return Value

my $r;

# Data Buffers and Lengths

my $buf;
my $buflen = 255;   # Maximum length of parameter data.
my $rlen;           # Actual length of returned data.


## 
## DSN, username, and password from command line arguments.
##

my $DSN = '';
my $UserName = '';
my $PassWord = '';
my $Verbose = '';

# Help Text

my $usage=<<EOH;
Usage: sqltables [--help] | [--verbose] [--user=<username>] [--password=<password>] --dsn=<DSN> 
  --help       Print this help and exit.
  --verbose    Print tables' catalog, schema, name, and type.
  --dsn        Data source name.
  --user       DBMS login name.
  --password   DBMS login password.
EOH

# Get the DSN and login data from the command line.

GetOptions ('help' => \$help,
	    'verbose' => \$Verbose,
	    'dsn=s' => \$DSN,
	    'user=s' => \$UserName,
	    'password=s' => \$PassWord);

# If necessary print the help message and exit.

if ($help || (not length ($DSN)))
     {
	 print $usage;
	 exit 0;
     }

# Fields defined in SQLTables result set.

my ($table_cat, $table_schem, $table_name, $table_type, $remarks);

# Allocate Environment Handle.

$r = SQLAllocHandle ($SQL_HANDLE_ENV, $SQL_NULL_HANDLE, $evh);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    print "SQLAllocHandle evh: ";
    getdiagrec ($SQL_HANDLE_ENV, $evh);
    exit 1;
}

# Set the ODBC Version

$r = SQLSetEnvAttr($evh, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC2, 0);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_ENV, $evh);
    exit 1;
}

# Allocate a connection handle.

$r = SQLAllocHandle ($SQL_HANDLE_DBC, $evh, $cnh);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_ENV, $evh);
    exit 1;
}

# Connect to the DSN given on the command line.

$r = SQLConnect ($cnh, $DSN, $SQL_NTS,
		 $UserName, $SQL_NTS,
		 $PassWord, $SQL_NTS);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_DBC, $cnh);
    exit 1;
}

# Allocate a statement handle.

$r = SQLAllocHandle ($SQL_HANDLE_STMT, $cnh, $sth);
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_DBC, $cnh);
    exit 1;
}

# Get table information.  Blank parameters are treated as matching 
# every catalog, schema, table, and column for the DSN.

$r = SQLTables ($sth, '', 0, '', 0, '', 0, '', 0);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_STMT, $sth);
    exit 1;
}

while (1) {

    # Fetch the next row of data.

    $r = SQLFetch ($sth);

    # Exit the while loop if there are no more rows to fetch.

    last if $r == $SQL_NO_DATA;

    $r = SQLGetData ($sth, 1, $SQL_C_CHAR, $table_cat, $buflen, $rlen);
    $r = SQLGetData ($sth, 2, $SQL_C_CHAR, $table_schem, $buflen, $rlen);
    $r = SQLGetData ($sth, 3, $SQL_C_CHAR, $table_name, $buflen, $rlen);
    $r = SQLGetData ($sth, 4, $SQL_C_CHAR, $table_type, $buflen, $rlen);
    $r = SQLGetData ($sth, 5, $SQL_C_CHAR, $remarks, $buflen, $rlen);

    # Delimit fields with tabs and lines with newlines.

    if ($Verbose) {
	print "$table_cat\t$table_schem\t$table_name\t$table_type\t$remarks\n";
    } else {
	print "$table_name\n";
    }
}

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_STMT, $sth);
    exit 1;
}

# Clean up.  Disconnect from DSN and de-allocate statement, 
# connection, and environment handles.

$r = SQLFreeHandle ($SQL_HANDLE_STMT, $sth);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_STMT, $sth);
    exit 1;
}

$r = SQLDisconnect ($cnh);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_DBC, $cnh);
    exit 1;
}

$r = SQLFreeConnect ($cnh);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_DBC, $cnh);
    exit 1;
}

$r = SQLFreeHandle ($SQL_HANDLE_ENV, $evh);

if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_ENV, $evh);
    exit 1;
}

# Subroutine to print a SQL diagnostic record.

sub getdiagrec {
    my ($handle_type, $handle) = @_;
    my ($sqlstate, $native, $message_text, $mlen);
    my $diagrecno = 1;
    print 'SQLGetDiagRec: ';
    $r = SQLGetDiagRec ($handle_type, $handle, $diagrecno, 
			$sqlstate, $native, $buf, $buflen,
			$rlen);
    if ($r == $SQL_NO_DATA) { 
	print "result \= SQL_NO_DATA\n";
    } elsif (($r == $SQL_SUCCESS_WITH_INFO) 
	     || ($r == $SQL_SUCCESS)) { 
	print "$buf\n";
    } else { 
	print "sqlresult = $r\n";
    }

    return $r;
}

=head1 NAME

sqltables - Print names of a data source's tables.

=head1 SYNOPSIS

sqltables [--help] | [--verbose] [--user=<username>] [--password=<password>] --dsn=<DSN> 

=head1 DESCRIPTION

Sqltables prints the names of a data source's  tables and optionally
the catalog and schema.

=head1 OPTIONS

=head2 --help

Print help message and exit.

=head2 --verbose

Print catalog and schema information as well as table information.

=head2 --dsn

Name of the data source to query.

=head2 --user

User's DSN login name.

=head2 --password

User's DSN login password.

=head1 VERSION INFORMATION AND CREDITS

Revision: $Revision: 1.6 $

Written by: Robert 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).

=cut