# 
#  Licensed Materials - Property of IBM
#
#  (c) Copyright IBM Corp. 2007-2008
#

import unittest, sys
import ibm_db
import config
from testfunctions import IbmDbTestFunctions

class IbmDbTestCase(unittest.TestCase):

  def test_111_FieldNumAddCol(self):
    obj = IbmDbTestFunctions()
    obj.assert_expect(self.run_test_111)

  def run_test_111(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if conn:
      ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

      insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
      ibm_db.exec_immediate(conn, insert)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
    
      if (server.DBMS_NAME[0:3] == 'IDS'):
        num1 = ibm_db.field_num(stmt, "id")
        num2 = ibm_db.field_num(stmt, "breed")
        num3 = ibm_db.field_num(stmt, "number")
        num4 = ibm_db.field_num(stmt, "NUMBER")
        num5 = ibm_db.field_num(stmt, "bREED")
        num6 = ibm_db.field_num(stmt, 8)
        num7 = ibm_db.field_num(stmt, 1)
        num8 = ibm_db.field_num(stmt, "WEIGHT")
      else:
        num1 = ibm_db.field_num(stmt, "ID")
        num2 = ibm_db.field_num(stmt, "BREED")
        num3 = ibm_db.field_num(stmt, "NUMBER")
        num4 = ibm_db.field_num(stmt, "number")
        num5 = ibm_db.field_num(stmt, "Breed")
        num6 = ibm_db.field_num(stmt, 8)
        num7 = ibm_db.field_num(stmt, 1)
        num8 = ibm_db.field_num(stmt, "weight")
  
      print("%s" % num1)
      print("int(%d)" % num2)
      print("int(%d)" % num3)
      print("%s" % num4)
      
      print("%s" % num5)
      print("%s" % num6)
      print("int(%d)" % num7)
      print("%s" % num8)

      ibm_db.rollback(conn)
    else:
      print("Connection failed.")

#__END__
#__LUW_EXPECTED__
#False
#int(0)
#int(1)
#False
#False
#False
#int(1)
#False
#__ZOS_EXPECTED__
#False
#int(0)
#int(1)
#False
#False
#False
#int(1)
#False
#__SYSTEMI_EXPECTED__
#False
#int(0)
#int(1)
#False
#False
#False
#int(1)
#False
#__IDS_EXPECTED__
#False
#int(0)
#int(1)
#False
#False
#False
#int(1)
#False

[((15, 10, 15, 30), 'testfunctions.IbmDbTestFunctions', 'IbmDbTestFunctions', ({}, {}), '()', False, 'from testfunctions import IbmDbTestFunctions\n'), ((19, 11, 19, 72), 'ibm_db.connect', 'ibm_db.connect', ({(19, 26, 19, 41): 'config.database', (19, 43, 19, 54): 'config.user', (19, 56, 19, 71): 'config.password'}, {}), '(config.database, config.user, config.password)', False, 'import ibm_db\n'), ((20, 13, 20, 39), 'ibm_db.server_info', 'ibm_db.server_info', ({(20, 33, 20, 37): 'conn'}, {}), '(conn)', False, 'import ibm_db\n'), ((23, 6, 23, 56), 'ibm_db.autocommit', 'ibm_db.autocommit', ({(23, 24, 23, 28): 'conn', (23, 30, 23, 55): 'ibm_db.SQL_AUTOCOMMIT_OFF'}, {}), '(conn, ibm_db.SQL_AUTOCOMMIT_OFF)', False, 'import ibm_db\n'), ((26, 6, 26, 41), 'ibm_db.exec_immediate', 'ibm_db.exec_immediate', ({(26, 28, 26, 32): 'conn', (26, 34, 26, 40): 'insert'}, {}), '(conn, insert)', False, 'import ibm_db\n'), ((28, 13, 28, 123), 'ibm_db.exec_immediate', 'ibm_db.exec_immediate', ({(28, 35, 28, 39): 'conn', (28, 41, 28, 122): '"""SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"""'}, {}), "(conn,\n    'SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed'\n    )", False, 'import ibm_db\n'), ((59, 6, 59, 27), 'ibm_db.rollback', 'ibm_db.rollback', ({(59, 22, 59, 26): 'conn'}, {}), '(conn)', False, 'import ibm_db\n'), ((31, 15, 31, 43), 'ibm_db.field_num', 'ibm_db.field_num', ({(31, 32, 31, 36): 'stmt', (31, 38, 31, 42): '"""id"""'}, {}), "(stmt, 'id')", False, 'import ibm_db\n'), ((32, 15, 32, 46), 'ibm_db.field_num', 'ibm_db.field_num', ({(32, 32, 32, 36): 'stmt', (32, 38, 32, 45): '"""breed"""'}, {}), "(stmt, 'breed')", False, 'import ibm_db\n'), ((33, 15, 33, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(33, 32, 33, 36): 'stmt', (33, 38, 33, 46): '"""number"""'}, {}), "(stmt, 'number')", False, 'import ibm_db\n'), ((34, 15, 34, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(34, 32, 34, 36): 'stmt', (34, 38, 34, 46): '"""NUMBER"""'}, {}), "(stmt, 'NUMBER')", False, 'import ibm_db\n'), ((35, 15, 35, 46), 'ibm_db.field_num', 'ibm_db.field_num', ({(35, 32, 35, 36): 'stmt', (35, 38, 35, 45): '"""bREED"""'}, {}), "(stmt, 'bREED')", False, 'import ibm_db\n'), ((36, 15, 36, 40), 'ibm_db.field_num', 'ibm_db.field_num', ({(36, 32, 36, 36): 'stmt', (36, 38, 36, 39): '8'}, {}), '(stmt, 8)', False, 'import ibm_db\n'), ((37, 15, 37, 40), 'ibm_db.field_num', 'ibm_db.field_num', ({(37, 32, 37, 36): 'stmt', (37, 38, 37, 39): '1'}, {}), '(stmt, 1)', False, 'import ibm_db\n'), ((38, 15, 38, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(38, 32, 38, 36): 'stmt', (38, 38, 38, 46): '"""WEIGHT"""'}, {}), "(stmt, 'WEIGHT')", False, 'import ibm_db\n'), ((40, 15, 40, 43), 'ibm_db.field_num', 'ibm_db.field_num', ({(40, 32, 40, 36): 'stmt', (40, 38, 40, 42): '"""ID"""'}, {}), "(stmt, 'ID')", False, 'import ibm_db\n'), ((41, 15, 41, 46), 'ibm_db.field_num', 'ibm_db.field_num', ({(41, 32, 41, 36): 'stmt', (41, 38, 41, 45): '"""BREED"""'}, {}), "(stmt, 'BREED')", False, 'import ibm_db\n'), ((42, 15, 42, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(42, 32, 42, 36): 'stmt', (42, 38, 42, 46): '"""NUMBER"""'}, {}), "(stmt, 'NUMBER')", False, 'import ibm_db\n'), ((43, 15, 43, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(43, 32, 43, 36): 'stmt', (43, 38, 43, 46): '"""number"""'}, {}), "(stmt, 'number')", False, 'import ibm_db\n'), ((44, 15, 44, 46), 'ibm_db.field_num', 'ibm_db.field_num', ({(44, 32, 44, 36): 'stmt', (44, 38, 44, 45): '"""Breed"""'}, {}), "(stmt, 'Breed')", False, 'import ibm_db\n'), ((45, 15, 45, 40), 'ibm_db.field_num', 'ibm_db.field_num', ({(45, 32, 45, 36): 'stmt', (45, 38, 45, 39): '8'}, {}), '(stmt, 8)', False, 'import ibm_db\n'), ((46, 15, 46, 40), 'ibm_db.field_num', 'ibm_db.field_num', ({(46, 32, 46, 36): 'stmt', (46, 38, 46, 39): '1'}, {}), '(stmt, 1)', False, 'import ibm_db\n'), ((47, 15, 47, 47), 'ibm_db.field_num', 'ibm_db.field_num', ({(47, 32, 47, 36): 'stmt', (47, 38, 47, 46): '"""weight"""'}, {}), "(stmt, 'weight')", False, 'import ibm_db\n')]