def run_test_195(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
drop = 'DROP TABLE test_195'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
ibm_db.exec_immediate(conn, insert)
sql = "SELECT data FROM test_195"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
while( result ):
print "Output:", result
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print "Native XML datatype is not supported."
def run_test_006(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
print ("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
def run_test_300(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER)
print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE
print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
ibm_db.close(conn)
else:
print "Error."
def run_test_022(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_116(self):
conn = None
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
conn = ibm_db.connect(config.database, config.user, config.password)
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
ibm_db.close(conn)
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
# Executing active method multiple times to reproduce a customer reported defect
print(ibm_db.active(conn))
print(ibm_db.active(conn))
print(ibm_db.active(conn))
conn = ibm_db.connect(config.database, config.user, config.password)
print(ibm_db.active(conn))
print(ibm_db.active(conn))
print(ibm_db.active(conn))
def run_test_034(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "select * from staff")
row = ibm_db.fetch_assoc(result)
if row:
# printf("%5d ",row['ID'])
# printf("%-10s ",row['NAME'])
# printf("%5d ",row['DEPT'])
# printf("%-7s ",row['JOB'])
# printf("%5d ", row['YEARS'])
# printf("%15s ", row['SALARY'])
# printf("%10s ", row['COMM'])
# puts ""
print "%5d %-10s %5d %-7s %5d %15s %10s" % (
row["ID"],
row["NAME"],
row["DEPT"],
row["JOB"],
row["YEARS"],
row["SALARY"],
row["COMM"],
)
ibm_db.close(conn)
def 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_020(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
ac = ibm_db.autocommit(conn)
if ac != 0:
print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_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_142(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, sql)
weight = 200.05
mass = 2.0
ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
if ( result ):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def appTransaction(p_vehicleNum):
if(vehicleDetails.has_key(p_vehicleNum)):
l_connection = dB_init()
if(l_connection == None):
print("Database Connection Failed on Database Query")
return
dateorder_query = "SELECT * FROM DASH6461.TOLL_DATA WHERE VEHICLE_NUMBER = \'"+str(p_vehicleNum)+"\' ORDER BY DATES DESC,TIME DESC LIMIT 5"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, dateorder_query)
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("appHistoricalGraph_twodatequery exec/fetch_ERROR : " + str(e))
l_count = 0
while l_temp_dict:
l_new_date = l_temp_dict["DATES"].strftime("%d-%m-%Y")
l_new_time = l_temp_dict["TIME"].strftime("%H:%M:%S")
l_final_date = l_new_date + " " + l_new_time
vehicleTransaction[l_count] = [l_final_date,l_temp_dict["TOLL_NAME"],l_temp_dict["AMOUNT_DEDUCT"],l_temp_dict["AMOUNT_ADDED"],l_temp_dict["AVAI_BAL"]]
l_count+=1
try:
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("appHistoricalGraph_twodatequery fetch_ERROR : " + str(e))
transVehicle[p_vehicleNum] = vehicleTransaction
ibm_db.free_stmt(l_db_statement)
ibm_db.close(l_connection)
pubnub.publish(channel=p_vehicleNum+p_vehicleNum,message=transVehicle[p_vehicleNum])
else:
pass
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
if (server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (server.DBMS_NAME == "DB2"):
pattern = self.expected_ZOS(callstack[1][1])
elif (server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[','\]','\(','\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
def run_test_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
def run_test_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()
def generalSetting(p_vehicleNumber):
if(p_vehicleNumber != None):
if(vehicleDetails.has_key(p_vehicleNumber)):
if(vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS] == 0):
pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\
"vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]})
l_connection = dB_init()
if(l_connection == None):
print("Database Connection Failed on Database Query")
return
l_database_query = "SELECT * FROM "+DB_SCHEMA+"."+DATABASE_TABLE_NAME_1+" WHERE VEHICLE_NUMBER = '"+str(p_vehicleNumber)+"'"
try:
l_db_statement = ibm_db.exec_immediate(l_connection, l_database_query)
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("rfid Register exec/fetch_ERROR : " + str(e))
while l_temp_dict:
if(l_temp_dict["VEHICLE_NUMBER"] == p_vehicleNumber):
vehicleDetails[p_vehicleNumber][DETAILS_BALANCE] = l_temp_dict["WALLET_BAL"]
try:
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("rfid Register fetch_ERROR : " + str(e))
ibm_db.free_stmt(l_db_statement)
ibm_db.close(l_connection)
pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\
"vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]})
else:
appSetting(p_vehicleNumber,vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS])
else:
pubnub.publish(channel=p_vehicleNumber, message={"warning":"Vehicle Not Registered with the Automated System"})
else:
pass
def run_test_311(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON )
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ibm_db.exec_immediate(conn, sql)
print "Number of updated rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ibm_db.exec_immediate(conn, sql)
print "Number of deleted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)
def run_test_001(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print ("Connection succeeded.")
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_002(self):
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.user, config.password)
conn = ibm_db.connect(conn_str, '', '')
if conn:
print ("Connection succeeded.")
ibm_db.close(conn)
else:
print ("Connection failed.")
def main():
conn = ibm_db.connect("cs421","","")
taxes = taxable_cash_dolla(conn)
for i in taxes:
print "Month: " + i + ", Taxes paid: " + taxes[i]
ibm_db.close(conn)
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 run_test_005(self):
baduser = "******"
badpass = "******"
dsn = "DATABASE=" + config.database + ";UID=" + baduser + ";PWD=" + badpass + ";"
try:
conn = ibm_db.connect(dsn, "", "")
print ("odd, ibm_db.connect succeeded with an invalid user / password")
ibm_db.close(conn)
except:
print ("Ooops")
def appHistoricalGraph(p_containerid,p_timeSpan):
global DATABASE_TABLE_NAME
#Connecting to the database
l_connection = dB_init()
if(l_connection == None):
logging.error("Database Connection Failed on Database Query")
return
#Evaluvating the number of days to query the db
p_timeSpan = p_timeSpan - 1
l_refill_history = dict()
l_consumption_history = dict()
l_temp_dict = dict()
l_sdat = datetime.datetime.now().date()
l_edat = l_sdat - datetime.timedelta(days=p_timeSpan)
l_sdate = l_sdat.strftime('%Y-%m-%d')
l_edate = l_edat.strftime('%Y-%m-%d')
#Parsing the data from the database and update the dictionary with respective time span
for i in range(p_timeSpan,-1,-1):
l_edat_loop = l_sdat - datetime.timedelta(days=i)
l_edate_loop = l_edat_loop.strftime('%Y-%m-%d')
l_refill_history[l_edate_loop] = [p_containerid,0,0,0]
l_consumption_history[l_edate_loop] = [p_containerid,0,0,0]
l_twodate_query = "SELECT * FROM "+DB_SCHEMA+"."+DATABASE_TABLE_NAME +" WHERE DATES BETWEEN DATE(\'" + l_edate + "\') AND DATE(\'" + l_sdate + "\') AND SCALE_ID =" + p_containerid
try:
l_db_statement = ibm_db.exec_immediate(l_connection, l_twodate_query)
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("appHistoricalGraph_twodatequery exec/fetch_ERROR : " + str(e))
while l_temp_dict:
if(l_temp_dict["SCALE_ID"] == p_containerid):
l_date = l_temp_dict["DATES"].strftime('%Y-%m-%d')
if(l_temp_dict["STATUS"] == 0):
l_refill_history[l_date] = [l_temp_dict["SCALE_ID"],l_temp_dict["TIME"],"%.2f"%l_temp_dict["QUANTITY"],l_temp_dict["STATUS"]]
else:
l_consumption_history[l_date] = [l_temp_dict["SCALE_ID"],l_temp_dict["TIME"],"%.2f"%l_temp_dict["QUANTITY"],l_temp_dict["STATUS"]]
try:
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("appHistoricalGraph_twodatequery fetch_ERROR : " + str(e))
pubnub.publish(channel="kitchenApp-refillHistory", message=l_refill_history)
pubnub.publish(channel="kitchenApp-consumptionHistory", message=l_consumption_history)
#deleting the history
del l_refill_history,l_consumption_history
#Closing the Database Connection
ibm_db.free_stmt(l_db_statement)
ibm_db.close(l_connection)
def run_test_220(self):
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_011(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, "DELETE FROM animals WHERE weight > 10.0")
print ("Number of affected rows: %d" % ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_210(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from staff")
cols = ibm_db.num_fields(result)
for i in range(0, cols):
size = ibm_db.field_display_size(result,i)
print "col:%d and size: %d" % (i, size)
ibm_db.close(conn)
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_003(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ibm_db.exec_immediate(conn, sql)
print ("Number of affected rows: %d" % ibm_db.num_rows(res))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
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 )
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print ("Connection failed.")
#__END__
#__LUW_EXPECTED__
#No Data[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
#__ZOS_EXPECTED__
#No Data[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
#__SYSTEMI_EXPECTED__
#No Data
#__IDS_EXPECTED__
#No Data
#[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
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 getrange():
try:
if request.method == "POST":
uppermag = request.form['uppermag']
lowermag = request.form['lowermag']
startdate = request.form['startdate']
enddate = request.form['enddate']
#connect to db
conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" +
db2cred['hostname'] + ";PORT=" + str(db2cred['port']) +
";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] +
";", "", "")
if conn:
print("in if loop")
sql = 'select * from RZG77856.ALL_MONTH where mag between ? and ? and date >=? and date <=?'
prep = ibm_db.prepare(conn, sql)
ibm_db.bind_param(prep, 1, lowermag)
ibm_db.bind_param(prep, 2, uppermag)
ibm_db.bind_param(prep, 3, startdate)
ibm_db.bind_param(prep, 4, enddate)
ibm_db.execute(prep)
rows = []
count = 0
print("conn 2")
# fetching the result
result = ibm_db.fetch_assoc(prep)
print(result)
while result != False:
count = count + 1
rows.append(result.copy())
result = ibm_db.fetch_assoc(prep)
# close database connection
ibm_db.close(conn)
print(len(rows))
return render_template('rangeresult.html',
rows=rows,
count=count)
else:
print("no connection established")
return render_template('main.html')
except Exception as e:
print(e)
return "<html><body><p>In Exception</p></body></html>"
def getJobReq(id):
try:
ibm_db_conn = ibm_db.connect(dsn, '', '')
conn = ibm_db_dbi.Connection(ibm_db_conn)
cursor = conn.cursor()
print("Connected to {0}".format(DB2_DB))
except:
print("Couldn't Connect to Database")
return False
try:
q1 = "SELECT GITID, GIT_MUL, BIG5_MUL, VALUES_MUL, SELF_DESC, SELF_DESC_MUL, JOB_WANT_WHY, JOB_WANT_WHY_MUL, JOB_REQ_WHAT, JOB_REQ_WHAT_MUL, PASSION, PASSION_MUL, JOBSKILLS, JOBSKILLS_MUL, JOBYOE, JOBYOE_MUL, APT_MUL, DATE_JOIN, DATE_JOIN_MUL"
q1 = q1 + " FROM JOBS_RAW WHERE JOBID = " + str(id) + ";"
cursor.execute(q1)
jobReq = cursor.fetchall()[0]
except:
print('Error Querying JOB Requirement')
ibm_db.close(ibm_db_conn)
print("connection closed")
return False
print('fetched job requirement')
ibm_db.close(ibm_db_conn)
print("connection closed")
return {
'gitId': jobReq[0],
'gitId_mul': jobReq[1],
'big5_mul': jobReq[2],
'values_mul': jobReq[3],
'self_desc': jobReq[4],
'self_desc_mul': jobReq[5],
'job_want_why': jobReq[6],
'job_want_why_mul': jobReq[7],
'job_req_what': jobReq[8],
'job_req_what_mul': jobReq[9],
'passion': jobReq[10],
'passion_mul': jobReq[11],
'jobskills': jobReq[12],
'jobskills_mul': jobReq[13],
'yoe': jobReq[14],
'yoe_mul': jobReq[15],
'apt_mul': jobReq[16],
'date_join': jobReq[17],
'date_join_mul': jobReq[18]
}
def Welcome():
db2conn = ibm_db.connect(
"DATABASE=" + ibmdb2cred['db'] + ";HOSTNAME=" +
ibmdb2cred['hostname'] + ";PORT=" + str(ibmdb2cred['port']) + ";UID=" +
ibmdb2cred['username'] + ";PWD=" + ibmdb2cred['password'] + ";", "",
"")
if db2conn:
sql = "select * from people;"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
print(rows)
page = ""
stmt = ibm_db.exec_immediate(
db2conn,
"select host_name,os_name,os_version,total_cpus,configured_cpus, total_memory,"
"os_kernel_version,os_arch_type, os_release,os_full_version from sysibmadm.env_sys_info"
)
# fetch the result
result = ibm_db.fetch_assoc(stmt)
page += "OS Name: " + result["OS_NAME"] + "<br/>OS Version: " + result[
"OS_VERSION"]
page += "<br/>Hostname: " + result[
"HOST_NAME"] + "<br/> Total CPUs: " + str(result["TOTAL_CPUS"])
page += "<br/>Configured CPUs: " + str(
result["CONFIGURED_CPUS"]) + "<br/>Total memory: " + str(
result["TOTAL_MEMORY"]) + " MB"
page += "<br/>OS Kernel Version: " + result[
"OS_KERNEL_VERSION"] + "<br/>OS Architecture Tpye: " + result[
"OS_ARCH_TYPE"]
page += "<br/>OS Release: " + result[
"OS_RELEASE"] + "<br/>OS full version: " + result["OS_FULL_VERSION"]
ibm_db.close(db2conn)
return render_template('index.html',
data=rows,
system_info=page,
script_root=request.script_root)
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()
def run_test_execute_many(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the tabmany table, in case it exists
drop = "DROP TABLE TABMANY"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
#create table tabmany
create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))"
ibm_db.exec_immediate(conn, create)
#Populate the tabmany table with execute_many
insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)"
params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'),
(40, 'OBrien'))
stmt_insert = ibm_db.prepare(conn, insert)
ibm_db.execute_many(stmt_insert, params)
#check the number of rows inserted
row_count = ibm_db.num_rows(stmt_insert)
print row_count
# chaeck the inserted columns
select = "SELECT * FROM TABMANY"
stmt_select = ibm_db.exec_immediate(conn, select)
cols = ibm_db.fetch_tuple(stmt_select)
while (cols):
print "%s, %s" % (cols[0], cols[1])
cols = ibm_db.fetch_tuple(stmt_select)
#populate the tabmany table
params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'),
(60, 'Quigley'), (70, None))
try:
ibm_db.execute_many(stmt_insert, params)
except Exception, inst:
#check the no. of inserted rows
row_count = ibm_db.num_rows(stmt_insert)
#check the exception raised by execute_many API
print inst
print row_count
ibm_db.close(conn)
def run_test_021(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.commit(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
# Populate the animal table
animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty',
350.0), (3, 'gold fish', 'Bubbles',
0.1), (4, 'budgerigar', 'Gizmo', 0.2),
(5, 'goat', 'Rickety Ride', 9.7), (6, 'llama',
'Sweater', 150))
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
ibm_db.commit(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def verificar_status(self):
erro = ''
self.decriptar()
if self.tipo == 2:
try:
conection = psycopg2.connect(
f'host={self.ip} user={self.usuario} dbname={self.nomebanco} port={self.porta} password={self.senhasemcripto}'
)
conection.close()
erro = ''
except Exception as e:
print(e)
erro = str(e)
else:
try:
conection = ibm_db.connect(
f'DATABASE={self.nomebanco};HOSTNAME={self.ip};PORT={self.porta};PROTOCOL={self.protocolo};UID={self.usuario};PWD={self.senhasemcripto};',
'', '')
ibm_db.close(conection)
erro = ''
except Exception as e:
print(ibm_db.conn_errormsg())
erro = str(e)
if erro == '':
status = 1
else:
status = 2
monitoramento = db.session.query(Monitoramento).filter(
Monitoramento.idbanco == self.idbanco).order_by(
Monitoramento.idmonitoramento.desc()).first()
if monitoramento and monitoramento.idstatus == status:
monitoramento.dtmonitoramento = datetime.now()
if status == 2:
monitoramento.observacao = erro
monitoramento.atualizar()
elif monitoramento and monitoramento.idstatus != status:
monitoramento.dhfinal = datetime.now()
monitoramento.atualizar()
monitoramento_novo = Monitoramento(self.idbanco, datetime.now(),
status, datetime.now(), erro)
monitoramento_novo.atualizar()
else:
monitoramento_novo = Monitoramento(self.idbanco, datetime.now(),
status, datetime.now(), erro)
monitoramento_novo.atualizar()
def largest():
# connect to DB2
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
# we have a Db2 connection, query the database
sql = 'Select "mag","time","place" from EARTHQ where "mag" is not NULL order by "mag" DESC limit 5'
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('largest.html', r=rows)
def getQuestions():
db2conn = createConnection()
if db2conn:
# if we have a Db2 connection, query the database
sql = "SELECT question_content FROM question;"
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return rows
def index():
con = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=lbx88746;PWD=s05d49+z1hv98prc;",
"", "")
query = "SELECT * FROM people"
stmt = ibm_db.prepare(con, query)
ibm_db.execute(stmt)
row = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
row.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(con)
return render_template('assign1.html', value=row)
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user,
config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(
pconn[33],
"UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows(stmt))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
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 register(self, testClient, name, email, password, confirm, tutorname):
"""
Deletes the test users and then re registers them
:return: the response of the insert
"""
db2conn = createConnection()
if db2conn:
sql = ("DELETE from student where email = '" + email + "'")
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
sql = ("DELETE from tutor where email = '" + email + "'")
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
ibm_db.close(db2conn)
return self.registerIndividual(testClient, name, email, password,
confirm, tutorname)
def run_test_260(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")
row = ibm_db.fetch_tuple(stmt)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def query_search(q):
try:
db2conn = ibm_db.connect(conn_str, "", "")
if db2conn:
statement = ibm_db.prepare(db2conn, q)
ibm_db.execute(statement)
rows = []
result = ibm_db.fetch_assoc(statement)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(statement)
ibm_db.close(db2conn)
return rows
else:
return False
except:
print("Connection to Database failed")
exit(1)
def updateGrades():
student = request.args.get('student')
grades = request.args.get('grades')
sql = "update people set grade=%s where name=%s" % ('\'' + grades + '\'',
'\'' + student + '\'')
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
sql = "select name, grade from people where name=%s" % ('\'' + student +
'\'')
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('city.html', results=result)
def run_test_310(self):
conn = ibm_db.connect(config.database, config.user, config.password)
client = ibm_db.client_info(conn)
if client:
print "DRIVER_NAME: string(%d) \"%s\"" % (len(client.DRIVER_NAME), client.DRIVER_NAME)
print "DRIVER_VER: string(%d) \"%s\"" % (len(client.DRIVER_VER), client.DRIVER_VER)
print "DATA_SOURCE_NAME: string(%d) \"%s\"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME)
print "DRIVER_ODBC_VER: string(%d) \"%s\"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER)
print "ODBC_VER: string(%d) \"%s\"" % (len(client.ODBC_VER), client.ODBC_VER)
print "ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE)
print "APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE
print "CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE
ibm_db.close(conn)
else:
print "Error."
def db_operation(sql):
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
rows = []
# fetch the result
header = ibm_db.fetch_assoc(stmt)
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return header, rows
def fd_main(sys_code, tab_code, etl_date, date_offset, alg, sample_size, start_date_str):
etl_dates = date_trans(etl_date, date_offset)
conf = Config()
output_conn = None
if conf.output_db == "db2":
output_conn = get_db2_connect(conf.output_db_url)
else:
logging.error("输出配置数据库未适配 :{}".format(conf.output_db))
exit(-1)
# 检查输出，已分析的表跳过分析步骤
# 函数依赖分析
fd_sche = get_analysis_schedule_single(output_conn, conf.output_schema, sys_code, tab_code)['FD_SCHE']
ibm_db.close(output_conn)
if fd_sche == "1":
logging.warning("该表已完成函数依赖分析：{}".format(tab_code))
exit(-1)
else:
analyse_table_fds(conf, sys_code, tab_code, alg, etl_dates, start_date_str, sample_size, status=fd_sche)
def updatekeyword(nameToUpdateKeyword=None, keyword=None):
# connect to DB2
db2conn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")
if db2conn:
# we have a Db2 connection, query the database
sql='Update PEOPLE set "Keywords"=? where "Name"=?'
# Note that for security reasons we are preparing the statement first,
# then bind the form input as value to the statement to replace the
# parameter marker.
stmt = ibm_db.prepare(db2conn,sql)
ibm_db.bind_param(stmt, 1, keyword)
ibm_db.bind_param(stmt, 2, nameToUpdateKeyword)
ibm_db.execute(stmt)
rows=[]
# close database connection
ibm_db.close(db2conn)
return render_template('updateppl.html')
def largest_n(number=5):
number = request.args.get('number', '')
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "","")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE ORDER BY MAG DESC FETCH FIRST ? ROWS ONLY;"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, number)
ibm_db.execute(stmt)
rows=[]
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('large_n.html', ci=rows)
def updateVerififcationStatus(userType, email, stringBool):
"""
:type stringBool: String ("TRUE") or String("FALSE")
"""
db2conn = createConnection()
if db2conn:
sql = ("UPDATE " + str(userType) + " SET verified = " +
str(stringBool) + " WHERE email = '" + str(email).lower() +
"';")
#print(sql)
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
# close database connection
ibm_db.close(db2conn)
def run_test_102(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print(ibm_db.conn_errormsg())
server = ibm_db.server_info(conn)
if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2")
and (server.DBMS_NAME[0:3] != "IDS")
and (server.DBMS_NAME[0:3] != "DSN")):
result = ibm_db.exec_immediate(conn, "VALUES(1)")
#throw :unsupported unless result
if (not result):
raise Exception('Unsupported')
print(ibm_db.num_fields(result))
else:
print('1')
ibm_db.close(conn)
def location_in_range():
magX = request.args.get('magX', 5, type=float)
magY = request.args.get('magY', 6, type=float)
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE WHERE MAGTYPE=\'ml\'"
stmt = ibm_db.exec_immediate(db2conn, sql)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
mag = float(result['MAG'])
if mag >= magX and mag <= magY:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('location_in_range.html', rows=rows)
def generalSetting(p_vehicleNumber):
if (p_vehicleNumber != None):
if (vehicleDetails.has_key(p_vehicleNumber)):
if (vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS] == 0):
pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\
"vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]})
l_connection = dB_init()
if (l_connection == None):
print("Database Connection Failed on Database Query")
return
l_database_query = "SELECT * FROM " + DB_SCHEMA + "." + DATABASE_TABLE_NAME_1 + " WHERE VEHICLE_NUMBER = '" + str(
p_vehicleNumber) + "'"
try:
l_db_statement = ibm_db.exec_immediate(
l_connection, l_database_query)
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("rfid Register exec/fetch_ERROR : " + str(e))
while l_temp_dict:
if (l_temp_dict["VEHICLE_NUMBER"] == p_vehicleNumber):
vehicleDetails[p_vehicleNumber][
DETAILS_BALANCE] = l_temp_dict["WALLET_BAL"]
try:
l_temp_dict = ibm_db.fetch_assoc(l_db_statement)
except Exception as e:
logging.error("rfid Register fetch_ERROR : " + str(e))
ibm_db.free_stmt(l_db_statement)
ibm_db.close(l_connection)
pubnub.publish(channel=p_vehicleNumber, message={"vehicleNumber":p_vehicleNumber,"availableBal":vehicleDetails[p_vehicleNumber][DETAILS_BALANCE],"ownerName":vehicleDetails[p_vehicleNumber][DETAILS_OWNER_NAME],\
"vehicleType":vehicleDetails[p_vehicleNumber][DETAILS_VEHICLE_TYPE]})
else:
appSetting(
p_vehicleNumber,
vehicleDetails[p_vehicleNumber][DETAILS_BLOCK_STATUS])
else:
pubnub.publish(
channel=p_vehicleNumber,
message={
"warning":
"Vehicle Not Registered with the Automated System"
})
else:
pass
def login():
try:
if (session["login"]):
pass
except:
session["login"] = False
session["user_id"] = -1
session["username"] = ""
session["cat"] = ""
if (request.method == "POST"):
try:
name = int(request.form["id"])
except:
flash("User ID is numeric !", category="error")
return redirect(url_for('login'))
password = request.form["password"]
conn = ibm_db.connect(dsn, "", "")
sql = "select * from USERS where user_id = " + str(
name) + " and password = '******';"
res = ibm_db.exec_immediate(conn, sql)
k = ibm_db.fetch_assoc(res)
ibm_db.close(conn)
if (k == False):
flash("Invalid Credentials !!!", category="error")
return render_template("login.html")
else:
session["user_id"] = k["USER_ID"]
session["username"] = k["USERNAME"]
session["cat"] = k["CATEGORY"]
session["login"] = True
cat = k["CATEGORY"]
if (cat == "p"):
session["cat"] = "p"
return redirect(url_for('p_home'))
elif (cat == "d"):
session["cat"] = "d"
return redirect(url_for('d_home'))
elif (cat == "h"):
session["cat"] = "h"
return redirect(url_for('h_home'))
return render_template("login.html")
def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info(conn)
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
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)
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_300(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(
server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(
server.DBMS_VER), server.DBMS_VER)
print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE
print "DB_NAME: string(%d) \"%s\"" % (len(
server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(
server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(
server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(
server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(
server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(
server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
ibm_db.close(conn)
else:
print "Error."
def run_test_007(self):
options1 = {
ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN
}
options2 = {
ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY
}
if sys.platform == 'zos':
conn = ibm_db.pconnect(config.database, '', '')
else:
conn = ibm_db.pconnect(config.database, config.user,
config.password)
if conn:
try:
serverinfo = ibm_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0",
options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
print("")
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0",
options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
finally:
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_030(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)
ibm_db.close(conn)
else:
print "Connection failed."
