def run_test_124(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = IfxPy.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 = IfxPy.num_fields(result)
j = 0
row = IfxPy.fetch_both(result)
while (row):
for i in range(0, cols):
field = IfxPy.field_name(result, i)
value = row[IfxPy.field_name(result, i)]
if (value == None):
value = ''
print "%s:%s" % (field, value)
print "---------"
j += 1
if (j == 10):
break
row = IfxPy.fetch_both(result)
IfxPy.close(conn)
print "done"
else:
print IfxPy.conn_errormsg()
def run_test_092(self):
try:
conn = IfxPy.connect(config.ConnStr, config.user, "z")
print("??? No way.")
except:
err = IfxPy.conn_errormsg()
print(err[0:68])
def run_test_071(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
rc = IfxPy.close(conn)
if (rc == True):
print "IfxPy.close succeeded"
else:
print "IfxPy.close FAILED\n"
else:
print "%s" % IfxPy.conn_errormsg()
print ",sqlstate=%s" % IfxPy.conn_error()
print "%s" % IfxPy.conn_errormsg()
print "%s" % IfxPy.conn_errormsg()
print "%s" % IfxPy.conn_errormsg()
print "%s" % IfxPy.conn_errormsg()
def run_test_015(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = IfxPy.exec_immediate(
conn, "insert into t_string values(123,1.222333,'one to one')")
if result:
cols = IfxPy.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 = IfxPy.num_rows(result)
print "affected row:", rows
else:
print IfxPy.stmt_errormsg()
result = IfxPy.exec_immediate(conn,
"delete from t_string where a=123")
if result:
cols = IfxPy.num_fields(result)
print "col:", cols
rows = IfxPy.num_rows(result)
print "affected row:", rows
else:
print IfxPy.stmt_errormsg()
IfxPy.close(conn)
else:
print "no connection:", IfxPy.conn_errormsg()
def run_test_091(self):
try:
conn = IfxPy.connect(config.ConnStr, "y", config.password)
print("??? No way.")
except:
err = IfxPy.conn_errormsg()
print(err[0:68])
def run_test_018(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON)
if conn:
stmt = IfxPy.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = IfxPy.execute(stmt)
if result:
rows = IfxPy.num_rows(stmt)
print("affected row:", rows)
IfxPy.free_result(stmt)
else:
print(IfxPy.stmt_errormsg())
IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = IfxPy.execute(stmt)
if result:
rows = IfxPy.num_rows(stmt)
print("affected row:", rows)
IfxPy.free_result(stmt)
else:
print(IfxPy.stmt_errormsg())
IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = IfxPy.execute(stmt)
if result:
rows = IfxPy.num_rows(stmt)
print("affected row:", rows)
else:
print(IfxPy.stmt_errormsg())
IfxPy.close(conn)
else:
print("no connection:", IfxPy.conn_errormsg())
def run_test_102(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if (not conn):
print IfxPy.conn_errormsg()
server = IfxPy.server_info( conn )
if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2") and (server.DBMS_NAME[0:3] != "Inf")):
result = IfxPy.exec_immediate(conn, "VALUES(1)")
#throw :unsupported unless result
if (not result):
raise Exception('Unsupported')
print IfxPy.num_fields(result)
else:
print '1'
IfxPy.close(conn)
def run_test_197(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info( conn )
if conn:
try:
rc = IfxPy.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = IfxPy.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = IfxPy.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = IfxPy.statistics(conn,None,config.user,"index_test",True)
else:
result = IfxPy.statistics(conn,None,None,"INDEX_TEST",True)
row = IfxPy.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = IfxPy.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
try:
rc = IfxPy.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = IfxPy.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = IfxPy.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = IfxPy.statistics(conn,None,config.user,"index_test2",True)
else:
result = IfxPy.statistics(conn,None,None,"INDEX_TEST2",True)
row = IfxPy.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = IfxPy.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
print "Test non-existent table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = IfxPy.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = IfxPy.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = IfxPy.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + IfxPy.conn_errormsg()
def run_test_061(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = IfxPy.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = IfxPy.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = IfxPy.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = IfxPy.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = IfxPy.exec_immediate(conn, create)
except:
pass
if conn:
server = IfxPy.server_info(conn)
op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER}
IfxPy.set_option(conn, op, 1)
result = IfxPy.tables(conn, None, 't')
i = 0
row = IfxPy.fetch_both(result)
while (row):
str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
if (i < 4):
print(str)
i = i + 1
row = IfxPy.fetch_both(result)
IfxPy.exec_immediate(conn, 'DROP TABLE t.t1')
IfxPy.exec_immediate(conn, 'DROP TABLE t.t2')
IfxPy.exec_immediate(conn, 'DROP TABLE t.t3')
IfxPy.exec_immediate(conn, 'DROP TABLE t.t4')
print("done!")
else:
print("no connection: %s" % IfxPy.conn_errormsg())
def run_test_023(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if (conn != 0):
stmt = IfxPy.column_privileges(conn, None, config.user, 'animals')
row = IfxPy.fetch_tuple(stmt)
if row:
print row[0]
print row[1]
print row[2]
print row[3]
print row[4]
print row[5]
print row[6]
print row[7]
IfxPy.close(conn)
else:
print IfxPy.conn_errormsg()
print "Connection failed\n\n"
def run_test_025(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if (conn != 0):
drop = 'DROP TABLE test_primary_keys'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = IfxPy.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))'
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES (1)"
result = IfxPy.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'Inf'):
stmt = IfxPy.primary_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = IfxPy.primary_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = IfxPy.fetch_tuple(stmt)
print row[2]
print row[3]
print row[4]
IfxPy.close(conn)
else:
print IfxPy.conn_errormsg()
print "Connection failed\n"
def run_test_103(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = IfxPy.exec_immediate(
conn,
"select * from org, project order by project.projname,org.deptnumb"
)
cols = IfxPy.num_fields(result)
j = 1
row = IfxPy.fetch_tuple(result)
while (row):
print "%d) " % j
for i in range(0, cols):
print "%s " % row[i]
j += 1
if (j > 10):
break
row = IfxPy.fetch_tuple(result)
IfxPy.close(conn)
else:
print IfxPy.conn_errormsg()
#__END__
#__IDS_EXPECTED__
#1) 10 Head Office 160 Corporate New York AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#2) 15 New England 50 Eastern Boston AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#3) 20 Mid Atlantic 10 Eastern Washington AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#4) 38 South Atlantic 30 Eastern Atlanta AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#5) 42 Great Lakes 100 Midwest Chicago AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#6) 51 Plains 140 Midwest Dallas AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#7) 66 Pacific 270 Western San Francisco AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#8) 84 Mountain 290 Western Denver AD3113 ACCOUNT PROGRAMMING D21 000270 2.00 1982-01-01 1983-02-01 AD3110
#9) 10 Head Office 160 Corporate New York AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01
#10) 15 New England 50 Eastern Boston AD3100 ADMIN SERVICES D01 000010 6.50 1982-01-01 1983-02-01
def run_test_190(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'Inf'):
result = IfxPy.columns(conn, None, config.user, "employee")
else:
result = IfxPy.columns(conn, None, None, "EMPLOYEE")
row = IfxPy.fetch_tuple(result)
while (row):
str = row[1] + "/" + row[3]
print(str)
row = IfxPy.fetch_tuple(result)
print("done!")
else:
print("no connection:", IfxPy.conn_errormsg())
def run_test_191(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info( conn )
if conn:
result = IfxPy.columns(conn,None,config.user,"emp_photo");
i = 0
row = IfxPy.fetch_both(result)
while ( row ):
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 = IfxPy.fetch_both(result)
print "done!"
else:
print "no connection: ", IfxPy.conn_errormsg()
def run_test_016(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
result = IfxPy.exec_immediate(
conn, "insert into t_string values(123,1.222333,'one to one')")
if result:
cols = IfxPy.num_fields(result)
print("col:", cols)
rows = IfxPy.num_rows(result)
print("affected row:", rows)
else:
print(IfxPy.stmt_errormsg())
result = IfxPy.exec_immediate(conn,
"delete from t_string where a=123")
if result:
cols = IfxPy.num_fields(result)
print("col:", cols)
rows = IfxPy.num_rows(result)
print("affected row:", rows)
else:
print(IfxPy.stmt_errormsg())
IfxPy.close(conn)
else:
print("no connection:", IfxPy.conn_errormsg())
def run_test_024(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = IfxPy.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = IfxPy.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = IfxPy.exec_immediate(conn, statement)
stmt = IfxPy.foreign_keys(conn, None, config.user,
'test_primary_keys')
row = IfxPy.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
stmt = IfxPy.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
row = IfxPy.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys',
None, None, None)
row = IfxPy.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys',
None, config.user, 'test_foreign_keys')
row = IfxPy.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
stmt = IfxPy.foreign_keys(conn, None, config.user, 'test_keys',
None, 'dummy_schema')
row = IfxPy.fetch_tuple(stmt)
if (not row):
print("No Data Found")
else:
print(row[2])
print(row[3])
print(row[6])
print(row[7])
IfxPy.close(conn)
else:
print(IfxPy.conn_errormsg())
print("Connection failed\n")
