#!/usr/local/bin/perl 

use UnixODBC qw(:all);

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

## 
## Set the name of the DSN, username, and password.
##

my $DSN="Gutenberg cube1";
my $UserName="postgres";
my $PassWord="postgres";

my $table = 'titles';
my $column_number = 1;
my ($rbuf, $rbuflen, $numeric_attr, $numeric_buf);

my $query = "select \* from titles\;";

my %col_attrs = ('SQL_COLUMN_COUNT', 0,
		'SQL_COLUMN_NAME', 1,
		'SQL_COLUMN_TYPE', 2,
		'SQL_COLUMN_LENGTH', 3,
		'SQL_COLUMN_PRECISION', 4,
		'SQL_COLUMN_SCALE', 5,
		'SQL_COLUMN_DISPLAY_SIZE', 6,
		'SQL_COLUMN_NULLABLE', 7,
		'SQL_COLUMN_UNSIGNED', 8,
		'SQL_COLUMN_MONEY', 9,
		'SQL_COLUMN_UPDATABLE', 10,
		'SQL_COLUMN_AUTO_INCREMENT', 11,
		'SQL_COLUMN_CASE_SENSITIVE', 12,
		'SQL_COLUMN_SEARCHABLE', 13,
		'SQL_COLUMN_TYPE_NAME', 14,
		'SQL_COLUMN_TABLE_NAME', 15,
		'SQL_COLUMN_OWNER_NAME', 16,
		'SQL_COLUMN_QUALIFIER_NAME', 17,
		'SQL_COLUMN_LABEL', 18,
		'SQL_COLATT_OPT_MIN', 0,
		'SQL_COLATT_OPT_MAX', 18,
		'SQL_COLUMN_DRIVER_START', 1000 );

$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;
}

$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;
}

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

$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;
}

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

$r = &UnixODBC::SQLPrepare ($sth, $query, length ($query));
if (($r!=$SQL_SUCCESS)&&($r!=$SQL_NO_DATA)) {
    &getdiagrec ($SQL_HANDLE_STMT, $sth);
    exit 1;
}

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

foreach my $attr (keys %col_attrs) {
    $rbuf = '';
    $rbuflen = 0;
    $numeric_attr = 0;
    $r = SQLColAttributes ($sth, $column_number, $col_attrs{$attr},
			   $rbuf, 255, $rbuflen, $numeric_attr);
    print "$attr \= ";
    if (length($rbuf)) {
	print "$rbuf\n";
    } else {
	print "$numeric_attr\n";
    }

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

$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;
}

sub getdiagrec {
    my ($handle_type, $handle) = @_;
    my ($sqlstate, $native, $message_text, $mlen);
    print 'SQLGetDiagRec: ';
    $r = &UnixODBC::SQLGetDiagRec ($handle_type, $handle, 1, $sqlstate,
				   $native, $message_text, 255,
				   $mlen);
    if ($r == $SQL_NO_DATA) { 
	print "result \= SQL_NO_DATA\n";
    } elsif (($r == 1) || ($r == 0)) { 
     print "$message_text\n";
    } else { 
     print "sqlresult = $r\n";
    }
    return $r;
}
