def run_test_116(self):
    conn = None
    is_alive = ibm_db.active(conn)
    if is_alive:
      print("Is active")
    else:
      print("Is not active")

    conn = ibm_db.connect(config.database, config.user, config.password)
    is_alive = ibm_db.active(conn)
    if is_alive:
      print("Is active")
    else:
      print("Is not active")

    ibm_db.close(conn)
    is_alive = ibm_db.active(conn)
    if is_alive:
      print("Is active")
    else:
      print("Is not active")

    # Executing active method multiple times to reproduce a customer reported defect
    print(ibm_db.active(conn))
    print(ibm_db.active(conn))
    print(ibm_db.active(conn))
    conn = ibm_db.connect(config.database, config.user, config.password)
    print(ibm_db.active(conn))
    print(ibm_db.active(conn))
    print(ibm_db.active(conn))
    def __init__(self, vcapServices):  

        db = "SQLDB"
        hostname="75.126.155.153"
        port = "50000"
        username="******"
        password="******"

        if vcapServices is not None: 
            db2info = json.loads(os.environ['VCAP_SERVICES'])['sqldb'][0]  
            db2cred = db2info["credentials"]
            self.sqlConn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")
        else:
            self.sqlConn = ibm_db.connect("DATABASE="+db+";HOSTNAME="+hostname+";PORT="+port+";UID="+username+";PWD="+password+";","","")  
  def run_test_312(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
    
    query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
    
    if conn:
      stmt = ibm_db.prepare(conn, query)
      params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']

      print("Binding parameters")
      for i,p in enumerate(params, 1):
        ibm_db.bind_param(stmt, i, Wrapper(p))
      
      if ibm_db.execute(stmt):
        print("Executing statement")
        ibm_db.execute(stmt)

        # force the cache to be unbound
        for i,p in enumerate(params, 1):
          ibm_db.bind_param(stmt, i, p)
        
        ibm_db.rollback(conn)
      else:
        print("Connection failed.")
 def run_test_091(self):
   try:
     conn = ibm_db.connect(config.database, "y", config.password)
     print "??? No way."
   except:
     err = ibm_db.conn_errormsg()
     print err
 def run_test_124(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
 
   if conn:
      result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id")
      cols = ibm_db.num_fields(result)
      j = 0
      row = ibm_db.fetch_both(result)
      while ( row ):
         for i in range(0, cols):
            field = ibm_db.field_name(result, i)
            value = row[ibm_db.field_name(result, i)]
            if (value == None): 
               value = ''
            print("%s:%s" % (field, value))
         print("---------")
         j += 1
         if (j == 10):
           break
      
         row = ibm_db.fetch_both(result)
      
      ibm_db.close(conn)
      print("done")
   else:
      print(ibm_db.conn_errormsg())
  def run_test_040(self): 
    conn = ibm_db.connect(config.database, config.user, config.password)

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
      
    # Drop the test table, in case it exists
    drop = 'DROP TABLE animals'
    try:
      result = ibm_db.exec_immediate(conn, drop)
    except:
      pass
      
    # Create the test table
    create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
    result = ibm_db.exec_immediate(conn, create)
      
    insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
      
    ibm_db.exec_immediate(conn, insert)
      
    stmt = ibm_db.exec_immediate(conn, "select * from animals")
    
    onerow = ibm_db.fetch_tuple(stmt)
     
    for element in onerow:
      print element

    ibm_db.rollback(conn)
 def run_test_300(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   server = ibm_db.server_info(conn)
   
   if server:
     print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME)
     print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER)
     print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE
     print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME)
     print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME)
     print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS)
     print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
     print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION)
     il = ''
     for opt in server.ISOLATION_OPTION:
       il += opt + " "
     print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
     print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
     print "PROCEDURES:", server.PROCEDURES
     print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
     print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
     print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
     print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
     print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
     print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
     print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
     print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
     print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
     print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
     print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
   
     ibm_db.close(conn)
   else:
     print "Error."
 def run_test_144(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   if conn:
     # Drop the test table, in case it exists
     drop = 'DROP TABLE pictures'
     try:
       result = ibm_db.exec_immediate(conn, drop)
     except:
       pass
     
     # Create the test table
     create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
     result = ibm_db.exec_immediate(conn, create)
     
     stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
     
     picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
     rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BINARY)
   
     rc = ibm_db.execute(stmt)
     
     num = ibm_db.num_rows(stmt)
     
     print(num)
   else:
     print("Connection failed.")
 def run_test_082(self):
   try:
     conn = ibm_db.connect(config.database, config.user, "z")
     print("??? No way.")
   except:
     err = ibm_db.conn_error()
     print(err)
    def run_test_152(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        server = ibm_db.server_info(conn)
        if server.DBMS_NAME[0:3] == "IDS":
            op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
            ibm_db.set_option(conn, op, 1)

        result = ibm_db.exec_immediate(conn, "select * from project")

        row = ibm_db.fetch_assoc(result)
        while row:
            # printf("%6s ",row['PROJNO'])
            # printf("%-24s ",row['PROJNAME'])
            # printf("%3s ",row['DEPTNO'])
            # printf("%6s",row['RESPEMP'])
            # printf("%7s ",row['PRSTAFF'])
            # printf("%10s ",row['PRSTDATE'])
            # printf("%10s ",row['PRENDATE'])
            # printf("%6s",row['MAJPROJ'])
            # puts ""
            if row["MAJPROJ"] == None:
                row["MAJPROJ"] = ""
            print "%6s %-24s %3s %6s%7s %10s %10s %6s" % (
                row["PROJNO"],
                row["PROJNAME"],
                row["DEPTNO"],
                row["RESPEMP"],
                row["PRSTAFF"],
                row["PRSTDATE"],
                row["PRENDATE"],
                row["MAJPROJ"],
            )
            row = ibm_db.fetch_assoc(result)
 def run_test_155(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   serverinfo = ibm_db.server_info( conn )
   
   result = ibm_db.exec_immediate(conn, "select * from employee where lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
   i=0
   row = ibm_db.fetch_assoc(result)
   while ( row ):
     i += 1
     if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
       if (row['midinit'] == None):
         row['midinit'] = ''
       print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
         (row['empno'], row['firstnme'], row['midinit'], row['lastname'], row['workdept'], \
         row['phoneno'], row['hiredate'], row['job'], row['edlevel'], row['sex'], \
         row['birthdate'], row['salary'], row['bonus'], row['comm']))
       row = ibm_db.fetch_assoc(result)
     else:
       if (row['MIDINIT'] == None):
         row['MIDINIT'] = ''
       print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
         (row['EMPNO'], row['FIRSTNME'], row['MIDINIT'], row['LASTNAME'], row['WORKDEPT'], \
         row['PHONENO'], row['HIREDATE'], row['JOB'], row['EDLEVEL'], row['SEX'], \
         row['BIRTHDATE'], row['SALARY'], row['BONUS'], row['COMM']))
       row = ibm_db.fetch_assoc(result)
   print("%d record(s) selected." % i)
Esempio n. 12
0
  def assert_expectf(self, testFuncName):
    callstack = inspect.stack(0)
    try:
      prepconn = ibm_db.connect(config.database, config.user, config.password)
      server = ibm_db.server_info(prepconn)
      ibm_db.close(prepconn)
      if (server.DBMS_NAME[0:2] == "AS"):
          pattern = self.expected_AS(callstack[1][1])
      elif (server.DBMS_NAME == "DB2"):
          pattern = self.expected_ZOS(callstack[1][1])
      elif (server.DBMS_NAME[0:3] == "IDS"):
          pattern = self.expected_IDS(callstack[1][1])
      else:
          pattern = self.expected_LUW(callstack[1][1])
      
      sym = ['\[','\]','\(','\)']
      for chr in sym:
          pattern = re.sub(chr, '\\' + chr, pattern)

      pattern = re.sub('%s', '.*?', pattern)
      pattern = re.sub('%d', '\\d+', pattern)

      result = re.match(pattern, self.capture(testFuncName))
      self.assertNotEqual(result, None)
    finally:
      del callstack
  def run_test_017(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    if conn:
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()


      ibm_db.close(conn)
    else:
      print "no connection:", ibm_db.conn_errormsg()
  def run_test_195(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
      drop = 'DROP TABLE test_195'
      try:
        result = ibm_db.exec_immediate(conn, drop)
      except:
        pass
      create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
      result = ibm_db.exec_immediate(conn, create)
    
      insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
    
      ibm_db.exec_immediate(conn, insert)
    
      sql =  "SELECT data FROM test_195"
      stmt = ibm_db.prepare(conn, sql)
      ibm_db.execute(stmt)
      result = ibm_db.fetch_assoc(stmt)
      while( result ):
        print "Output:", result
	result = ibm_db.fetch_assoc(stmt)
      ibm_db.close(conn)
    else:
      print "Native XML datatype is not supported."
 def run_test_022(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   if conn:
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.autocommit(conn, 0)
     ac = ibm_db.autocommit(conn)
     if ac != 0:
       print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
       #continue
       
     ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
       
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.rollback(conn)
     
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
     ibm_db.close(conn)
   else:
     print("Connection failed.")
 def run_test_142(self):
   sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
   
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   if conn:
     stmt = ibm_db.prepare(conn, sql)
   
     weight = 200.05
     mass = 2.0
     
     ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
     ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
   
     result = ibm_db.execute(stmt) 
     if ( result ):
       row = ibm_db.fetch_tuple(stmt)
       while ( row ):
         #row.each { |child| print child }
         for i in row:
           print i
         row = ibm_db.fetch_tuple(stmt)
     ibm_db.close(conn)
   else:
     print "Connection failed."
  def run_test_038(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    serverinfo = ibm_db.server_info( conn )

    if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
    else:
      result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")

    row = ibm_db.fetch_row(result)
    while ( row ):
      if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
        result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
      else:
        result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
      ibm_db.execute(result2)
      row2 = ibm_db.fetch_row(result2)
      while ( row2 ):
        print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
                                            ibm_db.result(result2, 1), \
                                            ibm_db.result(result2, 2), \
                                            ibm_db.result(result2, 3), \
                                            ibm_db.result(result2, 5)))
        row2 = ibm_db.fetch_row(result2)
      row = ibm_db.fetch_row(result)
  def run_test_03a(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if conn:
      stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
      
      while ( ibm_db.fetch_row(stmt) ):
         breed = ibm_db.result(stmt, 1)
         print ("string(%d) \"%s\"" % (len(breed), breed))
         if (server.DBMS_NAME[0:3] == 'IDS'):
            name = ibm_db.result(stmt, "name")
         else:
            name = ibm_db.result(stmt, "NAME")
         print ("string(%d) \"%s\"" % (len(name), name))
    
         # following field does not exist in result set
         if (server.DBMS_NAME[0:3] == 'IDS'):
           name = ibm_db.result(stmt, "passport")
         else:
           name = ibm_db.result(stmt, "PASSPORT")
         print (name)
      ibm_db.close(conn)
      
    else:
      print ("Connection failed.")
Esempio n. 19
0
 def run_test_180(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   if conn:
     result = ''
     result2 = ''
     try:
       result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')")
     except:
       pass
     if result:
       cols = ibm_db.num_fields(result)
       print("col:", cols,", ") 
       rows = ibm_db.num_rows(result)
       print("affected row:", rows)
     else:
       print(ibm_db.stmt_errormsg())
     try:
       result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
     except:
       pass
     if result:
       cols = ibm_db.num_fields(result)
       print("col:", cols,", ")
       rows = ibm_db.num_rows(result)
       print("affected row:", rows)
     else:
       print(ibm_db.stmt_errormsg())
   
   else:
     print("no connection")
  def run_test_032(self):
      conn = ibm_db.connect(config.database, config.user, config.password)
      server = ibm_db.server_info( conn )

      if conn:
        stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6")
        
        while (ibm_db.fetch_row(stmt)):
          if (server.DBMS_NAME[0:3] == 'IDS'):
            id = ibm_db.result(stmt, "id")
            breed = ibm_db.result(stmt, "breed")
            name = ibm_db.result(stmt, "name")
            weight = ibm_db.result(stmt, "weight")
          else:
            id = ibm_db.result(stmt, "ID")
            breed = ibm_db.result(stmt, "BREED")
            name = ibm_db.result(stmt, "NAME")
            weight = ibm_db.result(stmt, "WEIGHT")
          print "int(%d)" % id
          print "string(%d) \"%s\"" % (len(breed), breed)
          print "string(%d) \"%s\"" % (len(name), name)
          print "string(%d) \"%s\"" % (len(str(weight)), weight)
        ibm_db.close(conn)
      else:
        print "Connection failed."
  def run_test_006(self):    

    options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE:  ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
    options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
      
    conn = ibm_db.connect(config.database, config.user, config.password)
  
    if conn:
      serverinfo = ibm_db.server_info( conn )

      if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
        options1 = options2

      stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
      ibm_db.execute(stmt)
      data = ibm_db.fetch_both(stmt)
      while ( data ):
        print (data[0])
        data = ibm_db.fetch_both(stmt)
      
      print ("")

      stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
      ibm_db.execute(stmt)
      data = ibm_db.fetch_both(stmt)
      while ( data ):
        print (data[0])
        data = ibm_db.fetch_both(stmt)
    
      ibm_db.close(conn)
    else:
      print ("Connection failed.")
  def run_test_112(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    if conn:
      drop = "DROP TABLE ftest"
      try:
        ibm_db.exec_immediate( conn, drop )
      except:
        pass
      
      create = "CREATE TABLE ftest ( \"TEST\" INTEGER, \"test\" INTEGER, \"Test\" INTEGER  )"
      ibm_db.exec_immediate(conn, create)
      
      insert = "INSERT INTO ftest VALUES (1,2,3)"
      ibm_db.exec_immediate(conn, insert)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest")
    
      num1 = ibm_db.field_num(stmt, "TEST")
      num2 = ibm_db.field_num(stmt, 'test')
      num3 = ibm_db.field_num(stmt, 'Test')
      
      print("int(%d)" % num1)
      print("int(%d)" % num2)
      print("int(%d)" % num3)
      
    else:
      print("Connection failed.")
  def run_test_150(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
      ibm_db.set_option(conn, op, 1)
    
    result = ibm_db.exec_immediate(conn, "select * from staff")

    row = ibm_db.fetch_assoc(result)    
    while ( row ):
      #print "%5d  " % row['ID']
      #print "%-10s " % row['NAME']
      #print "%5d " % row['DEPT']
      #print "%-7s " % row['JOB']
      #print "%5d " % row['YEARS']
      #print "%15s " % row['SALARY']
      #print "%10s " % row['COMM']
      if (row['YEARS'] == None):
        row['YEARS'] = 0
      if (row['COMM'] == None):
        row['COMM'] = ''
      print "%5d  %-10s %5d %-7s %5s %15s %10s " % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM'])
      row = ibm_db.fetch_assoc(result)
  def run_test_158(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
      ibm_db.set_option(conn, op, 1)

    result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
    
    output = ''
    row = ibm_db.fetch_assoc(result)
    while ( row ):
      output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
      row = ibm_db.fetch_assoc(result)
      
    result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
    row2 = ibm_db.fetch_assoc(result2)
    while ( row2 ):
        if (row2['MGRNO'] == None): 
            row2['MGRNO'] = ''
        if (row2['LOCATION'] == None): 
            row2['LOCATION'] = ''
        output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
        row2 = ibm_db.fetch_assoc(result2)
    
    result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
    row3 = ibm_db.fetch_tuple(result3)
    while ( row3 ):
        output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
        row3=ibm_db.fetch_tuple(result3)
    print(output)
Esempio n. 25
0
 def get_conn(self):
     conn = ibm_db.connect("HOSTNAME=%s;PROTOCOL=TCPIP;PORT=%d;DATABASE=%s;UID=%s;PWD=%s;"
                           % (self.host, self.port, self.dbname, self.user, self.password), '', '')
     # turn off autocommit: SQL_AUTOCOMMIT_OFF
     if self.manual_commit:
         ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
     return conn
  def run_test_100(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

      stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals ORDER BY breed")
    
      fields1 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields1)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
      fields2 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields2)
      
      stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
      fields3 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields3)
      
      stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
      fields4 = ibm_db.num_fields(stmt)
        
      print("int(%d)" % fields4)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
      fields5 = ibm_db.num_fields(stmt)
        
      print("int(%d)" % fields5)

      ibm_db.rollback(conn)
    else:
      print("Connection failed.")
def dB_init():
	dbtry = 0
	while (dbtry < 3):
		try:
			if 'VCAP_SERVICES' in os.environ:
			    hasVcap = True
			    import json
			    vcap_services = json.loads(os.environ['VCAP_SERVICES'])
			    if 'dashDB' in vcap_services:
			        hasdashDB = True
			        service = vcap_services['dashDB'][0]
			        credentials = service["credentials"]
			        url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % ( credentials["db"],credentials["username"],credentials["password"],credentials["host"],credentials["port"])
			    	print "VCAP",url    
			    else:
			        hasdashDB = False
			else:
			    hasVcap = False
			    url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % (DB_NAME,DB_USER_NAME,DB_PASSWORD,DB_HOST,DB_PORT)
			connection = ibm_db.connect(url, '', '')
			if (active(connection)):
				return connection
		except Exception as error:
			logging.debug("dataBase connection_ERROR : " + str(error))
			dbtry+=1
	return None	
 def run_test_090(self):
   try:
     conn = ibm_db.connect("x", config.user, config.password)
     print("??? No way.")
   except:
     err = ibm_db.conn_errormsg()
     print(err)
Esempio n. 29
0
    def run_test_034(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        server = ibm_db.server_info(conn)
        if server.DBMS_NAME[0:3] == "IDS":
            op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
            ibm_db.set_option(conn, op, 1)

        result = ibm_db.exec_immediate(conn, "select * from staff")
        row = ibm_db.fetch_assoc(result)
        if row:
            # printf("%5d  ",row['ID'])
            # printf("%-10s ",row['NAME'])
            # printf("%5d ",row['DEPT'])
            # printf("%-7s ",row['JOB'])
            # printf("%5d ", row['YEARS'])
            # printf("%15s ", row['SALARY'])
            # printf("%10s ", row['COMM'])
            # puts ""
            print "%5d %-10s %5d %-7s %5d %15s %10s" % (
                row["ID"],
                row["NAME"],
                row["DEPT"],
                row["JOB"],
                row["YEARS"],
                row["SALARY"],
                row["COMM"],
            )

        ibm_db.close(conn)
def dashdB_Init():
	global connection,url
	dbtry = 0
	while(dbtry <3):
		try:
			if 'VCAP_SERVICES' in os.environ:
			    hasVcap = True
			    import json
			    vcap_services = json.loads(os.environ['VCAP_SERVICES'])
			    if 'dashDB' in vcap_services:
			        hasdashDB = True
			        service = vcap_services['dashDB'][0]
			        credentials = service["credentials"]
			        url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % ( credentials["db"],credentials["username"],credentials["password"],credentials["host"],credentials["port"])
			    else:
			        hasdashDB = False
			  
			else:
			    hasVcap = False
			    url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % (DB_NAME,DB_USER_NAME,DB_PASSWORD,DB_HOST,DB_PORT)
   
			connection = ibm_db.connect(url, '', '')
			if (active(connection)):
				return connection
		except Exception as dberror:
			logging.error("dberror Exception %s"%dberror)
			dbtry+=1
	return False
class IbmDbTestFunctions(unittest.TestCase):
    prepconn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info(prepconn)
    ibm_db.close(prepconn)

    # See the tests.py comments for this function.
    def setUp(self):
        pass

    # This function captures the output of the current test file.
    def capture(self, func):
        buffer = StringIO()
        sys.stdout = buffer
        func()
        sys.stdout = sys.__stdout__
        # str() ensures not Unicode object on Python 2
        var = str(buffer.getvalue())
        var = var.replace('\n', '').replace('\r', '')
        return var

    # This function grabs the expected output of the current test function for LUW,
    #   located at the bottom of the current test file.
    def expected_LUW(self, fileName):
        fileHandle = open(fileName, 'r')
        fileInput = fileHandle.read().split('#__LUW_EXPECTED__')[-1].split(
            '#__ZOS_EXPECTED__')[0].replace('\n', '').replace('#', '')
        fileHandle.close()
        return fileInput

    # This function grabs the expected output of the current test function for IDS,
    #   located at the bottom of the current test file.
    def expected_IDS(self, fileName):
        fileHandle = open(fileName, 'r')
        fileInput = fileHandle.read().split('#__IDS_EXPECTED__')[-1].replace(
            '\n', '').replace('#', '')
        fileHandle.close()
        return fileInput

    # This function grabs the expected output of the current test function for zOS,
    #   located at the bottom of the current test file.
    def expected_ZOS(self, fileName):
        fileHandle = open(fileName, 'r')
        fileInput = fileHandle.read().split('#__ZOS_EXPECTED__')[-1].split(
            '#__SYSTEMI_EXPECTED__')[0].replace('\n', '').replace('#', '')
        fileHandle.close()
        return fileInput

    # This function grabs the expected output of the current test function for zOS,
    #   located at the bottom of the current test file.
    def expected_AS(self, fileName):
        fileHandle = open(fileName, 'r')
        fileInput = fileHandle.read().split('#__SYSTEMI_EXPECTED__')[-1].split(
            '#__IDS_EXPECTED__')[0].replace('\n', '').replace('#', '')
        fileHandle.close()
        return fileInput

    # This function compares the captured outout with the expected out of
    #   the current test file.
    def assert_expect(self, testFuncName):
        callstack = inspect.stack(0)
        try:
            if (self.server.DBMS_NAME[0:2] == "AS"):
                self.assertEqual(self.capture(testFuncName),
                                 self.expected_AS(callstack[1][1]))
            elif (self.server.DBMS_NAME == "DB2"):
                self.assertEqual(self.capture(testFuncName),
                                 self.expected_ZOS(callstack[1][1]))
            elif (self.server.DBMS_NAME[0:3] == "IDS"):
                self.assertEqual(self.capture(testFuncName),
                                 self.expected_IDS(callstack[1][1]))
            else:
                self.assertEqual(self.capture(testFuncName),
                                 self.expected_LUW(callstack[1][1]))

        finally:
            del callstack

    # This function will compare using Regular Expressions
    # based on the servre
    def assert_expectf(self, testFuncName):
        callstack = inspect.stack(0)
        try:
            if (self.server.DBMS_NAME[0:2] == "AS"):
                pattern = self.expected_AS(callstack[1][1])
            elif (self.server.DBMS_NAME == "DB2"):
                pattern = self.expected_ZOS(callstack[1][1])
            elif (self.server.DBMS_NAME[0:3] == "IDS"):
                pattern = self.expected_IDS(callstack[1][1])
            else:
                pattern = self.expected_LUW(callstack[1][1])

            sym = ['\[', '\]', '\(', '\)']
            for chr in sym:
                pattern = re.sub(chr, '\\' + chr, pattern)

            pattern = re.sub('%s', '.*?', pattern)
            if sys.version_info >= (3, 7):
                pattern = re.sub('%d', r'\\d+', pattern)
            else:
                pattern = re.sub('%d', '\\d+', pattern)

            result = re.match(pattern, self.capture(testFuncName))
            self.assertNotEqual(result, None)
        finally:
            del callstack

    #def assert_throw_blocks(self, testFuncName):
    #  callstack = inspect.stack(0)
    #  try:

    # This function needs to be declared here, regardless of if there
    #   is any body to this function
    def runTest(self):
        pass
def value_based_prediction():
    if (check_correct_login("p")):
        if (request.method == "POST"):
            age = int(request.form["age"])
            weight = float(request.form["weight"])
            height = float(request.form["height"])
            ap_hi = float(request.form["ap_hi"])
            ap_lo = float(request.form["ap_lo"])
            chol = int(request.form["chol"])
            gluc = int(request.form["gluc"])
            gender = int(request.form["gender"])
            smoke = int(request.form["smoke"])
            alco = int(request.form["alco"])
            active = int(request.form["active"])
            enm = float(request.form["enm"])
            csd = float(request.form["csd"])
            dob = float(request.form["dob"])
            gd = float(request.form["gd"])
            dms = float(request.form["dms"])

            features = [
                enm, csd, dob, gd, dms, weight, ap_hi, ap_lo, smoke, gender
            ]
            lab_dict = request.form
            dis_count = enm + csd + dob + gd + dms
            features.append(dis_count)
            fin_features = [np.array(features)]
            result = diabetes_model.predict(fin_features)
            result = result[0]
            diab = 0
            card = 0
            if result:
                diab = 1
            df["age"] = age
            if (gender == 0):
                df["gender"] = 1
            df["ap_hi"] = ap_hi
            df["ap_lo"] = ap_lo
            df["smoke"] = smoke
            df["alco"] = alco
            df["active"] = active
            bmi = weight / ((height / 100)**2)
            df["bmi"] = bmi
            df["pulse_pressure"] = ap_hi - ap_lo

            if (bmi < 18.5):
                df["Underweight"] = 1
            elif (bmi < 25):
                df["Healthy"] = 1
            elif (30 <= bmi < 35):
                df["Obese"] = 1
            elif (35 <= bmi < 40):
                df["Severly Obese"] = 1
            elif (35 <= bmi < 40):
                df["Abnormal"] = 1

            if (chol < 200):
                df["chol_1"] = 1
            elif (chol > 240):
                df["chol_3"] = 1

            if (gluc < 115):
                df["gluc_1"] = 1
            elif (gluc > 185):
                df["gluc_3"] = 1
            ypred = cardio_model.predict(df)
            if (ypred[0] > (0.5)):
                card = 1
            elif (ypred[0] > (0.3)):
                card = 2

            prob = int(ypred[0] * 100)
            # return "<h1>"+str(card)+""+str(diab)+"</h1>"
            conn = ibm_db.connect(dsn, "", "")
            from datetime import datetime
            now = datetime.now()

            formatted_date = now.strftime('%Y-%m-%d %H:%M:%S')
            lis = [
                session["user_id"], formatted_date, age, weight, height, ap_hi,
                ap_lo, chol, gluc, gender, smoke, alco, active, enm, csd, dob,
                gd, dms, card, diab
            ]
            sql = "insert into history values %r;" % (tuple(lis), )
            res = ibm_db.exec_immediate(conn, sql)
            return render_template("result_vbp.html",
                                   card=card,
                                   diab=diab,
                                   prob=prob)

        return render_template("p_value_pred.html")

    else:
        flash("Patient Login Required !", category="error")
        return redirect(url_for('login'))
Esempio n. 33
0
    def run_test_200(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        serverinfo = ibm_db.server_info(conn)
        server = serverinfo.DBMS_NAME[0:3]
        if (server == 'IDS'):
            procedure = """
        CREATE FUNCTION multiResults()
         RETURNING CHAR(16), INT;
                
         DEFINE p_name CHAR(16);
         DEFINE p_id INT;
               
         FOREACH c1 FOR
             SELECT name, id
              INTO p_name, p_id
               FROM animals
               ORDER BY name
              RETURN p_name, p_id WITH RESUME;
         END FOREACH;
                
       END FUNCTION;
       """
        else:
            procedure = """
        CREATE PROCEDURE multiResults ()
        RESULT SETS 3
        LANGUAGE SQL
        BEGIN
         DECLARE c1 CURSOR WITH RETURN FOR
          SELECT name, id
          FROM animals
          ORDER BY name;
    
         DECLARE c2 CURSOR WITH RETURN FOR
          SELECT name, id
          FROM animals
          WHERE id < 4
          ORDER BY name DESC;
    
         DECLARE c3 CURSOR WITH RETURN FOR
          SELECT name, id
          FROM animals
          WHERE weight < 5.0
          ORDER BY name;
    
         OPEN c1;
         OPEN c2;
         OPEN c3;
        END
       """

        if conn:
            try:
                ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
            except:
                pass
            ibm_db.exec_immediate(conn, procedure)
            stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()')
            #print(stmt)
            print("Fetching first result set")
            row = ibm_db.fetch_tuple(stmt)
            while (row):
                for i in row:
                    print(i)
                row = ibm_db.fetch_tuple(stmt)

            if (server == 'IDS'):
                print(
                    "Fetching second result set (should fail -- IDS does not support multiple result sets)"
                )
            else:
                print("Fetching second result set")
            #print(stmt)
            res = ibm_db.next_result(stmt)
            if res:
                row = ibm_db.fetch_tuple(res)
                while (row):
                    for i in row:
                        print(i)
                    row = ibm_db.fetch_tuple(res)

            if (server == 'IDS'):
                print(
                    "Fetching third result set (should fail -- IDS does not support multiple result sets)"
                )
            else:
                print("Fetching third result set")
            res2 = ibm_db.next_result(stmt)
            if res2:
                row = ibm_db.fetch_tuple(res2)
                while (row):
                    for i in row:
                        print(i)
                    row = ibm_db.fetch_tuple(res2)

            print("Fetching fourth result set (should fail)")
            res3 = ibm_db.next_result(stmt)
            if res3:
                row = ibm_db.fetch_tuple(res3)
                while (row):
                    for i in row:
                        print(i)
                    row = ibm_db.fetch_tuple(res3)

            ibm_db.close(conn)
        else:
            print("Connection failed.")
Esempio n. 34
0
 def run_test_080(self):
     try:
         conn = ibm_db.connect("x", config.user, config.password)
         print("??? No way.")
     except:
         print(ibm_db.conn_error())
Esempio n. 35
0
from nltk.tokenize import RegexpTokenizer
from nltk.corpus import stopwords
from itertools import chain
from datetime import datetime, date, time, timedelta
import re, string
from textblob import TextBlob

import os
import time

import ibm_db

##################### Extracción de datos operaciones #########################

conn = ibm_db.connect(
    "DATABASE=bludb;HOSTNAME=slpedw.iadb.org;PORT=50001;security=ssl;UID=mariarey;PWD=password;",
    "", "")  #Abriendo conexión con repositorio de datos DB2

sql = "SELECT DISTINCT 	C.OPER_NUM as OPERATION_NUMBER,	C.OPER_ENGL_NM as OPERATION_NAME, C.OPERTYP_ENGL_NM AS OPERATION_TYPE_NAME, C.MODALITY_CD AS OPERATION_MODALITY, C.PREP_RESP_DEPT_CD AS DEPARTMENT, C.PREP_RESP_DIV_CD AS DIVISION,\
	C.REGN AS REGION, C.CNTRY_BENFIT AS COUNTRY, C.STS_CD AS STATUS, C.STG_ENGL_NM AS STAGE, C.STS_ENGL_NM AS TAXONOMY, C.OPER_EXEC_STS AS EXEC_STS, C.APPRVL_DT AS APPROVAL_DATE, 	C.APPRVL_DT_YR as APPROVAL_YEAR,\
    C.ORIG_APPRVD_USEQ_AMNT AS APPROVAL_AMOUNT, C.CURNT_DISB_EXPR_DT as CURRENT_EXPIRATION_DATE, C.RELTN_NUM AS RELATED_OPER,C.FACILITY_TYP_CD AS RELATION_TYPE, C.OPER_TYP_CD AS OPERATION_TYPE, A.OBJTV_ENGL as OBJECTIVE_EN,\
    A.OBJTV_SPANISH as OBJECTIVE_ES, B.CMPNT_STMNT as COMPONENT_NAME, B.OUTPUT_DEFNTN as OUTPUT_NAME, C.FACILITY_TYP_ENGL_NM AS OUTPUT_DESCRIPTION \
FROM ODS.SPD_ODS_HOPERMAS C \
	JOIN ( select OPER_NUM, MAX(DW_CRTE_TS) AS MAX_DT from ODS.SPD_ODS_HOPERMAS GROUP BY OPER_NUM) t ON C.OPER_NUM= t.OPER_NUM and C.DW_CRTE_TS = t.MAX_DT \
 	JOIN ODS.OPER_ODS_OPER A ON C.OPER_NUM = A.OPER_NUM \
 	JOIN ODS.OPER_ODS_OUTPUT_IND B ON C.OPER_NUM = B.OPER_NUM \
WHERE C.APPRVL_DT_YR > 2015  AND C.PREP_RESP_DEPT_CD='SCL' AND DATE(C.APPRVL_DT)<DATE(NOW()) "                                                                                               #SQL query de datos deseados, MRT: se agrega filtro de fecha de aprobación menor al día de hoy y se quita =ACTIVE

stmt = ibm_db.exec_immediate(conn, sql)  #Querying data

#Creando base de datos con query
Esempio n. 36
0
 def __init__(self, target_db, db_user, db_password):
     self.target_db = target_db
     self.db_user = db_user
     self.db_password = db_password
     self.dbconn = ibm_db.connect(self.target_db, self.db_user, self.db_password)
Esempio n. 37
0
    def run_test_050(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        ac = ibm_db.autocommit(conn)

        print(ac)
Esempio n. 38
0
    except mariadb.Error as error:
        print("ERROR: No se pudo conectar a MongoDB :".format(error))
    MariaDB_Cnx.close()
elif (MBD == "7"):
    import ibm_db
    ################################################
    # Conexión con una base de datos no catalogada #
    ################################################
    # ibm_db.connect("DATABASE=DBAS; HOSTNAME=HOST; PORT=PORT; PROTOCOL=TCPIP; UID=USER; PWD=PASS;", "", "")
    # Conn = ibm_db.connect(DBAS, USER, PASS)

    #####################################################
    # Conexión con una base de datos local o catalogada #
    #####################################################
    try:
        Conn = ibm_db.connect(DBAS, USER, PASS)
        Sql = QRY
        Stmt = ibm_db.exec_immediate(Conn, Sql)
        if Conn:
            if (TdC != "2"):
                print("Número de filas afectadas : ", ibm_db.num_rows(Stmt))
            else:
                Result = ibm_db.fetch_both(Stmt)
                while (Result):
                    print(Result)
                    Result = ibm_db.fetch_both(Stmt)
    except:
        print("ERROR: No se pudo realizar la conecxión :",
              ibm_db.conn_errormsg())
    else:
        print("AVISO: La conexión se realizó de manera existosa.....")
Esempio n. 39
0
def register2(request, forename, surname, postcode, email):

    # DB2 Connection
    ibm_db_conn = ibm_db.connect(
        "DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
        "", "")
    conn = ibm_db_dbi.Connection(ibm_db_conn)
    cur = conn.cursor()

    cur.execute(
        """
    select distinct t1.PER_GID, t1.PER_TITLE_CODE as TITLE, t1.PER_FIRST_INITIAL as INITIAL, t1.PER_FORENAME as FORENAME, t1.PER_SURNAME as SURNAME, t1.PER_STATUS_CODE as STATUS,
        CASE t1.PER_GENDER_CODE
        WHEN '1' THEN 'MALE'
        WHEN '2' THEN 'FEMALE'
        ELSE 'Other'
        END as GENDER,
        t3.PER_CP_CAT_CODE as CONTACT_TYPE, t3.PER_CP_VALUE as CONTACT_VALUE, t2.PER_POSTAL_ADDR_POSTCODE as POSTCODE, t2.PER_POSTAL_ADDR_LINE_1 as ADDRESS_LINE1, t2.PER_POSTAL_ADDR_LINE_2 as ADDRESS_LINE2
    from OPGCUK.PERSON_OPGC t1
    join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
    join OPGCUK.PERSON_CONTACT_POINT_OPGC t3 on t3.PER_GID = t1.PER_GID
    where t2.PER_POSTAL_ADDR_POSTCODE = ?
    and t1.PER_SURNAME = ?
    and t1.PER_FIRST_INITIAL = ?""",
        [upper(postcode), upper(surname),
         upper(forename[0])])

    cust = []
    for obj in cur.fetchall():
        cust.append({
            "PER_GID": obj[0],
            "TITLE": obj[1],
            "INITIAL": obj[2],
            "FORENAME": obj[3],
            "SURNAME": obj[4],
            "STATUS": obj[5],
            "GENDER": obj[6],
            "CONTACT_TYPE": obj[7],
            "CONTACT_VALUE": obj[8],
            "POSTCODE": obj[9],
            "ADDRESS_LINE1": obj[10],
            "ADDRESS_LINE2": obj[11]
        })
    tab_len = len(cust)

    cur.execute(
        """
    select distinct t2.PER_GID, t4.ITEM_ID as PLAN
    from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
    join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
    join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
    join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
    where left(t4.ITEM_ID,4) = 'PLN:'
    and t3.ITEM_COVER_DG_CONTR_STATUS_CODE in ('N','R','L') -- exclude Cancelled
    and t2.PER_GID IN (
    select distinct t1.PER_GID
            from OPGCUK.PERSON_OPGC t1
            join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
            where t2.PER_POSTAL_ADDR_POSTCODE = ?
            and t1.PER_SURNAME = ?
            and t1.PER_FIRST_INITIAL = ?)""",
        [upper(postcode), upper(surname),
         upper(forename[0])])

    plans = []
    for obj in cur.fetchall():
        plans.append({"PER_GID": obj[0], "PLAN": obj[1]})

    # DB2 Connection CLOSE
    cur.close()
    conn.close()

    gid = ''

    if request.method == 'POST':
        form = Register2(request.POST)
        if form.is_valid():
            phone = form.cleaned_data.get('phone')
            line1 = form.cleaned_data.get('line1')
            plan = form.cleaned_data.get('plan')

            gid_count = 0

            if plan:
                for row in plans:
                    if plan == row['PLAN']:
                        gid = row['PER_GID']
                        gid_count = 1
            else:
                i = 0
                for row in cust:
                    if row['CONTACT_VALUE'] == phone or line1 == row[
                            'ADDRESS_LINE1'] or line1 == row['ADDRESS_LINE2']:
                        i += 1
                        if i == 1:
                            gid = row['PER_GID']
                            gid_count = 1
                        elif gid != row['PER_GID']:
                            gid_count = gid_count + 1

            if gid_count == 1:
                messages.success(
                    request,
                    f'Customer {forename} {surname} @ {postcode} was found.')
                # phone = ''
                return redirect('find-dashboard', gid, forename, surname,
                                postcode, email)
            elif plan and gid_count == 0:
                messages.warning(request, f'Invalid Plan Number: {plan}')
                form = Register2()
            else:
                messages.warning(request,
                                 f'Please enter more details or Plan Number.')
                form = Register2()

    else:

        i = 0
        for row in cust:
            if row['CONTACT_VALUE'] == upper(
                    email) or forename == row['FORENAME']:
                i += 1
                if i == 1:
                    gid = row['PER_GID']
                elif gid != row['PER_GID']:
                    messages.warning(
                        request,
                        f'Please enter additional details required for your identification.'
                    )

        if i == 1:
            print('Found one - GID :', gid)
            messages.success(
                request,
                f'Thank you {forename} {surname} your account was created.')
            phone = ''
            return redirect('find-dashboard', gid, forename, surname, postcode,
                            email)
        else:
            messages.warning(
                request,
                f'Please enter additional details required for your identification.'
            )
            # send data to form
            form = Register2()
            args = {
                'form': form,
                'table': cust,
                'tab_len': tab_len,
                'plans': plans,
                'forename': forename,
                'surname': surname,
                'postcode': postcode,
                'email': email
            }
            return render(request, 'find/home.html', args)

    args = {
        'form': form,
        'table': cust,
        'tab_len': tab_len,
        'plans': plans,
        'forename': forename,
        'surname': surname,
        'postcode': postcode,
        'email': email
    }
    return render(request, 'find/home.html', args)
Esempio n. 40
0
import ibm_db, os, json

connection = ibm_db.connect(
    "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=vft79804;PWD=7c4r31g+3bwb6fxf;",
    "", "")
Esempio n. 41
0
# -*- coding: utf-8 -*-
import ibm_db
import pandas as pd
conn = ibm_db.connect("dsn=sample", "administrator", "ctl7220fe")
sql = "SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE EMPNO > ? AND EMPNO < ?"
stmt = ibm_db.prepare(conn, sql)
max = 50
min = 0
# Explicitly bind parameters
ibm_db.bind_param(stmt, 1, min)
ibm_db.bind_param(stmt, 2, max)
ibm_db.execute(stmt)
# Process results
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
df = pd.DataFrame(dictionary.items())
print(df, '-----------------------------------')
while dictionary != False:
    for key in dictionary:
        print(key, dictionary[key])
    dictionary = ibm_db.fetch_both(stmt)
sql1 = "update A1 set N = ?"
stmt1 = ibm_db.prepare(conn, sql1)
i = 'scy'
ibm_db.bind_param(stmt1, 1, i)
ibm_db.execute(stmt1)

#
dsn = ("DRIVER={0};"
       "DATABASE={1};"
       "HOSTNAME={2};"
       "PORT={3};"
       "PROTOCOL={4};"
       "UID={5};"
       "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
                          dsn_protocol, dsn_uid, dsn_pwd)

#print the connection string to check correct values are specified
print(dsn)

#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create database connection
try:
    conn = ibm_db.connect(dsn, "", "")
    print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)

except:
    print("Unable to connect: ", ibm_db.conn_errormsg())

#Retrieve Metadata for the Database Server
server = ibm_db.server_info(conn)

print("DBMS_NAME: ", server.DBMS_NAME)
print("DBMS_VER:  ", server.DBMS_VER)
print("DB_NAME:   ", server.DB_NAME)

#Retrieve Metadata for the Database Client / Driver
client = ibm_db.client_info(conn)
Esempio n. 43
0
    def run_test_decfloat(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            serverinfo = ibm_db.server_info(conn)

            drop = "DROP TABLE STOCKPRICE"
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass

            # Create the table stockprice
            if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
                create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
            else:
                create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
            result = ibm_db.exec_immediate(conn, create)

            # Insert Directly
            insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
            result = ibm_db.exec_immediate(conn, insert)

            # Prepare and Insert in the stockprice table
            stockprice = (\
                    (20, "Zaral", 102.205, "100.234"),\
                    (30, "Megabyte", 98.65, "1002.112"),\
                    (40, "Visarsoft", 123.34, "1652.345"),\
                    (50, "Mailersoft", 134.22, "1643.126"),\
                    (60, "Kaerci", 100.97, "9876.765")\
                )
            insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
            stmt = ibm_db.prepare(conn, insert)
            if stmt:
                for company in stockprice:
                    result = ibm_db.execute(stmt, company)

            id = 70
            company = 'Nirvana'
            stockshare = 100.1234
            stockprice = "100.567"
            try:
                ibm_db.bind_param(stmt, 1, id)
                ibm_db.bind_param(stmt, 2, company)
                ibm_db.bind_param(stmt, 3, stockshare)
                ibm_db.bind_param(stmt, 4, stockprice)
                error = ibm_db.execute(stmt)
            except:
                excp = sys.exc_info()
                # slot 1 contains error message
                print excp[1]

            # Select the result from the table and
            query = 'SELECT * FROM STOCKPRICE ORDER BY id'
            if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
                stmt = ibm_db.prepare(conn, query, {
                    ibm_db.SQL_ATTR_CURSOR_TYPE:
                    ibm_db.SQL_CURSOR_KEYSET_DRIVEN
                })
            else:
                stmt = ibm_db.prepare(conn, query)
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while (data):
                print "%s : %s : %s : %s\n" % (data[0], data[1], data[2],
                                               data[3])
                data = ibm_db.fetch_both(stmt)
            try:
                stmt = ibm_db.prepare(conn, query, {
                    ibm_db.SQL_ATTR_CURSOR_TYPE:
                    ibm_db.SQL_CURSOR_KEYSET_DRIVEN
                })
                ibm_db.execute(stmt)
                rc = ibm_db.fetch_row(stmt, -1)
                print "Fetch Row -1:%s " % str(rc)
            except:
                print "Requested row number must be a positive value"
            ibm_db.close(conn)
        else:
            print "Connection failed."
    def run_test_createdbNX(self):
        database = 'test001'
        conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
            database, config.hostname, config.port, config.user,
            config.password)
        conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
            config.hostname, config.port, config.user, config.password
        )  #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
        conn_attach = ibm_db.connect(conn_str_attach, '', '')

        if conn_attach:
            conn = False
            try:
                conn = ibm_db.connect(conn_str, '', '')
            except:
                pass

            if conn:
                ibm_db.close(conn)
                conn = False
                try:
                    ibm_db.dropdb(conn_attach, database)
                except:
                    print('Errors occurred during drop database')
            try:
                # call createdbNX without  codeset argument when specified database not exeist
                rc = ibm_db.createdbNX(conn_attach, database)
                if rc:
                    conn = ibm_db.connect(conn_str, '', '')
                    if conn:
                        print('database created sucessfully')
                        ibm_db.close(conn)
                        conn = False
                    else:
                        print('database is not created')
                else:
                    print('Error occurred during create db if not exist')

                conn = ibm_db.connect(conn_str, '', '')
                if conn:
                    ibm_db.close(conn)
                    conn = False
                    # call recreate db with codeset argument when specified database  exist
                    rc = ibm_db.createdbNX(conn_attach, database, 'iso88591')
                    if rc:
                        conn = ibm_db.connect(conn_str, '', '')
                        server_info = ibm_db.server_info(conn)
                        if conn and (server_info.DB_CODEPAGE != 819):
                            print('database with codeset created sucessfully')
                            ibm_db.close(conn)
                            conn = False
                        else:
                            print('Database not created')
                    else:
                        print(
                            'Error occurred during create db if not exist with codeset'
                        )

                #drop database
                rc = ibm_db.dropdb(conn_attach, database)
                if rc:
                    try:
                        conn = ibm_db.connect(conn_str, '', '')
                    except:
                        print('datbase droped sucessfully')
                    if conn:
                        print('Errors occurred during drop database')
                        ibm_db.close(conn)
                        conn = False
                else:
                    print('Errors occurred during drop database')
            except:
                print(ibm_db.conn_errormsg())
                pass
            ibm_db.close(conn_attach)
        else:
            print(ibm_db.conn_errormsg())
Esempio n. 45
0
    def get(self):
        self.set_header('Content-Type', 'application/json')

        # fetch data from db and return a json result = {}
        # hotel: id, name, latitude, longitude
        # attraction: id, name, latitude, longitude
        # hawker center: id, latitude, longitude
        bnb_dict = {}
        attr_dict = {}
        hawker_center_dict = {}
        conn = ibm_db.connect(
            "DATABASE=BLUDB;HOSTNAME=dashdb-entry-yp-dal09-09.services.dal.bluemix.net;\
        					PORT=50000;PROTOCOL=TCPIP;UID=dash9787;\
                  				PWD=X_c03EeYTe#u;", "", "")

        sql_airbnb = "SELECT ROOMID,NAME,LATITUDE,LONGITUDE,PRICE,RATING,IMGURL,ROOMURL FROM AIRBNB"
        stmt = ibm_db.exec_immediate(conn, sql_airbnb)
        while True:
            dict_airbnb = ibm_db.fetch_assoc(stmt)
            if dict_airbnb is False:
                break
            bnb_dict[int(dict_airbnb['ROOMID'].strip())] = {
                'id': int(dict_airbnb['ROOMID'].strip()),
                'name': dict_airbnb['NAME'].strip(),
                'price': float(dict_airbnb['PRICE'].strip()),
                'rating': float(dict_airbnb['RATING'].strip()),
                'lat': float(dict_airbnb['LATITUDE'].strip()),
                'lng': float(dict_airbnb['LONGITUDE'].strip()),
                'img': dict_airbnb['IMGURL'],
                'roomURL': dict_airbnb['ROOMURL'],
            }

        sql_attr = "SELECT ATTRACTIONID,NAME,LATITUDE,LONGITUDE,POPULARITY, RATING, CATEGORY,TICKET_PRICE FROM TOURISM_ATTRACTIONS"
        stmt = ibm_db.exec_immediate(conn, sql_attr)
        while True:
            dict_attr = ibm_db.fetch_assoc(stmt)
            if dict_attr is False:
                break
            attr_dict[int(dict_attr['ATTRACTIONID'].strip())] = {
                'ATTRACTIONID': int(dict_attr['ATTRACTIONID'].strip()),
                'NAME': dict_attr['NAME'].strip(),
                'TICKET_PRICE': float(dict_attr['TICKET_PRICE'].strip()),
                'LATITUDE': float(dict_attr['LATITUDE'].strip()),
                'LONGITUDE': float(dict_attr['LONGITUDE'].strip()),
                'CATEGORY': dict_attr['CATEGORY'],
                'POPULARITY': dict_attr['POPULARITY'],
                'RATING': dict_attr['RATING'],
            }

        sql_food = "SELECT FOODID,NAME,LATITUDE,LONGITUDE FROM FOOD"
        stmt = ibm_db.exec_immediate(conn, sql_food)
        while True:
            dict_food = ibm_db.fetch_assoc(stmt)
            if dict_food is False:
                break
            hawker_center_dict[dict_food['FOODID']] = {
                'id': dict_food['FOODID'],
                'name': dict_food['NAME'],
                'lat': float(dict_food['LATITUDE'].strip()),
                'lng': float(dict_food['LONGITUDE'].strip()),
            }
        dict_all = {
            'hotels': bnb_dict,
            'attractions': attr_dict,
            'hawker_centers': hawker_center_dict
        }
        self.write(json_encode(dict_all))
Esempio n. 46
0
    def run_test_024(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        if conn != 0:
            drop = 'DROP TABLE test_primary_keys'
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass
            drop = 'DROP TABLE test_keys'
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass
            drop = 'DROP TABLE test_foreign_keys'
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass

            statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
            result = ibm_db.exec_immediate(conn, statement)
            statement = "INSERT INTO test_primary_keys VALUES (1)"
            result = ibm_db.exec_immediate(conn, statement)
            statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
                   PRIMARY KEY(name))'

            result = ibm_db.exec_immediate(conn, statement)
            statement = "INSERT INTO test_keys VALUES ('vince', 1)"
            result = ibm_db.exec_immediate(conn, statement)
            statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
            result = ibm_db.exec_immediate(conn, statement)
            statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
            result = ibm_db.exec_immediate(conn, statement)

            if (server.DBMS_NAME[0:3] == 'IDS'):
                stmt = ibm_db.foreign_keys(conn, None, config.user,
                                           'test_primary_keys')
            else:
                stmt = ibm_db.foreign_keys(conn, None, None,
                                           'TEST_PRIMARY_KEYS')
            row = ibm_db.fetch_tuple(stmt)
            print(row[2])
            print(row[3])
            print(row[6])
            print(row[7])

            if (server.DBMS_NAME[0:3] == 'IDS'):
                stmt = ibm_db.foreign_keys(conn, None, None, None, None,
                                           config.user, 'test_keys')
            else:
                stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
                                           'TEST_KEYS')
            row = ibm_db.fetch_tuple(stmt)
            print(row[2])
            print(row[3])
            print(row[6])
            print(row[7])

            if (server.DBMS_NAME[0:3] == 'IDS'):
                stmt = ibm_db.foreign_keys(conn, None, config.user,
                                           'test_keys', None, None, None)
            else:
                stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
                                           None, None)
            row = ibm_db.fetch_tuple(stmt)
            print(row[2])
            print(row[3])
            print(row[6])
            print(row[7])

            if (server.DBMS_NAME[0:3] == 'IDS'):
                stmt = ibm_db.foreign_keys(conn, None, config.user,
                                           'test_keys', None, config.user,
                                           'test_foreign_keys')
            else:
                stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
                                           None, 'TEST_FOREIGN_KEYS')
            row = ibm_db.fetch_tuple(stmt)
            print(row[2])
            print(row[3])
            print(row[6])
            print(row[7])

            try:
                stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
                                           None)
                row = ibm_db.fetch_tuple(stmt)
            except:
                if (not stmt):
                    print(ibm_db.stmt_errormsg())

            if (server.DBMS_NAME[0:3] == 'IDS'):
                stmt = ibm_db.foreign_keys(conn, None, config.user,
                                           'test_keys', None, 'dummy_schema')
            else:
                stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
                                           'dummy_schema')
            row = ibm_db.fetch_tuple(stmt)
            if (not row):
                print("No Data Found")
            else:
                print(row)
            ibm_db.close(conn)
        else:
            print(ibm_db.conn_errormsg())
            print("Connection failed\n")
  def run_test_148(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      ##### Set up #####
      serverinfo = ibm_db.server_info( conn )
      server = serverinfo.DBMS_NAME[0:3]
      try:
          sql = "DROP TABLE sptb"
          ibm_db.exec_immediate(conn, sql)
      except:
          pass
      
      try:
          sql = "DROP PROCEDURE sp"
          ibm_db.exec_immediate(conn, sql)
      except:
          pass
      
      if (server == 'IDS'):
        sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 CLOB)"
      else:
        sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 BIGINT, c5 CLOB)"
      
      ibm_db.exec_immediate(conn, sql)
      
      sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'clob data clob data')"
      ibm_db.exec_immediate(conn, sql)
      
      if (server == 'IDS'):
        sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 CLOB);
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
      else:
        sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 BIGINT, OUT out5 CLOB)
                 DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END"""
      ibm_db.exec_immediate(conn, sql)
      #############################

      ##### Run the test #####

      out1 = 0
      out2 = 0.00
      out3 = ""
      out4 = 0
      out5 = ""

      stmt, out1, out2, out3, out4, out5 = ibm_db.callproc(conn, 'sp', (out1, out2, out3, out4, out5))

      print("out 1:")
      print(out1)
      print("out 2:")
      print(out2)
      print("out 3:")
      print(out3)
      print("out 4:")
      print(out4)
      print("out 5:")
      print(out5)
      #############################
    else:
      print("Connection failed.")
Esempio n. 48
0
    def run_test_008(self):
        op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
        conn = ibm_db.connect(config.database, config.user, config.password,
                              op)
        server = ibm_db.server_info(conn)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.columns(conn, None, None, "employee")
        else:
            result = ibm_db.columns(conn, None, None, "EMPLOYEE")
        row = ibm_db.fetch_both(result)
        value1 = None
        value2 = None
        value3 = None
        value4 = None
        if (row.has_key('TABLE_NAME')):
            value1 = row['TABLE_NAME']
        if (row.has_key('COLUMN_NAME')):
            value2 = row['COLUMN_NAME']
        if (row.has_key('table_name')):
            value3 = row['table_name']
        if (row.has_key('column_name')):
            value4 = row['column_name']
        print value1
        print value2
        print value3
        print value4

        op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
        ibm_db.set_option(conn, op, 1)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.columns(conn, None, None, "employee")
        else:
            result = ibm_db.columns(conn, None, None, "EMPLOYEE")
        row = ibm_db.fetch_both(result)
        value1 = None
        value2 = None
        value3 = None
        value4 = None
        if (row.has_key('TABLE_NAME')):
            value1 = row['TABLE_NAME']
        if (row.has_key('COLUMN_NAME')):
            value2 = row['COLUMN_NAME']
        if (row.has_key('table_name')):
            value3 = row['table_name']
        if (row.has_key('column_name')):
            value4 = row['column_name']
        print value1
        print value2
        print value3
        print value4

        op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
        ibm_db.set_option(conn, op, 1)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.columns(conn, None, None, "employee")
        else:
            result = ibm_db.columns(conn, None, None, "EMPLOYEE")
        row = ibm_db.fetch_both(result)
        value1 = None
        value2 = None
        value3 = None
        value4 = None
        if (row.has_key('TABLE_NAME')):
            value1 = row['TABLE_NAME']
        if (row.has_key('COLUMN_NAME')):
            value2 = row['COLUMN_NAME']
        if (row.has_key('table_name')):
            value3 = row['table_name']
        if (row.has_key('column_name')):
            value4 = row['column_name']
        print value1
        print value2
        print value3
        print value4
Esempio n. 49
0
  def run_test_066(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.tables(conn, None, config.user.lower(), 'animals')
    else:
      result = ibm_db.tables(conn, None, config.user.upper(), 'ANIMALS')
      
#    NOTE: This is a workaround
#    function fetch_object() to be implemented...
#    row = ibm_db.fetch_object(result)
    
    class Row:
        pass

    data = ibm_db.fetch_assoc(result)
    while ( data ):
      row = Row()
      if (server.DBMS_NAME[0:3] == 'IDS'):
        row.table_schem = data['table_schem']
        row.table_name = data['table_name']
        row.table_type = data['table_type']
        row.remarks = data['remarks']
          
        print "Schema:  %s" % row.table_schem
        print "Name:    %s" % row.table_name
        print "Type:    %s" % row.table_type
        print "Remarks: %s\n" % row.remarks
      else:
        row.TABLE_SCHEM = data['TABLE_SCHEM']
        row.TABLE_NAME = data['TABLE_NAME']
        row.TABLE_TYPE = data['TABLE_TYPE']
        row.REMARKS = data['REMARKS']
          
        print "Schema:  %s" % row.TABLE_SCHEM
        print "Name:    %s" % row.TABLE_NAME
        print "Type:    %s" % row.TABLE_TYPE
        print "Remarks: %s\n" % row.REMARKS
#      row = ibm_db.fetch_object(result)
      data = ibm_db.fetch_assoc(result)

    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.tables(conn, None, config.user.lower(), 'animal_pics')
    else:
      result = ibm_db.tables(conn, None, config.user.upper(), 'ANIMAL_PICS')
    
#    row = ibm_db.fetch_object(result)
    data = ibm_db.fetch_assoc(result)
    while (data ):
      row = Row()
      if (server.DBMS_NAME[0:3] == 'IDS'):
        row.table_schem = data['table_schem']
        row.table_name = data['table_name']
        row.table_type = data['table_type']
        row.remarks = data['remarks']
        
        print "Schema:  %s" % row.table_schem
        print "Name:    %s" % row.table_name
        print "Type:    %s" % row.table_type
        print "Remarks: %s\n" % row.remarks
      else:
        row.TABLE_SCHEM = data['TABLE_SCHEM']
        row.TABLE_NAME = data['TABLE_NAME']
        row.TABLE_TYPE = data['TABLE_TYPE']
        row.REMARKS = data['REMARKS']
        
        print "Schema:  %s" % row.TABLE_SCHEM
        print "Name:    %s" % row.TABLE_NAME
        print "Type:    %s" % row.TABLE_TYPE
        print "Remarks: %s\n" % row.REMARKS
#      row = ibm_db.fetch_object(result)
      data = ibm_db.fetch_assoc(result)
      
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.tables(conn, None, config.user.lower(), 'anime_cat')
    else:
      result = ibm_db.tables(conn, None, config.user.upper(), 'ANIME_CAT')
    
#    row = ibm_db.fetch_object(result)
    data = ibm_db.fetch_assoc(result)
    while ( data ): 
      row = Row()
      if (server.DBMS_NAME[0:3] == 'IDS'):
        row.table_schem = data['table_schem']
        row.table_name = data['table_name']
        row.table_type = data['table_type']
        row.remarks = data['remarks']
        
        print "Schema:  %s" % row.table_schem
        print "Name:    %s" % row.table_name
        print "Type:    %s" % row.table_type
        print "Remarks: %s\n" % row.remarks
      else:
        row.TABLE_SCHEM = data['TABLE_SCHEM']
        row.TABLE_NAME = data['TABLE_NAME']
        row.TABLE_TYPE = data['TABLE_TYPE']
        row.REMARKS = data['REMARKS']
        
        print "Schema:  %s" % row.TABLE_SCHEM
        print "Name:    %s" % row.TABLE_NAME
        print "Type:    %s" % row.TABLE_TYPE
        print "Remarks: %s\n" % row.REMARKS
#      row = ibm_db.fetch_object(result)
      data = ibm_db.fetch_assoc(result)
    
    ibm_db.free_result(result)
    ibm_db.close(conn)
Esempio n. 50
0
 def getDbConnection(self):
     try:
     	url="DATABASE="+DB2_SAMPLE_DB+";HOSTNAME="+DB2_HOST+";PORT="+DB2_PORT+";PROTOCOL=TCPIP;UID="+DB2_USERNAME+";PWD="+DB2_PASSWORD+";"
         db = ibm_db.connect(url, "", "")  #Connect to an uncataloged database
         self.connection = db
Esempio n. 51
0
        sql_modify_Station = "UPDATE Station " \
                             "SET food=%s, water=%s, clothes=%s, medicine=%s" \
                             " WHERE Driver=%s" %(food, water, clothes, medicine, stationId)
        ibm_db.exec_immediate(conn, sql_modify_Station)
        dict = {}
        if food == 0:
            dict['food'] = 0
        if water == 0:
            dict['water'] = 0
        if clothes == 0:
            dict['clothes'] = 0
        if medicine == 0:
            dict['medicine'] = 0
        ibm_db.close(conn)
        return dict
    except:
        ibm_db.close(conn)
        return False

if __name__ == "__main__":
    conn = ibm_db.connect(
        "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=tpj29337;PWD=8kzn@v6p7wlb4r75;Security=SSL;",
        "", "")

# s = "SELECT * FROM Vehicle"
# print(ibm_db.fetch_both(ibm_db.exec_immediate(conn, s)))




Esempio n. 52
0
    def run_test_bool_callproc(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        if (not conn):
            print("Could not make a connection.")
            return 0
        server = ibm_db.server_info(conn)

        if (not server.DBMS_NAME.startswith('DB2/')):
            print("Boolean is not supported")
            return 0

        try:
            ibm_db.exec_immediate(conn, "DROP PROCEDURE bool_procparams")
            ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
        except:
            pass

        try:
            ibm_db.exec_immediate(
                conn,
                "CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
            )
        except:
            pass

        try:
            procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
                           RESULT SETS  1
                           LANGUAGE SQL
                           BEGIN
                             DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
                             OPEN c1;
                             set param2 = parm1;
                           END"""

            ibm_db.exec_immediate(conn, procedure)
        except Exception as e:
            print(str(e))
            exit(-1)

        try:
            insert_sql = "INSERT INTO bool_test values(?, ?)"
            stmt = ibm_db.prepare(conn, insert_sql)

            rows = ((True, 'bindparam true'), (False, 'bindparam false'),
                    (None, 'bindparam None'))

            for row in rows:
                ibm_db.bind_param(stmt, 1, row[0])
                ibm_db.bind_param(stmt, 2, row[1])
                ibm_db.execute(stmt)
            stmt = None

            inparam = 11
            outparam = -1
            stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
                                                      (inparam, outparam))
            print("Fetching first result set")
            row = ibm_db.fetch_row(stmt)
            while row:
                row0 = ibm_db.result(stmt, 0)
                row1 = ibm_db.result(stmt, 1)
                print(row0)
                print(row1)
                row = ibm_db.fetch_row(stmt)

            ibm_db.close(conn)
        except Exception as e:
            print("Error:{}".format(str(e)))
