def run_test_124(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_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 = ifx_db.num_fields(result)
j = 0
row = ifx_db.fetch_both(result)
while (row):
for i in range(0, cols):
field = ifx_db.field_name(result, i)
value = row[ifx_db.field_name(result, i)]
if (value == None):
value = ''
print "%s:%s" % (field, value)
print "---------"
j += 1
if (j == 10):
break
row = ifx_db.fetch_both(result)
ifx_db.close(conn)
print "done"
else:
print ifx_db.conn_errormsg()
def run_test_092(self):
try:
conn = ifx_db.connect(config.ConnStr, config.user, "z")
print "??? No way."
except:
err = ifx_db.conn_errormsg()
print err[0:68]
def run_test_091(self):
try:
conn = ifx_db.connect(config.ConnStr, "y", config.password)
print "??? No way."
except:
err = ifx_db.conn_errormsg()
print err[0:68]
def run_test_071(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
rc = ifx_db.close(conn)
if (rc == True):
print "ifx_db.close succeeded"
else:
print "ifx_db.close FAILED\n"
else:
print "%s" % ifx_db.conn_errormsg()
print ",sqlstate=%s" % ifx_db.conn_error()
print "%s" % ifx_db.conn_errormsg()
print "%s" % ifx_db.conn_errormsg()
print "%s" % ifx_db.conn_errormsg()
print "%s" % ifx_db.conn_errormsg()
def run_test_018(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
ifx_db.autocommit(conn, ifx_db.SQL_AUTOCOMMIT_ON)
if conn:
stmt = ifx_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" )
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
ifx_db.free_result(stmt)
else:
print ifx_db.stmt_errormsg()
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
ifx_db.free_result(stmt)
else:
print ifx_db.stmt_errormsg()
ifx_db.set_option(stmt, {ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH : ifx_db.SQL_ROWCOUNT_PREFETCH_ON}, 2)
result = ifx_db.execute(stmt)
if result:
rows = ifx_db.num_rows(stmt)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
ifx_db.close(conn)
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_191(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ifx_db.columns(conn,None,config.user,"emp_photo");
else:
result = ifx_db.columns(conn,None,None,"EMP_PHOTO");
i = 0
row = ifx_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 = ifx_db.fetch_both(result)
print "done!"
else:
print "no connection: ", ifx_db.conn_errormsg()
def run_test_197(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if conn:
try:
rc = ifx_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ifx_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ifx_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ifx_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = ifx_db.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 = ifx_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ifx_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ifx_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ifx_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ifx_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'Inf'):
row = ifx_db.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 = ifx_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ifx_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ifx_db.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + ifx_db.conn_errormsg()
def run_test_102(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if (not conn):
print ifx_db.conn_errormsg()
server = ifx_db.server_info(conn)
if ((server.DBMS_NAME[0:2] != "AS") and (server.DBMS_NAME != "DB2")
and (server.DBMS_NAME[0:3] != "Inf")):
result = ifx_db.exec_immediate(conn, "VALUES(1)")
#throw :unsupported unless result
if (not result):
raise Exception('Unsupported')
print ifx_db.num_fields(result)
else:
print '1'
ifx_db.close(conn)
def run_test_061(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ifx_db.exec_immediate(conn, create)
except:
pass
if conn:
server = ifx_db.server_info( conn )
op = {ifx_db.ATTR_CASE: ifx_db.CASE_UPPER}
ifx_db.set_option(conn, op, 1)
result = ifx_db.tables(conn, None, 't');
i = 0
row = ifx_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 = ifx_db.fetch_both(result)
ifx_db.exec_immediate(conn, 'DROP TABLE t.t1')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t2')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t3')
ifx_db.exec_immediate(conn, 'DROP TABLE t.t4')
print "done!"
else:
print "no connection: %s" % ifx_db.conn_errormsg()
def run_test_023(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info( conn )
if (conn != 0):
stmt = ifx_db.column_privileges(conn, None, config.user, 'animals')
row = ifx_db.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]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n\n"
def run_test_103(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn, "select * from org, project order by project.projname")
cols = ifx_db.num_fields(result)
j = 1
row = ifx_db.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 = ifx_db.fetch_tuple(result)
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
def run_test_025(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if (conn != 0):
drop = 'DROP TABLE test_primary_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ifx_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES (1)"
result = ifx_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ifx_db.primary_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = ifx_db.primary_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[4]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n"
def run_test_103(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn,
"select * from org, project order by project.projname,org.deptnumb"
)
cols = ifx_db.num_fields(result)
j = 1
row = ifx_db.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 = ifx_db.fetch_tuple(result)
ifx_db.close(conn)
else:
print ifx_db.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 = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'Inf'):
result = ifx_db.columns(conn, None, config.user, "employee")
else:
result = ifx_db.columns(conn, None, None, "EMPLOYEE")
row = ifx_db.fetch_tuple(result)
while (row):
str = row[1] + "/" + row[3]
print str
row = ifx_db.fetch_tuple(result)
print "done!"
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_191(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn:
result = ifx_db.columns(conn, None, config.user, "emp_photo")
i = 0
row = ifx_db.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 = ifx_db.fetch_both(result)
print "done!"
else:
print "no connection: ", ifx_db.conn_errormsg()
def run_test_017(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0",
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_KEYSET_DRIVEN})
if result:
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
result = ifx_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0",
{ifx_db.SQL_ATTR_CURSOR_TYPE: ifx_db.SQL_CURSOR_FORWARD_ONLY})
if result:
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
result = ifx_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ifx_db.SQL_ROWCOUNT_PREFETCH_ON
})
if result:
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
result = ifx_db.exec_immediate(
conn, "SELECT * from animals WHERE weight < 10.0", {
ifx_db.SQL_ATTR_ROWCOUNT_PREFETCH:
ifx_db.SQL_ROWCOUNT_PREFETCH_OFF
})
if result:
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
ifx_db.close(conn)
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_015(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ifx_db.num_fields(result)
# NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb)
print "col:", cols
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
result = ifx_db.exec_immediate(conn,"delete from t_string where a=123")
if result:
cols = ifx_db.num_fields(result)
print "col:", cols
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
ifx_db.close(conn)
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_016(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
if conn:
result = ifx_db.exec_immediate(
conn, "insert into t_string values(123,1.222333,'one to one')")
if result:
cols = ifx_db.num_fields(result)
print "col:", cols
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
result = ifx_db.exec_immediate(conn,
"delete from t_string where a=123")
if result:
cols = ifx_db.num_fields(result)
print "col:", cols
rows = ifx_db.num_rows(result)
print "affected row:", rows
else:
print ifx_db.stmt_errormsg()
ifx_db.close(conn)
else:
print "no connection:", ifx_db.conn_errormsg()
def run_test_024(self):
conn = ifx_db.connect(config.ConnStr, config.user, config.password)
server = ifx_db.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ifx_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ifx_db.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 = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ifx_db.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 = ifx_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ifx_db.exec_immediate(conn, statement)
stmt = ifx_db.foreign_keys(conn, None, config.user,
'test_primary_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, None, None)
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, config.user, 'test_foreign_keys')
row = ifx_db.fetch_tuple(stmt)
print row[2]
print row[3]
print row[6]
print row[7]
stmt = ifx_db.foreign_keys(conn, None, config.user, 'test_keys',
None, 'dummy_schema')
row = ifx_db.fetch_tuple(stmt)
if (not row):
print "No Data Found"
else:
print row[2]
print row[3]
print row[6]
print row[7]
ifx_db.close(conn)
else:
print ifx_db.conn_errormsg()
print "Connection failed\n"
