#!/usr/bin/perl

# $Id: sample.pl 3633 2010-06-06 23:56:41Z unsaved $

# Sample Perl script accessing HyperSQL through the Perl DBI and DBD/ODBC
# modules.

# This test HyperSQL client uses the ODBC DSN "tstdsn" to connect up to a
# HyperSQL server.  Just configure your own DSN to use the HyperSQL ODBC
# driver, specifying the HyperSQL server host name, database name, user,
# password, etc.

# Author:  Blaine Simpson  (blaine dot simpson at admc dot com)


use strict;
use DBI;

use vars qw:$dsn $dbh $sth $row $retval %conAttr:;

$conAttr{AutoCommit} = 0;

# In addition to the DSN name, you can override or supply additional DSN
# settings, such as "Uid" and "Pwd"; or define the DSN from scratch, starting
# with Driver.  These settings are delimited with "; ".  See pyodbc docs.
$dsn = "dbi:ODBC:dsn=tstdsn";

#$dbh = DBI->connect($dsn, undef, undef)
$dbh = DBI->connect($dsn, undef, undef, \%conAttr)
    or die("Failed to connect: ($DBI::err) $DBI::errstr\n");

$dbh->do("DROP TABLE tsttbl IF EXISTS");
$dbh->do(
    "CREATE TABLE tsttbl(\n"
    . "    id BIGINT generated BY DEFAULT AS IDENTITY,\n"
    . "    vc VARCHAR(20),\n"
    . "    entrytime TIMESTAMP DEFAULT current_timestamp NOT NULL\n"
    . ")");

# First a simple/non-parameterized Insertion
$retval = $dbh->do("INSERT INTO tsttbl (id, vc) values (1, 'one')");
die "First insertion inserted $retval rows instead of 1\n" unless $retval eq 1;

# Now same thing with parameters
$sth = $dbh->prepare("INSERT INTO tsttbl (id, vc) values (?, ?)")
    or die("Failed to prepare Insertion statement: ($DBI::err) $DBI::errstr\n");
$retval = $sth->execute(2, 'two')
    or die("2nd insertion failed: ($DBI::err) $DBI::errstr\n");
die "2nd insertion inserted $retval rows instead of 1\n" unless $retval eq 1;

# The disabled testa re due to known bug with driver.
# The misleading warnings withe "SQL-HY000" may be ignored.
$retval = $sth->execute(3, 'three');
    #or die("3rd insertion failed: ($DBI::err) $DBI::errstr\n");
#die "3rd insertion inserted $retval rows instead of 1\n" unless $retval eq 1;
$retval = $sth->execute(4, 'four');
    #or die("4th insertion failed: ($DBI::err) $DBI::errstr\n");
#die "4th insertion inserted $retval rows instead of 1\n" unless $retval eq 1;
$retval = $sth->execute(5, 'five');
    #or die("5th insertion failed: ($DBI::err) $DBI::errstr\n");
#die "5th insertion inserted $retval rows instead of 1\n" unless $retval eq 1;
$dbh->commit;
    # Some recent change to the HyperSQL server or to unixODBC has made this
    # necessary, at least on UNIX.  Some other transaction control command
    # would probably be more appropriate here.

# Now a simple/non-parameterized Query
$sth = $dbh->prepare("SELECT * FROM tsttbl WHERE id < 3")
    or die("Failed to prepare SELECT statement: ($DBI::err) $DBI::errstr\n");
$sth->execute()
    or die("Execution of non-param. query failed : ($DBI::err) $DBI::errstr\n");

while ($row = $sth->fetch()) {
    print(join '|', @$row);
    print("\n");
}
$sth->finish();

$dbh->do('rollback');

# Now a parameterized Query
$sth = $dbh->prepare("SELECT * FROM tsttbl WHERE id > ?")
    or die("Failed to prepare SELECT statement: ($DBI::err) $DBI::errstr\n");
# Use bind_param for variety
$sth->bind_param(1, 3);
$sth->execute()
    or die("Exec. of parameterized query failed : ($DBI::err) $DBI::errstr\n");

while ($row = $sth->fetch()) {
    print(join '|', @$row);
    print("\n");
}
$sth->finish();

$dbh->disconnect();

exit(0);