Esempio n. 53
0
def home(request):
    if request.method == 'POST':
        form = Register1(request.POST)
        if form.is_valid():
            forename = form.cleaned_data.get('forename')
            surname = form.cleaned_data.get('surname')
            postcode = form.cleaned_data.get('postcode')
            email = form.cleaned_data.get('email')

            #request.session['_cust'] = form

            # DB2 Connection
            ibm_db_conn = ibm_db.connect(
                "DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
                "", "")
            conn = ibm_db_dbi.Connection(ibm_db_conn)

            cur = conn.cursor()
            # count the number of customers that match surname and postcode
            cur.execute(
                """
            select distinct t1.PER_GID
            from OPGCUK.PERSON_OPGC t1
            join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
            where t2.PER_POSTAL_ADDR_POSTCODE = ?
            and t1.PER_SURNAME = ?
            and t1.PER_FIRST_INITIAL = ?""",
                [upper(postcode),
                 upper(surname),
                 upper(forename[0])])

            gid_all = []
            for obj in cur.fetchall():
                gid_all.append({"PER_GID": obj[0]})
            gid_num = len(gid_all)

            gid = ''
            for row in gid_all:
                gid = row['PER_GID']

            # DB2 Connection CLOSE
            cur.close()
            conn.close()

            if gid_num == 0:
                messages.warning(
                    request,
                    f'No customers in the database with the information provided.'
                )
            elif gid_num == 1:
                messages.success(
                    request,
                    f'Customer {forename} {surname} @ {postcode} was found.')
                # phone = ''
                return redirect('find-dashboard', gid, forename, surname,
                                postcode, email)
            else:
                # messages.warning(request, f'Please enter additional details required for your identification.')
                return redirect('find-register2', forename, surname, postcode,
                                email)
    else:
        form = Register1()
    return render(request, 'find/home.html', {'form': form})
