def run_test_114(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, num INTEGER )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, num) values (1,5)" ibm_db.exec_immediate(conn, insert) insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral") ibm_db.execute(stmt) result = ibm_db.fetch_row( stmt ) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print row0 print row1 result = ibm_db.fetch_row( stmt ) 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_115(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) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # NOTE: This is a workaround # function fetch_object() to be implemented... # row = ibm_db.fetch_object(stmt, 0) class Row: pass row = Row() ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print(row.DATA) insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # row = ibm_db.fetch_object(stmt, 0) ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print(row.DATA) else: print("Connection failed.")
def run_test_035(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from staff") i=0 row = ibm_db.fetch_row(result) while ( row ): print "%d, " % i i+=1 row = ibm_db.fetch_row(result) ibm_db.close(conn)
def run_test_264(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: 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) # Drop the tab_bigint table, in case it exists drop = "DROP TABLE tab_bigint" result = "" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if server.DBMS_NAME[0:3] == "IDS": create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)" else: create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = ibm_db.exec_immediate(conn, insert) print "Number of inserted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while data: print data[0] print data[1] print data[2] print data[3] print type(data[0]) is long print type(data[1]) is long print type(data[2]) is long data = ibm_db.fetch_both(stmt) # test ibm_db.result for fetch of bigint stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint") ibm_db.execute(stmt1) ibm_db.fetch_row(stmt1, 0) if server.DBMS_NAME[0:3] != "IDS": row1 = ibm_db.result(stmt1, "COL2") else: row1 = ibm_db.result(stmt1, "col2") print row1 ibm_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE tab_datetime' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = datetime.time(10, 42, 34) d_val = datetime.date(1981, 7, 8) #ts_val = datetime.datetime.today() ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10) server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) statement = "SELECT * FROM tab_datetime" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + ibm_db.field_type(result,i)) statement = "SELECT * FROM tab_datetime" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) print(type(row0), row0) print(type(row1), row1) print(type(row2), row2) result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print("Connection failed.")
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
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_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.")
def run_test_6792(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10:42:34', '1981-07-08', '1981-07-08 10:42:34')" result = ibm_db.exec_immediate(conn, statement) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10.42.34', '1981-07-08', '1981-07-08-10.42.34')" result = ibm_db.exec_immediate(conn, statement) statement = "SELECT * FROM table_6792" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + ibm_db.field_type(result,i) statement = "SELECT * FROM table_6792" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) print row0 print row1 print row2 result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print "Connection failed."
def run_test_036(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from staff") i=0 row = ibm_db.fetch_row(result) while ( row ): result2 = ibm_db.exec_immediate(conn, "select * from staff") j=0 row2 = ibm_db.fetch_row(result2) while ( row2 ): print("%d)%d," % (i, j)) j+=1 row2 = ibm_db.fetch_row(result2) print("%d, " % i) i+=1 row = ibm_db.fetch_row(result) ibm_db.close(conn)
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)
def items(): page = '' if db2conn: stmt = ibm_db.exec_immediate(db2conn, "SELECT * FROM " + db2cred['username'] + ".ITEMS") # Look through each row, while there is a row while ibm_db.fetch_row(stmt) != False: # Get the rows Item column value page += ibm_db.result(stmt, "ITEM") + '\n' return page
def run_test_113(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "DROP TABLE datetest" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )" ibm_db.exec_immediate(conn, create) server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) else: insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM datetest") ibm_db.execute(stmt) result = ibm_db.fetch_row( stmt ) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print row0 print row1 result = ibm_db.fetch_row( stmt ) 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)
def run_test_031(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ibm_db.fetch_row(stmt)): id = ibm_db.result(stmt, 0) print("int(%d)" % id) breed = ibm_db.result(stmt, 1) print("string(%d) \"%s\"" % (len(breed), breed)) name = ibm_db.result(stmt, 2) print("string(%d) \"%s\"" % (len(name), name)) weight = ibm_db.result(stmt, 3) print("string(%d) \"%s\"" % (len(str(weight)), weight)) ibm_db.close(conn) else: print("Connection failed.")
def run_test_033(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)): weight = ibm_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) 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) ibm_db.close(conn) else: print "Connection failed."
def connectBD2(): # 连接数据库 # conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '') conn = ibm_db.connect(conn_str, '', '') # 获取数据库服务器信息 server = ibm_db.server_info(conn) # 查询数据库sql sql ='select PID, "NAME" from PRODUCT' arr = [] dc = {} if conn: # 执行查询语句 stmt = ibm_db.exec_immediate(conn, sql) # 处理查询结果 while (ibm_db.fetch_row(stmt)): acno, dc[acno] = ibm_db.result(stmt, 0), ibm_db.result(stmt, 1) arr.append(acno) # 打印服务器名称 print(server.DBMS_NAME) # 关闭数据库连接 ibm_db.close(conn) return arr, dc
def run_test_014(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info( conn ) query = 'SELECT * FROM animals ORDER BY name' 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)
def main(args): global conn ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] if globals().get("conn") is None: print("creating connection") conn = ibm_db.connect(ssldsn, "", "") else: print("reusing connection") drop = "DROP TABLE CUSTOMER_FEEDBACK" result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) # Check table creation statement = "SELECT * FROM CUSTOMER_FEEDBACK" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i), ":", ibm_db.field_type(result, i)) ts_val = datetime.datetime.today() print("the time is", ts_val) # Try to insert sample test row statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val)) statement = "SELECT * FROM CUSTOMER_FEEDBACK" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) print(ibm_db.result(stmt, 0), ibm_db.result( stmt, 1), ibm_db.result(stmt, 2)) if not result: return {"err": "error :" + statement} return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
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
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
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)))
if resultSet is False: print("\nERROR: Unable to execute the SQL statement specified.\n") conn.closeConnection() exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Query results:\n") print("DEPTNAME") print("____________________________") # As Long As There Are Records In The Result Set Produced, ... while (ibm_db.fetch_row(resultSet) is True): # Retrieve The Data From The Current Row try: dataValue = ibm_db.result(resultSet, 0) except Exception: pass # Display The Data Retrieved if not dataValue is None: print(dataValue) # Add A Blank Line To The End Of The Report print() # Close The Database Connection That Was Opened Earlier
insertQuery2 = "insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')" insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2) #***query data in the table #Construct the query that retrieves all rows from the INSTRUCTOR table selectQuery = "select * from INSTRUCTOR" #Execute the statement selectStmt = ibm_db.exec_immediate(conn, selectQuery) #Fetch the Dictionary (for the first row only) ibm_db.fetch_both(selectStmt) #Fetch the rest of the rows and print the ID and FNAME for those rows while ibm_db.fetch_row(selectStmt) != False: print(" ID:", ibm_db.result(selectStmt, 0), " FNAME:", ibm_db.result(selectStmt, "FNAME")) #**Retrieve data into Pandas::: import pandas import ibm_db_dbi #connection for pandas pconn = ibm_db_dbi.Connection(conn) #query statement to retrieve all rows in INSTRUCTOR table selectQuery = "select * from INSTRUCTOR" #retrieve the query results into a pandas dataframe pdf = pandas.read_sql(selectQuery, pconn) #print just the LNAME for first row in the pandas data frame
uid = args.user pwd = args.pw # Connect to DB2 database try: conn_people = ibm_db.connect("DATABASE=" + peopledb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "") conn_comm = ibm_db.connect("DATABASE=" + communitydb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "") except: print "No connection to DBs:", ibm_db.conn_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) + ";;")
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_6792(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = '10:42:34' d_val = '1981-07-08' ts_val = '1981-07-08 10:42:34' ts_withT_val = '2013-06-06T15:30:39' server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val)) statement = "SELECT * FROM table_6792" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + ibm_db.field_type(result,i) statement = "SELECT * FROM table_6792" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) row3 = ibm_db.result(stmt, 3) print row0 print row1 print row2 print row3 result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print "Connection failed." #__END__ #__LUW_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__ZOS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__SYSTEMI_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__IDS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39