#!/usr/bin/perl -w

use strict;
use DBI;
use Config::IniFiles;

use vars qw/$servers/;
my $server = 'servername';

eval{
    $servers = Config::IniFiles->new( -file => 'config.cnf');

    my $dbh = get_dbh($server);
    print "OpenClient version: *$dbh->{syb_oc_version}*\n"; 
                                           # prints nothing
    print "Dynamic Supported: *$dbh->{syb_dynamic_supported}*\n"; 
                                           # prints 1

    print "Performing sql (no placeholders):\n";
    my @vals = do_sql_direct($server,
        qq#select * from ct_countries where code = '000'#);
    print_vals(@vals);

    print "Performing sql (with placeholders):\n";
    my @vals2 = do_sql($server,
        qq#select * from ct_countries where code = ?#, '000');
    print_vals(@vals2);
};
die "Error: $@\n" if $@;

################################################################
# perform sql without placeholders #

sub do_sql_direct{
    my ($server, $sql) = @_;
    my $dbh = get_dbh($server);
    my $sth;
    $sth = $dbh->prepare($sql);
    $sth->execute();
    my @rows;
    eval{
        while(my $r = $sth->fetchrow_hashref()){
            push @rows, $r;
        }
    };
    warn "***$@***\n" if $@;
    return @rows;
}

################################################################
# perform sql using ?-style placeholders
sub do_sql{
    my ($server, $sql, @params) = @_;

    my $dbh = get_dbh($server);
    my $sth;
    $sth = $dbh->prepare($sql)
        or die "Cannot prepare $sql: \n";
    my $i = 1;
    for(@params){
        $sth->bind_param($i, $_) or die "Cannot bind param $i: $_\n";
        $i++;
    }
    $sth->execute() or die "Cannot execute\n";
    # also tried $sth->execute(@params) without bind_params 
    # didn't work either.

    my @rows;
    eval{
        while(my $r = $sth->fetchrow_hashref()){
            push @rows, $r;
        }
    };
    warn "***$@***\n" if $@;
    return @rows;
}

##</code><code>##

################################################################
# you don't need to read below this point.

#
# the rest of the code
#

# get the database handle associated with database name #
sub get_dbh{
    my $dbname = shift;
    $servers->SectionExists($dbname) or die "Unknown database: $dbname\n";
    my $dbh = $servers->val($dbname, 'dbh');
    return $dbh if $dbh;

    my $driver = $servers->val($dbname, 'driver')
        or die "Database $dbname requires a driver\n";
    my $server = $servers->val($dbname, 'server')
        or die "Database $dbname requires a server\n";
    my $user = $servers->val($dbname, 'user')
        or die "Database $dbname requires a user\n";
    my $pass = $servers->val($dbname, 'pass')
        or die "Database $dbname requires a pass\n";
    my $database = $servers->val($dbname, 'database');

    my $dsn = "dbi:$driver:server=$server";
    $dsn .= ";database=$database" if $database;
    $dbh = DBI->connect( $dsn,
        $user, $pass,
        {
            RaiseError => 1
        }
    );
    $servers->setval($dbname, 'dbh', $dbh);
    return $dbh;
}

sub print_vals{
    for my $row (@_){
        for(sort keys %$row){
            printf "%15s: %s\n", $_, $row->{$_};
        }
        print "-" x 78 . "\n";
    }
    printf "%d record(s) found\n", scalar(@_);
}