Esempio n. 54
0
    def run_test_decimal(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            serverinfo = ibm_db.server_info(conn)

            drop = "DROP TABLE STOCKSHARE"
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass

            # Create the table stockprice
            create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
            result = ibm_db.exec_immediate(conn, create)

            # Insert Directly
            insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
            result = ibm_db.exec_immediate(conn, insert)

            # Prepare and Insert in the stockprice table
            stockprice = (\
              (20, "Zaral", 102.205),\
              (30, "Megabyte", "98.65"),\
              (40, "Visarsoft", Decimal("123.34")),\
              (50, "Mailersoft", Decimal("134.222")),\
              (60, "Kaerci", Decimal("100.976"))\
              )
            insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
            stmt = ibm_db.prepare(conn, insert)
            if stmt:
                for company in stockprice:
                    result = ibm_db.execute(stmt, company)

            id = 70
            company = 'Nirvana'
            stockshare = Decimal("100.1234")
            try:
                ibm_db.bind_param(stmt, 1, id)
                ibm_db.bind_param(stmt, 2, company)
                ibm_db.bind_param(stmt, 3, stockshare)
                error = ibm_db.execute(stmt)
            except:
                excp = sys.exc_info()
                # slot 1 contains error message
                print excp[1]

            # Select the result from the table and
            query = 'SELECT * FROM STOCKSHARE ORDER BY id'
            if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
                stmt = ibm_db.prepare(conn, query, {
                    ibm_db.SQL_ATTR_CURSOR_TYPE:
                    ibm_db.SQL_CURSOR_KEYSET_DRIVEN
                })
            else:
                stmt = ibm_db.prepare(conn, query)
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while (data):
                print "%s : %s : %s\n" % (data[0], data[1], data[2])
                data = ibm_db.fetch_both(stmt)
            try:
                stmt = ibm_db.prepare(conn, query, {
                    ibm_db.SQL_ATTR_CURSOR_TYPE:
                    ibm_db.SQL_CURSOR_KEYSET_DRIVEN
                })
                ibm_db.execute(stmt)
                rc = ibm_db.fetch_row(stmt, -1)
                print "Fetch Row -1:%s " % str(rc)
            except:
                print "Requested row number must be a positive value"
            ibm_db.close(conn)
        else:
            print "Connection failed."


#__END__
#__LUW_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__ZOS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__IDS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
Esempio n. 55
0
import ibm_db
import os
from flask import Flask, render_template, url_for, request, redirect
from werkzeug.utils import secure_filename

conn = ibm_db.connect(
    "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-10.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=zgx10325;PWD=nk^ttsk5lffq6v0h;",
    "", "")

app = Flask(__name__)
img_path = 'static/images/'
app.config['UPLOAD_FOLDER'] = img_path
app.config['SECRET_KEY'] = 'blah blah blah blah'


@app.route('/')
def index():
    arr = []
    query = "SELECT * FROM PEOPLE"
    stmt = ibm_db.exec_immediate(conn, query)
    data = ibm_db.fetch_tuple(stmt)

    while data:
        arr.append(data)
        data = ibm_db.fetch_tuple(stmt)
    return render_template('main.html', table=arr)


@app.route('/search', methods=['POST', 'GET'])
def search_user():
    if request.method == 'POST':
Esempio n. 56
0
        user = a
    if o == "-p":
        pwd = a
    if o == "-t":
        targetdb = a


if db is None or user is None or pwd is None or targetdb is None:
    print("Usage: DBMove.py [-h <host> -P <port>] -d <db> -u <user> -p <pwd> -t <target>")
    sys.exit(1)

db = db.upper()
targetdb = targetdb.upper()

cfg = (db, host, port, user, pwd)
conn = ibm_db.connect("DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s" % cfg, "", "")

get_db_type = "values nya.get_db_type()"

find_edges = """
SELECT rtrim(t.tabschema) || '.' || rtrim(t.tabname)
     , coalesce(rtrim(r.reftabschema) || '.' || rtrim(r.reftabname), 'dummy')
FROM syscat.tables t
LEFT JOIN syscat.references r
    ON (t.tabschema, t.tabname) = (r.tabschema, r.tabname)
WHERE t.tabschema not like 'SYS%' 
  AND t.type = 'T'
  AND rtrim(t.tabschema) not like 'NYA_%' 
  AND t.tabschema <> 'TMP'
ORDER BY 1
"""
Esempio n. 57
0
import ibm_db

conn_string = "DATABASE=Ifr;HOSTNAME=172.18.8.117;PORT=50000;UID=AMASISFULL;PWD=AMASISFULL;"

conn = None

try:
    ibm_db.connect(conn_string, "", "")
except Exception as ex:
    print(ex)

if conn:
    print('success')
else:
    print('failure')
Esempio n. 58
0
    def run_test_cursortype(self):
        if sys.platform == 'zos':
            options = {}
        else:
            options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'}
        conn = ibm_db.connect(config.database, config.user, config.password,
                              options)

        if conn:
            drop = "DROP TABLE TEMP_TEST"
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass

            # Create the table temp_test

            create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))"
            result = ibm_db.exec_immediate(conn, create)

            insert = "INSERT INTO temp_test values (1, 'cat')"
            ibm_db.exec_immediate(conn, insert)

            print("Setting cursor type to SQL_CURSOR_FORWARD_ONLY")
            op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
            stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1",
                                  op)
            val = ibm_db.cursor_type(stmt)
            print("statement cursor type = ", end="")
            print(val, end="\n")
            value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
            print(value)
            print()

            print("Setting cursor type to SQL_CURSOR_KEYSET_DRIVEN")
            op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
            stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
            val = ibm_db.cursor_type(stmt)
            print("statement cursor type = ", end="")
            print(val, end="\n")
            value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
            print(value)
            print()

            print("Setting cursor type to SQL_CURSOR_STATIC")
            op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
            stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
            val = ibm_db.cursor_type(stmt)
            print("statement cursor type = ", end="")
            print(val)
            value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
            print(value)
            print()

            print("Setting cursor type to SQL_CURSOR_DYNAMIC - zOS only")
            op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_DYNAMIC}
            stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
            val = ibm_db.cursor_type(stmt)
            print("statement cursor type = ", end="")
            print(val)
            value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
            print(value)
            print()

            ibm_db.close(conn)
        else:
            print("Connection failed.")
