def run_test_InsertRetrieveDateTimeTypeColumn(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
drop = 'DROP TABLE tab_datetime'
result = ''
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
t_val = datetime.time(10, 42, 34)
d_val = datetime.date(1981, 7, 8)
#ts_val = datetime.datetime.today()
ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
server = IfxPy.server_info(conn)
if (server.DBMS_NAME[0:3] == 'Inf'):
statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = IfxPy.prepare(conn, statement)
result = IfxPy.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
stmt = IfxPy.prepare(conn, statement)
result = IfxPy.execute(stmt, (t_val, d_val, ts_val))
statement = "SELECT * FROM tab_datetime"
result = IfxPy.exec_immediate(conn, statement)
for i in range(0, IfxPy.num_fields(result)):
print(str(i) + ":" + IfxPy.field_type(result, i))
statement = "SELECT * FROM tab_datetime"
stmt = IfxPy.prepare(conn, statement)
rc = IfxPy.execute(stmt)
result = IfxPy.fetch_row(stmt)
while (result):
row0 = IfxPy.result(stmt, 0)
row1 = IfxPy.result(stmt, 1)
row2 = IfxPy.result(stmt, 2)
print(type(row0), row0)
print(type(row1), row1)
print(type(row2), row2)
result = IfxPy.fetch_row(stmt)
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_6528(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
if (server.DBMS_NAME[0:3] == 'Inf'):
sql = "SELECT TRIM(TRAILING FROM name) FROM animals WHERE breed = ?"
else:
sql = "SELECT RTRIM(name) FROM animals WHERE breed = ?"
stmt = IfxPy.prepare(conn, sql)
var = "cat"
IfxPy.bind_param(stmt, 1, var, IfxPy.SQL_PARAM_INPUT)
self.checked_ids_execute(stmt)
IfxPy.close(conn)
else:
print "Connection failed."
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_013(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = IfxPy.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0")
IfxPy.execute(stmt)
data = IfxPy.fetch_both( stmt )
while (data):
print data[0]
data = IfxPy.fetch_both( stmt )
IfxPy.close(conn)
else:
print "Connection failed."
def run_test_033(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info( conn )
if conn:
stmt = IfxPy.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while (IfxPy.fetch_row(stmt)):
weight = IfxPy.result(stmt, 3)
print("string(%d) \"%s\"" % (len(str(weight)), weight))
breed = IfxPy.result(stmt, 1)
print("string(%d) \"%s\"" % (len(breed), breed))
name = IfxPy.result(stmt, "name")
print("string(%d) \"%s\"" % (len(name), name))
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_021(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print rows
IfxPy.autocommit(conn, 0)
ac = IfxPy.autocommit(conn)
if ac != 0:
print "Cannot set IfxPy.AUTOCOMMIT_OFF\nCannot run test"
#continue
IfxPy.exec_immediate(conn, "DELETE FROM animals")
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print rows
IfxPy.commit(conn)
stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals")
res = IfxPy.fetch_tuple(stmt)
rows = res[0]
print rows
# Populate the animal table
animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty',
350.0), (3, 'gold fish', 'Bubbles',
0.1), (4, 'budgerigar', 'Gizmo', 0.2),
(5, 'goat', 'Rickety Ride', 9.7), (6, 'llama',
'Sweater', 150))
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = IfxPy.prepare(conn, insert)
if stmt:
for animal in animals:
result = IfxPy.execute(stmt, animal)
IfxPy.commit(conn)
IfxPy.close(conn)
else:
print "Connection failed."
def run_test_036(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
result = IfxPy.exec_immediate(conn, "select * from staff")
i = 0
row = IfxPy.fetch_row(result)
while (row):
result2 = IfxPy.exec_immediate(conn, "select * from staff")
j = 0
row2 = IfxPy.fetch_row(result2)
while (row2):
print("%d)%d," % (i, j))
j += 1
row2 = IfxPy.fetch_row(result2)
print("%d, " % i)
i += 1
row = IfxPy.fetch_row(result)
IfxPy.close(conn)
def run_test_300(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(
server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(
server.DBMS_VER), server.DBMS_VER)
print "DB_NAME: string(%d) \"%s\"" % (len(
server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(
server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(
server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(
server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(
server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(
server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
IfxPy.close(conn)
else:
print "Error."
def run_test_201(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
procedure = """CREATE FUNCTION multiResults ()
RETURNING CHAR(16), INT, VARCHAR(32), NUMERIC(7,2);
DEFINE p_name CHAR(16);
DEFINE p_id INT;
DEFINE p_breed VARCHAR(32);
DEFINE p_weight NUMERIC(7,2);
FOREACH c1 FOR
SELECT name, id, breed, weight
INTO p_name, p_id, p_breed, p_weight
FROM animals
ORDER BY name DESC
RETURN p_name, p_id, p_breed, p_weight WITH RESUME;
END FOREACH;
END FUNCTION;"""
if conn:
try:
IfxPy.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
IfxPy.exec_immediate(conn, procedure)
stmt = IfxPy.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = IfxPy.fetch_tuple(stmt)
while (row):
for i in row:
print str(i).strip()
row = IfxPy.fetch_tuple(stmt)
IfxPy.close(conn)
else:
print "Connection failed."
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_019(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.SQL_ATTR_ROWCOUNT_PREFETCH:
IfxPy.SQL_ROWCOUNT_PREFETCH_ON
})
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.close(conn)
else:
print "no connection:", IfxPy.conn_errormsg()
def run_test_070(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
if (type(conn) == IfxPy.IFXConnection):
print("Resource is a Ifx Connection")
rc = IfxPy.close(conn)
print(rc)
else:
print("Connection failed.")
def run_test_200(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info(conn)
procedure = """
CREATE FUNCTION multiResults()
RETURNING CHAR(16), INT;
DEFINE p_name CHAR(16);
DEFINE p_id INT;
FOREACH c1 FOR
SELECT name, id
INTO p_name, p_id
FROM animals
ORDER BY name
RETURN p_name, p_id WITH RESUME;
END FOREACH;
END FUNCTION;
"""
if conn:
try:
IfxPy.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
IfxPy.exec_immediate(conn, procedure)
stmt = IfxPy.exec_immediate(conn, 'CALL multiResults()')
print "Fetching first result set"
row = IfxPy.fetch_tuple(stmt)
while (row):
for i in row:
print i
row = IfxPy.fetch_tuple(stmt)
IfxPy.close(conn)
else:
print "Connection failed."
def run_test_261(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if (server.DBMS_NAME[0:3] == 'Inf'):
op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER}
IfxPy.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'Inf'):
sql = "SELECT breed, TRIM(TRAILING FROM name) AS name FROM animals WHERE id = ?"
else:
sql = "SELECT breed, RTRIM(name) AS name FROM animals WHERE id = ?"
if conn:
stmt = IfxPy.prepare(conn, sql)
IfxPy.execute(stmt, (0, ))
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# pet = IfxPy.fetch_object(stmt)
# while (pet):
# print "Come here, %s, my little %s!" % (pet.NAME, pet.BREED)
# pet = IfxPy.fetch_object(stmt)
class Pet:
pass
data = IfxPy.fetch_assoc(stmt)
while (data):
pet = Pet()
pet.NAME = data['NAME']
pet.BREED = data['BREED']
print("Come here, %s, my little %s!" % (pet.NAME, pet.BREED))
data = IfxPy.fetch_assoc(stmt)
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_310(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
client = IfxPy.client_info(conn)
if client:
print("DRIVER_NAME: string(%d) \"%s\"" %
(len(client.DRIVER_NAME), client.DRIVER_NAME))
print("DRIVER_VER: string(%d) \"%s\"" %
(len(client.DRIVER_VER), client.DRIVER_VER))
print("DATA_SOURCE_NAME: string(%d) \"%s\"" %
(len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME))
print("DRIVER_ODBC_VER: string(%d) \"%s\"" %
(len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER))
print("ODBC_VER: string(%d) \"%s\"" %
(len(client.ODBC_VER), client.ODBC_VER))
print("ODBC_SQL_CONFORMANCE: string(%d) \"%s\"" % (len(
client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE))
IfxPy.close(conn)
else:
print("Error.")
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_6755(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
drop = 'DROP TABLE table_6755'
result = ''
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
if (server.DBMS_NAME[0:3] == 'Inf'):
create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB)'
insert = "INSERT INTO table_6755 VALUES ('database', FILETOCLOB('C:\\work\\IfxPy\\IfxPy\\tests\\data\\test_6755.txt','client'))"
else:
create = 'CREATE TABLE table_6755 (col1 VARCHAR(20), col2 CLOB(20))'
insert = "INSERT INTO table_6755 VALUES ('database', FILETOCLOB('C:\\work\\IfxPy\\IfxPy\\tests\\data\\test_6755.txt','client'))"
result = IfxPy.exec_immediate(conn, create)
result = IfxPy.exec_immediate(conn, insert)
statement = "SELECT col1, col2 FROM table_6755"
result = IfxPy.prepare(conn, statement)
IfxPy.execute(result)
row = IfxPy.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 = IfxPy.fetch_tuple(result)
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_034(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER}
IfxPy.set_option(conn, op, 1)
result = IfxPy.exec_immediate(conn, "select * from staff")
row = IfxPy.fetch_assoc(result)
if (row):
#printf("%5d ",row['ID'])
#printf("%-10s ",row['NAME'])
#printf("%5d ",row['DEPT'])
#printf("%-7s ",row['JOB'])
#printf("%5d ", row['YEARS'])
#printf("%15s ", row['SALARY'])
#printf("%10s ", row['COMM'])
#puts ""
print("%5d %-10s %5d %-7s %5d %15s %10s" %
(row['ID'], row['NAME'], row['DEPT'], row['JOB'],
row['YEARS'], row['SALARY'], row['COMM']))
IfxPy.close(conn)
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_6561(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF)
stmt = IfxPy.exec_immediate(conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)")
statement = "SELECT count(id) FROM animals"
result = IfxPy.exec_immediate(conn, statement)
if ( (not result) and IfxPy.stmt_error() ):
print("ERROR: %s" % (IfxPy.stmt_errormsg(), ))
row = IfxPy.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = IfxPy.fetch_tuple(result)
IfxPy.rollback(conn)
IfxPy.close(conn)
else:
print("Connection failed.")
def run_test_141(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?"
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
stmt = IfxPy.prepare(conn, sql)
animal = 5
mass = 2.0
IfxPy.bind_param(stmt, 1, animal)
IfxPy.bind_param(stmt, 2, mass)
if IfxPy.execute(stmt):
row = IfxPy.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = IfxPy.fetch_tuple(stmt)
IfxPy.close(conn)
else:
print "Connection failed."
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_014(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info( conn )
query = 'SELECT * FROM animals ORDER BY name'
if (serverinfo.DBMS_NAME[0:3] != 'Inf'):
stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = IfxPy.prepare(conn, query)
IfxPy.execute(stmt)
data = IfxPy.fetch_both( stmt )
while ( data ):
print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]))
data = IfxPy.fetch_both( stmt )
try:
stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
IfxPy.execute(stmt)
rc = IfxPy.fetch_row(stmt, -1)
print("Fetch row -1: %s" % str(rc))
except:
print("Requested row number must be a positive value")
IfxPy.close(conn)
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_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()
#!/root/anaconda3/bin/ipython
# Environment settings for Database
import os
try:
informix_dir=os.environ['INFORMIXDIR']
except KeyError:
print ( "INFORMIX PATH is not setup - Exit ")
os.exit()
try:
ld_lib_path=os.environ['LD_LIBRARY_PATH']
LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli:"+ld_lib_path
os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH
except KeyError:
print ( "No environment set for LD_LIBRARY_PATH")
LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli"
os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH
os.environ
print(os.environ)
import IfxPy
conStr = "SERVER=pronto_net;DATABASE=sysmaster;HOST=pronto;SERVICE=9088;UID=informix;PWD=pronto1$;"
conn = IfxPy.connect( conStr, "", "")
sql = "select fname from syschunks"
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_both(stmt)
while dictionary != False:
print(dictionary[0])
dictionary = IfxPy.fetch_both(stmt)
IfxPy.close(conn)
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")
def run_test_6792(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
drop = 'DROP TABLE table_6792'
result = ''
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
t_val = '10:42:34'
d_val = '1981-07-08'
ts_val = '1981-07-08 10:42:34'
ts_withT_val = '2013-06-06T15:30:39'
server = IfxPy.server_info(conn)
if (server.DBMS_NAME[0:3] == 'Inf'):
statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)"
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)"
stmt = IfxPy.prepare(conn, statement)
result = IfxPy.execute(stmt, (t_val, d_val, ts_val))
else:
statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)"
result = IfxPy.exec_immediate(conn, statement)
statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)"
stmt = IfxPy.prepare(conn, statement)
result = IfxPy.execute(stmt,
(t_val, d_val, ts_val, ts_withT_val))
statement = "SELECT * FROM table_6792"
result = IfxPy.exec_immediate(conn, statement)
for i in range(0, IfxPy.num_fields(result)):
print str(i) + ":" + IfxPy.field_type(result, i)
statement = "SELECT * FROM table_6792"
stmt = IfxPy.prepare(conn, statement)
rc = IfxPy.execute(stmt)
result = IfxPy.fetch_row(stmt)
while (result):
row0 = IfxPy.result(stmt, 0)
row1 = IfxPy.result(stmt, 1)
row2 = IfxPy.result(stmt, 2)
row3 = IfxPy.result(stmt, 3)
print row0
print row1
print row2
print row3
result = IfxPy.fetch_row(stmt)
IfxPy.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__ZOS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__SYSTEMI_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#2013-06-06 15:30:39
#__IDS_EXPECTED__
#0:time
#1:date
#2:timestamp
#3:timestamp
#10:42:34
#1981-07-08
#1981-07-08 10:42:34
#None
def run_test_decimal(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = IfxPy.server_info( conn )
drop = "DROP TABLE STOCKSHARE"
try:
result = IfxPy.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 = IfxPy.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = IfxPy.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 = IfxPy.prepare(conn,insert)
if stmt:
for company in stockprice:
result = IfxPy.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
IfxPy.bind_param(stmt, 1, id)
IfxPy.bind_param(stmt, 2, company)
IfxPy.bind_param(stmt, 3, stockshare)
error = IfxPy.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] != 'Inf'):
stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = IfxPy.prepare(conn, query)
IfxPy.execute(stmt)
data = IfxPy.fetch_both( stmt )
while ( data ):
print "%s : %s : %s\n" % (data[0], data[1], data[2])
data = IfxPy.fetch_both( stmt )
try:
stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN})
IfxPy.execute(stmt)
rc = IfxPy.fetch_row(stmt, -1)
print "Fetch Row -1:%s " %str(rc)
except:
print "Requested row number must be a positive value"
IfxPy.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.21
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.98
#70 : Nirvana : 100.12
#Requested row number must be a positive value
def run_test_311(self):
# Make a connection
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON)
# Drop the tab_num_literals table, in case it exists
drop = 'DROP TABLE tab_num_literals'
result = ''
try:
result = IfxPy.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 = IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
res = IfxPy.exec_immediate(conn, insert)
print("Number of inserted rows:", IfxPy.num_rows(res))
stmt = IfxPy.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'"
)
IfxPy.execute(stmt)
data = IfxPy.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = IfxPy.fetch_both(stmt)
sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
res = IfxPy.exec_immediate(conn, sql)
print("Number of updated rows:", IfxPy.num_rows(res))
stmt = IfxPy.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'"
)
IfxPy.execute(stmt)
data = IfxPy.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = IfxPy.fetch_both(stmt)
sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
res = IfxPy.exec_immediate(conn, sql)
print("Number of deleted rows:", IfxPy.num_rows(res))
stmt = IfxPy.prepare(
conn,
"SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'"
)
IfxPy.execute(stmt)
data = IfxPy.fetch_both(stmt)
while (data):
print(data[0])
print(data[1])
print(data[2])
data = IfxPy.fetch_both(stmt)
IfxPy.rollback(conn)
IfxPy.close(conn)
