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 connDb(dbname, dbuser, dbpasswd, host='182.29.0.1', port=51001, autocomm=1):
conn = db2.connect("HOSTNAME=%s;PROTOCOL=TCPIP;\
PORT=%d;DATABASE=%s;UID=%s;PWD=%s;" \
%(host, port, dbname, dbuser, dbpasswd), '', '')
if autocomm == 0:
db2.autocommit(conn, db2.SQL_AUTOCOMMIT_OFF)
return conn
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_100(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, "SELECT * FROM animals ORDER BY breed")
fields1 = ibm_db.num_fields(stmt)
print("int(%d)" % fields1)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ibm_db.num_fields(stmt)
print("int(%d)" % fields2)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ibm_db.num_fields(stmt)
print("int(%d)" % fields3)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ibm_db.num_fields(stmt)
print("int(%d)" % fields4)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ibm_db.num_fields(stmt)
print("int(%d)" % fields5)
ibm_db.rollback(conn)
else:
print("Connection failed.")
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_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_6561(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(
conn,
"INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
)
statement = "SELECT count(id) FROM animals"
result = ibm_db.exec_immediate(conn, statement)
if ((not result) and ibm_db.stmt_error()):
print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))
row = ibm_db.fetch_tuple(result)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def 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 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_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_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_100(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, "SELECT * FROM animals ORDER BY breed")
fields1 = ibm_db.num_fields(stmt)
print("int(%d)" % fields1)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ibm_db.num_fields(stmt)
print("int(%d)" % fields2)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ibm_db.num_fields(stmt)
print("int(%d)" % fields3)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ibm_db.num_fields(stmt)
print("int(%d)" % fields4)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ibm_db.num_fields(stmt)
print("int(%d)" % fields5)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def get_conn(self):
conn = ibm_db.connect("HOSTNAME=%s;PROTOCOL=TCPIP;PORT=%d;DATABASE=%s;UID=%s;PWD=%s;"
% (self.host, self.port, self.dbname, self.user, self.password), '', '')
# turn off autocommit: SQL_AUTOCOMMIT_OFF
if self.manual_commit:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
return conn
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 run_test_040(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Drop the test table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "select * from animals")
onerow = ibm_db.fetch_tuple(stmt)
for element in onerow:
print element
ibm_db.rollback(conn)
def run_test_147(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.prepare(
conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = "\"999\""
breed = None
name = 'PythonDS'
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
error = ibm_db.execute(stmt)
print("Should not make it this far")
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
else:
print("Connection failed.")
def run_test_018(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
if conn:
stmt = ibm_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
ibm_db.free_result(stmt)
else:
print ibm_db.stmt_errormsg()
ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ibm_db.execute(stmt)
if result:
rows = ibm_db.num_rows(stmt)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
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 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 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_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 setAutoComOff(connection, outFile):
ibm_db.autocommit(connection, ibm_db.SQL_AUTOCOMMIT_OFF)
# will return current state of autocommit, should be 0
ac = ibm_db.autocommit(connection)
if ac == 0:
outFile.write("Autocommit set to off \n")
if ac != 0:
outFile.write("Cannot set autocommit to off. FAIL \n")
def run_test_052(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
print(ac)
def run_test_052(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
print(ac)
def parseCommit(sql):
global _hdbc, _hdbi, _connected, _stmt, _stmtID, _stmtSQL
if (_connected == False): return # Nothing to do if we are not connected
cParms = sql.split()
if (len(cParms) == 0): return # Nothing to do but this shouldn't happen
keyword = cParms[0].upper() # Upper case the keyword
if (keyword == "COMMIT"): # Commit the work that was done
try:
result = ibm_db.commit(_hdbc) # Commit the connection
if (len(cParms) > 1):
keyword = cParms[1].upper()
if (keyword == "HOLD"):
return
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "ROLLBACK"): # Rollback the work that was done
try:
result = ibm_db.rollback(_hdbc) # Rollback the connection
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "AUTOCOMMIT"): # Is autocommit on or off
if (len(cParms) > 1):
op = cParms[1].upper() # Need ON or OFF value
else:
return
try:
if (op == "OFF"):
ibm_db.autocommit(_hdbc, False)
elif (op == "ON"):
ibm_db.autocommit(_hdbc, True)
return
except Exception as err:
db2_error()
return
return
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_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_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_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_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_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_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_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 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 run_test_111(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
if (server.DBMS_NAME[0:3] == 'IDS'):
num1 = ibm_db.field_num(stmt, "id")
num2 = ibm_db.field_num(stmt, "breed")
num3 = ibm_db.field_num(stmt, "number")
num4 = ibm_db.field_num(stmt, "NUMBER")
num5 = ibm_db.field_num(stmt, "bREED")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "WEIGHT")
else:
num1 = ibm_db.field_num(stmt, "ID")
num2 = ibm_db.field_num(stmt, "BREED")
num3 = ibm_db.field_num(stmt, "NUMBER")
num4 = ibm_db.field_num(stmt, "number")
num5 = ibm_db.field_num(stmt, "Breed")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "weight")
print("%s" % num1)
print("int(%d)" % num2)
print("int(%d)" % num3)
print("%s" % num4)
print("%s" % num5)
print("%s" % num6)
print("int(%d)" % num7)
print("%s" % num8)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_051(self):
options = { ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_OFF }
conn = ibm_db.connect(config.database, config.user, config.password, options)
ac = ibm_db.autocommit(conn)
print(ac)
def run_test_051(self):
options = { ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_OFF }
conn = ibm_db.connect(config.database, config.user, config.password, options)
ac = ibm_db.autocommit(conn)
print(ac)
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 write(q,index,mutex):
# initialize vars
write_str = q[0]
matchList = re.findall('\?', write_str)
nbParams = len(matchList)
write_data = 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)
ret2 = None
if TL:
ret2 = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
print "Lock: %s"%ret
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
# Perform insertions/updates
for i in range(NBWRITES/NBTHREADS):
mutex.acquire()
v = index.value
index.value -= 1
mutex.release()
t = write_data.getWrite(v)
if v >= 0 and t != None:
# 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'):
print "commit"
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
print "commit"
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def update1(q):
write_str = q[0]
# 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 statement
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(write_stmt) == False:
raise Usage("Failed to execute the sum query")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def run_test_111(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
if (server.DBMS_NAME[0:3] == 'IDS'):
num1 = ibm_db.field_num(stmt, "id")
num2 = ibm_db.field_num(stmt, "breed")
num3 = ibm_db.field_num(stmt, "number")
num4 = ibm_db.field_num(stmt, "NUMBER")
num5 = ibm_db.field_num(stmt, "bREED")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "WEIGHT")
else:
num1 = ibm_db.field_num(stmt, "ID")
num2 = ibm_db.field_num(stmt, "BREED")
num3 = ibm_db.field_num(stmt, "NUMBER")
num4 = ibm_db.field_num(stmt, "number")
num5 = ibm_db.field_num(stmt, "Breed")
num6 = ibm_db.field_num(stmt, 8)
num7 = ibm_db.field_num(stmt, 1)
num8 = ibm_db.field_num(stmt, "weight")
print "%s" % num1
print "int(%d)" % num2
print "int(%d)" % num3
print "%s" % num4
print "%s" % num5
print "%s" % num6
print "int(%d)" % num7
print "%s" % num8
ibm_db.rollback(conn)
else:
print "Connection failed."
def run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(
conn,
"SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed"
)
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print "string(%d) \"%s\"" % (len(name1), name1)
print "string(%d) \"%s\"" % (len(name2), name2)
print "%s" % name3
print "%s" % name4
print "string(%d) \"%s\"" % (len(name5), name5)
print "%s" % name6
print "%s" % name7
print "string(%d) \"%s\"" % (len(name8), name8)
ibm_db.rollback(conn)
else:
print "Connection failed."
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 run_test_121(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed")
name1 = ibm_db.field_name(stmt, 0)
name2 = ibm_db.field_name(stmt, 1)
name3 = ibm_db.field_name(stmt, 2)
name4 = ibm_db.field_name(stmt, 3)
if (server.DBMS_NAME[0:3] == 'IDS'):
name5 = ibm_db.field_name(stmt, "breed")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"nUMBER"')
name8 = ibm_db.field_name(stmt, "number")
else:
name5 = ibm_db.field_name(stmt, "BREED")
name6 = ibm_db.field_name(stmt, 7)
name7 = ibm_db.field_name(stmt, '"Number"')
name8 = ibm_db.field_name(stmt, "NUMBER")
print("string(%d) \"%s\"" % (len(name1), name1))
print("string(%d) \"%s\"" % (len(name2), name2))
print("%s" % name3)
print("%s" % name4)
print("string(%d) \"%s\"" % (len(name5), name5))
print("%s" % name6)
print("%s" % name7)
print("string(%d) \"%s\"" % (len(name8), name8))
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_145(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.prepare(
conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string
ibm_db.execute(stmt)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string.
# Use the dynamically bound parameters to ensure that the code paths
# for both ibm_db.bind_param and ibm_db.execute treat Python Nones and empty
# strings the right way.
ibm_db.execute(stmt, (1000, None, 'PythonDS'))
result = ibm_db.exec_immediate(
conn,
"SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ibm_db.fetch_tuple(result)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_049(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert)
if ibm_db.execute(stmt, (None, 'ghost', None, None)):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| puts child }
for child in row:
print child
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print "Connection failed."
def run_test_145(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.prepare(conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string
ibm_db.execute(stmt)
# After this statement, we expect that the BREED column will contain
# an SQL NULL value, while the NAME column contains an empty string.
# Use the dynamically bound parameters to ensure that the code paths
# for both ibm_db.bind_param and ibm_db.execute treat Python Nones and empty
# strings the right way.
ibm_db.execute(stmt, (1000, None, 'PythonDS'))
result = ibm_db.exec_immediate(conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ibm_db.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_6561(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)")
statement = "SELECT count(id) FROM animals"
result = ibm_db.exec_immediate(conn, statement)
if ( (not result) and ibm_db.stmt_error() ):
print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))
row = ibm_db.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_019(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)
if conn:
if ('zos' in sys.platform):
stmt = ibm_db.prepare(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
stmt = ibm_db.prepare(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ibm_db.SQL_ROWCOUNT_PREFETCH_ON
})
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.close(conn)
else:
print("no connection:", ibm_db.conn_errormsg())
def get_db_connection(db_type, user, password, host, port, db_name):
""" Connects to the database.
Parameters
----------
db_type : str
The database type
user : str
The database user
password : str
The database user password
host : str
The database host or ip address
port : str
The port to connect to
db_name : str
The database or service name
Returns
-------
conn
A database connection
"""
try:
if db_type == DBType.ORACLE.value:
import cx_Oracle
conn = cx_Oracle.connect(user,
password,
host + ":" + port + "/" + db_name,
encoding="UTF-8",
nencoding="UTF-8")
elif db_type == DBType.MYSQL.value:
import mysql.connector
conn = mysql.connector.connect(user=user,
password=password,
host=host,
port=int(port),
database=db_name)
elif db_type == DBType.POSTGRES.value:
import psycopg2
conn = psycopg2.connect("""user='******'
password='******'
host='{2}'
port='{3}'
dbname='{4}'""".format(
user, password, host, port, db_name))
elif db_type == DBType.DB2.value:
import ibm_db
import ibm_db_dbi
conn = ibm_db.connect(
"PROTOCOL=TCPIP;AUTHENTICATION=SERVER;"
"UID={0};PWD={1};HOSTNAME={2};PORT={3};DATABASE={4};".format(
user, password, host, port, db_name), "", "")
# Set autocommit explicitly off
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
return ibm_db_dbi.Connection(conn)
elif db_type == DBType.SQLSERVER.value:
import pymssql
conn = pymssql.connect(server=host,
user=user,
password=password,
database=db_name)
# 'pymssql.Connection' object attribute 'autocommit' is read-only
conn.autocommit(False)
return conn
else:
raise ValueError(
"Database type '{0}' is not supported.".format(db_type))
# Set autocommit explicitly off for all database types
conn.autocommit = False
return conn
except ModuleNotFoundError as err:
raise ConnectionError(
"Database driver module is not installed: {0}. Please install it first."
.format(str(err)))
def autocommit_on(self):
self.isautapp()
global db
return ibm_db.autocommit(db, ibm_db.SQL_AUTOCOMMIT_ON)
def run_test_133(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = ibm_db.prepare(conn, sql)
res = ibm_db.execute(stmt,(128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3))
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
for i in row:
print i
ibm_db.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
try:
stmt = ibm_db.prepare(conn, "SELECT breed, name FROM animals WHERE id = ?")
res = ibm_db.execute(stmt, (128,))
row = ibm_db.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % ibm_db.stmt_error(stmt)
print "Message: %s" % ibm_db.stmt_errormsg(stmt)
ibm_db.rollback(conn)
print "Done"
#__END__
#__LUW_EXPECTED__
#Starting test ...
#None
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
#Done
#__ZOS_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][DB2] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__SYSTEMI_EXPECTED__
#Starting test ...
#
#SQLSTATE: 22001
#Message: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 SQLCODE=-99999
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][AS] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#Done
#__IDS_EXPECTED__
#Starting test ...
#None
#SQLSTATE: 22001
#Message: [IBM][CLI Driver][IDS%s] Value exceeds string column length.
#True
#SQLSTATE: 02000
#Message: [IBM][CLI Driver][IDS%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000
#Done
dbConnection = ibm_db.connect(connString, '', '', connOption)
except Exception:
pass
# If A Db2 Database Connection Could Not Be Established, Display An Error Message And Exit
if dbConnection is None:
print("\nERROR: Unable to connect to the \'" + dbName + "\' database.")
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Determine Whether Autocommit Behavior Is OFF or ON (Should Match The connOption Setting)
try:
returnCode = ibm_db.autocommit(dbConnection)
except Exception:
pass
# If Autocommit Behavior Is OFF, Turn It ON
if returnCode is 0:
print("AUTOCOMMIT behavior is OFF; turning it ON ... ", end="")
try:
returnCode = ibm_db.autocommit(dbConnection, ibm_db.SQL_AUTOCOMMIT_ON)
except Exception:
pass
# If AUTOCOMMIT Behavior Could Not Be Turned ON, Display An Error Message And Continue
if returnCode is False:
print("\nERROR: Unable to turn AUTOCOMMIT behavior ON.")
