#!/usr/bin/python

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

# Sample Python script accessing HyperSQL through the Python pyodbc module.

# This test HyperSQL client uses the ODBC DSN "tstdsn-a" 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.

# N.b. there is some dependency or bug which requires pyodbc to use the
# ANSI variant of the HyperSQL ODBC Driver.  Using the normal Unicode
# variant will generate the following error message when you try to connect:
#    pyodbc.Error: ('0', '[0] [unixODBC]c (202) (SQLDriverConnectW)')
# It is quite possible that this issue will be taken care of when we fix a
# high-priority bug to do with switching between SQLDriverConnect and
# SQLDriverConnectW on UNIX.

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

import pyodbc

# Get a connection handle.
# 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.
conn = pyodbc.connect("DSN=tstdsn-a")
try:
    conn.autocommit = 0

    cursor = conn.cursor();

    cursor.execute("DROP TABLE tsttbl IF EXISTS");
    conn.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.

    cursor.execute(
        "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 = cursor.execute("INSERT INTO tsttbl (id, vc) values (1, 'one')");
    if retval != 1:
        raise Exception(('1st insertion inserted ' + repr(retval)
            + ' rows instead of 1'))
    # Now parameterized.  Unfortunately, the Python DB API and pyodbc API do
    # not allow re-use of a parsed statement.  Cursor must be reparsed for
    # each usage.
    retval = cursor.execute("INSERT INTO tsttbl (id, vc) values (?, ?)",
            2, 'two');
    if retval != 1:
        raise Exception(('2nd insertion inserted ' + repr(retval)
            + ' rows instead of 2'))
    retval = cursor.execute("INSERT INTO tsttbl (id, vc) values (?, ?)",
            3, 'three');
    if retval != 1:
        raise Exception(('3rd insertion inserted ' + repr(retval)
            + ' rows instead of 3'))
    retval = cursor.execute("INSERT INTO tsttbl (id, vc) values (?, ?)",
            4, 'four');
    if retval != 1:
        raise Exception(('4th insertion inserted ' + repr(retval)
            + ' rows instead of 4'))
    retval = cursor.execute("INSERT INTO tsttbl (id, vc) values (?, ?)",
            5, 'five');
    if retval != 1:
        raise Exception(('5th insertion inserted ' + repr(retval)
            + ' rows instead of 5'))
    conn.commit();

    # Non-parameterized query
    for row in cursor.execute(
            "SELECT * FROM tsttbl WHERE id < 3"):
        print row

    # Non-parameterized query.  As noted above, can't re-use parsed cursor.
    for row in cursor.execute(
            "SELECT * FROM tsttbl WHERE id > ?", 3):
        # For variety, we format the files ourselves this time
        print repr(row.ID) + '|' + row.VC + '|' + repr(row.ENTRYTIME)

except Exception as e:
    conn.rollback();
    raise e

finally:
    conn.close();