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_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())
def run_test_180(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
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())
try:
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
except:
pass
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())
else:
print("no connection")
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_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_180(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = ibm_db.exec_immediate(
conn,
"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
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())
try:
result = ibm_db.exec_immediate(
conn, "delete from t_string where a=123")
except:
pass
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())
else:
print("no connection")
def run_test_311(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON )
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ibm_db.exec_immediate(conn, sql)
print "Number of updated rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ibm_db.exec_immediate(conn, sql)
print "Number of deleted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)
def run_test_311(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON )
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = ibm_db.exec_immediate(conn, insert)
print "Number of inserted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = ibm_db.exec_immediate(conn, sql)
print "Number of updated rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = ibm_db.exec_immediate(conn, sql)
print "Number of deleted rows:", ibm_db.num_rows(res)
stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'")
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print data[0]
print data[1]
print data[2]
data = ibm_db.fetch_both(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)
def run_test_err_executemany(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP TABLE CLI0126E')
except:
pass
create_ddl = "create table CLI0126E \
(\
offer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\
position_id int NOT NULL,\
title VARCHAR(5000) NOT NULL,\
type VARCHAR(5000),\
quantity decimal(10, 3) NOT NULL,\
price_btc decimal(10, 8) NOT NULL,\
city VARCHAR(5000),\
country VARCHAR(500)\
)"
try:
ibm_db.exec_immediate(conn, create_ddl)
except:
pass
insert_statement = "INSERT INTO CLI0126E (position_id, title, type, quantity, price_btc, city, country)\
VALUES (?, ?, ?, ?, ?, ?, ?)"
stmt = ibm_db.prepare(conn, insert_statement)
# deliberately use wrong size decimal values to trigger CLI0111E (hidden by execute_many()).
parms1 = (15628, 'correct value in row1 column4', '', 1999999.0,
0.0067762, 'Belarus', 'Belarus1')
parms2 = (15629, 'incorrect value in row2 column4', '', 99999999.0,
0.0067762, 'Belarus', 'Belarus2')
parms3 = (15630, 'correct value in row3 column4', '', 1999999.0,
0.0067762, 'Belarus', 'Belarus3')
parms = (parms1, parms2, parms3)
try:
ibm_db.execute_many(stmt, parms)
print(
str(ibm_db.num_rows(stmt)) +
" - Rows inserted successfully")
except:
print(
"Failed to insert multiple-rows with ibm_db.execute_many()"
)
print(ibm_db.stmt_errormsg())
print("Number of rows inserted: " + str(ibm_db.num_rows(stmt)))
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_execute_many(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if( not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
if conn:
# Drop the tabmany table, in case it exists
drop = "DROP TABLE TABMANY"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
#create table tabmany
create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32), bflag boolean)"
ibm_db.exec_immediate(conn, create)
#Populate the tabmany table with execute_many
insert = "INSERT INTO TABMANY (id, name, bflag) VALUES(?, ?, ?)"
params = ((10, 'Sanders', True), (20, 'Pernal', False), (30, 'Marenghi', True), (40, 'OBrien', False))
stmt_insert = ibm_db.prepare(conn, insert)
ibm_db.execute_many(stmt_insert, params)
#check the number of rows inserted
row_count = ibm_db.num_rows(stmt_insert)
print(row_count)
# check the inserted columns
select = "SELECT * FROM TABMANY"
stmt_select = ibm_db.exec_immediate(conn, select)
cols = ibm_db.fetch_tuple( stmt_select )
while( cols ):
print("%s, %s, %s" % (cols[0], cols[1], cols[2]))
cols = ibm_db.fetch_tuple( stmt_select )
#populate the tabmany table
params = ((50, 'Hanes', False), (55, 'Mike', False, 'Extra'), (55.5, 'invalid row','not a bool'), (60, 'Quigley'), (70, None, None), [75, 'List', True] )
try:
ibm_db.execute_many(stmt_insert, params)
except Exception as inst:
#check the no. of inserted rows
row_count = ibm_db.num_rows(stmt_insert)
#check the exception raised by execute_many API
print(inst)
print(row_count)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
def run_test_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())
def run_test_144(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BINARY)
rc = ibm_db.execute(stmt)
num = ibm_db.num_rows(stmt)
print(num)
else:
print("Connection failed.")
def create_resource(self, name, desc, units, amount):
rname = name.current_item
rdesc = desc.text
runits = units.text
ramount = amount.text
valid_str = ""
if rname == "":
valid_str = "Select an Item"
elif runits == "":
valid_str = "Units is blank"
elif ramount == "":
valid_str = "Cost is blank"
if valid_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert !',
text=valid_str,
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
query1 = f'''INSERT INTO RESOURCES(CONTACT_ID, ACTIVITY_ID, NAME, DESC, UNITS, AMOUNT, DATE)
VALUES ({globalvariables.var_userid}, {globalvariables.var_act_id}, '{rname}', '{rdesc}', {runits},
{ramount}, SYSDATE)'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query1)
if ibm_db.num_rows(stmt) > 0:
#self.ids['name'].text=""
self.ids['desc'].text = ""
self.ids['units'].text = ""
self.ids['amount'].text = ""
self.snackbar = Snackbar(text="Item Added!")
self.snackbar.show()
def run_test_144(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT,
ibm_db.SQL_BINARY)
rc = ibm_db.execute(stmt)
num = ibm_db.num_rows(stmt)
print num
else:
print "Connection failed."
def make_payment(self,payamount):
amount = payamount.text
valid_str=""
if amount == "":
valid_str = "Amount is blank"
elif int(amount) < 10:
valid_str = "Minimum Amount to donate is 10"
if valid_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7,1),
buttons=[ok_button])
self.dialog.open()
else:
query = f'''INSERT INTO TRANSACTION(ACTIVITY_ID, CONTACT_ID, TXN_DATE, AMOUNT)
VALUES ({globalvariables.var_act_id}, {globalvariables.var_userid}, SYSDATE, {amount})'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
if ibm_db.num_rows(stmt) > 0 :
payment_mail(globalvariables.var_email, globalvariables.var_fname, amount, globalvariables.var_org_name, globalvariables.var_act_name)
self.ids['payamount'].text = "" #Setting the values to NULL after sucessfull registration
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Payment Successfull!',
text="Thank you for your donation!", size_hint=(0.7,1), buttons=[ok_button])
self.dialog.open()
self.manager.transition.direction = 'right'
self.manager.current = 'my_transaction_window'
self.manager.get_screen('my_transaction_window').load_txn_page()
def update_caption_name():
name = str(request.form["name"])
keyword = str(request.form["keyword"])
if name == "" or keyword == "":
result_dict = {}
result_dict["RESULT"] = 0
return render_template('update_caption.html', result=result_dict)
sql = "UPDATE names SET KEYWORDS = ? WHERE NAME=?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, keyword)
ibm_db.bind_param(stmt, 2, name)
result = ibm_db.execute(stmt)
result = ibm_db.num_rows(stmt)
sql = "SELECT * FROM names WHERE NAME = ?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, name)
result = ibm_db.execute(stmt)
result_dict = ibm_db.fetch_assoc(stmt)
print(result_dict)
result_dict["RESULT"] = result
return render_template('update_caption.html', result=result_dict)
def run_test_101(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: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
else:
print("no connection");
def run_test_101(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: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ibm_db.num_fields(result)
print("col: %d" % cols)
rows = ibm_db.num_rows(result)
print("affected row: %d" % rows)
else:
print("no connection");
def main():
conn = None
stmt = None
if not conf.has_option('passwd'):
conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8'))
try:
conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'),
conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '')
stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8'))
try:
result = ibm_db.fetch_tuple(stmt)
except:
rows = ibm_db.num_rows(stmt)
if rows != -1:
print u'Обработано строк %d' % rows
else:
print u'Команда выполнена'
return
if result:
column_conv = []
head = u''
underline=u''
for i in xrange(len(result)):
if i != 0:
head += u'|'
underline += u'+'
name = ibm_db.field_name(stmt, i)
size = ibm_db.field_display_size(stmt, i)
if len(name) > size:
size = len(name)
if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size:
size = len(u'NULL')
type_field = ibm_db.field_type(stmt, i)
if type_field == 'float' or type_field == 'real' or type_field == 'decimal':
column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float})
elif type_field == 'int' or type_field == 'bigint':
column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int})
else:
column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert})
head += name.center(size)
underline += u'-' * size
print head
print underline
while( result ):
print conv(result, column_conv)
result = ibm_db.fetch_tuple(stmt)
else:
print u'Результата не возвращено'
except Exception as e:
print >> sys.stderr, e
sys.exit(-1)
finally:
if stmt:
ibm_db.free_result(stmt)
if conn:
ibm_db.close(conn)
def create_org(self, groupname, regnum, desc):
ContentNavigationDrawer.populateNavDrawerValues(self)
groupname = groupname.text
regnum = regnum.text
desc = desc.text
valid_str = ""
if groupname == "":
valid_str = "Name is blank"
elif regnum == "":
valid_str = "Registration is blank"
elif desc == "":
valid_str = "Description is blank"
if valid_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert !',
text=valid_str,
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
status = "Y"
query1 = f'''INSERT INTO ORG(NAME, REGISTRATION, DESC, OWNER_ID, STATUS)
VALUES (UPPER('{groupname}'),UPPER('{regnum}'),'{desc}',{globalvariables.var_userid}, '{status}')'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query1)
if ibm_db.num_rows(stmt) > 0:
query2 = f'''SELECT ORG_ID FROM ORG WHERE REGISTRATION=UPPER('{regnum}') '''
stmt = ibm_db.exec_immediate(connection.conn, query2)
orglist = ibm_db.fetch_both(stmt)
orgid = ""
while (orglist):
orgid = orglist[0]
query3 = f'''INSERT INTO CONTACT_ORG(ORG_ID, CONTACT_ID, MEMBER_FLAG, STATUS)
VALUES ({orgid},{globalvariables.var_userid},'Y', '{status}')'''
stmt1 = ibm_db.exec_immediate(connection.conn, query3)
orglist = ibm_db.fetch_both(stmt)
self.ids[
'groupname'].text = "" #Setting the values to NULL after sucessfull registration
self.ids['regnum'].text = ""
self.ids['desc'].text = ""
#To upload Org Logo
if globalvariables.var_img_path != "":
logo_path = globalvariables.var_img_path
tgt_logo_path = "org_" + str(orgid) + ".png"
upload_org_logo(logo_path, tgt_logo_path)
globalvariables.var_img_path = ""
ok_button = MDFlatButton(text='OK',
on_release=self.dialog_close)
self.dialog = MDDialog(title='Successfully Registered',
text="Lets start helping!",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
self.manager.transition.direction = 'left'
self.manager.current = 'home_window'
self.manager.get_screen('home_window').load_home_page()
def insert_new_user(self, fname, lname, uname, pwd1, pwd2):
fname = fname.text
lname = lname.text
email = uname.text
pwd1 = pwd1.text
pwd2 = pwd2.text
regex = '^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$'
valid_str = ""
print(str(re.search(regex, email)))
if fname == "":
valid_str = "First Name is blank"
elif lname == "":
valid_str = "Last Name is blank"
elif email == "":
valid_str = "Email is blank"
elif pwd1 == "":
valid_str = "Password is blank"
elif pwd2 == "":
valid_str = "Confirm Password is blank"
elif pwd1 != pwd2:
valid_str = "Passwords not matching"
elif re.search(regex, email) is None:
valid_str = "Email is not valid"
if valid_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert !',
text=valid_str,
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
encrypted_pwd = encrypt_pwd(pwd1)
query = f'''INSERT INTO CONTACT(FIRST_NAME, LAST_NAME, EMAIL, PASSCODE)
VALUES (UPPER('{fname}'),UPPER('{lname}'),'{email}',{repr(encrypted_pwd)}) '''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
if ibm_db.num_rows(stmt) > 0:
send_registration_mail(email, fname,
lname) #Call function to send mail
self.ids[
'fname'].text = "" #Setting the values to NULL after sucessfull registration
self.ids['lname'].text = ""
self.ids['uname'].text = ""
self.ids['pwd1'].text = ""
self.ids['pwd2'].text = ""
ok_button = MDFlatButton(text='OK',
on_release=self.dialog_close)
self.dialog = MDDialog(
title='Successfully Registered',
text="Please use login screen to login.",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
self.manager.transition.direction = 'right'
self.manager.current = 'login_window'
def run_test_execute_many(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the tabmany table, in case it exists
drop = "DROP TABLE TABMANY"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
#create table tabmany
create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))"
ibm_db.exec_immediate(conn, create)
#Populate the tabmany table with execute_many
insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)"
params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'), (40, 'OBrien'))
stmt_insert = ibm_db.prepare(conn, insert)
ibm_db.execute_many(stmt_insert, params)
#check the number of rows inserted
row_count = ibm_db.num_rows(stmt_insert)
print row_count
# chaeck the inserted columns
select = "SELECT * FROM TABMANY"
stmt_select = ibm_db.exec_immediate(conn, select)
cols = ibm_db.fetch_tuple( stmt_select )
while( cols ):
print "%s, %s" % (cols[0], cols[1])
cols = ibm_db.fetch_tuple( stmt_select )
#populate the tabmany table
params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None) )
try:
ibm_db.execute_many(stmt_insert, params)
except Exception, inst:
#check the no. of inserted rows
row_count = ibm_db.num_rows(stmt_insert)
#check the exception raised by execute_many API
print inst
print row_count
ibm_db.close(conn)
def run_test_011(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals WHERE weight > 10.0")
print ("Number of affected rows: %d" % ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_220(self):
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def execute_statements(conn: str, sql: str):
"""
Execute whatever SQL statements
"""
try:
stmt = ibm_db.exec_immediate(conn, sql)
print("Number of affected rows: ", ibm_db.num_rows(stmt))
except Exception:
print(f"Failed execute query: {ibm_db.stmt_errormsg}")
raise
def execute_sql(self, sql_com):
self.isautapp()
try:
global db
stmt = ibm_db.exec_immediate(db, sql_com)
if stmt:
self.num_row_aff = ibm_db.num_rows(stmt)
return stmt
except Exception as e:
self.funlog().logger.error("database error")
raise (e, None, sys.exc_info()[2])
def run_test_003(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ibm_db.exec_immediate(conn, sql)
print("Number of affected rows: %d" % ibm_db.num_rows(res))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_003(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
sql = 'UPDATE animals SET id = 9'
res = ibm_db.exec_immediate(conn, sql)
print ("Number of affected rows: %d" % ibm_db.num_rows(res))
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_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 int)
print(type(data[1]) is int)
print(type(data[2]) is int)
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_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_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 forgot_password(self, username):
email = username.text
check_str = ""
if email is "":
check_str = "Please enter an Email"
if check_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert',
text=check_str,
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
query = f"""SELECT CONTACT_ID,FIRST_NAME,LAST_NAME FROM CONTACT
WHERE EMAIL='{email}' """
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
userrow = ibm_db.fetch_tuple(stmt)
if userrow != False:
new_pwd = self.get_random_string()
print(new_pwd)
encrypted_pwd = encrypt_pwd(new_pwd)
query = f'''UPDATE CONTACT SET PASSCODE={repr(encrypted_pwd)}
WHERE EMAIL='{email}' '''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
if ibm_db.num_rows(stmt) > 0:
print("Password changed")
forgot_password_mail(email, userrow[1], userrow[2],
new_pwd) #Call function to send mail
self.ids[
'username'].text = "" #Setting the values to NULL after sucessfull registration
self.ids['pwd'].text = ""
ok_button = MDFlatButton(text='OK',
on_release=self.dialog_close)
self.dialog = MDDialog(
title='Password Changed',
text=
"Mail sent with new password. Please change you password after login",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
ok_button = MDFlatButton(text='OK',
on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert',
text="Email is not registered",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
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()
def consultaDB2(sql, conn):
try:
stmt = ibm_db.exec_immediate(conn, sql)
except:
print('Code SQL with problem!\nError Code: {0}'.format(
ibm_db.stmt_error()))
sys.exit()
#print('Number of Fields: {0}'.format(ibm_db.num_fields(stmt)))
print('Number of rows deleted, inserted, or updated: {0}'.format(
ibm_db.num_rows(stmt)))
return stmt
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 update_keyword_name():
name = str(request.form["name"])
keyword = str(request.form["keyword"])
sql = "UPDATE names SET KEYWORDS = ? WHERE NAME=?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, keyword)
ibm_db.bind_param(stmt, 2, name)
result = ibm_db.execute(stmt)
result = ibm_db.num_rows(stmt)
return render_template('Update_Keyword.html', result=result)
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def run_test_221(self):
pconn = list(range(100))
for i in range(100):
pconn[i] = ibm_db.pconnect(config.database, config.user, config.password)
if pconn[33]:
conn = pconn[22]
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0")
print("Number of affected rows:", ibm_db.num_rows( stmt ))
ibm_db.rollback(conn)
ibm_db.close(pconn[33])
else:
print("Connection failed.")
def join_group(self):
query = f'''INSERT INTO CONTACT_ORG(ORG_ID, CONTACT_ID, STATUS, MEMBER_FLAG)
VALUES ({globalvariables.var_org_id},{globalvariables.var_userid}, 'N', 'P')'''
stmt = ibm_db.exec_immediate(connection.conn, query)
if ibm_db.num_rows(stmt) > 0:
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(
title='Group Request Submitted',
text=
"Your group would be available in My Groups when a moderator approves",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
self.manager.transition.direction = 'left'
self.manager.current = 'home_window'
self.manager.get_screen('home_window').load_home_page()
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])
if sys.version_info >= (3, ):
print(type(data[0]) is int)
print(type(data[1]) is int)
print(type(data[2]) is int)
else:
print(type(data[0]) is long)
print(type(data[1]) is long)
print(type(data[2]) is long)
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
def saveInsight(self, snippet, insightJson):
insight = json.loads(insightJson)
print(type(insight))
bigFive = insight['tree']['children'][0]['children'][0]['children'][0]['name']
bigFivePerc = insight['tree']['children'][0]['children'][0]['children'][0]['percentage']
snippet = snippet.replace(",", "")
snippet = snippet.replace(".", "")
if self.sqlConn:
result = ibm_db.exec_immediate(self.sqlConn,"insert into insights (TEXT_SNIPPET, BIG_FIVE, BIG_FIVE_PERCENTAGE) values('"+str(snippet)+"' , '"+str(bigFive)+"' , '"+str(bigFivePerc)+"' )")
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
return rows
else:
print("ERROR: Connection not found")
def execute(self, conn):
try:
stmt = ibm_db.exec_immediate(conn, self.sql)
if ibm_db.num_fields(stmt) > 0:
result = []
dictionary = ibm_db.fetch_assoc(stmt)
print("here")
while dictionary != False:
result.append(dictionary)
dictionary = ibm_db.fetch_assoc(stmt)
return {'result': result, 'message': '1 Query executed.'}, 200
else:
return {'result': [], 'message': str(ibm_db.num_rows(stmt)) + 'rows affected.'}, 200
except Exception as e:
app.logger.error(e)
msg = ibm_db.stmt_errormsg()
if not msg:
msg = str(e)
return {'result': [], 'message': 'Query failed: ' + msg}, 500
def create_activity(self, actname, loc, drop_item, target):
actname = actname.text
loc = loc.text
disaster = drop_item.current_item
target = target.text
valid_str = ""
if actname == "":
valid_str = "Name is blank"
elif loc == "":
valid_str = "Location is blank"
elif target == "":
valid_str = "Target Amount is blank"
if valid_str != "":
ok_button = MDFlatButton(text='OK', on_release=self.dialog_close)
self.dialog = MDDialog(title='Alert !',
text=valid_str,
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
else:
status = "Y"
query1 = f'''INSERT INTO ACTIVITY(ORG_ID, NAME, LOCATION, DISASTER, TARGET_AMT, STATUS)
VALUES ({globalvariables.var_org_id},UPPER('{actname}'),'{loc}','{disaster}',{target}, '{status}')'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query1)
if ibm_db.num_rows(stmt) > 0:
self.ids[
'actname'].text = "" #Setting the values to NULL after sucessfull registration
self.ids['loc'].text = ""
self.ids['drop_item'].text = ""
self.ids['target'].text = ""
self.ids['planned_amount'].text = ""
ok_button = MDFlatButton(text='OK',
on_release=self.dialog_close)
self.dialog = MDDialog(title='Activity Created',
text="Lets start helping!",
size_hint=(0.7, 1),
buttons=[ok_button])
self.dialog.open()
self.manager.transition.direction = 'left'
self.manager.current = 'activity_window'
self.manager.get_screen('activity_window').load_activity_page(
globalvariables.var_org_name)
def modify_name_room_number_name():
name = str(request.form["name"])
room = str(request.form["room"])
if name == "" or room == "":
result = 0
return render_template('modify_name_room_number.html', result=result)
sql = "UPDATE names SET NAME = ? WHERE ROOM=?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, name)
ibm_db.bind_param(stmt, 2, room)
result = ibm_db.execute(stmt)
result = ibm_db.num_rows(stmt)
return render_template('modify_name_room_number.html', result=result)
args_list[2])
statement = ibm_db.exec_immediate(conn, full_sql)
break
elif len(args_list) == 2:
for row_item in mon_list:
if args_list[0] == row_item[0] and args_list[1] == row_item[4]:
sql = """update MONITOR.DB2MON_MONITOR set DB_MON_VALUE = '%s',
DB_MON_TIME = current timestamp where DB_NODE_ID = '%s'
and DB_MON_SP_NAME = '%s'"""
full_sql = sql % (actual_value, args_list[0], args_list[1])
statement = ibm_db.exec_immediate(conn, full_sql)
break
# store the status for debugging
if statement:
num_of_updates = ibm_db.num_rows(statement)
if num_of_updates > 0:
update_status = 'success'
else:
update_status = 'failed'
ibm_db.close(conn)
return
def CheckValidArgs(mon_list, args_list):
"""Verify the arguments passed are something valid to check.
Args:
mon_list: A list of tuples containing monitoring values and thresholds.
args_list: A list of arugments passed to nagios.
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
