def run_test_240(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + str(ibm_db.field_width(result, i)))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(
str(i) + ":" +
str(ibm_db.field_width(result2, ibm_db.field_name(result2, i)))
)
print("\n-----")
for i in range(0, 3):
print(
str(i) + ":" + str(ibm_db.field_width(result3, i)) + "," +
str(ibm_db.field_display_size(result3, i)))
print("\n-----")
print("region:%s" % ibm_db.field_type(result, 'region'))
print("5:%s" % ibm_db.field_type(result2, 5))
def run_test_231(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result) + 1):
print str(i) + ":" + str(
ibm_db.field_type(result, ibm_db.field_name(result, i)))
print "\n-----"
for i in range(0, ibm_db.num_fields(result2)):
print str(i) + ":" + ibm_db.field_type(
result2, ibm_db.field_name(result2, i))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + ibm_db.field_type(
result3, ibm_db.field_name(result3, i))
print "\n-----"
print "region:%s" % ibm_db.field_type(result, 'region')
print "5:%s" % ibm_db.field_type(result2, 5)
def run_test_checkBooleanType(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info( conn )
if( not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn,"DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(conn, "CREATE TABLE bool_test(col1 INTEGER, col2 BOOLEAN, col3 BOOLEAN)")
except Exception as e:
print("Error : {}\n".format(str(e)))
exit(-1)
try:
select_sql = 'SELECT * FROM bool_test'
stmt = ibm_db.exec_immediate(conn, select_sql)
for i in range(0,ibm_db.num_fields(stmt)):
print(str(ibm_db.field_type(stmt,i)))
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
def run_test_232(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
for i in range(0, ibm_db.num_fields(result) + 1):
field_name = ibm_db.field_name(result,i)
field_type = ibm_db.field_type(result, ibm_db.field_name(result,i))
print(str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i))))
print("-----")
t = ibm_db.field_type(result,99)
print(t)
t1 = ibm_db.field_type(result, "HELMUT")
print(t1)
def run_test_232(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
for i in range(0, ibm_db.num_fields(result) + 1):
field_name = ibm_db.field_name(result,i)
field_type = ibm_db.field_type(result, ibm_db.field_name(result,i))
print str(ibm_db.field_name(result, i)) + ":" + str(ibm_db.field_type(result, ibm_db.field_name(result, i)))
print "-----"
t = ibm_db.field_type(result,99)
print t
t1 = ibm_db.field_type(result, "HELMUT")
print t1
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))
if (server.DBMS_NAME.startswith('DB2/')):
d_val = datetime.date(2019, 10, 16)
ts_val = datetime.datetime(2019, 10, 16, 23, 0, 0, 0)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values ('24:00:00', ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (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 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 getColumns(stmt):
columns = []
types = []
colcount = 0
try:
colname = ibm_db.field_name(stmt, colcount)
coltype = ibm_db.field_type(stmt, colcount)
while (colname != False):
columns.append(colname)
types.append(coltype)
colcount += 1
colname = ibm_db.field_name(stmt, colcount)
coltype = ibm_db.field_type(stmt, colcount)
return columns, types
except Exception as err:
db2_error()
return None
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.")
def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = ibm_db.prepare(conn, statement)
result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = ibm_db.exec_immediate(conn, statement)
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + ibm_db.field_type(result,i))
statement = "SELECT * FROM tab_datetime"
stmt = ibm_db.prepare(conn, statement)
rc = ibm_db.execute(stmt)
result = ibm_db.fetch_row(stmt)
while ( result ):
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
row2 = ibm_db.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_240(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result)):
print(str(i) + ":" + str(ibm_db.field_width(result,i)))
print("\n-----")
for i in range(0, ibm_db.num_fields(result2)):
print(str(i) + ":" + str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))))
print("\n-----")
for i in range(0, 3):
print(str(i) + ":" + str(ibm_db.field_width(result3,i)) + "," + str(ibm_db.field_display_size(result3,i)))
print("\n-----")
print("region:%s" % ibm_db.field_type(result,'region'))
print("5:%s" % ibm_db.field_type(result2,5))
def run_test_230(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
result2 = ibm_db.exec_immediate(conn, "select * from staff")
result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
for i in range(0, ibm_db.num_fields(result) + 1):
print str(i) + ":" + str(ibm_db.field_type(result,i))
print "\n-----"
for i in range(0, ibm_db.num_fields(result2)):
print str(i) + ":" + str(ibm_db.field_type(result2,i))
print "\n-----"
for i in range(0, 3):
print str(i) + ":" + str(ibm_db.field_type(result3,i))
print "\n-----"
print "region:%s" % str(ibm_db.field_type(result,'region'))
print "5:%s" % str(ibm_db.field_type(result2,5))
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 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 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_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
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Result set information:\n")
print("COLUMN NAME DATA TYPE")
print("___________ _________")
# As Long As There Is Column Information, ...
for loopCounter in range(0, numColumns):
# Get The Name Of The Current Column
colName = ibm_db.field_name(resultSet, loopCounter)
# Get The Data Type Of The Current Column
colDataType = ibm_db.field_type(resultSet, loopCounter)
# Format And Display The Data Retrieved - Show Data Types In Upper Case
if (not colName is False) and (not colDataType is False):
print("{:<13} {:>7}".format(colName, colDataType.upper()))
# Add A Blank Line To The End Of The Report
print()
# Close The Database Connection That Was Opened Earlier
conn.closeConnection()
# Return Control To The Operating System
exit()
