def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
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 )
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print ("Connection failed.")
def main(args):
#Connect to the DB2 database
conn = ibm_db.connect("DATABASE=TEST;HOSTNAME=172.17.0.1;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=db2inst1-pwd;", "", "")
cmd = "DELETE FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
#output of the above can succeed or fail. Either is fine.
# Insert into TestTable
cmd = "INSERT INTO TESTTABLE (NAME, AGE, LOCATION) VALUES ('Angela', 27, 'Texas')"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
# Select from TestTable (the row just inserted)
cmd = "SELECT * FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
else:
ibm_db.fetch_both(result,0)
value = ibm_db.result(result,"NAME")
# Make sure the row was correctly inserted
if value != 'Angela' :
return {"err":"Expected name 'Angela', but instead found: "+ value}
# Delete the row from TestTable
cmd = "DELETE FROM TESTTABLE WHERE name='Angela'"
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"err":"error :"+cmd}
# If no detected errors occurred so far; return Success status
return {"message":"Tested db2 create, select, and delete of a table row."}
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
def load_group_page(self):
ContentNavigationDrawer.populateNavDrawerValues(self)
self.ids['allorgscroll'].clear_widgets() #To clear list of orgs
query = f'''SELECT ORG_ID,NAME,REGISTRATION FROM ORG WHERE
ORG_ID IN (
SELECT ORG_ID FROM ORG
MINUS
SELECT ORG_ID FROM CONTACT_ORG WHERE CONTACT_ID = {globalvariables.var_userid}
AND STATUS='Y')'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
orglist = ibm_db.fetch_both(stmt)
item = TwoLineAvatarListItem()
while (orglist):
file_found = download_org_image(str(orglist[0]))
if file_found == True:
image = ImageLeftWidget(source="resources/org/" +
str(orglist[0]) + ".png")
else:
image = ImageLeftWidget(source="resources/org/default.jpg")
item = TwoLineAvatarListItem(text=str(orglist[1]),
secondary_text=str(orglist[2]))
item.add_widget(image)
item.bind(on_release=self.row_press)
self.ids['allorgscroll'].add_widget(item)
orglist = ibm_db.fetch_both(
stmt) #for incrementing rows inside while loop
def run_test_162(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)
result = ibm_db.exec_immediate(
conn, "select * from emp_act order by projno")
row = ibm_db.fetch_both(result)
# will only retrieve 10 records
count = 1
while (row):
print(
"Record ", count, ": %6s %-6s %3d %9s %10s %10s %6s " %
(row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'],
row['EMENDATE'], row[0]))
result2 = ibm_db.exec_immediate(
conn, "select * from employee where employee.empno='" +
row['EMPNO'] + "'")
row2 = ibm_db.fetch_both(result2)
if row2:
print(">>%s,%s,%s,%s,%s,%s,%s" %
(row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'],
row2[3], row2[3], row2[5], row2[6]))
count = count + 1
if (count > 10):
break
row = ibm_db.fetch_both(result)
def getDatabaseContentsJson():
select_statement = 'SELECT * FROM RVB49192.ORDERS ORDER BY ID desc;'
try:
res = ibm_db.exec_immediate(conn, select_statement)
result = ibm_db.fetch_both(res)
resultDict = []
while (result):
returnDictBuffer = {
'ID': result['ID'],
'NAME': result['NAME'],
'PHONE': result['PHONE'],
'ORDERS': result['ORDERS'],
'ADDRESS': result['ADDRESS']
}
resultDict.append(returnDictBuffer)
result = ibm_db.fetch_both(res)
return jsonify(resultDict)
except:
resultDict = []
returnDictBuffer = {
'ID': 0,
'NAME': 'db2 not connected!',
'PHONE': '',
'ORDERS': '',
'ADDRESS': ''
}
resultDict.append(returnDictBuffer)
return jsonify(resultDict)
def load_page(self, greg):
ContentNavigationDrawer.populateNavDrawerValues(self)
query = "SELECT ORG_ID,NAME,REGISTRATION,DESC FROM ORG WHERE REGISTRATION='" + greg + "'"
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
userrow = ibm_db.fetch_tuple(stmt)
org_id = ""
org_name = ""
org_reg = ""
org_desc = ""
if userrow != False:
org_id = userrow[0]
org_name = userrow[1]
org_reg = userrow[2]
org_desc = userrow[3]
globalvariables.var_org_id = org_id
self.ids['gname'].text = org_name
self.ids['greg'].text = org_reg
self.ids['gdesc'].text = org_desc
#USED TO POPULATE DROP DOWN OF ACTIVITY FOR PAYMENTS FOR NON JOINERS
query = "SELECT ACTIVITY_ID,NAME FROM ACTIVITY WHERE ORG_ID=" + str(
globalvariables.var_org_id)
stmt = ibm_db.exec_immediate(connection.conn, query)
act = ibm_db.fetch_both(stmt)
actlist = []
while (act):
actlist.append(str(act[1]))
act = ibm_db.fetch_both(stmt)
self.menu = MDDropdownMenu(caller=self.ids['activity_item'],
position="center",
width_mult=5,
callback=self.set_item)
for i in actlist:
self.menu.items.append({"text": str(i)})
def within_distance():
value = []
lat = request.form.get("lat")
long = request.form.get("long")
dist = request.form.get("dist")
query = f"SELECT * FROM(\
SELECT *,\
(\
(\
(\
acos(\
sin(( {lat} * 0.01745329251 ))\
*\
sin(( latitude * 0.01745329251 )) + cos(( {lat} *0.01745329251 ))\
*\
cos(( latitude * 0.01745329251)) * cos((( {long} - longitude) * 0.01745329251)))\
) * 57.2957795131\
) * 60 * 1.1515 * 1.609344\
)\
as distance FROM earthquake)\
where distance <= {dist} order by distance asc"
stmt1 = ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt1)
while result:
value.append(result)
result = ibm_db.fetch_both(stmt1)
return render_template('within-distance.html',
search=value,
lat=lat,
long=long,
dist=dist)
def magnitude():
value = []
lat = request.form.get("lat")
long = request.form.get("long")
query = f"SELECT *,\
(\
(\
(\
acos(\
sin(( {lat} * 0.01745329251 ))\
*\
sin(( latitude * 0.01745329251 )) + cos(( {lat} *0.01745329251 ))\
*\
cos(( latitude * 0.01745329251)) * cos((( {long} - longitude) * 0.01745329251)))\
) * 57.2957795131\
) * 60 * 1.1515\
)\
as distance FROM earthquake where mag>6 order by distance asc limit 1"
stmt1 = ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt1)
while result:
value.append(result)
result = ibm_db.fetch_both(stmt1)
return render_template('magnitude.html', search=value, lat=lat, long=long)
def latlong():
value = []
lat1 = float(request.form.get("lat1"))
long1 = float(request.form.get("long1"))
lat2 = float(request.form.get("lat2"))
long2 = float(request.form.get("long2"))
#Set min. latitude
if lat1 < lat2:
min_lat = lat1
max_lat = lat2
else:
min_lat = lat2
max_lat = lat1
#Set min. longitude
if long1 < long2:
min_long = long1
max_long = long2
else:
min_long = long2
max_long = long1
query = f"SELECT * FROM LATLONG WHERE LATITUDE BETWEEN {min_lat} and {max_lat} AND LONGITUDE BETWEEN {min_long} and {max_long}"
stmt1 = ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt1)
while result:
value.append(result)
result = ibm_db.fetch_both(stmt1)
return render_template('latlong-results.html', search=value)
def main(args):
ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"]
conn = ibm_db.connect(ssldsn, "", "")
if conn:
print("Connection succeeded.")
else:
print("Connection failed.")
return {"error": "Error connecting to db2"}
# Select from TestTable (the row just inserted).
#
# Due to the db2 lite plan we use, the schema name is fixed to be
# the same as the username. We cut the value of the UID field
# in the ssldsn to get it and insert it in the select statement.
# The ssldsn looks like this:
# ssldsn="DATABASE=BLUDB;HOSTNAME=dashdb-xxxx.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=yyyyyyy;PWD=<hidden>;"
#
ssldsndict = dict(x.split("=") for x in ssldsn.rstrip(";").split(";"))
print("user={}".format(ssldsndict["UID"]))
cmd = "SELECT HISP_DESC FROM {}.HISPANIC_ORIGIN WHERE HISP_CODE='03'".format(
ssldsndict["UID"])
result = ibm_db.exec_immediate(conn, cmd)
if not result:
return {"error": "error :" + cmd}
else:
ibm_db.fetch_both(result, 0)
value = ibm_db.result(result, "HISP_DESC")
ibm_db.close(conn)
return {"HISP_DESC": value}
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 tasktwo():
lati1 = str(request.form['number1'])
longi1 = str(request.form['number2'])
lati2 = str(request.form['number3'])
longi2 = str(request.form['number4'])
value = str(request.form['number5'])
nir_dsn_hostname = "dashdb-txn-sbox-yp-dal09-11.services.dal.bluemix.net"
nir_dsn_uid = "mfm86030"
nir_dsn_pwd = "s17-fnd9c6p060xm"
nir_dsn_driver = "{IBM DB2 ODBC DRIVER}"
nir_dsn_database = "BLUDB"
nir_dsn_protocol = "TCPIP"
nir_dsn_port = "50000"
dsn_nir = ("DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(nir_dsn_driver, nir_dsn_database,
nir_dsn_hostname, nir_dsn_port,
nir_dsn_protocol, nir_dsn_uid, nir_dsn_pwd)
try:
conn = ibm_db.connect(dsn_nir, "", "")
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
server = ibm_db.server_info(conn)
sql = "SELECT COUNT(*) AS ct FROM EARTHQUAKE_TABLEQ WHERE (LATITUDE>=" + lati1 + " AND LATITUDE<=" + lati2 + ") AND (LONGITUDE>=" + longi1 + " AND LONGITUDE<= " + longi2 + ")"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
demo2 = []
while dictionary != False:
demo2.append(dictionary[0])
dictionary = ibm_db.fetch_both(stmt)
return render_template('tasktwo.html', demo2=demo2, value=value)
def load_org_list(self):
self.ids['scroll'].clear_widgets() #To clear list of orgs
query = f'''SELECT ORG_ID,NAME,REGISTRATION FROM ORG WHERE
ORG_ID IN (SELECT ORG_ID FROM CONTACT_ORG WHERE CONTACT_ID={globalvariables.var_userid}
AND MEMBER_FLAG='Y' AND STATUS='Y')'''
# run direct SQL
stmt = ibm_db.exec_immediate(connection.conn, query)
orglist = ibm_db.fetch_both(stmt)
item = TwoLineAvatarListItem()
if orglist is False:
self.ids['nogroupwarning'].pos_hint = {"x": 0.2, "y": 0.3}
self.ids['nogroupwarning'].font_size = '20sp'
self.ids[
'nogroupwarning'].text = """You have not joined any groups.
Your joined groups will show up here"""
else:
while (orglist):
file_found = download_org_image(str(orglist[0]))
if file_found == True:
image = ImageLeftWidget(source="resources/org/" +
str(orglist[0]) + ".png")
else:
image = ImageLeftWidget(source="resources/org/default.jpg")
item = TwoLineAvatarListItem(text=str(orglist[1]),
secondary_text=str(orglist[2]))
item.add_widget(image)
item.bind(on_release=self.row_press)
self.ids['scroll'].add_widget(item)
orglist = ibm_db.fetch_both(
stmt) #for incrementing rows inside while loop
def getDatabaseContentsJson():
try:
conn = ibm_db.connect(dsn, "", "")
except:
pass
select_statement = 'SELECT * FROM {0}.ORDERS ORDER BY ID desc;'.format(
dsn_uid)
res = ibm_db.exec_immediate(conn, select_statement)
result = ibm_db.fetch_both(res)
resultDict = []
while (result):
returnDictBuffer = {
'ID': result['ID'],
'COGNOME': result['COGNOME'],
'MODELLO': result['MODELLO'],
'VIN': result['VIN'],
'KMTOTALI': result['KMTOTALI'],
'TIPODISTRADA': result['TIPODISTRADA'],
'RIFORNIMENTO': result['RIFORNIMENTO'],
'LUOGO': result['LUOGO'],
'LITRI': result['LITRI'],
'SEGNALAZIONE': result['SEGNALAZIONE']
}
resultDict.append(returnDictBuffer)
result = ibm_db.fetch_both(res)
return jsonify(resultDict)
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
def run_test_007(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.pconnect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
print("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print(data[0].strip())
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def apply_on_row(query, f):
stmt = ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt)
while (result):
f(result)
result = ibm_db.fetch_both(stmt)
def jobInfo(request):
conn = ibm_db.connect(
"DATABASE=metadata;HOSTNAME=172.16.11.170;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;",
"", "")
if conn:
sql = "select JOB_SEQ_ID,JOB_STS,JOB_NM,DATA_PRD,ACT_ST_DT,ACT_ST_TM,ACT_ED_DT,ACT_ED_TM,JOB_LG,RW_RD,RW_IST,RW_UPT from dwmm.job_log"
statement = ibm_db.exec_immediate(conn, sql)
#print(type(stmt))
#print(stmt)
result = ibm_db.fetch_both(statement)
print(type(result))
print(type(ibm_db.fetch_both(statement)))
while (result):
#
print(result[0], result[1], result[2], result[3], result[4],
result[5], result[6], result[7], result[8], result[9],
result[10], result[11])
#result = ibm_db.fetch_both(stmt)
#for i in range(len(result)):
#recordDir[stmt[i][2]]=stmt[i][4]
#print(recordDir[stmt[i][2]])
result = ibm_db.fetch_both(statement)
print(result)
ibm_db.close(conn)
return render(request, '/jobInfo/', {'data': result})
def run_test_006(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
print ("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_191(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,config.user,"emp_photo");
else:
result = ibm_db.columns(conn,None,None,"EMP_PHOTO");
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ( (row['column_name'] != 'emp_rowid') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['table_schem'],
row['table_name'], row['column_name'], row['is_nullable']))
else :
if ( (row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3) ):
print("%s,%s,%s,%s\n" % (row['TABLE_SCHEM'],
row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE']))
i = i + 1
row = ibm_db.fetch_both(result)
print("done!")
else:
print("no connection: ", ibm_db.conn_errormsg())
def print_contents(conn,sql):
print "SQL: ",sql
stmt = ibm_db.exec_immediate(conn,sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
print "ITEM: ",dictionary[0]
dictionary = ibm_db.fetch_both(stmt)
def injuryAmong(Longtitude, Latitude, Range=0.05): # key:[value1, value2, ...]
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=tpj29337;PWD=8kzn@v6p7wlb4r75;Security=SSL;",
"", "")
try:
sql_find = "SELECT * from Injury WHERE " \
"(POWER(longtitude - %f,2) + POWER(Latitude - %f,2)) <= POWER(%f, 2) LIMIT 100" \
%(Longtitude, Latitude, Range)
res = ibm_db.exec_immediate(conn, sql_find)
dict = {}
result = ibm_db.fetch_both(res)
for key in result.keys():
if type(key) != int:
dict[key] = [result[key]]
while result != False:
for key in result.keys():
if type(key) != int:
dict[key].append(result[key])
result = ibm_db.fetch_both(res)
ibm_db.close(conn)
return dict
except:
ibm_db.close(conn)
return -1
def run_test_191(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, config.user, "emp_photo")
else:
result = ibm_db.columns(conn, None, None, "EMP_PHOTO")
i = 0
row = ibm_db.fetch_both(result)
while (row):
if (server.DBMS_NAME[0:3] == 'IDS'):
if ((row['column_name'] != 'emp_rowid') and (i < 3)):
print("%s,%s,%s,%s\n" %
(row['table_schem'], row['table_name'],
row['column_name'], row['is_nullable']))
else:
if ((row['COLUMN_NAME'] != 'EMP_ROWID') and (i < 3)):
print("%s,%s,%s,%s\n" %
(row['TABLE_SCHEM'], row['TABLE_NAME'],
row['COLUMN_NAME'], row['IS_NULLABLE']))
i = i + 1
row = ibm_db.fetch_both(result)
print("done!")
else:
print("no connection: ", ibm_db.conn_errormsg())
def db_search(self, param):
timestamp = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
level = 'LEV' + '%s' % param['level']
user = g.user.uid
if user == 'admin':
source = 'Atas'
sql = "SELECT LEV10,LEV15,LEV17,LEV20,LEV30 from CMRDC.PRODUCTS_IMAGE WHERE %s = '%s'" % (
level, param['prod_id'])
stmt = ibm_db.exec_immediate(self.conn, sql)
results = ibm_db.fetch_both(stmt)
while results:
level10 = results[0]
level15 = results[1]
level17 = results[2]
level20 = results[3]
level30 = results[4]
results = ibm_db.fetch_both(stmt)
try:
insert_sql = "INSERT INTO CMRDC.LINEITEM(RLI_ID, MPP_NUMBER, VERSION, COUNTRY, DELETED, LEVEL10, LEVEL15, LEVEL17, LEVEL20, LEVEL30, SOURCE, DATE_ENTERED, DATE_MODIFIED) VALUES('%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%s','%s')" % (
param['rli_id'], param['account_mpp'], ESUtil().rule_index,
param['account_country'], param['deleted'], level10, level15,
level17, level20, level30, source, timestamp, timestamp)
ibm_db.exec_immediate(self.conn, insert_sql)
ibm_db.commit(self.conn)
except Exception as ex:
ibm_db.rollback(self.conn)
finally:
ibm_db.close(self.conn)
def process(self):
tSleep = self._plugin.get("config", "sleep")
if not self._canrun:
logger.info("We can't start the process,needed modules")
return
logger.info("Starting Database plugin")
rules = self._plugin.rules()
run_process = False
# logger.info(rules['start_query']['query'])
# if rules['start_query']['query']
if not self.tryConnectDB():
self.stop()
return
cVal = 0
plugin_source_type = self._plugin.get("config", "source_type")
while cVal == 0 and not self.stop_processing:
cVal = self.runStartQuery(plugin_source_type, rules)
if cVal <= 0:
logger.info("Waiting for next pooling...no data")
time.sleep(10)
else:
run_process = True
ref = int(rules["query"]["ref"])
while run_process and not self.stop_processing:
if self._plugin.get("config", "source_type") != "db2":
sql = rules["query"]["query"]
sql = sql.replace("$1", str(cVal))
logger.debug(sql)
self.__myDataBaseCursor.execute(sql)
ret = self.__myDataBaseCursor.fetchall()
if len(ret) > 0:
# We have to think about event order when processing
cVal = ret[len(ret) - 1][ref]
for e in ret:
# pdb.set_trace()
self.generate(e)
# cursor.close()
time.sleep(int(tSleep))
else:
sql = rules["query"]["query"]
sql = sql.replace("$1", str(cVal))
logger.debug(sql)
result = ibm_db.exec_immediate(self.__myDataBaseCursor, sql)
dictionary = ibm_db.fetch_both(result)
# print dictionary
ret = []
while dictionary != False:
ret1 = []
for i in dictionary.keys():
ret1.append(dictionary[i])
ret.append(ret1)
dictionary = ibm_db.fetch_both(result)
cVal = ret[len(ret) - 1][ref]
for e in ret:
logger.info("-.-->", e)
self.generate(e)
time.sleep(int(tSleep))
def process(self):
tSleep = self._plugin.get("config", "sleep")
if not self._canrun:
logger.info("We can't start the process,needed modules")
return
logger.info("Starting Database plugin")
rules = self._plugin.rules()
run_process = False
#logger.info(rules['start_query']['query'])
#if rules['start_query']['query']
if not self.tryConnectDB():
self.stop()
return
cVal = 0
plugin_source_type = self._plugin.get("config", "source_type")
while cVal == 0 and not self.stop_processing:
cVal = self.runStartQuery(plugin_source_type, rules)
if cVal <= 0:
logger.info("Waiting for next pooling...no data")
time.sleep(10)
else:
run_process = True
ref = int(rules['query']['ref'])
while run_process and not self.stop_processing:
if self._plugin.get("config", "source_type") != "db2":
sql = rules['query']['query']
sql = sql.replace("$1", str(cVal))
logger.debug(sql)
self.__myDataBaseCursor.execute(sql)
ret = self.__myDataBaseCursor.fetchall()
if len(ret) > 0:
#We have to think about event order when processing
cVal = ret[len(ret) - 1][ref]
for e in ret:
#pdb.set_trace()
self.generate(e)
#cursor.close()
time.sleep(int(tSleep))
else:
sql = rules['query']['query']
sql = sql.replace("$1", str(cVal))
logger.debug(sql)
result = ibm_db.exec_immediate(self.__myDataBaseCursor, sql)
dictionary = ibm_db.fetch_both(result)
#print dictionary
ret = []
while dictionary != False:
ret1 = []
for i in dictionary.keys():
ret1.append(dictionary[i])
ret.append(ret1)
dictionary = ibm_db.fetch_both(result)
cVal = ret[len(ret) - 1][ref]
for e in ret:
logger.info("-.-->", e)
self.generate(e)
time.sleep(int(tSleep))
def getResByDict(conn, sql):
''' Call fetch_both: '''
print sql
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_both(stmt)
while(res):
print 'Result from :', res[1], res
res = db2.fetch_both(stmt)
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while (row):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print str
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_062(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
schema = 't'
else:
schema = 'T'
result = ibm_db.tables(conn,None,schema);
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row[1] + "/" + row[2] + "/" + row[3]
if (i < 4):
print str
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: #{ibm_db.conn_errormsg}";
def run_test_061(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
result = ibm_db.tables(conn, None, 'T');
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print str
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ibm_db.conn_errormsg()
def run_test_062(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
schema = 't'
else:
schema = 'T'
result = ibm_db.tables(conn,None,schema);
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
str = row[1] + "/" + row[2] + "/" + row[3]
if (i < 4):
print(str)
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: #{ibm_db.conn_errormsg}");
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 getdata():
listofdata = []
query_1 = "SELECT * FROM PEOPLE1"
stmt_1 = ibm_db.exec_immediate(conn, query_1)
result1 = ibm_db.fetch_both(stmt_1)
while result1:
listofdata.append(result1)
result1 = ibm_db.fetch_both(stmt_1)
return render_template('main.html', table=listofdata)
def getdata():
listofdata = []
query1 = "SELECT * FROM DATA"
stmt = ibm_db.exec_immediate(conn, query1)
result = ibm_db.fetch_both(stmt)
while result:
listofdata.append(result)
result = ibm_db.fetch_both(stmt)
return render_template('main.html', table=listofdata)
def run_test_060(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while (row):
if (i < 4):
print("/%s/%s" % (row[1], row[2]))
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: #{ibm_db.conn_errormsg}")
def run_test_060(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = "CREATE SCHEMA AUTHORIZATION t"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = "CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))"
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
if server.DBMS_NAME[0:3] == "IDS":
result = ibm_db.tables(conn, None, "t")
else:
result = ibm_db.tables(conn, None, "T")
i = 0
row = ibm_db.fetch_both(result)
while row:
if i < 4:
print "/%s/%s" % (row[1], row[2])
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, "DROP TABLE t.t1")
ibm_db.exec_immediate(conn, "DROP TABLE t.t2")
ibm_db.exec_immediate(conn, "DROP TABLE t.t3")
ibm_db.exec_immediate(conn, "DROP TABLE t.t4")
print "done!"
else:
print "no connection: #{ibm_db.conn_errormsg}"
def chk_table(self, conn, schema, tabname):
tables = []
tables_set = ibm_db.tables(conn, None, schema)
result = ibm_db.fetch_both(tables_set)
while (result):
tables.append(result[2])
result = ibm_db.fetch_both(tables_set)
if tabname in tables:
return True
return False
def cdctable(cdcdo):
if cdcdo == 'archive' :
print('set tables to archive')
sql = "UPDATE " + ASN_SCHEMA + ".IBMSNAP_REGISTER SET STATE = 'A' WHERE SOURCE_OWNER <> '' "
stmt = ibm_db.exec_immediate(conn, sql)
else:
sql = "SELECT SOURCE_OWNER,SOURCE_TABLE, STATE FROM " + ASN_SCHEMA + ".IBMSNAP_REGISTER WHERE SOURCE_OWNER <> '' "
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
print(dictionary["SOURCE_OWNER"] + "." + dictionary["SOURCE_TABLE"] + " " + dictionary["STATE"])
dictionary = ibm_db.fetch_both(stmt)
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_060(self):
conn = ibm_db.connect(config.database, config.user, config.password)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if conn:
result = ibm_db.tables(conn, None, 'T')
i = 0
row = ibm_db.fetch_both(result)
while ( row ):
if (i < 4):
print "/%s/%s" % (row[1], row[2])
i = i + 1
row = ibm_db.fetch_both(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: #{ibm_db.conn_errormsg}";
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 * from emp"
if conn:
result = ibm_db.exec_immediate(conn, sql)
row = ibm_db.fetch_both(result)
print ('%s | %s | %s | %s | %s | %s | %s ' % ('EMPNO', 'FIRSTNME', 'MIDINIT', 'LASTNAME', 'HIREDATE', 'JOB', 'SALARY'))
while ( row ):
print ('%s | %s | %s | %s | %s | %s | %s ' % (row[0], row[1], row[2], row['LASTNAME'], row['HIREDATE'], row['JOB'], row['SALARY']))
row = ibm_db.fetch_both(result)
def runStartQuery(self, plugin_source_type, rules):
cVal = "NA"
if self.__myDataBaseCursor is None:
return cVal
try:
if plugin_source_type != "db2":
sql = rules['start_query']['query']
logger.debug("Running Start query: %s" % sql)
self.__myDataBaseCursor.execute(sql)
rows = self.__myDataBaseCursor.fetchone()
if not rows:
logger.warning("Initial query empty, please double-check")
return cVal
cVal = str((rows[0]))
sql = rules['query']['query']
elif plugin_source_type == "db2":
sql = rules['start_query']['query']
logger.debug("Start query: %s" % sql)
result = ibm_db.exec_immediate(self.__objDBConn, sql)
dictionary = ibm_db.fetch_both(result)
if not dictionary:
logger.warning("Initial query empty, please double-check")
return cVal
cVal = str((dictionary[0]))
logger.info("Connection closed")
if cVal==None or cVal =="None" or len(cVal)<=0:
cVal="NA"
except Exception, e:
cVal ="NA"
logger.error("Error running the start query: %s" % str(e))
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 executeQuery(self, con, query):
try:
stmt = ibm_db.exec_immediate(con, query)
result = ibm_db.fetch_both(stmt)
return result
except Exception as message:
pass
def run_test_012(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0")
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while (data):
print data[0]
data = ibm_db.fetch_both( stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_warn(self):
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
serverinfo = ibm_db.server_info( conn )
if conn:
drop = "DROP TABLE WITH_CLOB"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table with_clob
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
else:
create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
result = ibm_db.exec_immediate(conn, create)
# Select the result from the table. This is just to verify we get appropriate warning using
# ibm_db.stmt_warn() API
query = 'SELECT * FROM WITH_CLOB'
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 )
if data:
print("Success")
else:
print("No Data")
print(ibm_db.stmt_warn(stmt))
ibm_db.close(conn)
else:
print ("Connection failed.")
#__END__
#__LUW_EXPECTED__
#No Data[IBM][CLI Driver][DB2/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__ZOS_EXPECTED__
#No Data[IBM][CLI Driver][DB2]
# SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
#__SYSTEMI_EXPECTED__
#No Data
#__IDS_EXPECTED__
#No Data
#[IBM][CLI Driver][IDS/%s] SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 SQLCODE=100
def run_test_161(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)
result = ibm_db.exec_immediate(conn, "select * from emp_act order by projno desc")
row = ibm_db.fetch_both(result)
count = 1
while ( row ):
print "Record",count,": %6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])
result2 = ibm_db.exec_immediate(conn,"select * from employee where employee.empno='" + row['EMPNO'] + "'")
row2 = ibm_db.fetch_both(result2)
if row2:
print ">>%s,%s,%s,%s,%s,%s,%s" % (row2['EMPNO'], row2['FIRSTNME'],row2['MIDINIT'], row2[3], row2[3], row2[5], row2[6])
count = count + 1
if (count > 10):
break
row = ibm_db.fetch_both(result)
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_160(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, 0)
result = ibm_db.exec_immediate(conn, "select * from emp_act")
row = ibm_db.fetch_both(result)
while ( row ):
#printf("%6s ",row[0])
#printf("%-6s ",row[1])
#printf("%3d ",row[2])
#printf("%9s ",row['EMPTIME'])
#printf("%10s ", row['EMSTDATE'])
#printf("%10s ", row['EMENDATE'])
#printf("%6s ", row[0])
#puts ""
print "%6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])
row = ibm_db.fetch_both(result)
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 run_test_196(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE xml_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE xml_test (id INTEGER, data VARCHAR(50), xmlcol XML)")
rc = ibm_db.exec_immediate(conn, "INSERT INTO xml_test (id, data, xmlcol) values (1, 'xml test 1', '<address><street>12485 S Pine St.</street><city>Olathe</city><state>KS</state><zip>66061</zip></address>')")
sql = "SELECT * FROM xml_test"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result ID:", result[0])
print("Result DATA:", result[1])
print("Result XMLCOL:", result[2])
result = ibm_db.fetch_both(stmt)
sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") AS CLOB(32k)) FROM xml_test c WHERE id = 1"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result from XMLSerialize and XMLQuery:", result[0])
result = ibm_db.fetch_both(stmt)
sql = "select xmlquery('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") from xml_test c where id = 1"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_both(stmt)
while( result ):
print("Result from only XMLQuery:", result[0])
result = ibm_db.fetch_both(stmt)
else:
print('Native XML datatype is not supported.')
dct = ibm_db.fetch_assoc(stmt_hist)
routes[route] = {
'tot_resp': 1,
'cong_resp': -1,
'percentage': 0,
'ratings_sum': post['CONGESTION'],
'est_time': est_time,
'density': density,
'0': 0, '1': 0, '2': 0, '3': 0, '4': 0}
routes[route][str(int(post['CONGESTION']))] = 1
else:
routes[route]['tot_resp']+=1
routes[route]['ratings_sum']+=post['CONGESTION']
routes[route][str(int(post['CONGESTION']))]+=1
post = ibm_db.fetch_both(res)
print "GOT ALL POSTS IN LAST HOUR"
print routes
print
print "CALCULATING AND UPDATING LIVE TRAFFIC CONGESTION INFO..."
# calculate averages for all routes and update live table (and maybe some other count table)
for route in routes:
congestion_av = routes[route]['ratings_sum']/float(routes[route]['tot_resp'])
print "AVERAGE FOR", (route), ":"
print congestion_av
cong = int(round(congestion_av))
cong_resp, tot_resp = routes[route][str(cong)], routes[route]['tot_resp']
p = float(cong_resp)/float(tot_resp)*100.
percentage = int(round(p))
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password, op)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 0)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn,None,None,"employee")
else:
result = ibm_db.columns(conn,None,None,"EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
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
dct = ibm_db.fetch_assoc(stmt_hist)
routes[route] = {
'tot_resp': 1,
'cong_resp': -1,
'percentage': 0,
'ratings_sum': dictionary['CONGESTION'],
'est_time': est_time,
'density': density,
'0': 0, '1': 0, '2': 0, '3': 0, '4': 0}
routes[route][str(int(dictionary['CONGESTION']))] = 1
else:
routes[route]['tot_resp']+=1
routes[route]['ratings_sum']+=dictionary['CONGESTION']
routes[route][str(int(dictionary['CONGESTION']))]+=1
dictionary = ibm_db.fetch_both(res)
print "GOT ALL POSTS IN LAST HOUR"
print routes
print
print "CALCULATING AND UPDATING LIVE TRAFFIC CONGESTION INFO..."
# calculate averages for all routes and update live table (and maybe some other count table)
for route in routes:
congestion_av = routes[route]['ratings_sum']/float(routes[route]['tot_resp'])
print "AVERAGE FOR", (route), ":"
print congestion_av
cong = int(round(congestion_av))
cong_resp, tot_resp = routes[route][str(cong)], routes[route]['tot_resp']
p = float(cong_resp)/float(tot_resp)*100.
percentage = int(round(p))
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 doEverything():
commands = []
# connect to database
conn = ibm_db.connect(url, '', '')
commands.append("Connected to " + url)
# set up variables and data
tableName = "pythonDRDATest"
user1 = DataFormat("test1", 1)
user2 = DataFormat("test2", 2)
user3 = DataFormat("test3", 3)
# 1 Create table
commands.append("\n#1 Create table")
sql = "create table " + tableName + "(name varchar(255), value integer)"
ibm_db.exec_immediate(conn, sql)
commands.append( "\tCreate a table named: " + tableName)
commands.append("\tCreate Table SQL: " + sql)
# 2 Inserts
commands.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
commands.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user1.name)
ibm_db.bind_param(statement, 2, user1.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user1.name + " : " + str(user1.value))
commands.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user2.name)
ibm_db.bind_param(statement, 2, user2.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user2.name + " : " + str(user2.value))
commands.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user3.name)
ibm_db.bind_param(statement, 2, user3.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user3.name + " : " + str(user3.value))
commands.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
# 3 Queries
commands.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
commands.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind document with name: " + user1.name)
commands.append("\tFirst document with name -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
commands.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
commands.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind all documents with name: " + user1.name)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
commands.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind All Documents SQL: " + sql)
# 4 Update documents in a table
commands.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set value = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 4)
ibm_db.bind_param(statement, 2, user2.name)
ibm_db.execute(statement)
commands.append( "\tDocument to update: " + user2.name)
commands.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
commands.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + user1.name + "'"
ibm_db.exec_immediate(conn, sql)
commands.append("\tDelete documents with name: " + user1.name)
commands.append("\tDelete By name SQL: " + sql)
# 6 Drop a table
commands.append("\n#6 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
commands.append("\tDrop table: " + tableName)
commands.append("\tDrop Table SQL: " + sql)
ibm_db.close(conn)
commands.append("\nConnection closed")
return commands
def run_test_154(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, 0)
try:
statement = 'DROP TABLE fetch_test'
result = ibm_db.exec_immediate(conn, statement)
except:
pass
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
else:
statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)'
st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
result = ibm_db.exec_immediate(conn, statement)
result = ibm_db.exec_immediate(conn, st0)
result = ibm_db.exec_immediate(conn, st1)
result = ibm_db.exec_immediate(conn, st2)
result = ibm_db.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_tuple(result)
while ( row ):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row[0],row[0].length, row[1],row[1].length)
print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row[0], len(row[0]), row[1], len(row[1]))
row = ibm_db.fetch_tuple(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_assoc(result)
while ( row ):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length)
print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
(row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2']))
row = ibm_db.fetch_assoc(result)
result = ibm_db.prepare(conn, statement)
ibm_db.execute(result)
row = ibm_db.fetch_both(result)
while ( row ):
#printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
# row['COL1'], row['COL1'].length, row[1], row[1].length)
print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \
(row['COL1'],len(row['COL1']), row[1], len(row[1]))
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
