def run_test_240(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + str(ibm_db.field_width(result, i))) print("\n-----") for i in range(0, ibm_db.num_fields(result2)): print( str(i) + ":" + str(ibm_db.field_width(result2, ibm_db.field_name(result2, i))) ) print("\n-----") for i in range(0, 3): print( str(i) + ":" + str(ibm_db.field_width(result3, i)) + "," + str(ibm_db.field_display_size(result3, i))) print("\n-----") print("region:%s" % ibm_db.field_type(result, 'region')) print("5:%s" % ibm_db.field_type(result2, 5))
def run_test_231(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result) + 1): print str(i) + ":" + str( ibm_db.field_type(result, ibm_db.field_name(result, i))) print "\n-----" for i in range(0, ibm_db.num_fields(result2)): print str(i) + ":" + ibm_db.field_type( result2, ibm_db.field_name(result2, i)) print "\n-----" for i in range(0, 3): print str(i) + ":" + ibm_db.field_type( result3, ibm_db.field_name(result3, i)) print "\n-----" print "region:%s" % ibm_db.field_type(result, 'region') print "5:%s" % ibm_db.field_type(result2, 5)
def run_test_checkBooleanType(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 TABLE bool_test") except: pass try: ibm_db.exec_immediate(conn, "CREATE TABLE bool_test(col1 INTEGER, col2 BOOLEAN, col3 BOOLEAN)") except Exception as e: print("Error : {}\n".format(str(e))) exit(-1) try: select_sql = 'SELECT * FROM bool_test' stmt = ibm_db.exec_immediate(conn, select_sql) for i in range(0,ibm_db.num_fields(stmt)): print(str(ibm_db.field_type(stmt,i))) ibm_db.close(conn) except Exception as e: print("Error:{}".format(str(e)))
def run_test_232(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") for i in range(0, ibm_db.num_fields(result) + 1): field_name = ibm_db.field_name(result,i) field_type = ibm_db.field_type(result, ibm_db.field_name(result,i)) print(str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i)))) print("-----") t = ibm_db.field_type(result,99) print(t) t1 = ibm_db.field_type(result, "HELMUT") print(t1)
def run_test_232(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") for i in range(0, ibm_db.num_fields(result) + 1): field_name = ibm_db.field_name(result,i) field_type = ibm_db.field_type(result, ibm_db.field_name(result,i)) print str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i))) print "-----" t = ibm_db.field_type(result,99) print t t1 = ibm_db.field_type(result, "HELMUT") print t1
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)) if (server.DBMS_NAME.startswith('DB2/')): d_val = datetime.date(2019, 10, 16) ts_val = datetime.datetime(2019, 10, 16, 23, 0, 0, 0) statement = "INSERT INTO tab_datetime (col1, col2, col3) values ('24:00:00', ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (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 main(): conn = None stmt = None if not conf.has_option('passwd'): conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8')) try: conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'), conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '') stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8')) try: result = ibm_db.fetch_tuple(stmt) except: rows = ibm_db.num_rows(stmt) if rows != -1: print u'Обработано строк %d' % rows else: print u'Команда выполнена' return if result: column_conv = [] head = u'' underline=u'' for i in xrange(len(result)): if i != 0: head += u'|' underline += u'+' name = ibm_db.field_name(stmt, i) size = ibm_db.field_display_size(stmt, i) if len(name) > size: size = len(name) if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size: size = len(u'NULL') type_field = ibm_db.field_type(stmt, i) if type_field == 'float' or type_field == 'real' or type_field == 'decimal': column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float}) elif type_field == 'int' or type_field == 'bigint': column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int}) else: column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert}) head += name.center(size) underline += u'-' * size print head print underline while( result ): print conv(result, column_conv) result = ibm_db.fetch_tuple(stmt) else: print u'Результата не возвращено' except Exception as e: print >> sys.stderr, e sys.exit(-1) finally: if stmt: ibm_db.free_result(stmt) if conn: ibm_db.close(conn)
def getColumns(stmt): columns = [] types = [] colcount = 0 try: colname = ibm_db.field_name(stmt, colcount) coltype = ibm_db.field_type(stmt, colcount) while (colname != False): columns.append(colname) types.append(coltype) colcount += 1 colname = ibm_db.field_name(stmt, colcount) coltype = ibm_db.field_type(stmt, colcount) return columns, types except Exception as err: db2_error() return None
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.")
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 run_test_240(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + str(ibm_db.field_width(result,i))) print("\n-----") for i in range(0, ibm_db.num_fields(result2)): print(str(i) + ":" + str(ibm_db.field_width(result2,ibm_db.field_name(result2,i)))) print("\n-----") for i in range(0, 3): print(str(i) + ":" + str(ibm_db.field_width(result3,i)) + "," + str(ibm_db.field_display_size(result3,i))) print("\n-----") print("region:%s" % ibm_db.field_type(result,'region')) print("5:%s" % ibm_db.field_type(result2,5))
def run_test_230(self): conn = ibm_db.connect(config.database, config.user, config.password) result = ibm_db.exec_immediate(conn, "select * from sales") result2 = ibm_db.exec_immediate(conn, "select * from staff") result3 = ibm_db.exec_immediate(conn, "select * from emp_photo") for i in range(0, ibm_db.num_fields(result) + 1): print str(i) + ":" + str(ibm_db.field_type(result,i)) print "\n-----" for i in range(0, ibm_db.num_fields(result2)): print str(i) + ":" + str(ibm_db.field_type(result2,i)) print "\n-----" for i in range(0, 3): print str(i) + ":" + str(ibm_db.field_type(result3,i)) print "\n-----" print "region:%s" % str(ibm_db.field_type(result,'region')) print "5:%s" % str(ibm_db.field_type(result2,5))
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 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 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_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
# Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Result set information:\n") print("COLUMN NAME DATA TYPE") print("___________ _________") # As Long As There Is Column Information, ... for loopCounter in range(0, numColumns): # Get The Name Of The Current Column colName = ibm_db.field_name(resultSet, loopCounter) # Get The Data Type Of The Current Column colDataType = ibm_db.field_type(resultSet, loopCounter) # Format And Display The Data Retrieved - Show Data Types In Upper Case if (not colName is False) and (not colDataType is False): print("{:<13} {:>7}".format(colName, colDataType.upper())) # Add A Blank Line To The End Of The Report print() # Close The Database Connection That Was Opened Earlier conn.closeConnection() # Return Control To The Operating System exit()