def run_test_114(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, num INTEGER )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, num) values (1,5)"
ibm_db.exec_immediate(conn, insert)
insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral")
ibm_db.execute(stmt)
result = ibm_db.fetch_row( stmt )
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print row0
print row1
result = ibm_db.fetch_row( stmt )
else:
print "Connection failed."
def run_test_038(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
ibm_db.execute(result2)
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3), \
ibm_db.result(result2, 5)))
row2 = ibm_db.fetch_row(result2)
row = ibm_db.fetch_row(result)
def run_test_115(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if conn:
drop = "drop table numericliteral"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(stmt, 0)
class Row:
pass
row = Row()
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral")
ibm_db.execute(stmt)
# row = ibm_db.fetch_object(stmt, 0)
ibm_db.fetch_row(stmt, 0)
if (server.DBMS_NAME[0:3] != 'IDS'):
row.DATA = ibm_db.result(stmt, 'DATA')
else:
row.DATA = ibm_db.result(stmt, 'data')
print(row.DATA)
else:
print("Connection failed.")
def run_test_035(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from staff")
i=0
row = ibm_db.fetch_row(result)
while ( row ):
print "%d, " % i
i+=1
row = ibm_db.fetch_row(result)
ibm_db.close(conn)
def run_test_264(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
server = ibm_db.server_info(conn)
if server.DBMS_NAME[0:3] == "IDS":
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
# Drop the tab_bigint table, in case it exists
drop = "DROP TABLE tab_bigint"
result = ""
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if server.DBMS_NAME[0:3] == "IDS":
create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)"
else:
create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while data:
print data[0]
print data[1]
print data[2]
print data[3]
print type(data[0]) is long
print type(data[1]) is long
print type(data[2]) is long
data = ibm_db.fetch_both(stmt)
# test ibm_db.result for fetch of bigint
stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint")
ibm_db.execute(stmt1)
ibm_db.fetch_row(stmt1, 0)
if server.DBMS_NAME[0:3] != "IDS":
row1 = ibm_db.result(stmt1, "COL2")
else:
row1 = ibm_db.result(stmt1, "col2")
print row1
ibm_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result,i))
statement = "SELECT * FROM tab_datetime"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def getResByRow(conn, sql):
''' Call fetch_row '''
n = 0
try:
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_row(stmt)
while res != False:
print 'Result from :', db2.result(stmt, 0)
n += db2.result(stmt, 0)
res = db2.fetch_row(stmt)
except:
print "Transaction couldn't be completed:" , db2.stmt_errormsg()
else:
return n
def run_test_032(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6")
while (ibm_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ibm_db.result(stmt, "id")
breed = ibm_db.result(stmt, "breed")
name = ibm_db.result(stmt, "name")
weight = ibm_db.result(stmt, "weight")
else:
id = ibm_db.result(stmt, "ID")
breed = ibm_db.result(stmt, "BREED")
name = ibm_db.result(stmt, "NAME")
weight = ibm_db.result(stmt, "WEIGHT")
print "int(%d)" % id
print "string(%d) \"%s\"" % (len(breed), breed)
print "string(%d) \"%s\"" % (len(name), name)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_03a(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while ( ibm_db.fetch_row(stmt) ):
breed = ibm_db.result(stmt, 1)
print ("string(%d) \"%s\"" % (len(breed), breed))
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print ("string(%d) \"%s\"" % (len(name), name))
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "passport")
else:
name = ibm_db.result(stmt, "PASSPORT")
print (name)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10:42:34', '1981-07-08', '1981-07-08 10:42:34')"
result = ibm_db.exec_immediate(conn, statement)
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10.42.34', '1981-07-08', '1981-07-08-10.42.34')"
result = ibm_db.exec_immediate(conn, statement)
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + ibm_db.field_type(result,i)
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print row0
print row1
print row2
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_036(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from staff")
i=0
row = ibm_db.fetch_row(result)
while ( row ):
result2 = ibm_db.exec_immediate(conn, "select * from staff")
j=0
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print("%d)%d," % (i, j))
j+=1
row2 = ibm_db.fetch_row(result2)
print("%d, " % i)
i+=1
row = ibm_db.fetch_row(result)
ibm_db.close(conn)
def check_if_user_active(prof_uid_lower):
sql = "select PROF_MAIL from EMPINST.employee where prof_uid_lower = '" + prof_uid_lower + "' and prof_state = '0';"
stmt = ibm_db.prepare(conn_people, sql)
try:
ibm_db.execute(stmt)
if ibm_db.fetch_row(stmt):
return True
else:
return False
except:
print "Transaction couldn't be completed, error getting profiles data: " , ibm_db.stmt_errormsg()
sys.exit(3)
def items():
page = ''
if db2conn:
stmt = ibm_db.exec_immediate(db2conn, "SELECT * FROM " + db2cred['username'] + ".ITEMS")
# Look through each row, while there is a row
while ibm_db.fetch_row(stmt) != False:
# Get the rows Item column value
page += ibm_db.result(stmt, "ITEM") + '\n'
return page
def run_test_113(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "DROP TABLE datetest"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )"
ibm_db.exec_immediate(conn, create)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
else:
insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')"
ibm_db.exec_immediate(conn, insert)
insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.prepare(conn, "SELECT * FROM datetest")
ibm_db.execute(stmt)
result = ibm_db.fetch_row( stmt )
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print row0
print row1
result = ibm_db.fetch_row( stmt )
else:
print "Connection failed."
def check_community_owners(community_id):
active = False
sql = "select DISTINCT LOWER_LOGIN from sncomm.memberlogin where member_uuid in (SELECT DISTINCT MEMBER_UUID from sncomm.member WHERE COMMUNITY_UUID = '" + community_id + "' AND ROLE='1' AND LOWER_LOGIN NOT LIKE '%@%');"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
member_login = ibm_db.result(stmt, 0)
if check_if_user_active(member_login):
active = True
return True
break
else:
return False
except:
print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg()
sys.exit(3)
def run_test_031(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
id = ibm_db.result(stmt, 0)
print("int(%d)" % id)
breed = ibm_db.result(stmt, 1)
print("string(%d) \"%s\"" % (len(breed), breed))
name = ibm_db.result(stmt, 2)
print("string(%d) \"%s\"" % (len(name), name))
weight = ibm_db.result(stmt, 3)
print("string(%d) \"%s\"" % (len(str(weight)), weight))
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_033(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (ibm_db.fetch_row(stmt)):
weight = ibm_db.result(stmt, 3)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
breed = ibm_db.result(stmt, 1)
print "string(%d) \"%s\"" % (len(breed), breed)
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print "string(%d) \"%s\"" % (len(name), name)
ibm_db.close(conn)
else:
print "Connection failed."
def connectBD2():
# 连接数据库
# conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '')
conn = ibm_db.connect(conn_str, '', '')
# 获取数据库服务器信息
server = ibm_db.server_info(conn)
# 查询数据库sql
sql ='select PID, "NAME" from PRODUCT'
arr = []
dc = {}
if conn:
# 执行查询语句
stmt = ibm_db.exec_immediate(conn, sql)
# 处理查询结果
while (ibm_db.fetch_row(stmt)):
acno, dc[acno] = ibm_db.result(stmt, 0), ibm_db.result(stmt, 1)
arr.append(acno)
# 打印服务器名称
print(server.DBMS_NAME)
# 关闭数据库连接
ibm_db.close(conn)
return arr, dc
def run_test_014(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
query = 'SELECT * FROM animals ORDER BY name'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print ("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print ("Fetch row -1: %s" % str(rc))
except:
print ("Requested row number must be a positive value")
ibm_db.close(conn)
def main(args):
global conn
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
if globals().get("conn") is None:
print("creating connection")
conn = ibm_db.connect(ssldsn, "", "")
else:
print("reusing connection")
drop = "DROP TABLE CUSTOMER_FEEDBACK"
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
# Check table creation
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i), ":", ibm_db.field_type(result, i))
ts_val = datetime.datetime.today()
print("the time is", ts_val)
# Try to insert sample test row
statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val))
statement = "SELECT * FROM CUSTOMER_FEEDBACK"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
print(ibm_db.result(stmt, 0), ibm_db.result(
stmt, 1), ibm_db.result(stmt, 2))
if not result:
return {"err": "error :" + statement}
return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table stockprice
create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205),\
(30, "Megabyte", "98.65"),\
(40, "Visarsoft", Decimal("123.34")),\
(50, "Mailersoft", Decimal("134.222")),\
(60, "Kaerci", Decimal("100.976"))\
)
insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
stmt = ibm_db.prepare(conn,insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.execute(stmt);
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKSHARE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print("%s : %s : %s\n" % (data[0], data[1], data[2]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " %str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
#__END__
#__LUW_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__ZOS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__IDS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205),\
(30, "Megabyte", "98.65"),\
(40, "Visarsoft", Decimal("123.34")),\
(50, "Mailersoft", Decimal("134.222")),\
(60, "Kaerci", Decimal("100.976"))\
)
insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print excp[1]
# Select the result from the table and
query = 'SELECT * FROM STOCKSHARE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print "%s : %s : %s\n" % (data[0], data[1], data[2])
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print "Fetch Row -1:%s " % str(rc)
except:
print "Requested row number must be a positive value"
ibm_db.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__ZOS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__IDS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
def run_test_bool_callproc(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info(conn)
if (not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn, "DROP PROCEDURE bool_procparams")
ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(
conn,
"CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
)
except:
pass
try:
procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
OPEN c1;
set param2 = parm1;
END"""
ibm_db.exec_immediate(conn, procedure)
except Exception as e:
print(str(e))
exit(-1)
try:
insert_sql = "INSERT INTO bool_test values(?, ?)"
stmt = ibm_db.prepare(conn, insert_sql)
rows = ((True, 'bindparam true'), (False, 'bindparam false'),
(None, 'bindparam None'))
for row in rows:
ibm_db.bind_param(stmt, 1, row[0])
ibm_db.bind_param(stmt, 2, row[1])
ibm_db.execute(stmt)
stmt = None
inparam = 11
outparam = -1
stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
(inparam, outparam))
print("Fetching first result set")
row = ibm_db.fetch_row(stmt)
while row:
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
row = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
if resultSet is False:
print("\nERROR: Unable to execute the SQL statement specified.\n")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Query results:\n")
print("DEPTNAME")
print("____________________________")
# As Long As There Are Records In The Result Set Produced, ...
while (ibm_db.fetch_row(resultSet) is True):
# Retrieve The Data From The Current Row
try:
dataValue = ibm_db.result(resultSet, 0)
except Exception:
pass
# Display The Data Retrieved
if not dataValue is None:
print(dataValue)
# Add A Blank Line To The End Of The Report
print()
# Close The Database Connection That Was Opened Earlier
insertQuery2 = "insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')"
insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)
#***query data in the table
#Construct the query that retrieves all rows from the INSTRUCTOR table
selectQuery = "select * from INSTRUCTOR"
#Execute the statement
selectStmt = ibm_db.exec_immediate(conn, selectQuery)
#Fetch the Dictionary (for the first row only)
ibm_db.fetch_both(selectStmt)
#Fetch the rest of the rows and print the ID and FNAME for those rows
while ibm_db.fetch_row(selectStmt) != False:
print(" ID:", ibm_db.result(selectStmt, 0), " FNAME:",
ibm_db.result(selectStmt, "FNAME"))
#**Retrieve data into Pandas:::
import pandas
import ibm_db_dbi
#connection for pandas
pconn = ibm_db_dbi.Connection(conn)
#query statement to retrieve all rows in INSTRUCTOR table
selectQuery = "select * from INSTRUCTOR"
#retrieve the query results into a pandas dataframe
pdf = pandas.read_sql(selectQuery, pconn)
#print just the LNAME for first row in the pandas data frame
uid = args.user
pwd = args.pw
# Connect to DB2 database
try:
conn_people = ibm_db.connect("DATABASE=" + peopledb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "")
conn_comm = ibm_db.connect("DATABASE=" + communitydb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "")
except:
print "No connection to DBs:", ibm_db.conn_errormsg()
sys.exit(3)
# Query DB2 database
sql = "select DISTINCT COMMUNITY_UUID, NAME, LASTMOD from sncomm.community;"
stmt = ibm_db.prepare(conn_comm, sql)
try:
ibm_db.execute(stmt)
while ibm_db.fetch_row(stmt) != False:
name = ibm_db.result(stmt, "NAME")
lastupdate = ibm_db.result(stmt, "LASTMOD")
if (datetime.datetime.now() - datetime.timedelta(days=int(args.critical)) > lastupdate):
count_critical = count_critical + 1
list_critical = list_critical + name + "\n"
elif (datetime.datetime.now() - datetime.timedelta(days=int(args.warning)) > lastupdate):
count_warning = count_warning + 1
list_warning = list_warning + name + "\n"
except:
print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg()
sys.exit(3)
# Nagios standard feedback
if (count_critical!=0):
print("Critical: There are communites which were not updates since " + args.critical + " days" + list_critical.encode('utf-8') + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;")
def run_test_decfloat(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
drop = "DROP TABLE STOCKPRICE"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table stockprice
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
else:
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205, "100.234"),\
(30, "Megabyte", 98.65, "1002.112"),\
(40, "Visarsoft", 123.34, "1652.345"),\
(50, "Mailersoft", 134.22, "1643.126"),\
(60, "Kaerci", 100.97, "9876.765")\
)
insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
stmt = ibm_db.prepare(conn,insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = 100.1234
stockprice = "100.567"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
ibm_db.bind_param(stmt, 4, stockprice)
error = ibm_db.execute(stmt);
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKPRICE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " %str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_decfloat(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
drop = "DROP TABLE STOCKPRICE"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table stockprice
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
else:
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205, "100.234"),\
(30, "Megabyte", 98.65, "1002.112"),\
(40, "Visarsoft", 123.34, "1652.345"),\
(50, "Mailersoft", 134.22, "1643.126"),\
(60, "Kaerci", 100.97, "9876.765")\
)
insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
stmt = ibm_db.prepare(conn,insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = 100.1234
stockprice = "100.567"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
ibm_db.bind_param(stmt, 4, stockprice)
error = ibm_db.execute(stmt);
except:
excp = sys.exc_info()
# slot 1 contains error message
print(excp[1])
# Select the result from the table and
query = 'SELECT * FROM STOCKPRICE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " %str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_6792(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print str(i) + ":" + ibm_db.field_type(result,i)
statement = "SELECT * FROM table_6792"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
row3 = ibm_db.result(stmt, 3)
print row0
print row1
print row2
print row3
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__ZOS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__SYSTEMI_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__IDS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