Esempio n. 59
0
def dashboard(request, gid, forename, surname, postcode, email):

    # DB2 Connection
    ibm_db_conn = ibm_db.connect(
        "DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
        "", "")
    conn = ibm_db_dbi.Connection(ibm_db_conn)
    cur = conn.cursor()

    # retrieve all plans from customer
    cur.execute(
        """
    select distinct t2.PER_GID, t3.ITEM_GID, t4.ITEM_ID as PLAN, t3.ITEM_LOC_ADDR_LINE_1 as ITEM_LOCATION, t3.ITEM_LOC_POSTCODE AS POSTCODE, t3.ITEM_MANUF_BRAND_CODE AS BRAND, t3.ITEM_MODEL_NUM AS MODEL, t3.ITEM_SERIAL_NUM AS SERIAL, 
       date(t3.ITEM_PURCHASE_DTS) AS PURCHASE_DATE, date(t3.ITEM_COVER_DG_CONTR_RENEWAL_DTS) AS RENEWAL_DATE, 
       CASE t3.ITEM_COVER_DG_CONTR_STATUS_CODE
       WHEN 'N' THEN 'NEW'
       WHEN 'R' THEN 'RENEWED'
       WHEN 'L' THEN 'LAPSED'
       WHEN 'C' THEN 'CANCELLED'
       ELSE 'ERROR'
       END as PLAN_STATUS,
       t3.ITEM_CODE AS ITEM_CODE, t5.ITEM_DESCRIPTION AS DESCRIPTION, t5.ITEM_FAMILY_CODE AS FAMILY
    from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
    join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
    join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
    join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
    where t2.PER_GID = ?
    and left(t4.ITEM_ID,4) = 'PLN:'
    and t3.ITEM_COVER_DG_CONTR_STATUS_CODE in ('N','R','L')""", [gid])

    plans = []
    for obj in cur.fetchall():
        plans.append({
            "PER_GID": obj[0],
            "ITEM_GID": obj[1],
            "PLAN": obj[2],
            "ITEM_LOCATION": obj[3],
            "POSTCODE": obj[4],
            "BRAND": obj[5],
            "MODEL": obj[6],
            "SERIAL": obj[7],
            "PURCHASE_DATE": obj[8],
            "RENEWAL_DATE": obj[9],
            "PLAN_STATUS": obj[10],
            "ITEM_CODE": obj[11],
            "DESCRIPTION": obj[12],
            "FAMILY": obj[13]
        })
    tab_len = len(plans)

    # retrieve all mailers from customer
    cur.execute(
        """
    select distinct t2.PER_GID, t3.ITEM_GID, t3.ITEM_LOC_ADDR_LINE_1 as ITEM_LOCATION, t3.ITEM_LOC_POSTCODE AS POSTCODE, 
        t3.ITEM_MANUF_BRAND_CODE AS BRAND, t3.ITEM_MODEL_NUM AS MODEL, t3.ITEM_SERIAL_NUM AS SERIAL, 
        date(t3.ITEM_PURCHASE_DTS) AS PURCHASE_DATE,
        t3.ITEM_CODE AS ITEM_CODE, t5.ITEM_DESCRIPTION AS DESCRIPTION, t5.ITEM_FAMILY_CODE AS FAMILY
    from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
    join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
    join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
    join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
    where t2.PER_GID = ?
    and left(t4.ITEM_ID,4) <> 'PLN:'
    and t3.ITEM_COVER_DG_CONTR_STATUS_CODE is NULL""", [gid])

    mailers = []
    for obj in cur.fetchall():
        mailers.append({
            "PER_GID": obj[0],
            "ITEM_GID": obj[1],
            "ITEM_LOCATION": obj[2],
            "POSTCODE": obj[3],
            "BRAND": obj[4],
            "MODEL": obj[5],
            "SERIAL": obj[6],
            "PURCHASE_DATE": obj[7],
            "ITEM_CODE": obj[8],
            "DESCRIPTION": obj[9],
            "FAMILY": obj[10]
        })
    tab_len = tab_len + len(mailers)

    # DB2 Connection CLOSE
    cur.close()
    conn.close()

    # send data to form
    form = Dashboard(
        initial={
            'gid': gid,
            'forename': forename,
            'surname': surname,
            'postcode': postcode,
            'email': email
        })
    args = {
        'form': form,
        'plans': plans,
        'mailers': mailers,
        'item_len': tab_len
    }
    return render(request, 'find/dashboard.html', args)
Esempio n. 60
0
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
from flask import Flask, render_template, url_for, flash, redirect, request
from flask_wtf import FlaskForm
from flask_wtf.file import FileField, FileRequired, FileAllowed
from flask_uploads import UploadSet, configure_uploads, IMAGES, patch_request_class
from flask_bootstrap import Bootstrap
from wtforms import StringField, IntegerField, SubmitField, SelectField
from wtforms.validators import DataRequired
import sys
import os

import ibm_db

conn = ibm_db.connect(
    "DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-10.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=pqc12497;PWD=n2l8-gc4t4khhkw1;",
    "", "")

app = Flask(__name__)

bootstrap = Bootstrap(app)

# configurations
app.config['SECRET_KEY'] = 'blah blah blah blah'
cf_port = os.getenv("PORT")

# Routes
# Index Page


@app.route('/', methods=['POST', 'GET'])