def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print "%s:%s" % (field, value)
print "---------"
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print "done"
else:
print ibm_db.conn_errormsg()
def 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_071(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
rc = ibm_db.close(conn)
if (rc == True):
print("ibm_db.close succeeded")
else:
print("ibm_db.close FAILED\n")
else:
print("%s" % ibm_db.conn_errormsg())
print(",sqlstate=%s" % ibm_db.conn_error())
print("%s" % ibm_db.conn_errormsg())
print("%s" % ibm_db.conn_errormsg())
print("%s" % ibm_db.conn_errormsg())
print("%s" % ibm_db.conn_errormsg())
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_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
def run_test_102(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print ibm_db.conn_errormsg()
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2")):
result = ibm_db.exec_immediate(conn, "VALUES(1)")
#throw :unsupported unless result
if (not result):
raise Exception('Unsupported')
print ibm_db.num_fields(result)
else:
print '1'
ibm_db.close(conn)
def run_test_091(self):
try:
conn = ibm_db.connect(config.database, "y", config.password)
print "??? No way."
except:
err = ibm_db.conn_errormsg()
print err
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_090(self):
try:
conn = ibm_db.connect("x", config.user, config.password)
print("??? No way.")
except:
err = ibm_db.conn_errormsg()
print(err)
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_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_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
print "Test non-existent table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ibm_db.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + ibm_db.conn_errormsg()
def run_test_191(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,config.user,"emp_photo");
else:
result = ibm_db.columns(conn,None,None,"EMP_PHOTO");
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ( (row['column_name'] != 'emp_rowid') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['table_schem'],
row['table_name'], row['column_name'], row['is_nullable']))
else :
if ( (row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['TABLE_SCHEM'],
row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE']))
i = i + 1
row = ibm_db.fetch_both(result)
print("done!")
else:
print("no connection: ", ibm_db.conn_errormsg())
def run_test_092(self):
try:
conn = ibm_db.connect(config.database, config.user, "z")
print "??? No way."
except:
err = ibm_db.conn_errormsg()
print err
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
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, 0)
result = ibm_db.tables(conn, None, 'T');
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print str
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_103(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from org, project order by project.projname")
cols = ibm_db.num_fields(result)
j = 1
row = ibm_db.fetch_tuple(result)
while row:
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if j > 10:
break
row = ibm_db.fetch_tuple(result)
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
def __enter__(self):
try:
self._conn = ibm_db.pconnect(self.app.config['DBNAME']
, self.app.config['DBUSER']
, self.app.config['DBPW'])
except:
log.error("Database connection failed.")
log.error(ibm_db.conn_errormsg())
raise
else:
log.debug("Connected to {dbname} user {dbuser} using ****".format(dbname = self.app.config['DBNAME']
, dbuser = self.app.config['DBUSER']))
return self
def run_test_023(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (conn != 0):
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.column_privileges(conn, None, config.user, 'animals')
else:
stmt = ibm_db.column_privileges(conn, None, None, 'ANIMALS')
row = ibm_db.fetch_tuple(stmt)
if row:
print row[0]
print row[1]
print row[2]
print row[3]
print row[4]
print row[5]
print row[6]
print row[7]
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
print "Connection failed\n\n"
def run_test_025(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (conn != 0):
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.primary_keys(conn, None, config.user, 'test_primary_keys')
else:
stmt = ibm_db.primary_keys(conn, None, None, 'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[4]
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
print "Connection failed\n"
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 run_test_190(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
if server.DBMS_NAME[0:3] == "IDS":
result = ibm_db.columns(conn, None, config.user, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_tuple(result)
while row:
str = row[1] + "/" + row[3]
print str
row = ibm_db.fetch_tuple(result)
print "done!"
else:
print "no connection:", ibm_db.conn_errormsg()
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 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_103(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(
conn, "select * from org, project order by project.projname")
cols = ibm_db.num_fields(result)
j = 1
row = ibm_db.fetch_tuple(result)
while (row):
print("%d) " % j)
for i in range(0, cols):
print("%s " % row[i])
j += 1
if (j > 10):
break
row = ibm_db.fetch_tuple(result)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
def write_to_db():
# connect to DB2
try:
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
except:
connerr = "Connection to database failed"
print(connerr + ": " + str(ibm_db.conn_errormsg()))
return {"error": connerr}
delete_stmt = ibm_db.exec_immediate(db2conn, "DELETE from for_app")
filesAll = get_bucket_contents()
for file in filesAll:
sql = "INSERT INTO for_app VALUES (?)"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, file[0])
ibm_db.execute(stmt)
ibm_db.close(db2conn)
return render_template("success.html")
def run_test_025(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if (conn != 0):
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.primary_keys(conn, None, config.user,
'test_primary_keys')
elif ('zos' in sys.platform):
stmt = ibm_db.primary_keys(conn, None, config.user,
'TEST_PRIMARY_KEYS')
else:
stmt = ibm_db.primary_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[4])
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
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:
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 run_test_016(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
print ("affected row:", rows)
else:
print (ibm_db.stmt_errormsg())
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
print ("affected row:", rows)
else:
print (ibm_db.stmt_errormsg())
ibm_db.close(conn)
else:
print ("no connection:", ibm_db.conn_errormsg())
def db2conn():
try:
if conType == 'LOCAL':
conn = ibm_db_dbi.connect('DATABASE=*LOCAL')
elif conType == 'REMOTE':
conn = pyodbc.connect(DRIVER='IBM i Access ODBC Driver',
SYSTEM='your IBMi IP',
UID='IBM I USERNAME',
PWD='IBM I PASSWORD')
else:
return None, None
c1 = conn.cursor()
return c1, conn
except:
if conType == 'LOCAL':
print("Connection Error:", ibm_db.conn_errormsg())
else:
print("Connection Error:", conn.conn_errormsg())
return None, None
def Submit(self):
response = natural_language_understanding.analyze(
text=r1,features=Features(sentiment=SentimentOptions())).get_result()
print(json.dumps(response, indent=2))
response = response["sentiment"]["document"]["label"]
print(response)
text1 = self.textEdit_3.toPlainText()
text2 = self.textEdit_4.toPlainText()
text3 = self.textEdit_5.toPlainText()
try:
conn = ibm_db.connect(dsn, "", "")
print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print ("Unable to connect: ", ibm_db.conn_errormsg() )
insertQuery = "insert into REVIEW values (?, ?, ?, ?, ?)"
params=(text3,text1,text2,r1,response)
stmt = ibm_db.prepare(conn, insertQuery)
ibm_db.execute(stmt,params)
ibm_db.close(conn)
def run_test_016(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
print ("affected row:", rows)
else:
print (ibm_db.stmt_errormsg())
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print ("col:", cols)
rows = ibm_db.num_rows(result)
print ("affected row:", rows)
else:
print (ibm_db.stmt_errormsg())
ibm_db.close(conn)
else:
print ("no connection:", ibm_db.conn_errormsg())
def run_test_023(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (conn != 0):
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.column_privileges(conn, None, config.user, 'animals')
else:
stmt = ibm_db.column_privileges(conn, None, None, 'ANIMALS')
row = ibm_db.fetch_tuple(stmt)
if row:
print(row[0])
print(row[1])
print(row[2])
print(row[3])
print(row[4])
print(row[5])
print(row[6])
print(row[7])
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n\n")
def run_test_015(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ibm_db.num_fields(result)
# NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
print "col:", cols
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print "col:", cols
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
#Deleting only the rows which meet some criteria
## DeleteQuery = "delete from "+TABLENAME+" WHERE id="+'EXAMPLE'
## DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)
# Or you may just want to delete everything:
DeleteQuery = "delete from " + TABLENAME
DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)
#Checking the result
selectQuery = "select * from " + TABLENAME
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
#Fetch the Dictionary, if the only row is enough for you
#ibm_db.fetch_both(selectStmt)
#Fetch all the rows and print the first([0]) and the second ("USERNAME") columns for those rows
while ibm_db.fetch_row(selectStmt) != False:
print(" ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME"))
except:
print("something went wrong, but I don't know what exactly ",
ibm_db.conn_errormsg())
ibm_db.close(conn)
print("Connection closed")
#populate the tabmany table
params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'),
(60, 'Quigley'), (70, None))
try:
ibm_db.execute_many(stmt_insert, params)
except Exception, inst:
#check the no. of inserted rows
row_count = ibm_db.num_rows(stmt_insert)
#check the exception raised by execute_many API
print inst
print row_count
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
#__END__
#__LUW_EXPECTED__
#4
#10, Sanders
#20, Pernal
#30, Marenghi
#40, OBrien
#Error 1: Value parameter tuple: 2 has less no of param
#Error 2: Value parameters array 3 is not homogeneous with privious parameters array
#3
#__ZOS_EXPECTED__
#4
#10, Sanders
def run_test_createDropDB(self):
database = "test001"
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
database,
config.hostname,
config.port,
config.user,
config.password,
)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.hostname,
config.port,
config.user,
config.password,
) # for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, "", "")
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, "", "")
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print "Errors occurred during drop database"
try:
# create databse
rc = ibm_db.createdb(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, "", "")
if conn:
print "database created sucessfully"
ibm_db.close(conn)
conn = False
else:
print "database is not created"
else:
print "Errors occurred during create database"
# drop databse
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, "", "")
except:
print "datbase droped sucessfully"
if conn:
print "Errors occurred during drop database"
ibm_db.close(conn)
conn = False
else:
print "Errors occurred during delete database"
# create database with codeset option
rc = ibm_db.createdb(conn_attach, database, "iso88591")
if rc:
conn = ibm_db.connect(conn_str, "", "")
server_info = ibm_db.server_info(conn)
if conn and (server_info.DB_CODEPAGE == 819):
print "database with codeset created sucessfully"
ibm_db.close(conn)
conn = False
else:
print "database is not created"
else:
print "Errors occurred during create database"
# drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, "", "")
except:
print "datbase droped sucessfully"
if conn:
print "Errors occurred during drop database"
ibm_db.close(conn)
conn = False
else:
print "Errors occurred during drop database"
except:
print ibm_db.conn_errormsg()
pass
ibm_db.close(conn_attach)
else:
print ibm_db.conn_errormsg()
def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(
conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print("Test first index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST",
True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(
conn,
"CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(
conn, "CREATE INDEX index2 ON index_test2 (data)")
print("Test second index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"index_test2", True)
else:
result = ibm_db.statistics(conn, None, None, "INDEX_TEST2",
True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
print("Test non-existent table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn, None, config.user,
"non_existent_table", True)
else:
result = ibm_db.statistics(conn, None, None,
"NON_EXISTENT_TABLE", True)
row = ibm_db.fetch_tuple(result)
if row:
print("Non-Empty")
else:
print("Empty")
else:
print('no connection: ' + ibm_db.conn_errormsg())
connString = "ATTACH=FALSE" # Attach To A Database; Not A Server
connString += ";DATABASE=" + dbName # Required To Connect To A Database
connString += ";PROTOCOL=TCPIP"
connString += ";UID=" + userID
connString += ";PWD=" + passWord
# Attempt To Establish A Connection To The Database Specified
try:
dbConnection = ibm_db.connect(connString, '', '')
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.")
errorMsg = ibm_db.conn_errormsg()
print("\n" + errorMsg + "\n")
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Add Additional Db2 Database-Related Processing Here ...
# Attempt To Close The Db2 Database Connection That Was Just Opened
if not dbConnection is None:
print("Disconnecting from the \'" + dbName + "\' database ... ", end="")
try:
returnCode = ibm_db.close(dbConnection)
except Exception:
def run_test_trusted_context_connect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
tc_all_options = {
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.database, config.hostname, config.port, config.auth_user,
config.auth_pass)
# Makeing normal connection and playing with it.
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_all_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "", tc_all_options)
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
get_tc_user = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if config.tc_user != get_tc_user:
print("But trusted user is not switched.")
ibm_db.close(tc_conn)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Making trusted connection and switching to fake user.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
ibm_db.set_option(
tc_conn, {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser",
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD:
"******"
}, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_options_reversed = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user
}
if tc_conn:
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options_reversed, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_user_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user
}
tc_pass_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
try:
ibm_db.set_option(tc_conn, tc_pass_options, 1)
except:
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing only user while switching when both user and password are required.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
ibm_db.set_option(tc_conn, tc_user_options, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Dropping the trusted context, in case it exists
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
# Dropping Role.
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
# Creating Role.
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
# Granting permissions to role.
try:
sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01"
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
# Creating trusted context
try:
sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION"
result = ibm_db.exec_immediate(conn,
sql_create_trusted_context_01)
except:
pass
# Closing connection
ibm_db.close(conn)
else:
print("Connection failed.")
# Making trusted connection
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn,
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_user_options, 1)
userAfter = ibm_db.get_option(
tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_insert)
except:
print(ibm_db.stmt_errormsg())
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20"
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_trusted_context_connect(self):
sql_drop_role = "DROP ROLE role_01"
sql_create_role = "CREATE ROLE role_01"
sql_drop_trusted_context = "DROP TRUSTED CONTEXT ctx"
sql_create_trusted_context = "CREATE TRUSTED CONTEXT ctx BASED UPON CONNECTION USING SYSTEM AUTHID "
sql_create_trusted_context += config.auth_user
sql_create_trusted_context += " ATTRIBUTES (ADDRESS '"
sql_create_trusted_context += config.hostname
sql_create_trusted_context += "') DEFAULT ROLE role_01 ENABLE WITH USE FOR "
sql_create_trusted_context += config.tc_user
sql_drop_table = "DROP TABLE trusted_table"
sql_create_table = "CREATE TABLE trusted_table (i1 int, i2 int)"
sql_select = "SELECT * FROM trusted_table"
# Setting up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
sql_grant_permission = "GRANT INSERT ON TABLE trusted_table TO ROLE role_01"
sql_create_trusted_context_01 = sql_create_trusted_context + " WITH AUTHENTICATION"
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Populate the trusted_table
values = (\
(10, 20),\
(20, 40),\
)
sql_insert = 'INSERT INTO trusted_table (i1, i2) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, sql_insert)
if stmt:
for value in values:
result = ibm_db.execute(stmt, value)
ibm_db.close(conn)
else:
print("Connection failed.")
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
tc_options = {
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
tc_all_options = {
ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass
}
dsn = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.database, config.hostname, config.port, config.auth_user, config.auth_pass)
# Makeing normal connection and playing with it.
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "")
if tc_conn:
print("Normal connection established.")
result = ibm_db.set_option(tc_conn, tc_all_options, 1)
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
tc_conn = ibm_db.connect(dsn, "", "", tc_all_options)
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
get_tc_user = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if config.tc_user != get_tc_user:
print("But trusted user is not switched.")
ibm_db.close(tc_conn)
# Making trusted connection and performing normal operations.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (?, ?)"
stmt = ibm_db.prepare(tc_conn, sql_insert)
result = ibm_db.execute(stmt, (300, 500))
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Trusted connection failed.")
# Making trusted connection and switching to fake user.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
ibm_db.set_option(tc_conn, {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "fakeuser", ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: "******"}, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_options_reversed = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}
if tc_conn:
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
print("Trusted connection succeeded.")
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_options_reversed, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing password first then user while switching.
tc_conn = ibm_db.connect(dsn, "", "", options)
tc_user_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: config.tc_user}
tc_pass_options = {ibm_db.SQL_ATTR_TRUSTED_CONTEXT_PASSWORD: config.tc_pass}
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
try:
ibm_db.set_option(tc_conn, tc_pass_options, 1)
except:
print(ibm_db.conn_errormsg(tc_conn))
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Making trusted connection and passing only user while switching when both user and password are required.
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
ibm_db.set_option(tc_conn, tc_user_options, 1)
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 500"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
except:
print(ibm_db.stmt_errormsg())
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Dropping the trusted context, in case it exists
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
# Dropping Role.
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
# Creating Role.
try:
result = ibm_db.exec_immediate(conn, sql_create_role)
except:
pass
# Granting permissions to role.
try:
sql_grant_permission = "GRANT UPDATE ON TABLE trusted_table TO ROLE role_01"
result = ibm_db.exec_immediate(conn, sql_grant_permission)
except:
pass
# Creating trusted context
try:
sql_create_trusted_context_01 = sql_create_trusted_context + " WITHOUT AUTHENTICATION"
result = ibm_db.exec_immediate(conn, sql_create_trusted_context_01)
except:
pass
# Closing connection
ibm_db.close(conn)
else:
print("Connection failed.")
# Making trusted connection
tc_conn = ibm_db.connect(dsn, "", "", options)
if tc_conn:
print("Trusted connection succeeded.")
val = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT, 1)
if val:
userBefore = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
ibm_db.set_option(tc_conn, tc_user_options, 1)
userAfter = ibm_db.get_option(tc_conn, ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID, 1)
if userBefore != userAfter:
print("User has been switched.")
# Inserting into table using trusted_user.
sql_insert = "INSERT INTO " + config.user + ".trusted_table (i1, i2) VALUES (300, 500)"
try:
stmt = ibm_db.exec_immediate(tc_conn, sql_insert)
except:
print(ibm_db.stmt_errormsg())
# Updating table using trusted_user.
sql_update = "UPDATE " + config.user + ".trusted_table set i1 = 400 WHERE i2 = 20"
stmt = ibm_db.exec_immediate(tc_conn, sql_update)
ibm_db.close(tc_conn)
else:
print("Connection failed.")
# Cleaning up database.
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
print("Connection succeeded.")
try:
result = ibm_db.exec_immediate(conn, sql_drop_trusted_context)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_table)
except:
pass
try:
result = ibm_db.exec_immediate(conn, sql_drop_role)
except:
pass
ibm_db.close(conn)
else:
print("Connection failed.")
def openConnection(self):
"""Attempt to establish a Db2 server or database connection."""
# Define And Initialize The Appropriate Local Variables
connString = "DRIVER={IBM DB2 ODBC DRIVER}"
msgString = ""
# If A Host Name Was Not Specified, Use The Name That Is Assigned To The Current Server
if self.hostName is '':
self.hostName = socket.gethostname()
# If A Port Number Was Not Specified, Use Port Number 50000
if self.portNum is '':
self.portNum = '50000'
# Display An Appropriate Status Message And Add The Correct "ATTACH" Value To The
# Connection String Variable (connString)
msgString = "\nConnecting to the "
if self.dsType is 'LOCAL_SVR':
print(msgString + "local server ... ", end="")
if get_ipython(): # If Running With IPython, ...
connString += ";ATTACH=TRUE" # Attach To A Server; Not A Database
else:
connString = "ATTACH=TRUE" # Attach To A Server; Not A Database
elif self.dsType is 'SERVER':
print(msgString + self.hostName + " server ... ", end="")
connString += ";ATTACH=TRUE" # Attach To A Server; Not A Database
else:
print(msgString + self.dbName + " database ... ", end="")
connString += ";ATTACH=FALSE" # Attach To A Database; Not A Server
# If Appropriate, Construct The Portion Of The Connection String That Will Be Used To
# Establish A Connection To The Local Db2 Server
if self.dsType is 'LOCAL_SVR':
if get_ipython(): # If Running With IPython, ...
connString += ";HOSTNAME=" + socket.gethostname()
connString += ";PORT=" + self.portNum
connString += ";PROTOCOL=TCPIP"
connString += ";UID=" + self.userID
connString += ";PWD=" + self.passWord
# Otherwise, Construct The Portion Of The Connection String That Will Be Used To
# Establish A Connection To A Remote Db2 Server Or A Db2 Database
else:
connString += ";DATABASE=" + self.dbName # Only Used To Connect To A Database
connString += ";HOSTNAME=" + self.hostName # Only Used To Connect To A Server
connString += ";PORT=" + self.portNum # Only Used To Connect To A Server
connString += ";PROTOCOL=TCPIP"
connString += ";UID=" + self.userID
connString += ";PWD=" + self.passWord
# Attempt To Establish A Connection To The Appropriate Db2 Server Or Database
# If Running With IPython (i.e., Jupyter Notebook), ...
if get_ipython():
try:
self.connectionID = ibm_db.connect(connString, '', '')
except Exception:
pass
# If Running With Python, ...
else:
try:
self.connectionID = ibm_db.connect(connString, self.userID,
self.passWord)
except Exception:
pass
# If A Connection Could Not Be Established, Display An Appropriate Error Message
# And Set The Function Return Code Attribute To "False"
if self.connectionID is None:
msgString = "\nERROR: Unable to connect to the "
if self.dsType is 'LOCAL_SVR':
print(msgString + "local server ... ", end="")
elif self.dsType is 'SERVER':
print(msgString + self.hostName + " server.")
else:
print(msgString + self.dbName + " database.")
msgString = ibm_db.conn_errormsg()
print(msgString + "\n")
print("Connection string used: " + connString + "\n")
self.returnCode = False
# If A Connection Could Be Established, Complete The Status Message And Set The
# Return Code Attribute To "True"
else:
print("Done!\n")
self.returnCode = True
def main():
mess = ['']
module = AnsibleModule(
argument_spec = dict(
db2server=dict(default="localhost"),
db2port=dict(default=50000),
db2user=dict(required=True),
db2userpwd=dict(required=True),
db2db=dict(required=True)
)
)
if not db2py_installed:
module.fail_json(msg="IBM_DB has not been located", changed=False)
raise
db2server = module.params["db2server"]
db2port = module.params["db2port"]
db2user = module.params["db2user"]
db2userpwd = module.params["db2userpwd"]
db2db = module.params["db2db"]
conStr = "DATABASE="+db2db+";"
conStr += "HOSTNAME="+db2server+";"
conStr += "PORT="+db2port+";"
conStr += "PROTOCOL=TCPIP;"
conStr += "UID="+db2user+";"
conStr += "PWD="+db2userpwd
con = ibm_db.connect (conStr,"","")
if not con:
mess[0] = ibm_db.conn_errormsg()
module.fail_json(msg=mess[0], changed=False)
raise
dbParams = list()
if not read_dbparams(con,mess,dbParams):
module.fail_json(msg=mess[0], changed=False)
raise
dbInsParams = list()
if not read_dbInsparams(con, mess,dbInsParams):
module.fail_json(msg=mess[0], changed=False)
raise
dbTableSpaces = list()
if not read_tablespaces(con, mess,dbTableSpaces):
module.fail_json(msg=mess[0], changed=False)
raise
dbSchemas = list()
if not read_schemas(con,mess,dbSchemas):
module.fail_json(msg=mess[0], changed=False)
raise
module.exit_json(
ansible_facts = dict(
dbParams = dbParams,
dbInsParams = dbInsParams,
dbTableSpaces = dbTableSpaces,
dbSchemas = dbSchemas
),
changed = False
)
def __init__(self, id, nome, compra, preco):
self.id = id
self.nome = nome
self.compra = compra
self.preco = preco
#Replace the placeholder values with your actual Db2 hostname, username, and password:
dsn_hostname = "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net" # e.g.: "dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net"
dsn_uid = "mxb40007" # e.g. "abc12345"
dsn_pwd = "z887bnjptc6sd^p0" # e.g. "7dBZ3wWt9XN6$o0J"
dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB" # e.g. "BLUDB"
dsn_port = "50000" # e.g. "50000"
dsn_protocol = "TCPIP" # i.e. "TCPIP"
dsn = ("DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname,
dsn_port, dsn_protocol, dsn_uid, dsn_pwd)
#print the connection string to check correct values are specified
print(dsn)
#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create database connection
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
#Apaga a tabela PI_7sem
#dropQuery = "drop table PI_7sem"
#Executa o statement
#ibm_db.exec_immediate(conn, dropQuery)
#Construindo a tabela. Caso haja mudanças na tabela é aqui que que deve-se alterar
#createQuery = "create table PI_7sem(ID VARCHAR(40) NOT NULL, Nome VARCHAR(20),Compra VARCHAR(20), Valor DOUBLE)"
#Não está como PRIMARY KEY
#ibm_db.exec_immediate(conn,createQuery)
data = (self.id, self.nome, self.compra, self.preco)
format_str = "insert into PI_7sem values ('{id_pi}', '{name}', '{comp}', '{value}')"
insertQuery = format_str.format(id_pi=data[0],
name=data[1],
comp=data[2],
value=data[3])
ibm_db.exec_immediate(conn, insertQuery)
selectStmt = ibm_db.exec_immediate(conn, "SELECT * FROM PI_7sem")
slct = ibm_db.fetch_tuple(selectStmt)
n = 0
selecao = []
while (slct):
selecao.append(slct)
slct = ibm_db.fetch_tuple(selectStmt)
n = n + 1
self.selecao = selecao
ibm_db.close(conn)
def run_test_execute_many(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the tabmany table, in case it exists
drop = "DROP TABLE TABMANY"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
#create table tabmany
create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))"
ibm_db.exec_immediate(conn, create)
#Populate the tabmany table with execute_many
insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)"
params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'),
(40, 'OBrien'))
stmt_insert = ibm_db.prepare(conn, insert)
ibm_db.execute_many(stmt_insert, params)
#check the number of rows inserted
row_count = ibm_db.num_rows(stmt_insert)
print(row_count)
# chaeck the inserted columns
select = "SELECT * FROM TABMANY"
stmt_select = ibm_db.exec_immediate(conn, select)
cols = ibm_db.fetch_tuple(stmt_select)
while (cols):
print("%s, %s" % (cols[0], cols[1]))
cols = ibm_db.fetch_tuple(stmt_select)
#populate the tabmany table
params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'),
(60, 'Quigley'), (70, None))
try:
ibm_db.execute_many(stmt_insert, params)
except Exception 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())
#__END__
#__LUW_EXPECTED__
#4
#10, Sanders
#20, Pernal
#30, Marenghi
#40, OBrien
#Error 1: Value parameter tuple: 2 has less no of param
#Error 2: Value parameters array 3 is not homogeneous with privious parameters array
#3
#__ZOS_EXPECTED__
#4
#10, Sanders
#20, Pernal
#30, Marenghi
#40, OBrien
#Error 1: Value parameter tuple: 2 has less no of param
#Error 2: Value parameters array 3 is not homogeneous with privious parameters array
#3
#__IDS_EXPECTED__
#4
#10, Sanders
#20, Pernal
#30, Marenghi
#40, OBrien
#Error 1: Value parameter tuple: 2 has less no of param
#Error 2: Value parameters array 3 is not homogeneous with privious parameters array
#3
if args.output_file:
fout = open(args.output_file, 'w')
else:
fout = sys.stdout
print_legend(args.hostname1, hostname2)
# Compare DNs/ModifyTimestamp from first and second LDAP servers
compare_all_entry_modify_timestamps(args.hostname1, conn1, schema1, hostname2, conn2, schema2, fout)
endtime = datetime.utcnow()
logger.info("End of Script: {}".format(endtime))
print("Script ran for: {}".format(endtime - starttime))
except Exception as e:
conn_error = ibm_db.conn_error()
stmt_error = ibm_db.stmt_error()
if conn_error != '':
print("Error Code: {} Msg: {}".format(conn_error, ibm_db.conn_errormsg()))
if conn1:
print("Connection issue with server#2 most probably.")
else:
print("Connection issue with server#1 most probably.")
elif stmt_error != '':
print("Error Code: {} Msg: {}".format(stmt_error, ibm_db.stmt_errormsg()))
raise e
finally:
if fout and fout is not sys.stdout:
fout.close()
if conn1:
ibm_db.close(conn1)
if conn2:
ibm_db.close(conn2)
print()
# Attempt To Create A New Database At The Remote Server Using Connection Number Five
if not svrConnection[5] is None:
print("Creating a database named " + dbName +
" using Connection 5. Please wait.")
currentConnection = svrConnection[5]
try:
returnCode = ibm_db.createdbNX(currentConnection, dbName)
except Exception:
pass
# If The Database Could Not Be Created, Display An Error Message And Exit
if returnCode is None:
print("ERROR: Unable to create the " + dbName + " database.\n")
errorMsg = ibm_db.conn_errormsg(svrConnection)
print(errorMsg + "\n")
# Otherwise, Display A Status Message Indicating The Database Was Created
else:
print("\nThe database \"" + dbName + "\" has been created!\n")
# Attempt To Close The Db2 Server Connection (Connection 5)
print("Closing Db2 server Connection 5 ... ", end="")
try:
returnCode = ibm_db.close(currentConnection)
except Exception:
pass
# If The Connection Was Not Closed, Display An Error Message
if returnCode is False:
#DB2数据与写csv数据
import ibm_db
import sys
import os
import time
import datetime
import csv
try:
conn = ibm_db.connect(
"DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=valen;PWD=kingling819@;",
"", "")
except:
print "no connection:", ibm_db.conn_errormsg()
else:
print "The connection was successful"
file_object = open('call_result.txt', 'a')
str_sql = "select region_code, call_time from valen.tbl_my_test b "
cresult = ""
file_csv = open('my.csv', 'wb')
writer = csv.writer(file_csv)
writer.writerow(['Column1', 'Column2'])
stmt = ibm_db.exec_immediate(conn, str_sql)
r = ibm_db.fetch_both(stmt)
while (r):
#print str(r[0]), str(r[1])
cresult = cresult + str(r[0]) + ',' + str(r[1]) + '\r\n'
def run_test_createdbNX(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print 'Errors occurred during drop database'
try:
# call createdbNX without codeset argument when specified database not exeist
rc = ibm_db.createdbNX(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print 'database created sucessfully'
ibm_db.close(conn)
conn = False
else:
print 'database is not created'
else:
print 'Error occurred during create db if not exist'
conn = ibm_db.connect(conn_str, '', '')
if conn:
ibm_db.close(conn)
conn = False
# call recreate db with codeset argument when specified database exist
rc = ibm_db.createdbNX(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info( conn )
if conn and (server_info.DB_CODEPAGE != 819):
print 'database with codeset created sucessfully'
ibm_db.close(conn)
conn = False
else:
print 'Database not created'
else:
print 'Error occurred during create db if not exist with codeset'
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print 'datbase droped sucessfully'
if conn:
print 'Errors occurred during drop database'
ibm_db.close(conn)
conn = False
else:
print 'Errors occurred during drop database'
except:
print ibm_db.conn_errormsg()
pass
ibm_db.close(conn_attach)
else:
print ibm_db.conn_errormsg()
def run_test_createDropDB(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print('Errors occurred during drop database')
try:
#create databse
rc = ibm_db.createdb(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print('database created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Errors occurred during create database')
#drop databse
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during delete database')
#create database with codeset option
rc = ibm_db.createdb(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info( conn )
if conn and (server_info.DB_CODEPAGE == 819):
print('database with codeset created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Errors occurred during create database')
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during drop database')
except:
print(ibm_db.conn_errormsg())
pass
ibm_db.close(conn_attach)
else:
print(ibm_db.conn_errormsg())
def run_test_024(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS' ):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_primary_keys')
elif ('zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_PRIMARY_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, None, None, None, config.user, 'test_keys')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, None, None, None, config.user,'TEST_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, 'TEST_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, None, None)
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, None, None)
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, None)
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, config.user, 'test_foreign_keys')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, config.user, 'TEST_FOREIGN_KEYS')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, None, 'TEST_FOREIGN_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
try:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None, None)
row = ibm_db.fetch_tuple(stmt)
except:
if (not stmt):
print(ibm_db.stmt_errormsg())
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'test_keys', None, 'dummy_schema')
elif ( 'zos' in sys.platform):
stmt = ibm_db.foreign_keys(conn, None, config.user, 'TEST_KEYS', None, 'dummy_schema')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None, 'dummy_schema')
row = ibm_db.fetch_tuple(stmt)
if(not row):
print("No Data Found")
else:
print(row)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
#__END__
#__LUW_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__ZOS_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__SYSTEMI_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#__IDS_EXPECTED__
#test_primary_keys
#id
#test_keys
#idf
#test_primary_keys
#id
#test_keys
#idf
#test_keys
#name
#test_foreign_keys
#namef
#test_keys
#name
#test_foreign_keys
#namef
#[IBM][CLI Driver] CLI0124E Invalid argument value. SQLSTATE=HY009 SQLCODE=-99999
#No Data Found
#__ZOS_ODBC_EXPECTED__
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_PRIMARY_KEYS
#ID
#TEST_KEYS
#IDF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#TEST_KEYS
#NAME
#TEST_FOREIGN_KEYS
#NAMEF
#{DB2 FOR OS/390}{ODBC DRIVER} SQLSTATE=HY009 ERRLOC=10:17:6 SQLCODE=-99999
#No Data Found
def run_test_024(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
'TEST_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, None, None)
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, None)
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, config.user,
'test_foreign_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, 'TEST_FOREIGN_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
try:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
None)
row = ibm_db.fetch_tuple(stmt)
except:
if (not stmt):
print(ibm_db.stmt_errormsg())
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, 'dummy_schema')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
'dummy_schema')
row = ibm_db.fetch_tuple(stmt)
if (not row):
print("No Data Found")
else:
print(row)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
cols = ibm_db.fetch_tuple( stmt_select )
#populate the tabmany table
params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None) )
try:
ibm_db.execute_many(stmt_insert, params)
except Exception, inst:
#check the no. of inserted rows
row_count = ibm_db.num_rows(stmt_insert)
#check the exception raised by execute_many API
print inst
print row_count
ibm_db.close(conn)
else:
print ibm_db.conn_errormsg()
#__END__
#__LUW_EXPECTED__
#4
#10, Sanders
#20, Pernal
#30, Marenghi
#40, OBrien
#Error 1: Value parameter tuple: 2 has less no of param
#Error 2: Value parameters array 3 is not homogeneous with privious parameters array
#3
#__ZOS_EXPECTED__
#4
#10, Sanders
#20, Pernal
def run_test_createdbNX(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (database, config.hostname, config.port, config.user, config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (config.hostname, config.port, config.user, config.password) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print('Errors occurred during drop database')
try:
# call createdbNX without codeset argument when specified database not exeist
rc = ibm_db.createdbNX(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print('database created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Error occurred during create db if not exist')
conn = ibm_db.connect(conn_str, '', '')
if conn:
ibm_db.close(conn)
conn = False
# call recreate db with codeset argument when specified database exist
rc = ibm_db.createdbNX(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info( conn )
if conn and (server_info.DB_CODEPAGE != 819):
print('database with codeset created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('Database not created')
else:
print('Error occurred during create db if not exist with codeset')
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during drop database')
except:
print(ibm_db.conn_errormsg())
pass
ibm_db.close(conn_attach)
else:
print(ibm_db.conn_errormsg())
def db2_error():
global _sqlerror, _sqlcode, _sqlstate, _connected
_sqlerror = "No error text available"
_sqlcode = -99999
_sqlstate = "-99999"
try:
if (_connected == True):
errmsg = ibm_db.stmt_errormsg().replace('\r', ' ')
errmsg = errmsg[errmsg.rfind("]") + 1:].strip()
else:
errmsg = ibm_db.conn_errormsg().replace('\r', ' ')
errmsg = errmsg[errmsg.rfind("]") + 1:].strip()
_sqlerror = errmsg
msg_start = errmsg.find("SQLSTATE=")
if (msg_start != -1):
msg_end = errmsg.find(" ", msg_start)
if (msg_end == -1):
msg_end = len(errmsg)
_sqlstate = errmsg[msg_start + 9:msg_end]
else:
_sqlstate = "0"
msg_start = errmsg.find("SQLCODE=")
if (msg_start != -1):
msg_end = errmsg.find(" ", msg_start)
if (msg_end == -1):
msg_end = len(errmsg)
_sqlcode = errmsg[msg_start + 8:msg_end]
try:
_sqlcode = int(_sqlcode)
except:
pass
else:
_sqlcode = 0
except:
errmsg = "Unknown error."
_sqlcode = -99999
_sqlstate = "-99999"
_sqlerror = errmsg
return
msg_start = errmsg.find("SQLSTATE=")
if (msg_start != -1):
msg_end = errmsg.find(" ", msg_start)
if (msg_end == -1):
msg_end = len(errmsg)
_sqlstate = errmsg[msg_start + 9:msg_end]
else:
_sqlstate = "0"
msg_start = errmsg.find("SQLCODE=")
if (msg_start != -1):
msg_end = errmsg.find(" ", msg_start)
if (msg_end == -1):
msg_end = len(errmsg)
_sqlcode = errmsg[msg_start + 8:msg_end]
try:
_sqlcode = int(_sqlcode)
except:
pass
else:
_sqlcode = 0
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
dsn_protocol, dsn_uid, dsn_pwd)
#print the connection string to check correct values are specified
print(dsn)
#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create database connection
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
#################################
#BASIC SQL
######################
#Lets first drop the table INSTRUCTOR in case it exists from a previous attempt
dropQuery = "drop table INSTRUCTOR"
#Now execute the drop statment
dropStmt = ibm_db.exec_immediate(conn, dropQuery)
####################
#Construct the Create Table DDL statement - replace the ... with rest of the statement
createQuery = "create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))"
#Now fill in the name of the method and execute the statement
createStmt = ibm_db.exec_immediate(conn, createQuery)
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
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)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while (row):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print(str)
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: %s" % ibm_db.conn_errormsg())
#DB credentials, stored in config file
dsn = (
"DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)
try:
conn = ibm_db.connect(dsn, "", "")
print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
#Table structure from the create_table file
#'''(id varchar(10) PRIMARY KEY NOT NULL, username VARCHAR (30), name varchar(30), Col3 varchar(30), Col4 varchar(30))"'''
#Insert sigle row of data:
insertQuery = "insert into "+TABLENAME+" values ('0000000','Fiction','Kathy','','')"
insertStmt = ibm_db.exec_immediate(conn, insertQuery)
#Or insert few rows:
##insertQuery2 = "insert into "+TABLENAME+" values ('0000000','Fiction','Kathy','',''), ('0000001','SAMPLE','DANA','','')
##insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)
except:
print ("something gone wrong, but I don't know what exactly ", ibm_db.conn_errormsg() )
ibm_db.close(conn)
print ("Connection closed")
def main():
inputfile = ''
mess = ''
module = AnsibleModule(argument_spec=dict(
db2server=dict(default="localhost"),
db2port=dict(default=50000),
db2user=dict(required=True),
db2userpwd=dict(required=True),
db2db=dict(required=True),
sqlFile=dict(required=True),
logFile=dict(default=None),
))
if not db2py_installed:
module.fail_json(msg="IBM_DB has not been located", changed=False)
raise
db2server = module.params["db2server"]
db2port = module.params["db2port"]
db2user = module.params["db2user"]
db2userpwd = module.params["db2userpwd"]
db2db = module.params["db2db"]
inputfile = module.params["sqlFile"]
outputfile = module.params["logFile"]
conStr = "DATABASE=" + db2db + ";"
conStr += "HOSTNAME=" + db2server + ";"
conStr += "PORT=" + db2port + ";"
conStr += "PROTOCOL=TCPIP;"
conStr += "UID=" + db2user + ";"
conStr += "PWD=" + db2userpwd
con = ibm_db.connect(conStr, "", "")
if not con:
mess[0] = ibm_db.conn_errormsg()
module.fail_json(msg=mess[0], changed=False)
raise
# If an output file has been specified, then create one
if (outputfile) and outputfile != 'STDOUT':
ofs = open(outputfile, "w")
sqlQuery = ''
with open(inputfile, 'r') as inp:
lineNo = 0
for line in inp:
lineNo = lineNo + 1
line = line.strip()
line = line.lstrip()
# Skip any blank lines or SQL remark characters
if line == '\n' or line.find('--', 0, 2) != -1 or line.find(
'REM', 0, 3) != -1:
sqlQuery = ''
elif line == '/\n' or line.find(';') != -1:
sqlQuery = sqlQuery + line
sqlQuery = sqlQuery.strip()
sqlQuery = sqlQuery.strip(';')
sqlQuery = sqlQuery.strip('/')
sqlQuery = sqlQuery.strip('\n')
if sqlQuery and not sqlQuery.isspace():
stmt = ibm_db.exec_immediate(con, sqlQuery)
if not stmt:
mess = "Line: " + str(
lineNo
) + ": " + sqlQuery + "->" + ibm_db.stmt_errormsg(stmt)
module.fail_json(msg=mess, changed=False)
raise
if (outputfile) and outputfile != 'STDOUT':
ofs.write(sqlQuery + "\nCommand processed\n")
elif (outputfile) and outputfile == 'STDOUT':
mess = mess + sqlQuery + "\nCommand processed\n"
sqlQuery = ''
else:
sqlQuery = sqlQuery + line
if (outputfile) and outputfile != 'STDOUT':
ofs.close()
inp.close()
ibm_db.close(con)
if not outputfile:
mess = "SQL file processed"
module.exit_json(msg=mess, changed=True)
def run_test_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_FORWARD_ONLY
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ibm_db.SQL_ROWCOUNT_PREFETCH_ON
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
if ('zos' in sys.platform):
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0")
else:
result = ibm_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ibm_db.SQL_ROWCOUNT_PREFETCH_OFF
})
if result:
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
ibm_db.close(conn)
else:
print("no connection:", ibm_db.conn_errormsg())
connectionStr = ''
security = ''
if (connections['db2']['host'].lower() == 'y'):
security = ';SECURITY=SSL;SSLClientKeystoredb=/opt/ibm/db2/GSK8Store/keyclient.kdb;SSLClientKeystash=/opt/ibm/db2/GSK8Store/keyclient.sth; '
connectionStr = "DATABASE=" + connections['db2']['database']
connectionStr = connectionStr + ";HOSTNAME=" + connections['db2']['host']
connectionStr = connectionStr + ";PORT=" + connections['db2']['port']
connectionStr = connectionStr + ";PROTOCOL=TCPIP;UID=" + connections['db2']['user']
connectionStr = connectionStr + ";PWD=" + connections['db2']['pwd']
connectionStr = connectionStr + security
try:
conn = ibm_db.connect(connectionStr, "", "")
except:
print(ibm_db.conn_errormsg())
else:
print("db2 connected ...")
def cdctable(cdcdo):
if cdcdo == 'archive' :
print('set tables to archive')
sql = "UPDATE " + ASN_SCHEMA + ".IBMSNAP_REGISTER SET STATE = 'A' WHERE SOURCE_OWNER <> '' "
stmt = ibm_db.exec_immediate(conn, sql)
else:
sql = "SELECT SOURCE_OWNER,SOURCE_TABLE, STATE FROM " + ASN_SCHEMA + ".IBMSNAP_REGISTER WHERE SOURCE_OWNER <> '' "
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False: