def run_test_018(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
    if conn:
      stmt = ibm_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
      ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
      result = ibm_db.execute(stmt)
      if result:
        rows = ibm_db.num_rows(stmt)
        print("affected row:", rows)
        ibm_db.free_result(stmt)
      else:
        print(ibm_db.stmt_errormsg())

      ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
      result = ibm_db.execute(stmt)
      if result:
        rows = ibm_db.num_rows(stmt)
        print("affected row:", rows)
        ibm_db.free_result(stmt)
      else:
        print(ibm_db.stmt_errormsg())

      ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
      result = ibm_db.execute(stmt)
      if result:
        rows = ibm_db.num_rows(stmt)
        print("affected row:", rows)
      else:
        print(ibm_db.stmt_errormsg())

      ibm_db.close(conn)
    else:
      print("no connection:", ibm_db.conn_errormsg())
Пример #2
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")
Пример #3
0
def get_maxID(conn=None):

    if not conn: # Exit
        return {"data":{ 
            "error": "Not connected to the database",
            },
            "status": "Fail",
            "id": -1
        }

    cont = dict()
    try:
        # "SELECT USER.ID, USER.NAME, USER.EMAIL, USER.PHONENUMBER, EMPLOYEETYPE.TYPE, SITELOCATION.SITE  FROM ((USER INNER JOIN EMPLOYEETYPE ON USER.EMPLOYEETYPE_ID = EMPLOYEETYPE.ID) INNER JOIN SITELOCATION ON USER.SITELOCATION_ID = SITELOCATION.ID)"
        # sql = "Select user.id, user.name, user.email, user.phonenumber, employeetype.type, sitelocation.site from  (( USER INNER JOIN EMPLOYEETYPE ON USER.EMPLOYEETYPE_ID = EMPLOYEETYPE.ID) INNER JOIN SITELOCATION ON USER.SITELOCATION_ID = SITELOCATION.ID) where USER.id = (Select Max(id) from user)"
        sql = "SELECT * FROM user WHERE  user.id = (SELECT MAX(id) FROM user)"
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_assoc(stmt)
        cont = {
            "user": result,
            "error": ibm_db.stmt_errormsg(),
            "status": "ok",
            "id": result["ID"]
        }
    except:
        cont = {"data": {
            "error": ibm_db.stmt_errormsg(),
            },
            "status": "bad",
            "id": -1
        }
    finally:
        return cont
    def run_test_048(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)

        fp = open("tests/spook_out.png", "wb")
        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.exec_immediate(
                conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'")
        else:
            result = ibm_db.exec_immediate(
                conn,
                "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'"
            )
        if (not result):
            print "Could not execute SELECT statement."
            return 0
        row = ibm_db.fetch_tuple(result)
        if row:
            fp.write(row[0])
        else:
            print ibm_db.stmt_errormsg()
        fp.close()
        cmp = (open('tests/spook_out.png',
                    "rb").read() == open('tests/spook.png', "rb").read())
        print "Are the files the same:", cmp
Пример #5
0
 def run_test_015(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     if conn:
         result = ibm_db.exec_immediate(
             conn, "insert into t_string values(123,1.222333,'one to one')")
         if result:
             cols = ibm_db.num_fields(result)
             # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
             print("col:", cols)
             rows = ibm_db.num_rows(result)
             print("affected row:", rows)
         else:
             print(ibm_db.stmt_errormsg())
         result = ibm_db.exec_immediate(conn,
                                        "delete from t_string where a=123")
         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())
         ibm_db.close(conn)
     else:
         print("no connection:", ibm_db.conn_errormsg())
Пример #6
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")
Пример #7
0
  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_045(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   fp = open("tests/pic1_out.jpg", "wb")
   result = ibm_db.exec_immediate(conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'")
   row = ibm_db.fetch_tuple(result)
   if row:
     fp.write(row[0])
   else:
     print ibm_db.stmt_errormsg()
   fp.close()
   cmp = (open('tests/pic1_out.jpg', 'rb').read() == open('tests/pic1.jpg', 'rb').read())
   print 'Are the files the same:', cmp
    def run_test_133(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if (not conn):
            print("Connection failed.")
            return 0

        ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

        print("Starting test ...")
        res = ''
        sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
        try:
            stmt = ibm_db.prepare(conn, sql)
            res = ibm_db.execute(
                stmt, (128, 'hacker of human and technological nature',
                       'Wez the ruler of all things PECL', 88.3))

            stmt = ibm_db.prepare(
                conn, "SELECT breed, name FROM animals WHERE id = ?")
            res = ibm_db.execute(stmt, (128, ))
            row = ibm_db.fetch_assoc(stmt)

            for i in row:
                print(i)

            ibm_db.rollback(conn)
            print("Done")
        except:
            print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
            print("Message: %s" % ibm_db.stmt_errormsg(stmt))

        try:
            stmt = ibm_db.prepare(
                conn, "SELECT breed, name FROM animals WHERE id = ?")
            res = ibm_db.execute(stmt, (128, ))
            row = ibm_db.fetch_assoc(stmt)
            if (row):
                for i in row:
                    print(i)
            print(res)
            print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
            print("Message: %s" % ibm_db.stmt_errormsg(stmt))
        except:
            print("An Exception is not expected")
            print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
            print("Message: %s" % ibm_db.stmt_errormsg(stmt))

        ibm_db.rollback(conn)
        print("Done")
Пример #10
0
 def run_test_045(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     fp = open("tests/pic1_out.jpg", "wb")
     result = ibm_db.exec_immediate(
         conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'")
     row = ibm_db.fetch_tuple(result)
     if row:
         fp.write(row[0])
     else:
         print ibm_db.stmt_errormsg()
     fp.close()
     cmp = (open('tests/pic1_out.jpg',
                 'rb').read() == open('tests/pic1.jpg', 'rb').read())
     print 'Are the files the same:', cmp
Пример #11
0
def user(user_id):
    #Connect To the database
    conn = passw.connect()

    if not conn:
        return jsonify(passw.reg_content(status="bad", error="Not connected to database", code=100))

    cont = dict()

    #Make SQL queries
    sql_delete_user = "******".format(user_id)
    sql_delete_user_bio = "DELETE FROM internbio WHERE user_id={}".format(
        user_id)
    sql_detele_password = "******".format(
        user_id)

    #Try to execute SQL 
    #Fails: Return status bad
    try:
        if request.method == 'DELETE':
            cont = passw.reg_content(status="ok", error="none", code=0)
            stmt_1 = ibm_db.exec_immediate(conn, sql_delete_user)
            stmt_2 = ibm_db.exec_immediate(conn, sql_delete_user_bio)
            stmt_3 = ibm_db.exec_immediate(conn, sql_detele_password)
    except:
        cont = passw.reg_content(status="bad", error=ibm_db.stmt_errormsg(), code= 400)
        ibm_db.rollback(conn)
    finally:
        ibm_db.close(conn)
        return jsonify(cont)
    def run_test_157a(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        print("Starting...")
        if conn:
            sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
            result = ibm_db.exec_immediate(conn, sql)

            try:
                i = 2
                row = ibm_db.fetch_assoc(result, i)
                while (row):
                    if (server.DBMS_NAME[0:3] == 'IDS'):
                        print("%-5d %-16s %-32s %10s" %
                              (row['id'], row['name'], row['breed'],
                               row['weight']))
                    else:
                        print("%-5d %-16s %-32s %10s" %
                              (row['ID'], row['NAME'], row['BREED'],
                               row['WEIGHT']))
                    i = i + 2
                row = ibm_db.fetch_assoc(result, i)
            except:
                print("SQLSTATE: %s" % ibm_db.stmt_error(result))
                print("Message: %s" % ibm_db.stmt_errormsg(result))

            print("DONE")
Пример #13
0
    def run_test_048(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)

        with open("ibm_db_tests/spook_out.png", "wb") as fp:
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.exec_immediate(
                    conn,
                    "SELECT picture FROM animal_pics WHERE name = 'Spook'")
            else:
                result = ibm_db.exec_immediate(
                    conn,
                    "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'"
                )
            if (not result):
                print("Could not execute SELECT statement.")
                return 0
            row = ibm_db.fetch_tuple(result)
            if row:
                fp.write(row[0])
            else:
                print(ibm_db.stmt_errormsg())
        with open('ibm_db_tests/spook_out.png', 'rb') as fp:
            pic_out = fp.read()
        with open('ibm_db_tests/spook.png', 'rb') as fp1:
            pic_in = fp1.read()
        cmp = pic_in == pic_out
        print("Are the files the same:", cmp)
    def run_test_6561(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,
                "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
            )
            statement = "SELECT count(id) FROM animals"
            result = ibm_db.exec_immediate(conn, statement)
            if ((not result) and ibm_db.stmt_error()):
                print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))

            row = ibm_db.fetch_tuple(result)
            while (row):
                for i in row:
                    print(i)
                row = ibm_db.fetch_tuple(result)

            ibm_db.rollback(conn)
            ibm_db.close(conn)

        else:
            print("Connection failed.")
def read_tablespaces(con, mess, dbTableSpaces):
    sqlQ = 'select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT from SYSIBMADM.DBA_TABLESPACES'

    stmt = ibm_db.exec_immediate(con, sqlQ)
    if not stmt:
        mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt)
        module.fail_json(msg=mess, changed=False)
        raise

    dictionary = ibm_db.fetch_assoc(stmt)
    while dictionary != False:
        tablespace_name = dictionary["TABLESPACE_NAME"]
        block_size = dictionary["BLOCK_SIZE"]
        status = dictionary["STATUS"]
        contents = dictionary["CONTENTS"]
        segment_management = dictionary["SEGMENT_SPACE_MANAGEMENT"]
        dbTableSpaces.append(
            dict(tablespace_name=tablespace_name,
                 block_size=block_size,
                 status=status,
                 contents=contents,
                 segment_management=segment_management))
        dictionary = ibm_db.fetch_assoc(stmt)

    return True
Пример #16
0
    def run_test_setgetOption(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)

        # Get the server type
        serverinfo = ibm_db.server_info(conn)

        if conn:
            if sys.platform != 'zos':
                value = ibm_db.get_option(conn,
                                          ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1)
                print("Connection options:\nSQL_ATTR_INFO_PROGRAMNAME = ",
                      end="")
                print(value)
            else:
                print("Connection options:\n", end="")

            returncode = ibm_db.set_option(conn,
                                           {ibm_db.SQL_ATTR_AUTOCOMMIT: 0}, 1)
            value = ibm_db.get_option(conn, ibm_db.SQL_ATTR_AUTOCOMMIT, 1)
            print("SQL_ATTR_AUTOCOMMIT = ", end="")
            print(str(value) + "\n")

            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)

            stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1")
            if sys.platform != 'zos':
                returnCode = ibm_db.set_option(
                    stmt, {ibm_db.SQL_ATTR_QUERY_TIMEOUT: 20}, 0)
                value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_QUERY_TIMEOUT,
                                          0)
                print("Statement options:\nSQL_ATTR_QUERY_TIMEOUT = ", end="")
                print(str(value) + "\n")

            ibm_db.execute(stmt)
            if result:
                ibm_db.free_result(stmt)
            else:
                print(ibm_db.stmt_errormsg())
            ibm_db.rollback(conn)
            ibm_db.close(conn)
        else:
            print("Connection failed.")
  def run_test_133(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    if (not conn):
      print "Connection failed."
      return 0

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

    print "Starting test ..."
    res = ''
    sql =  "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
    try:
      stmt = ibm_db.prepare(conn, sql)
      res = ibm_db.execute(stmt,(128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3))
      
      stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
      res = ibm_db.execute(stmt, (128,))
      row = ibm_db.fetch_assoc(stmt)
      
      for i in row:
	         print i

      ibm_db.rollback(conn)
      print "Done"
    except:
      print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
      print "Message: %s" % ibm_db.stmt_errormsg(stmt)

    try:
        stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
        res = ibm_db.execute(stmt, (128,))
        row = ibm_db.fetch_assoc(stmt)
        if (row):
            for i in row:
                print i
        print res
        print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
        print "Message: %s" % ibm_db.stmt_errormsg(stmt)
    except:
        print "An Exception is not expected"
        print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
        print "Message: %s" % ibm_db.stmt_errormsg(stmt)

    ibm_db.rollback(conn)
    print "Done"
Пример #18
0
    def run_test_err_executemany(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        serverinfo = ibm_db.server_info(conn)
        server = serverinfo.DBMS_NAME[0:3]

        if conn:
            try:
                ibm_db.exec_immediate(conn, 'DROP TABLE CLI0126E')
            except:
                pass
            create_ddl = "create table CLI0126E \
                (\
                    offer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\
                    position_id int NOT NULL,\
                    title VARCHAR(5000) NOT NULL,\
                    type VARCHAR(5000),\
                    quantity decimal(10, 3) NOT NULL,\
                    price_btc decimal(10, 8) NOT NULL,\
                    city VARCHAR(5000),\
                    country VARCHAR(500)\
                )"

            try:
                ibm_db.exec_immediate(conn, create_ddl)
            except:
                pass

            insert_statement = "INSERT INTO CLI0126E (position_id, title, type, quantity, price_btc, city, country)\
                                VALUES (?, ?, ?, ?, ?, ?, ?)"

            stmt = ibm_db.prepare(conn, insert_statement)

            # deliberately use wrong size decimal values to trigger CLI0111E (hidden by execute_many()).
            parms1 = (15628, 'correct value in row1 column4', '', 1999999.0,
                      0.0067762, 'Belarus', 'Belarus1')
            parms2 = (15629, 'incorrect value in row2 column4', '', 99999999.0,
                      0.0067762, 'Belarus', 'Belarus2')
            parms3 = (15630, 'correct value in row3 column4', '', 1999999.0,
                      0.0067762, 'Belarus', 'Belarus3')

            parms = (parms1, parms2, parms3)

            try:
                ibm_db.execute_many(stmt, parms)
                print(
                    str(ibm_db.num_rows(stmt)) +
                    " - Rows inserted successfully")
            except:
                print(
                    "Failed to insert multiple-rows with ibm_db.execute_many()"
                )
                print(ibm_db.stmt_errormsg())
                print("Number of rows inserted: " + str(ibm_db.num_rows(stmt)))

            ibm_db.close(conn)
        else:
            print("Connection failed.")
    def run_test_018(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
        if conn:
            stmt = ibm_db.prepare(conn,
                                  "SELECT * from animals WHERE weight < 10.0")
            ibm_db.set_option(stmt, {
                ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
                ibm_db.SQL_ROWCOUNT_PREFETCH_ON
            }, 2)
            result = ibm_db.execute(stmt)
            if result:
                rows = ibm_db.num_rows(stmt)
                print("affected row:", rows)
                ibm_db.free_result(stmt)
            else:
                print(ibm_db.stmt_errormsg())

            ibm_db.set_option(
                stmt, {
                    ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
                    ibm_db.SQL_ROWCOUNT_PREFETCH_OFF
                }, 2)
            result = ibm_db.execute(stmt)
            if result:
                rows = ibm_db.num_rows(stmt)
                print("affected row:", rows)
                ibm_db.free_result(stmt)
            else:
                print(ibm_db.stmt_errormsg())

            ibm_db.set_option(stmt, {
                ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
                ibm_db.SQL_ROWCOUNT_PREFETCH_ON
            }, 2)
            result = ibm_db.execute(stmt)
            if result:
                rows = ibm_db.num_rows(stmt)
                print("affected row:", rows)
            else:
                print(ibm_db.stmt_errormsg())

            ibm_db.close(conn)
        else:
            print("no connection:", ibm_db.conn_errormsg())
Пример #20
0
def db2_error(quiet):
    
    if quiet == True: return

    html = '<p style="border:2px; border-style:solid; border-color:#FF0000; background-color:#ffe6e6; padding: 1em;">'

    errmsg = ibm_db.stmt_errormsg().replace('\r',' ')
    errmsg = errmsg[errmsg.rfind("]")+1:].strip()
    pDisplay(pHTML(html+errmsg+"</p>"))
Пример #21
0
 def run_test_048(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     if not conn:
         print "Could not make a connection."
         return 0
     fp = open("tests/spook_out.png", "wb")
     result = ibm_db.exec_immediate(conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'")
     if not result:
         print "Could not execute SELECT statement."
         return 0
     row = ibm_db.fetch_tuple(result)
     if row:
         fp.write(row[0])
     else:
         print ibm_db.stmt_errormsg()
     fp.close()
     cmp = open("tests/spook_out.png").read() == open("tests/spook.png").read()
     print "Are the files the same:", cmp
Пример #22
0
    def fetch_statement_data(self, statement):
        self.connect()

        try:
            self.query_result = ibm_db.exec_immediate(self.connection,
                                                      statement)
        except Exception:
            raise DB2Exception(ibm_db.stmt_errormsg())

        return self._dump_query(self.query_result)
Пример #23
0
    def fetch_statement_data(self, statement):
        self.connect()

        try:
            self.query_result = ibm_db.exec_immediate(
                self.connection,
                statement
            )
        except Exception:
            logger.error(
                'Failed To Execute Query',
                extra={
                    'reason': ibm_db.stmt_errormsg(),
                    'time': datetime.now()
                }
            )
            raise DB2Exception(ibm_db.stmt_errormsg())

        return self._dump_query(self.query_result)
Пример #24
0
def insertDB(sql_stmt):

    connect()
    stmt = idb.prepare(con, sql_stmt)

    try:
        idb.execute(stmt)
    except:
        print(idb.stmt_errormsg())

    return
Пример #25
0
def insertRec(conn, sql):
    ''' Call num_rows '''
    try:
        stmt = db2.exec_immediate(conn, sql) 
        #print 'Number of affected rows:', db2.num_rows(stmt)
    except:
        print sql
        print "Transaction couldn't be completed:" , db2.stmt_errormsg()
        return False
    else:
        return True
Пример #26
0
def db_insert(db_conn_, insert_query):
    try:
        statement = ibm_db.exec_immediate(db_conn_, insert_query)
        ibm_db.free_stmt(statement)

    except Exception as e:
        logging.error("The dbInsert operation error is %s" % (e))
        return False
    except:
        logging.error("The dbInsert operation error is %s" % (ibm_db.stmt_errormsg()))
        return False
    return True
Пример #27
0
 def execute_command(self, command):
     mylog.debug("Executing command: %s" % command)
     try:
         execute_return = ibm_db.exec_immediate(self.connection, command)
     except:
         mylog.critical("Unable to execute command: %s" % command)
         result = ibm_db.stmt_errormsg()
         mylog.critical("Command return was: %s" % result)
         print "PROCESS ERROR: Failed to execute command %s; command return was %s" % (
             command, result)
         execute_return = result
     return execute_return
def check_if_user_active(prof_uid_lower):
    sql = "select PROF_MAIL from EMPINST.employee where prof_uid_lower = '" + prof_uid_lower + "' and prof_state = '0';"
    stmt = ibm_db.prepare(conn_people, sql)
    try:
        ibm_db.execute(stmt)
        if ibm_db.fetch_row(stmt):
            return True
        else:
            return False
    except:
        print "Transaction couldn't be completed, error getting profiles data: " , ibm_db.stmt_errormsg()
        sys.exit(3)
Пример #29
0
def immediateOper(connection, sql, outFile):
    outFile.write("Performing immediate operation: {} \n".format(sql))
    stmt = None
    try:
        stmt = ibm_db.exec_immediate(connection, sql)
    except:
        outFile.write(ibm_db.stmt_error())
        outFile.write(ibm_db.stmt_errormsg())
        raise
    if not stmt:
        outFile.write("Operation FAILED \n")
    return stmt
Пример #30
0
def add_user():
    #Connect To database
    conn = passw.connect()
    if not conn:
        return passw.reg_content(status="bad", error="Not connected to database", code=100)

    #Get JSON Data Parsed
    store_data = passw.getJSON()

    #Make SQL Queries
    sql_add_user = "******"
    sql_add_pass = "******"
    sql_add_bio = "INSERT INTO internbio (user_id, position) VALUES (?, ?)"
    
    #Prepare sql statement
    stmt_user = ibm_db.prepare(conn, sql_add_user)
    stmt_pass = ibm_db.prepare(conn, sql_add_pass)
    stmt_bio  = ibm_db.prepare(conn, sql_add_bio)

    cont = dict()
    #Try to Execute SQL queries, if so return json with user_id and ibm_db error
    #Else Return json with user_id,
    try:
        if request.method == 'POST':
            #ADDING TO USER TABLE
            params_user = store_data["name"], store_data["email"], store_data["phone_number"], store_data["employeeType_id"], store_data["siteLocation_id"]
            ibm_db.execute(stmt_user, params_user)

            #ADDING TO PASSWORD TABLE
            last_user = get_maxID(conn)
            params = last_user["id"], store_data["password"][1]
            ibm_db.execute(stmt_pass, params)

            #ADDING TO INTERNBIO TABLE
            params_1 = last_user["id"], "Software Engineer"
            ibm_db.execute(stmt_bio, params_1)

            print("Add User")
            print(store_data["phone_number"])
            #Send Message 
            try:
                user_s.send_sms(store_data["password"][0], store_data["phone_number"])
            except:
                pass

            cont = passw.content(user=last_user["id"], user_data=last_user, status="ok")
    except:
        cont = passw.content(user=None, err=ibm_db.stmt_errormsg(), status="bad")
        ibm_db.rollback(conn)
    finally:
        ibm_db.close(conn)
        return jsonify(cont)
 def run_test_016(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   if conn:
     result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
     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())
     result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
     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())
     ibm_db.close(conn)
   else:
     print ("no connection:", ibm_db.conn_errormsg())
Пример #32
0
def exec_sql_file(conn, path):

    print("        Executing %s ..." % path)

    # read file
    with open(path) as sql_file:
        commands = sql_file.read()
    # split file into seperate commands using @ as a delimiter
    commands = commands.split("@")
    # strip whitespace and remove empty commands
    commands = [command.strip() for command in commands if command.strip()]

    # execute each command
    for command in commands:
        try:
            ibm_db.exec_immediate(conn, command)
        except:
            if "statement is blank or empty" not in ibm_db.stmt_errormsg():
                print("        Failure executing statement: '%s' ..." %
                      command.replace('\n', ' ')[:100])
                print(ibm_db.stmt_errormsg())
                exit()
Пример #33
0
    def execute_query(self, query: str, bind_vars: Any) -> Tuple[List, List]:
        """
        Execute query at DB2 Database via connection

        Args:
            query(str): db2 query string
            bind_vars(Any): in case there are names and values - a bind_vars dict,
                            in case there are only values - list

        Returns:
            Tuple[results(List), headers(List)]
        """
        results = list()
        headers = list()
        status = False

        stmt = self._prepare_statement(query, bind_vars)

        try:
            demisto.info("Executing ...")
            status = ibm_db.execute(stmt)
            demisto.info("Done !!!")
        except Exception:
            demisto.error(clear(ibm_db.stmt_error()))
            demisto.error(clear(ibm_db.stmt_errormsg()))
            raise DemistoException(clear(ibm_db.stmt_errormsg()))

        demisto.info("Collecting results")
        if status:
            row = ibm_db.fetch_assoc(stmt)
            while row:
                results.append(row)
                row = ibm_db.fetch_assoc(stmt)

        if results:
            headers = [*results[0]]

        return results, headers
 def run_test_015(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   if conn:
     result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
     if result:
       cols = ibm_db.num_fields(result)
       # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
       print "col:", cols
       rows = ibm_db.num_rows(result)
       print "affected row:", rows
     else:
       print ibm_db.stmt_errormsg()
     result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
     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()
     ibm_db.close(conn)
   else:
     print "no connection:", ibm_db.conn_errormsg()
Пример #35
0
def getResByRow(conn, sql):
    ''' Call fetch_row '''
    n = 0
    try:
        stmt = db2.exec_immediate(conn, sql)
        res = db2.fetch_row(stmt)
        while res != False:
            print 'Result from :', db2.result(stmt, 0)
            n += db2.result(stmt, 0)
            res = db2.fetch_row(stmt)
    except:
        print "Transaction couldn't be completed:" , db2.stmt_errormsg()
    else:
        return n
Пример #36
0
def execute(sql):

    try:
        conn = ibm_db.connect(SSL_DSN, "", "")
        stmt = ibm_db.exec_immediate(conn, sql)
        # ibm_db.bind_param(stmt, 1, animal)
    except:
        print
        "Transaction couldn't be completed:", ibm_db.stmt_errormsg()
    else:
        print
        "Transaction complete."

    return "SUCCESS"
 def execute(self, command):
     """  Execute a command on the database and return the result if it is a query """
     result_set = []
     try:
         statement = ibm_db.exec_immediate(self._connection, command)
         result = ibm_db.fetch_both(statement)
         while result:
             result_set.append(result)
             result = ibm_db.fetch_both(statement)
     except Exception as e:
         print("Transaction could not be completed:", e,
               ibm_db.stmt_errormsg())
     finally:
         pass
     return result_set
Пример #38
0
 def run_test_016(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     if conn:
         result = ibm_db.exec_immediate(
             conn, "insert into t_string values(123,1.222333,'one to one')")
         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()
         result = ibm_db.exec_immediate(conn,
                                        "delete from t_string where a=123")
         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()
         ibm_db.close(conn)
     else:
         print "no connection:", ibm_db.conn_errormsg()
Пример #39
0
 def run_test_045(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     with open("ibm_db_tests/pic1_out.jpg", "wb") as fp:
         result = ibm_db.exec_immediate(
             conn, "SELECT picture FROM animal_pics WHERE name = 'Helmut'")
         row = ibm_db.fetch_tuple(result)
         if row:
             fp.write(row[0])
         else:
             print(ibm_db.stmt_errormsg())
     with open('ibm_db_tests/pic1_out.jpg', 'rb') as fp:
         pic_out = fp.read()
     with open('ibm_db_tests/pic1.jpg', 'rb') as fp:
         pic_in = fp.read()
     cmp = pic_in == pic_out
     print('Are the files the same:', cmp)
Пример #40
0
def get_latest_update(conn, schema, tablename):
    try:
        sql = "select max(id) from {}.{}".format(schema, tablename)
        logger.debug("Executing SQL: {}".format(sql))
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_tuple(stmt)
        if result:
            return result[0]
        else:
            return 0
    except Exception as e:
        if "SQL0204N" in ibm_db.stmt_errormsg():
            logger.debug("{}.{} was missing, returning -1 for max(id)".format(schema, tablename))
            return -1
        else:
            raise e
Пример #41
0
 def _check_table(self, tabschema, tabname):
     """ Checks if a table is defined.
         Returns True if defined, False otherwise.
     """
     cols = []
     rows = []
     log.debug("Checking if {tabschema}.{tabname} exist".format(tabschema = tabschema, tabname = tabname))
     try:
         self.query_db("select 1 from {tabschema}.{tabname}".format(tabschema = tabschema, tabname = tabname))
         log.debug("Exist.")
         return True
     except:
         if 'is an undefined name' in ibm_db.stmt_errormsg(): 
             return False
         else:
             raise
def read_dbInsparams(con, mess,dbInsParams):
    sqlQ = 'select name,value from SYSIBMADM.DBMCFG' 

    stmt = ibm_db.exec_immediate(con, sqlQ)
    if not stmt:
        mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt)
        module.fail_json(msg=mess, changed=False)
        raise

    dictionary = ibm_db.fetch_assoc(stmt)
    while dictionary != False:
        name = dictionary["NAME"]
        value = dictionary["VALUE"]
        dbInsParams.append(dict(name=name,value=value))   
        dictionary = ibm_db.fetch_assoc(stmt)  
        
    return True
def read_schemas(con,mess,dbSchemas):
    sqlQ = 'select SCHEMANAME,owner,cast(CREATE_TIME AS VARCHAR(35)) from syscat.SCHEMATA' 

    stmt = ibm_db.exec_immediate(con, sqlQ)
    if not stmt:
        mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt)
        module.fail_json(msg=mess, changed=False)
        raise

    dictionary = ibm_db.fetch_assoc(stmt)
    while dictionary != False:
        schema_name = dictionary["SCHEMANAME"]
        owner = dictionary["OWNER"]
        create_time = dictionary["3"]
        dbSchemas.append(dict(schema_name=schema_name,
                              owner=owner,
                              create_time=create_time))   
        dictionary = ibm_db.fetch_assoc(stmt)  
        
    return True
Пример #44
0
    def insert_many_records(self, recordslist, commitnum, tablename=None):
        conn = self.get_conn()
        for i in xrange(0, len(recordslist), commitnum):
            time.sleep(0.05)
            if tablename is None:
                import random

                tbname = 'xxx%d' % (random.randint(1, 5))
            else:
                tbname = tablename
            insertsql = "insert into %s(...) values(?, ?, ?, ?, ?, ?, \
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
                    ?, ?, ?, ?)" % tbname
            try:
                stmt = ibm_db.prepare(conn, insertsql)
                ibm_db.execute_many(stmt, tuple(recordslist[i:i + commitnum]))
            except:
                print ibm_db.stmt_error(), ibm_db.stmt_errormsg()
            finally:
                ibm_db.commit(conn)
 def run_test_048(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 )
   
   fp = open("tests/spook_out.png", "wb")
   if (server.DBMS_NAME[0:3] == 'IDS'):
       result = ibm_db.exec_immediate(conn, "SELECT picture FROM animal_pics WHERE name = 'Spook'")
   else:
       result = ibm_db.exec_immediate(conn, "SELECT picture, LENGTH(picture) FROM animal_pics WHERE name = 'Spook'")
   if (not result):
     print("Could not execute SELECT statement.")
     return 0
   row = ibm_db.fetch_tuple(result)
   if row:
     fp.write(row[0])
   else:
     print(ibm_db.stmt_errormsg())
   fp.close()
   cmp = (open('tests/spook_out.png', "rb").read() == open('tests/spook.png', "rb").read())
   print("Are the files the same:", cmp)
  def run_test_6561(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, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)")
      statement = "SELECT count(id) FROM animals"
      result = ibm_db.exec_immediate(conn, statement)
      if ( (not result) and ibm_db.stmt_error() ):
        print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))

      row = ibm_db.fetch_tuple(result)
      while ( row ):
        for i in row:
            print(i)
        row = ibm_db.fetch_tuple(result)
    
      ibm_db.rollback(conn)
      ibm_db.close(conn)
      
    else:
      print("Connection failed.")
def read_tablespaces(con, mess,dbTableSpaces):
    sqlQ = 'select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT from SYSIBMADM.DBA_TABLESPACES' 

    stmt = ibm_db.exec_immediate(con, sqlQ)
    if not stmt:
        mess[0] = sqlQ + "->" + ibm_db.stmt_errormsg(stmt)
        module.fail_json(msg=mess, changed=False)
        raise

    dictionary = ibm_db.fetch_assoc(stmt)
    while dictionary != False:
        tablespace_name = dictionary["TABLESPACE_NAME"]
        block_size = dictionary["BLOCK_SIZE"]
        status = dictionary["STATUS"]
        contents = dictionary["CONTENTS"]
        segment_management = dictionary["SEGMENT_SPACE_MANAGEMENT"]
        dbTableSpaces.append(dict(tablespace_name=tablespace_name,
                                  block_size=block_size,
                                  status=status,
                                  contents=contents,
                                  segment_management=segment_management))   
        dictionary = ibm_db.fetch_assoc(stmt)  
        
    return True
  def run_test_157a(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    print "Starting..."
    if conn:
      sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
      result = ibm_db.exec_immediate(conn, sql)

      try:
          i = 2
          row = ibm_db.fetch_assoc(result, i)
          while ( row ):
              if (server.DBMS_NAME[0:3] == 'IDS'):
                print "%-5d %-16s %-32s %10s" % (row['id'], row['name'], row['breed'], row['weight'])
              else:
                print "%-5d %-16s %-32s %10s" % (row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
              i = i + 2
          row = ibm_db.fetch_assoc(result, i)
      except:
          print "SQLSTATE: %s" % ibm_db.stmt_error(result)
          print "Message: %s" % ibm_db.stmt_errormsg(result)
	
      print "DONE"
def main():

    inputfile = '' 
    
    mess = ''

    module = AnsibleModule(
            argument_spec = dict(
            db2server=dict(default="localhost"),
            db2port=dict(default=50000),
            db2user=dict(required=True),
            db2userpwd=dict(required=True),
            db2db=dict(required=True),
            sqlFile=dict(required=True),
            logFile=dict(default=None),
        )
    )

    if not db2py_installed:
        module.fail_json(msg="IBM_DB has not been located", changed=False)    
        raise

    db2server = module.params["db2server"]
    db2port = module.params["db2port"]
    db2user = module.params["db2user"]
    db2userpwd = module.params["db2userpwd"]
    db2db = module.params["db2db"]
    inputfile = module.params["sqlFile"]
    outputfile = module.params["logFile"]
   
    conStr = "DATABASE="+db2db+";"
    conStr += "HOSTNAME="+db2server+";"
    conStr += "PORT="+db2port+";"
    conStr += "PROTOCOL=TCPIP;"
    conStr += "UID="+db2user+";"
    conStr += "PWD="+db2userpwd

    con = ibm_db.connect (conStr,"","")
    if not con:
        mess[0] = ibm_db.conn_errormsg()
        module.fail_json(msg=mess[0], changed=False)
        raise

    # If an output file has been specified, then create one
    if (outputfile) and outputfile != 'STDOUT':
        ofs = open(outputfile, "w")
 
    sqlQuery = ''
    with open(inputfile, 'r') as inp:
        lineNo = 0
        for line in inp:
            lineNo = lineNo + 1
            line = line.strip()
            line = line.lstrip()
            # Skip any blank lines or SQL remark characters
            if line == '\n' or line.find('--',0,2) != -1 or line.find('REM',0,3) != -1:
                sqlQuery = ''
            elif line == '/\n' or line.find(';') != -1:
                sqlQuery = sqlQuery + line
                sqlQuery = sqlQuery.strip()
                sqlQuery = sqlQuery.strip(';')
                sqlQuery = sqlQuery.strip('/')
                sqlQuery = sqlQuery.strip('\n')
                
                if sqlQuery and not sqlQuery.isspace():
                    stmt = ibm_db.exec_immediate(con, sqlQuery)
                    if not stmt:
                        mess = "Line: "+str(lineNo)+": "+sqlQuery + "->" + ibm_db.stmt_errormsg(stmt)
                        module.fail_json(msg=mess, changed=False)
                        raise

                    if (outputfile) and outputfile != 'STDOUT':
                        ofs.write(sqlQuery+"\nCommand processed\n")
                    elif (outputfile) and outputfile == 'STDOUT':
                        mess = mess + sqlQuery+"\nCommand processed\n"

                sqlQuery = ''

            else:
                sqlQuery = sqlQuery + line
    
    if (outputfile) and outputfile != 'STDOUT':
        ofs.close()
    inp.close()
    ibm_db.close(con)

    if not outputfile:
        mess = "SQL file processed"

    module.exit_json(msg=mess, changed=True)
	def run_test_trusted_context_connect(self):
		sql_drop_role = "DROP ROLE role_01"
		sql_create_role = "CREATE ROLE role_01"

		sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"

		sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
		sql_create_trusted_context += config.auth_user
		sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
		sql_create_trusted_context += config.hostname
		sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
		sql_create_trusted_context += config.tc_user

		sql_drop_table = "DROP TABLE trusted_table"
		sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"

		sql_select = "SELECT * FROM trusted_table"

		# Setting up database.
		conn = ibm_db.connect(config.database, config.user, config.password)
		if conn:
			sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
			sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_role)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_role)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_grant_permission)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
			except:
				pass
				
			# Populate the trusted_table
			values = (\
				(10, 20),\
				(20, 40),\
			)
			sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
			stmt = ibm_db.prepare(conn, sql_insert)		
			if stmt:
				for value in values:
					result = ibm_db.execute(stmt, value)
			ibm_db.close(conn)
		else:
			print("Connection failed.")

		options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
		tc_options = {
			ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, 
			ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
		}
		tc_all_options = {
			ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE, 
			ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, 
			ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
		}
		dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)

		# Makeing normal connection and playing with it.
		tc_conn = ibm_db.connect(dsn, "", "")
		if tc_conn:
			print("Normal connection established.")
			result = ibm_db.set_option(tc_conn, tc_options, 1)
			print(ibm_db.conn_errormsg(tc_conn))
			ibm_db.close(tc_conn)

		tc_conn = ibm_db.connect(dsn, "", "")
		if tc_conn:
			print("Normal connection established.")
			result = ibm_db.set_option(tc_conn, tc_all_options, 1)
			print(ibm_db.conn_errormsg(tc_conn))
			ibm_db.close(tc_conn)

		tc_conn = ibm_db.connect(dsn, "", "", tc_all_options)
		if tc_conn:
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				print("Trusted connection succeeded.")
				get_tc_user = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				if config.tc_user != get_tc_user:
					print("But trusted user is not switched.")
		ibm_db.close(tc_conn)

		# Making trusted connection and performing normal operations.
		tc_conn = ibm_db.connect(dsn, "", "", options)
		if tc_conn:
			print("Trusted connection succeeded.")
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				ibm_db.set_option(tc_conn, tc_options, 1)
				userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				if userBefore != userAfter:
					print("User has been switched.")
					
					# Inserting into table using trusted_user.
					sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
					stmt = ibm_db.prepare(tc_conn, sql_insert)
					result = ibm_db.execute(stmt, (300, 500))
					
					# Updating table using trusted_user.
					sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
					try:
						stmt = ibm_db.exec_immediate(tc_conn, sql_update)
					except:
						print(ibm_db.stmt_errormsg())
			
			ibm_db.close(tc_conn)
		else:
			print("Trusted connection failed.")

		# Making trusted connection and switching to fake user.
		tc_conn = ibm_db.connect(dsn, "", "", options)

		if tc_conn:
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				print("Trusted connection succeeded.")
				ibm_db.set_option(tc_conn, {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******"}, 1)

				sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
				try:
					stmt = ibm_db.exec_immediate(tc_conn, sql_update)
				except:
					print(ibm_db.stmt_errormsg())
			ibm_db.close(tc_conn)
		else:
			print("Connection failed.")

		# Making trusted connection and passing password first then user while switching.
		tc_conn = ibm_db.connect(dsn, "", "", options)
		tc_options_reversed = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}

		if tc_conn:
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				print("Trusted connection succeeded.")
				userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				ibm_db.set_option(tc_conn, tc_options_reversed, 1)
				userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				if userBefore != userAfter:
					print("User has been switched.")
			ibm_db.close(tc_conn)
		else:
			print("Connection failed.")	

		# Making trusted connection and passing password first then user while switching.
		tc_conn = ibm_db.connect(dsn, "", "", options)
		tc_user_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}
		tc_pass_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}

		if tc_conn:
			print("Trusted connection succeeded.")
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				try:
					ibm_db.set_option(tc_conn, tc_pass_options, 1)
				except:
					print(ibm_db.conn_errormsg(tc_conn))			
			ibm_db.close(tc_conn)
		else:
			print("Connection failed.")
		

		# Making trusted connection and passing only user while switching when both user and password are required.
		tc_conn = ibm_db.connect(dsn, "", "", options)

		if tc_conn:
			print("Trusted connection succeeded.")
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				ibm_db.set_option(tc_conn, tc_user_options, 1)

				sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
				try:
					stmt = ibm_db.exec_immediate(tc_conn, sql_update)
				except:
					print(ibm_db.stmt_errormsg())			
			ibm_db.close(tc_conn)
		else:
			print("Connection failed.")
		

		# Make a connection
		conn = ibm_db.connect(config.database, config.user, config.password)

		if conn:
			# Dropping the trusted context, in case it exists
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
			except:
				pass

			# Dropping Role.
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_role)
			except:
				pass

			# Creating Role.
			try:
				result = ibm_db.exec_immediate(conn, sql_create_role)
			except:
				pass
		
			# Granting permissions to role.
			try:
				sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01"
				result = ibm_db.exec_immediate(conn, sql_grant_permission)
			except:
				pass
		
			# Creating trusted context
			try:
				sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION"
				result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
			except:
				pass
			
			# Closing connection
			ibm_db.close(conn)
		else: 
			print("Connection failed.")			

		# Making trusted connection
		tc_conn = ibm_db.connect(dsn, "", "", options)
		if tc_conn:
			print("Trusted connection succeeded.")
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				ibm_db.set_option(tc_conn, tc_user_options, 1)
				userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				if userBefore != userAfter:
					print("User has been switched.")
					
					# Inserting into table using trusted_user.
					sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)"
					try:
						stmt = ibm_db.exec_immediate(tc_conn, sql_insert)
					except:
						print(ibm_db.stmt_errormsg())

					# Updating table using trusted_user.
					sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20"
					stmt = ibm_db.exec_immediate(tc_conn, sql_update)
			ibm_db.close(tc_conn)
		else:
			print("Connection failed.")	

		# Cleaning up database.
		conn = ibm_db.connect(config.database, config.user, config.password)

		if conn:
			print("Connection succeeded.")

			try:
				result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_role)
			except:
				pass
			ibm_db.close(conn)
		else:
			print("Connection failed.")
  def run_test_133(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    if (not conn):
      print("Connection failed.")
      return 0

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

    print("Starting test ...")
    res = ''
    sql =  "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
    try:
      stmt = ibm_db.prepare(conn, sql)
      res = ibm_db.execute(stmt,(128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3))
      
      stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
      res = ibm_db.execute(stmt, (128,))
      row = ibm_db.fetch_assoc(stmt)
      
      for i in row:
	         print(i)

      ibm_db.rollback(conn)
      print("Done")
    except:
      print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
      print("Message: %s" % ibm_db.stmt_errormsg(stmt))

    try:
        stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
        res = ibm_db.execute(stmt, (128,))
        row = ibm_db.fetch_assoc(stmt)
        if (row):
            for i in row:
                print(i)
        print(res)
        print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
        print("Message: %s" % ibm_db.stmt_errormsg(stmt))
    except:
        print("An Exception is not expected")
        print("SQLSTATE: %s" % ibm_db.stmt_error(stmt))
        print("Message: %s" % ibm_db.stmt_errormsg(stmt))

    ibm_db.rollback(conn)
    print("Done")

#__END__
#__LUW_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E  String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2/%s] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#Done
#__ZOS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E  String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#Done
#__SYSTEMI_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E  String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][AS] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#Done
#__IDS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length. SQLCODE=-1279
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][IDS%s] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#Done
	def run_test_trusted_context_pconnect(self):
		sql_drop_role = "DROP ROLE role_01"
		sql_create_role = "CREATE ROLE role_01"

		sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"

		sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
		sql_create_trusted_context += config.auth_user
		sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
		sql_create_trusted_context += config.hostname
		sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
		sql_create_trusted_context += config.tc_user

		sql_drop_table = "DROP TABLE trusted_table"
		sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"

		sql_select = "SELECT * FROM trusted_table"

		# Setting up database.
		conn = ibm_db.connect(config.database, config.user, config.password)
		if conn:
			sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
			sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_role)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_role)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_grant_permission)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
			except:
				pass
				
			# Populate the trusted_table
			values = (\
				(10, 20),\
				(20, 40),\
			)
			sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
			stmt = ibm_db.prepare(conn, sql_insert)
			if stmt:
				for value in values:
					result = ibm_db.execute(stmt, value)
			ibm_db.close(conn)
		else:
			print("Connection failed.")

		options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT:	ibm_db.SQL_TRUE}
		tc_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}
		dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)

		# Making trusted connection and performing normal operations.
		tc_conn = ibm_db.pconnect(dsn, "", "", options)
		if tc_conn:
			print("Trusted connection succeeded.")
			val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
			if val:
				userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				ibm_db.set_option(tc_conn, tc_options, 1)
				userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
				if userBefore != userAfter:
					print("User has been switched.")
					
					# Inserting into table using trusted_user.
					sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
					stmt = ibm_db.prepare(tc_conn, sql_insert)
					result = ibm_db.execute(stmt, (300, 500))
					
					# Updating table using trusted_user.
					sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
					try:
						stmt = ibm_db.exec_immediate(tc_conn, sql_update)
					except:
						print(ibm_db.stmt_errormsg())
			ibm_db.close(tc_conn)
		else:
			print("Trusted connection failed.")

		# Creating 10 Persistance connections and checking if trusted context is enabled (Cataloged connections)
		for i in range(10):
			tc_conn = ibm_db.pconnect(dsn, "", "")
			if tc_conn:
				val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
				if val:
					userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
					if userBefore != userAfter:
						print("Explicit Trusted Connection succeeded.")

		# Cleaning up database.
		conn = ibm_db.connect(config.database, config.user, config.password)

		if conn:
			print("Connection succeeded.")

			try:
				result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_table)
			except:
				pass
			try:
				result = ibm_db.exec_immediate(conn, sql_drop_role)
			except:
				pass
			ibm_db.close(conn)
		else:
			print("Connection failed.")
