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_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_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_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_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_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 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_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 collect(self, conn):
sql = self.sql
try:
stmt = ibm_db.prepare(conn, sql)
if ibm_db.execute(stmt):
result = ibm_db.fetch_assoc(stmt)
while result != False:
if self.add_inst_name:
if ('INST' not in result.keys()):
result['INST'] = self.server_info['inst_name'].upper()
else:
result['INST'] = result['INST'].upper()
if self.add_db_name:
if ('DB' not in result.keys()):
result['DB'] = self.server_info['db_name'].upper()
else:
result['DB'] = result['DB'].upper()
self.publish(result)
result = ibm_db.fetch_assoc(stmt)
except Exception:
self.log.error(traceback.format_exc())
return True
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 summation(q, txtBefore, txtAfter):
sum_str = q[2]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if sum_stmt == False:
raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False:
raise Usage("Failed to execute the sum query")
sum = ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
f.write(txtBefore + str(sum) + txtAfter)
f.close()
except IOError, e:
raise Usage("Failed to manipulate output.txt.\n")
def summation(q):
sum_str = q[2]
# Connect to DB
start=time.time()
#sys.stderr.write("Start sum %s\n"%(start))
for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
#sys.stderr.write("Sum %d"%n)
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if (sum_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
sum= ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH, 'a')
f.write(str(sum)+'\n')
# f.close
except IOError, e:
raise Usage("Failed to manipulate sum.sql.\n")
finally:
def run_test_144(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BINARY)
rc = ibm_db.execute(stmt)
num = ibm_db.num_rows(stmt)
print(num)
else:
print("Connection failed.")
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
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.rollback(conn)
else:
print("Connection failed.")
def run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def write(q,data):
# initialize vars
write_str = q
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
if TL:
ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
for t in data:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
ibm_db.commit(conn)
#
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def query_db(self, query, args=()):
"""Submits database query.
Examples:
for user in query_db('select * from users'):
print user['username'], 'has the id', user['user_id']
for user in query_db('select * from users where username = ?', [the_username]):
print user['username'], 'has the id', user['user_id']
Returns list
list = list of rows, where each row is represented using tuple
"""
rows = []
if self._conn:
log.debug("Running query\n" + query)
log.debug("Query params: " + pprint.pformat(args))
stmt = ibm_db.prepare(self._conn, query)
for i, param in enumerate(args):
ibm_db.bind_param(stmt, i, param)
ibm_db.execute(stmt)
if re.search('create|insert|update|delete', query, re.I):
return rows
row = ibm_db.fetch_tuple(stmt)
while (row):
rows.append(row)
row = ibm_db.fetch_tuple(stmt)
return rows
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 experiment(query_str,g):
# generate nb of parameters for query
matchList = re.findall('\?', query_str)
nbParams = len(matchList)
if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)")
# Connect to DB
conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','')
if conn is None: raise Usage(ibm_db.conn_errormsg())
# Prepare statement
query_stmt = ibm_db.prepare(conn, query_str)
if (query_stmt == False): raise Usage("Failed to prepare query")
# Execute statement
for i in range(NBQUERIES):
if (nbParams == 0):
if ibm_db.execute(query_stmt) == False:
raise Usage("Failed to execute the query")
else:
t = g.getWrite(i)
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(query_stmt, tuple(u)) == False:
raise Usage("Failed to execute the query")
nbtuples = 0
while (ibm_db.fetch_tuple(query_stmt) != False):
nbtuples += 1
print "Query"+str(i)+": "+str(nbtuples)+" fetched."
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
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_133(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = ibm_db.prepare(conn, sql)
res = ibm_db.execute(stmt,(128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3))
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
for i in row:
print i
ibm_db.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
try:
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
ibm_db.rollback(conn)
print "Done"
def run_test_012(self):
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'):
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0")
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 insert_record(self, tablename, record):
conn = self.get_conn()
insertsql = "insert into %s(...) values(?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,?,\
?, ?, ?, ?)" % tablename
stmt = ibm_db.prepare(conn, insertsql)
ibm_db.execute(stmt, record)
ibm_db.commit(conn)
def search_c():
rows = []
l = []
latitude = []
longitude = []
if conn:
lat1 = float(request.form['lat1'])
lon1 = float(request.form['lon1'])
print(lat1, lon1)
lat2 = float(request.form['lat2'])
lon2 = float(request.form['lon2'])
grid1 = int(request.form['size1'])
grid2 = int(request.form['size2'])
for longi in np.arange(lon1, lon2, grid2):
temp_longi = longi + grid2
if temp_longi > lon2:
temp_longi = lon2
for lat in np.arange(lat1, lat2, grid1):
temp_lat = lat + grid1
if temp_lat > lat2:
temp_lat = lat2
query = 'select count(*) from KDJ50223.EARTHQUAKE where "longitude" between ' + str(
longi) + ' and ' + str(
temp_longi) + ' and "latitude" between ' + str(
lat) + ' and ' + str(temp_lat) + ''
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
#print(result)
l.append(int(result['1']))
latitude.append(temp_lat)
#print(l)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
#print ("temp_lat:",temp_lat)
#print("temp_lon:",temp_longi)
longitude.append(temp_longi)
#print("list of temp lat:", latitude)
print("list of temp longitude:", longitude)
m = max(l)
print('Max:', m)
im = l.index(m)
lm = latitude[l.index(m)]
print(lm)
lonm = longitude[l.index(m)]
print(lonm)
count = (len(rows))
return render_template('table2.html',
count=count,
data=rows,
max=m,
imax=im,
latm=lm,
lonm=lonm)
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 delete_user(user_id):
sql = "SELECT * FROM USERS WHERE USER_ID='" + user_id + "' AND UNSUBSCRIBED_TIME IS NULL;"
stmt = ibm_db.exec_immediate(connection, sql)
row = ibm_db.fetch_tuple(stmt)
print(row)
if len(row) > 0:
sql = "UPDATE USERS SET UNSUBSCRIBED_TIME='" + str(
datetime.now()) + "' WHERE ID='" + str(row[0]) + "';"
stmt = ibm_db.prepare(connection, sql)
ibm_db.execute(stmt)
def store_model(self, model_name, model, user_name=None, serialize=True):
if serialize:
try:
model = pickle.dumps(model)
except Exception as ex:
raise Exception(
'Serialization of model %s that is supposed to be stored in ModelStore failed.' % model_name) from ex
if not self.is_postgre_sql:
sql_statement = "MERGE INTO %s.%s AS TARGET " \
"USING (VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)) " \
"AS SOURCE (ENTITY_TYPE_ID, MODEL_NAME, MODEL, UPDATED_TS, LAST_UPDATED_BY) " \
"ON TARGET.ENTITY_TYPE_ID = SOURCE.ENTITY_TYPE_ID " \
"AND TARGET.MODEL_NAME = SOURCE.MODEL_NAME " \
"WHEN MATCHED THEN " \
"UPDATE SET TARGET.MODEL = SOURCE.MODEL, " \
"TARGET.UPDATED_TS = SOURCE.UPDATED_TS " \
"WHEN NOT MATCHED THEN " \
"INSERT (ENTITY_TYPE_ID, MODEL_NAME, MODEL, UPDATED_TS, LAST_UPDATED_BY) " \
"VALUES (SOURCE.ENTITY_TYPE_ID, SOURCE.MODEL_NAME, SOURCE.MODEL, " \
"SOURCE.UPDATED_TS, SOURCE.LAST_UPDATED_BY)" % (
self.quoted_schema, self.quoted_store_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.bind_param(stmt, 3, model)
ibm_db.bind_param(stmt, 4, user_name)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception('Storing model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
else:
statement1 = "INSERT INTO %s.%s (entity_type_id, model_name, model, updated_ts, last_updated_by) " % (
self.quoted_schema, self.quoted_store_tablename)
statement3 = "ON CONFLICT ON CONSTRAINT %s DO update set entity_type_id = EXCLUDED.entity_type_id, " \
"model_name = EXCLUDED.model_name, model = EXCLUDED.model, " \
"updated_ts = EXCLUDED.updated_ts, last_updated_by = EXCLUDED.last_updated_by" % dbhelper.quotingTableName(
('uc_%s' % self.store_tablename), self.is_postgre_sql)
sql_statement = statement1 + " values (%s, %s, %s, current_timestamp, %s) " + statement3
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement,
(self.entity_type_id, model_name, psycopg2.Binary(model), user_name))
except Exception as ex:
raise Exception('Storing model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
logger.info('Model %s of size %d bytes has been stored in table %s.%s.' % (
model_name, len(model) if model is not None else 0, self.quoted_schema, self.quoted_store_tablename))
def night2():
rows = []
rows1 = []
if request.method == 'POST':
#for a day
#query = 'SELECT * FROM Earthquake WHERE "mag" BETWEEN ' + mag +' AND ' + request.form['mag1'] + ' AND "time" LIKE \'%' + request.form['date'] + '%\''
query = 'SELECT "mag","latitude","longitude","time" FROM KDJ50223.EARTHQUAKE WHERE "mag" > ' + request.form[
'mag']
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
timefield = []
while result != False:
t = []
temp = result["time"]
latitude = float(result["latitude"])
longitude = float(result["longitude"])
#print(latitude, longitude)
timeb = temp[11:19]
actualtime = datetime.strptime(timeb, '%H:%M:%S')
#print(actualtime)
tf = TimezoneFinder()
try:
newtimezone = pytz.timezone(
tf.timezone_at(lng=longitude, lat=latitude))
except pytz.UnknownTimeZoneError:
newtimezone = pytz.timezone("Greenwich")
oldtimezone = pytz.timezone("Greenwich")
mytimestamp = oldtimezone.localize(actualtime).astimezone(
newtimezone)
hours = int(mytimestamp.strftime('%H'))
if (hours >= 21 and hours <= 23) or (hours >= 0 and hours <= 6):
rows.append(result.copy())
else:
rows1.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
print(result)
#mytimestamp = mytimestamp.replace(tzinfo=None)
count = (len(rows))
count1 = (len(rows1))
return render_template('table1.html',
count=len(rows),
count1=len(rows1),
mag=request.form['mag'])
return render_template('table1.html', count=len(rows), count1=len(rows1))
def swap(q):
swap1_str = q[0]
swap2_str = q[1]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare Statements
swap1_stmt = ibm_db.prepare(conn, swap1_str)
if swap1_stmt == False:
raise Usage("Failed to prepare swap1 query")
swap2_stmt = ibm_db.prepare(conn, swap2_str)
if swap2_stmt == False:
raise Usage("Failed to prepare swap2 update")
# Execute Statements
nbrep = int(round(NBSWAPS / NBSWAPTHREADS))
for i in range(nbrep):
x = random.randint(RANGE_LOW, RANGE_UP / 2)
y = random.randint(x, RANGE_UP)
if ibm_db.execute(swap1_stmt, (x,)) == False:
raise Usage("Failed to execute the swap1 query (x)")
valX = ibm_db.fetch_tuple(swap1_stmt)
if valX == False:
raise Usage("Failed to iterate over the swap1 result set (x)")
if ibm_db.execute(swap1_stmt, (y,)) == False:
raise Usage("Failed to execute the swap1 query (y)")
valY = ibm_db.fetch_tuple(swap1_stmt)
if valY == False:
raise Usage("Failed to iterate over the swap1 result set (y)")
time.sleep(0.1)
if ibm_db.execute(swap2_stmt, (valY[0], x)) == False:
raise Usage("Failed to execute the swap2 query (x, valY)")
if ibm_db.execute(swap2_stmt, (valX[0], y)) == False:
raise Usage("Failed to execute the swap1 query (y, valX)")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False:
raise Usage("Failed to close db connection.\n")
def adddata(name=None):
db2cred = {
"hostname":
"dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
"password":
"******",
"https_url":
"https://dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
"port":
50000,
"ssldsn":
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=npv29724;PWD=8ljtzf^4m4p2mxj9;Security=SSL;",
"host":
"dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
"jdbcurl":
"jdbc:db2://dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50000/BLUDB",
"uri":
"db2://npv29724:8ljtzf%[email protected]:50000/BLUDB",
"db":
"BLUDB",
"dsn":
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=npv29724;PWD=8ljtzf^4m4p2mxj9;",
"username":
"******",
"ssljdbcurl":
"jdbc:db2://dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net:50001/BLUDB:sslConnection=true;"
}
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
sql = "select max(id) as id from sample"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
sql = "insert into sample(id, name) values(?, ?)"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, result['ID'] + 1)
ibm_db.bind_param(stmt, 2, name)
ibm_db.execute(stmt)
ibm_db.close(db2conn)
def update_user_id(user_id, email):
sql = "SELECT * FROM WPARS WHERE EMAIL='" + str(email) + "';"
stmt = ibm_db.exec_immediate(connection, sql)
row = ibm_db.fetch_tuple(stmt)
print(row)
if len(row) > 0:
sql = "UPDATE WPARS SET USER_ID='" + str(
user_id) + "' WHERE ID=" + str(row[0]) + ";"
stmt = ibm_db.prepare(connection, sql)
returnCode = ibm_db.execute(stmt)
print(returnCode)
def update():
room = request.form["room"]
keywords = request.form["keywords"]
sql = "UPDATE people SET name = ? WHERE points = ?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, keywords)
ibm_db.bind_param(stmt, 2, room)
result = ibm_db.execute(stmt)
return redirect(url_for("just_hello"))
def query_update(q):
try:
db2conn = ibm_db.connect(conn_str, "", "")
if db2conn:
statement = ibm_db.prepare(db2conn, q)
result = ibm_db.execute(statement)
ibm_db.close(db2conn)
return result
except:
print("Connection to Database failed")
exit(1)
def insertDB(sql_stmt):
connect()
stmt = idb.prepare(con, sql_stmt)
try:
idb.execute(stmt)
except:
print(idb.stmt_errormsg())
return
def getData(self):
"""
抽取数据
flds: 要抽取的字段列表
curpos: 从什么位置开始抽取数据；默认为-1，从0开始读取
fetch_tuple()：返回元组，以列的位置索引
fetch_tuple()：返回字典，以列名索引
fetch_both()：返回字典，以列名和列的位置做索引
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.python.doc%2Fdoc%2Ft0054388.html
注：返回的是元组列表
"""
lst = []
sql = self._createSql(flds, curpos)
#print sql
conn = self._getConn()
if conn == None:
return lst
try:
serverinfo = ibm_db.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, sql, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
tup = ibm_db.fetch_tuple(stmt)
while (tup):
#print tup[0]
lst.append(tup)
tup = ibm_db.fetch_tuple(stmt)
except:
raise
finally:
ibm_db.close(conn)
return lst
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 _read_tables_(self):
# tables
stmt = ibm_db.prepare(self._conn_, DBQueries.read_tables)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
tmp = DBTable(*tpl)
self._tables_[(tpl[0], tpl[1])] = tmp
tpl = ibm_db.fetch_tuple(stmt)
self._read_columns_()
# views
stmt = ibm_db.prepare(self._conn_, DBQueries.read_views)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
tabschema, tabname, query_opt, text, vtype, remarks = tpl
text = os.linesep.join([s for s in text.splitlines() if s])
tmp = DBView(tabschema, tabname, query_opt, text, vtype, remarks)
self._tables_[(tpl[0], tpl[1])] = tmp
tpl = ibm_db.fetch_tuple(stmt)
def update_range_name():
name = str(request.form["first"])
keyword = str(request.form["second"])
if name == "" or keyword == "":
obj = 0
return render_template('Range.html', result=obj)
print("ger")
print(name, keyword)
sql = " select * from names where GRADE < ? and GRADE >?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, name)
ibm_db.bind_param(stmt, 2, keyword)
result = ibm_db.execute(stmt)
ret = []
result_dict = ibm_db.fetch_assoc(stmt)
while result_dict is not False:
# print(json.dumps(result_dict))
ret.append(result_dict)
result_dict = ibm_db.fetch_assoc(stmt)
data = requests.get(base_url)
parse_dict = xmltodict.parse(data.text)
image_data = parse_dict["ListBucketResult"]["Contents"]
img_lt = []
for data in image_data:
img_lt.append(data["Key"])
if ret is not False:
for key in ret:
if key["PICTURE"] not in img_lt:
key["PICTURE"] = None
obj = {}
obj['list'] = ret
if name is "" or keyword is "":
obj = 0
# print(obj)
# if result_dict is False:
# result_dict = {"name":str(name), "RESULT":0}
# print(result_dict)
# else:
# result_dict["RESULT"] = 1
#
# if name is "" or keyword is "":
# result_dict = {"name":str(name), "RESULT":0}
return render_template('Range.html', result=obj)
def _read_index_columns(self):
stmt = ibm_db.prepare(self._conn_, DBQueries.read_index_columns)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
indschema, indname, colname, colorder = tpl
i = self._indexes_[(
indschema,
indname,
)]
i.add_column(colname, colorder)
tpl = ibm_db.fetch_tuple(stmt)
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 run_test_setgetOption(self):
if sys.platform == 'zos':
options = {}
else:
options = { ibm_db.SQL_ATTR_INFO_PROGRAMNAME : 'TestProgram'}
conn = ibm_db.connect(config.database, config.user, config.password, options)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
if sys.platform != 'zos':
value=ibm_db.get_option(conn, ibm_db.SQL_ATTR_INFO_PROGRAMNAME, 1)
print("Connection options:\nSQL_ATTR_INFO_PROGRAMNAME = ", end="")
print(value)
else:
print("Connection options:\n", end="")
returncode=ibm_db.set_option(conn, {ibm_db.SQL_ATTR_AUTOCOMMIT:0},1)
value=ibm_db.get_option(conn, ibm_db.SQL_ATTR_AUTOCOMMIT, 1)
print("SQL_ATTR_AUTOCOMMIT = ", end="")
print(str(value)+"\n")
drop = "DROP TABLE TEMP_TEST"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table temp_test
create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO temp_test values (1, 'cat')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1" )
if sys.platform != 'zos':
returnCode = ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_QUERY_TIMEOUT : 20}, 0)
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_QUERY_TIMEOUT, 0)
print("Statement options:\nSQL_ATTR_QUERY_TIMEOUT = ", end="")
print(str(value)+"\n")
ibm_db.execute(stmt)
if result:
ibm_db.free_result(stmt)
else:
print(ibm_db.stmt_errormsg())
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def delete_user(user):
if validate_credentials(user) == False:
return False
if is_user(user) == False:
return False
delete_query = "DELETE FROM USERS WHERE USERNAME = ?"
delete_stmt = ibm_db.prepare(conn, delete_query)
ibm_db.bind_param(delete_stmt, 1, user)
ibm_db.execute(delete_stmt)
return True
def execute_sql_stmt(self,
sql): # results: stmt; None: error; Exception: error
self.isautapp()
try:
global db
stmt = ibm_db.prepare(db, sql)
res = ibm_db.execute(stmt)
if res: return stmt
else: return None
except Exception as e:
self.funlog().logger.error("database error")
raise (e, None, sys.exc_info()[2])
def retrieve_model(self, model_name, deserialize=True):
if not self.is_postgre_sql:
sql_statement = "SELECT MODEL FROM %s.%s WHERE ENTITY_TYPE_ID = ? AND MODEL_NAME = ?" % (
self.quoted_schema, self.quoted_store_tablename)
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.execute(stmt)
row = ibm_db.fetch_tuple(stmt)
if row is False:
model = None
else:
model = row[0]
except Exception as ex:
raise Exception(
'Retrieval of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
finally:
ibm_db.free_result(stmt)
else:
sql_statement = 'SELECT model FROM %s.%s' % (self.quoted_schema, self.quoted_store_tablename)
sql_statement += ' WHERE entity_type_id = %s AND model_name = %s'
try:
row = dbhelper.execute_postgre_sql_select_query(self.db_connection, sql_statement,
(self.entity_type_id, model_name), fetch_one_only=True)
if row is None:
model = None
else:
model = bytes(row[0])
except Exception as ex:
raise Exception(
'Retrieval of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
if model is not None:
logger.info('Model %s of size %d bytes has been retrieved from table %s.%s' % (
model_name, len(model) if model is not None else 0, self.quoted_schema, self.quoted_store_tablename))
else:
logger.info('Model %s does not exist in table %s.%s' % (
model_name, self.quoted_schema, self.quoted_store_tablename))
if model is not None and deserialize:
try:
model = pickle.loads(model)
except Exception as ex:
raise Exception(
'Deserialization of model %s that has been retrieved from ModelStore failed.' % model_name) from ex
return model
def run_test_err_executemany(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
server = serverinfo.DBMS_NAME[0:3]
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP TABLE CLI0126E')
except:
pass
create_ddl = "create table CLI0126E \
(\
offer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\
position_id int NOT NULL,\
title VARCHAR(5000) NOT NULL,\
type VARCHAR(5000),\
quantity decimal(10, 3) NOT NULL,\
price_btc decimal(10, 8) NOT NULL,\
city VARCHAR(5000),\
country VARCHAR(500)\
)"
try:
ibm_db.exec_immediate(conn, create_ddl)
except:
pass
insert_statement = "INSERT INTO CLI0126E (position_id, title, type, quantity, price_btc, city, country)\
VALUES (?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, insert_statement)
# deliberately use wrong size decimal values to trigger CLI0111E (hidden by execute_many()).
parms1= (15628, 'correct value in row1 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus1')
parms2= (15629, 'incorrect value in row2 column4', '', 99999999.0, 0.0067762, 'Belarus', 'Belarus2')
parms3= (15630, 'correct value in row3 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus3')
parms=( parms1 , parms2, parms3)
try:
ibm_db.execute_many(stmt, parms)
print(str(ibm_db.num_rows(stmt)) +" - Rows inserted successfully")
except:
print("Failed to insert multiple-rows with ibm_db.execute_many()")
print(ibm_db.stmt_errormsg())
print("Number of rows inserted: "+ str(ibm_db.num_rows(stmt)) )
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_execute_many(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if( not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
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), bflag boolean)"
ibm_db.exec_immediate(conn, create)
#Populate the tabmany table with execute_many
insert = "INSERT INTO TABMANY (id, name, bflag) VALUES(?, ?, ?)"
params = ((10, 'Sanders', True), (20, 'Pernal', False), (30, 'Marenghi', True), (40, 'OBrien', False))
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)
# check 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, %s" % (cols[0], cols[1], cols[2]))
cols = ibm_db.fetch_tuple( stmt_select )
#populate the tabmany table
params = ((50, 'Hanes', False), (55, 'Mike', False, 'Extra'), (55.5, 'invalid row','not a bool'), (60, 'Quigley'), (70, None, None), [75, 'List', True] )
try:
ibm_db.execute_many(stmt_insert, params)
except Exception as 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)
else:
print(ibm_db.conn_errormsg())
def run_test_157b(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
if (server.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, sql, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, sql, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC})
result = ibm_db.execute(stmt)
i = 2
row = ibm_db.fetch_assoc(stmt, i)
while ( row ):
if (server.DBMS_NAME[0:3] == 'IDS'):
#printf("%-5d %-16s %-32s %10s\n", row['id'], row['name'], row['breed'], row['weight'])
print("%-5d %-16s %-32s %10s" % (row['id'], row['name'], row['breed'], row['weight']))
else:
#printf("%-5d %-16s %-32s %10s\n", row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
print("%-5d %-16s %-32s %10s" % (row['ID'], row['NAME'], row['BREED'], row['WEIGHT']))
i = i + 2
row = ibm_db.fetch_assoc(stmt, i)
def run_test_196(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")):
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE xml_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE xml_test (id INTEGER, data VARCHAR(50), xmlcol XML)")
rc = ibm_db.exec_immediate(conn, "INSERT INTO xml_test (id, data, xmlcol) values (1, 'xml test 1', '<address><street>12485 S Pine St.</street><city>Olathe</city><state>KS</state><zip>66061</zip></address>')")
sql = "SELECT * FROM xml_test"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result ID:", result[0])
print("Result DATA:", result[1])
print("Result XMLCOL:", result[2])
result = ibm_db.fetch_both(stmt)
sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") AS CLOB(32k)) FROM xml_test c WHERE id = 1"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result from XMLSerialize and XMLQuery:", result[0])
result = ibm_db.fetch_both(stmt)
sql = "select xmlquery('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") from xml_test c where id = 1"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result from only XMLQuery:", result[0])
result = ibm_db.fetch_both(stmt)
else:
print('Native XML datatype is not supported.')
def removeLocation(locationName):
db2conn = createConnection()
if db2conn:
sql = ("DELETE FROM location"
" WHERE location_name = '" + locationName + "';")
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
# close database connection
ibm_db.close(db2conn)
def run_test_013(self):
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'):
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0")
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_039(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.prepare(conn, "SELECT * FROM animals", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.prepare(conn, "SELECT * FROM animals")
ibm_db.execute(result)
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM animals", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM animals")
ibm_db.execute(result2)
while (ibm_db.fetch_row(result2)):
print("%s : %s : %s : %s" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3)))
row = ibm_db.fetch_row(result)
def insertRoute(routeName):
db2conn = createConnection()
if db2conn:
sql = ("INSERT INTO route (ROUTE_NAME) VALUES('" + (routeName) + "');")
#print(sql)
# Prepare the statement
stmt = ibm_db.prepare(db2conn, sql)
# Execute the sql
ibm_db.execute(stmt)
# close database connection
ibm_db.close(db2conn)
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_booleanInsertSelect(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 BOOLEAN, description varchar(50))"
)
except Exception as e:
print("Error : {}\n".format(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
select_sql = 'SELECT * FROM bool_test'
stmt = ibm_db.exec_immediate(conn, select_sql)
result = ibm_db.fetch_tuple(stmt)
while result:
for col in result:
print(col)
result = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
def run_test_130(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
if ibm_db.execute(stmt):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
else:
print "Connection failed."
def main(args):
global conn
customer_id = args["customer_id"]
text = args["payload"]
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
if globals().get("conn") is None:
conn = ibm_db.connect(ssldsn, "", "")
statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
ts_val = datetime.datetime.today()
result = ibm_db.execute(stmt,(customer_id, text, ts_val))
if not result:
return {"err": "error :" + statement}
return {"result": f"stored feed back {text} from customer {customer_id} at {ts_val}"}
def db_update(self, session_id, ani, loading):
"""db update"""
try:
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "UPDATE AFRIEND SET SESSION_ID = ? WHERE ANI = ?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, session_id)
ibm_db.bind_param(stmt, 2, ani)
ibm_db.execute(stmt)
return True
except Exception as e:
return False
def db_insert(self, session_id, ani, loading):
"""db insert"""
try:
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "INSERT INTO AFRIEND(ANI,SESSION_ID) VALUES(?,?)"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, ani)
ibm_db.bind_param(stmt, 2, session_id)
ibm_db.execute(stmt)
return True
except Exception as e:
return False
def db_select(self, ani, loading):
"""db select"""
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "SELECT SESSION_ID FROM AFRIEND WHERE ANI=?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, ani)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
if not result:
return False
ibm_db.close(db2conn)
return result.get('SESSION_ID')
