# -----------------------------------------------------------------------------
# Name         :  db.py
# Version      :  .0.1
# Description  :  misc database support functions
# Requirements :  Python 2.0, mxODBC
# -----------------------------------------------------------------------------
# Author       :  Scott Hathaway
# Contact      :  slhath@home.com
#                 http://members.home.net/slhath/
# -----------------------------------------------------------------------------
# Classes: n/a
# -----------------------------------------------------------------------------
# Functions:
#    dbCall - takes an sql call and returns a standard python dbapi dataset
#    dbCallAccess - takes an sql call and returns a standard python 
#                   dbapi dataset
#    dbPrep - prepares a string for insertion into a mySQL database
#    getFields - given an SQL call, returns a | separated string of the field
#                names
#    getFieldTypes - given an SQL call, returns a | separated string of the
#                    field types (string, integer, etc.)
#    getFieldsAndTypes - given an SQL call, returns a | separated string in
#                        the format:  field name|field type
# -----------------------------------------------------------------------------
# Disclaimer:  The following code is provided into the public domain.  The
#              author shall not be responsible for any use or misuse of this
#              code.
# -----------------------------------------------------------------------------

def dbCall(sql,dsn='',username='',password=''):
    # sql = valid SQL call
    # dsn = ODBC DSN
    # username = username for the database
    # password = password for the database
    import ODBC.Windows as myDB
    db = myDB.Connect(dsn,username,password,clear_auto_commit=0)
    c = db.cursor()
    c.execute(sql)
    data=[]
    try:
        data = c.fetchall()
    except:
        data=[]
    c.close()
    db.close()
    return data

def dbCallAccess(sql,dsn='',username='',password=''):
    # sql = valid SQL call
    # dsn = ODBC DSN
    # username = username for the database
    # password = password for the database
    import ODBC.Windows as myDB
    db = myDB.Connect(dsn,username,password,clear_auto_commit=0)
    c = db.cursor()
    c.execute(sql)
    data=[]
    try:
        data = c.fetchall()
    except:
        data=[]
    c.close()
    db.close()
    return data

def dbPrep(theData):
    # theData is the string to fix
    theData = str(theData)
    theData = theData.replace("\\","\\\\")
    theData = theData.replace("'","\'")
    return theData

def getFields(sql,dsn='',username='',password=''):
    # sql = valid SQL call
    # dsn = ODBC DSN
    # username = username for the database
    # password = password for the database
    import ODBC.Windows as myDB
    db = myDB.Connect(dsn,username,password,clear_auto_commit=0)
    c = db.cursor()
    c.execute(sql)
    fList=[]
    try:
        fList = c.description
    except:
        fList=[]
    c.close()
    db.close()
    fields=[]
    for eachOne in fList:
        fields.append(str(eachOne[0]))
    data = ''
    s='|'
    data = s.join(fields)
    return data

def getFieldTypes(sql,dsn='',username='',password=''):
    # sql = valid SQL call
    # dsn = ODBC DSN
    # username = username for the database
    # password = password for the database
    import ODBC.Windows as myDB
    db = myDB.Connect(dsn,username,password,clear_auto_commit=0)
    c = db.cursor()
    c.execute(sql)
    fList=[]
    try:
        fList = c.description
    except:
        fList=[]
    c.close()
    db.close()
    fields=[]
    for eachOne in fList:
        if(str(eachOne[1])=='4'):
            fields.append('integer')
        if(str(eachOne[1])=='12'):
            fields.append('string')
    data = ''
    s='|'
    data = s.join(fields)
    return data

def getFieldsAndTypes(sql,dsn='',username='',password=''):
    # sql = valid SQL call
    # dsn = ODBC DSN
    # username = username for the database
    # password = password for the database
    import ODBC.Windows as myDB
    db = myDB.Connect(dsn,username,password,clear_auto_commit=0)
    c = db.cursor()
    c.execute(sql)
    fList=[]
    try:
        fList = c.description
    except:
        fList=[]
    c.close()
    db.close()
    fields=[]
    for eachOne in fList:
        fields.append(str(eachOne[0]))
        if(str(eachOne[1])=='4'):
            fields.append('integer')
        if(str(eachOne[1])=='12'):
            fields.append('string')
    data = ''
    s='|'
    data = s.join(fields)
    return data