def check_community_owners(community_id):
    active = False
    sql = "select DISTINCT LOWER_LOGIN from sncomm.memberlogin where member_uuid in (SELECT DISTINCT MEMBER_UUID from sncomm.member WHERE COMMUNITY_UUID = '" + community_id + "' AND ROLE='1' AND LOWER_LOGIN NOT LIKE '%@%');"
    stmt = ibm_db.prepare(conn_comm, sql)
    try:
        ibm_db.execute(stmt)
        while ibm_db.fetch_row(stmt) != False:
            member_login = ibm_db.result(stmt, 0)
            if check_if_user_active(member_login):
                active = True
                return True
                break
            else:
                return False
    except:
        print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg()
        sys.exit(3)
# Query DB2 database
sql = "select DISTINCT COMMUNITY_UUID, NAME, LASTMOD from sncomm.community;"
stmt = ibm_db.prepare(conn_comm, sql)
try:
    ibm_db.execute(stmt)
    while ibm_db.fetch_row(stmt) != False:
        name = ibm_db.result(stmt, "NAME")
        lastupdate = ibm_db.result(stmt, "LASTMOD")
        if (datetime.datetime.now() - datetime.timedelta(days=int(args.critical)) > lastupdate):
            count_critical = count_critical + 1
            list_critical = list_critical + name + "\n"
        elif (datetime.datetime.now() - datetime.timedelta(days=int(args.warning)) > lastupdate):
            count_warning = count_warning + 1
            list_warning = list_warning + name + "\n"
except:
    print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg()
    sys.exit(3)

# Nagios standard feedback
if (count_critical!=0):
    print("Critical: There are communites which were not updates since " + args.critical + " days" + list_critical.encode('utf-8') + "| Warning Number=" +  str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
    sys.exit(2)
elif (count_warning!=0):
    print("Warning: There are communites which were not updates since " + args.warning + " days" + list_warning.encode('utf-8') + "| Warning Number=" +  str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
    sys.exit(1)
elif (count!=0):
    print("Communites are OK " + "| Warning Number=" +  str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
    sys.exit(0)

else:
    print("UKNOWN number of Communities without an owner!")
Пример #55
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")