def run_test_006(self):
options1 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_FORWARD_ONLY}
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
serverinfo = IfxPy.server_info(conn)
if (serverinfo.DBMS_NAME[0:3] == 'Inf'):
options1 = options2
stmt = IfxPy.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
IfxPy.execute(stmt)
data = IfxPy.fetch_both(stmt)
while (data):
print(data[0])
data = IfxPy.fetch_both(stmt)
print("")
stmt = IfxPy.prepare(
conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
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_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_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_161(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)
result = IfxPy.exec_immediate(
conn, "select * from emp_act order by projno desc")
row = IfxPy.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 = IfxPy.exec_immediate(
conn, "select * from employee where employee.empno='" +
row['EMPNO'] + "'")
row2 = IfxPy.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 = IfxPy.fetch_both(result)
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_062(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:
schema = 't'
result = IfxPy.tables(conn, None, schema)
i = 0
row = IfxPy.fetch_both(result)
while (row):
str = row[1] + "/" + row[2] + "/" + row[3]
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: #{IfxPy.conn_errormsg}")
def DBConnect(sql):
constr = "SERVER=pronto_net;DATABASE=sysmaster;HOST=pronto;SERVICE=9088;UID=informix;PWD=pronto1$;"
conn = IfxPy.connect(constr, "", "")
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_both(stmt)
while dictionary != False:
logging.info("touch " + dictionary[0])
logging.info("chown informix:informix" + dictionary[0])
logging.info("chmod 660 " + dictionary[0])
dictionary = IfxPy.fetch_both(stmt)
IfxPy.close(conn)
def run_test_264(self):
# Make a connection
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if conn:
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)
# Drop the tab_bigint table, in case it exists
drop = 'DROP TABLE tab_bigint'
result = ''
try:
result = IfxPy.exec_immediate(conn, drop)
except:
pass
# Create the tab_bigint table
if (server.DBMS_NAME[0:3] == 'Inf'):
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 = IfxPy.exec_immediate(conn, create)
insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
res = IfxPy.exec_immediate(conn, insert)
print "Number of inserted rows:", IfxPy.num_rows(res)
stmt = IfxPy.prepare(conn, "SELECT * FROM tab_bigint")
IfxPy.execute(stmt)
data = IfxPy.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 = IfxPy.fetch_both(stmt)
# test IfxPy.result for fetch of bigint
stmt1 = IfxPy.prepare(conn, "SELECT col2 FROM tab_bigint")
IfxPy.execute(stmt1)
IfxPy.fetch_row(stmt1, 0)
if (server.DBMS_NAME[0:3] != 'Inf'):
row1 = IfxPy.result(stmt1, 'COL2')
else:
row1 = IfxPy.result(stmt1, 'col2')
print row1
IfxPy.close(conn)
def run_test_060(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:
result = IfxPy.tables(conn, None, 't')
i = 0
row = IfxPy.fetch_both(result)
while (row):
if (i < 4):
print("/%s/%s" % (row[1], row[2]))
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: #{IfxPy.conn_errormsg}")
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_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_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)
def run_test_160(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)
result = IfxPy.exec_immediate(conn, "select * from emp_act")
row = IfxPy.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 = IfxPy.fetch_both(result)
#!/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_008(self):
op = {IfxPy.ATTR_CASE: IfxPy.CASE_NATURAL}
conn = IfxPy.connect(config.ConnStr, config.user, config.password, op)
server = IfxPy.server_info(conn)
result = IfxPy.columns(conn, None, None, "employee")
row = IfxPy.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 = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER}
IfxPy.set_option(conn, op, 1)
result = IfxPy.columns(conn, None, None, "employee")
row = IfxPy.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 = {IfxPy.ATTR_CASE: IfxPy.CASE_LOWER}
IfxPy.set_option(conn, op, 1)
result = IfxPy.columns(conn, None, None, "employee")
row = IfxPy.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_154(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)
try:
statement = 'DROP TABLE fetch_test'
result = IfxPy.exec_immediate(conn, statement)
except:
pass
server = IfxPy.server_info(conn)
if (server.DBMS_NAME[0:3] == 'Inf'):
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 = IfxPy.exec_immediate(conn, statement)
result = IfxPy.exec_immediate(conn, st0)
result = IfxPy.exec_immediate(conn, st1)
result = IfxPy.exec_immediate(conn, st2)
result = IfxPy.exec_immediate(conn, st3)
statement = "SELECT col1, col2 FROM fetch_test"
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)
result = IfxPy.prepare(conn, statement)
IfxPy.execute(result)
row = IfxPy.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 = IfxPy.fetch_assoc(result)
result = IfxPy.prepare(conn, statement)
IfxPy.execute(result)
row = IfxPy.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 = IfxPy.fetch_both(result)
IfxPy.close(conn)
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 my_Sample():
ConStr = "SERVER=ids0;DATABASE=db1;HOST=127.0.0.1;SERVICE=9088;UID=informix;PWD=xxxxx;"
try:
# netstat -a | findstr 9088
conn = IfxPy.connect(ConStr, "", "")
except Exception as e:
print('ERROR: Connect failed')
print(e)
quit()
SetupSqlSet = [
"create table t1 ( c1 int, c2 char(20), c3 int, c4 int ) ;",
"insert into t1 values( 1, 'Sunday', 101, 201 );",
"insert into t1 values( 2, 'Monday', 102, 202 );",
"insert into t1 values( 3, 'Tuesday', 103, 203 );",
"insert into t1 values( 4, 'Wednesday', 104, 204 );",
"insert into t1 values( 5, 'Thursday', 105, 2005 );",
"insert into t1 values( 6, 'Friday', 106, 206 );",
"insert into t1 values( 7, 'Saturday', 107, 207 );"
]
try:
sql = "drop table t1;"
print(sql)
stmt = IfxPy.exec_immediate(conn, sql)
except:
print('FYI: drop table failed')
i = 0
for sql in SetupSqlSet:
i += 1
print(sql)
stmt = IfxPy.exec_immediate(conn, sql)
# The first record executed is for create table
i -= 1
# Select records
sql = "SELECT * FROM t1"
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_both(stmt)
rc = 0
while dictionary != False:
rc += 1
print("-- Record {0} --".format(rc))
print("c1 is : ", dictionary[0])
print("c2 is : ", dictionary[1])
print("c3 is : ", dictionary["c3"])
print("c4 is : ", dictionary[3])
print(" ")
dictionary = IfxPy.fetch_both(stmt)
print()
print("Total Record Inserted {}".format(i))
print("Total Record Selected {}".format(rc))
# Free up memory used by result and then stmt too
IfxPy.free_result(stmt)
IfxPy.free_stmt(stmt)
IfxPy.close(conn)
print("Done")
def LoadCsvSample(csv_file_name, table_name, ConStr):
try:
# netstat -a | findstr 9088
conn = IfxPy.connect(ConStr, "", "")
except Exception as e:
print('ERROR: Connect failed')
print(e)
quit()
try:
sql = "drop table {};".format(table_name)
print(sql)
stmt = IfxPy.exec_immediate(conn, sql)
except:
print('FYI: drop table failed')
# head -n 5 sample.csv
# "Store","DayOfWeek","Date","Sales","Customers","Open","Promo","StateHoliday","SchoolHoliday"
# 1,5,2015-07-31,5263,555,1,1,"0","1"
# 2,5,2015-07-31,6064,625,1,1,"0","1"
# 3,5,2015-07-31,8314,821,1,1,"0","1"
# 4,5,2015-05-31,13995,1498,1,1,"0","1"
# The colum and its type for full list is.
# Store int64
# DayOfWeek int64
# Date object
# Sales int64
# Customers int64
# Open int64
# Promo int64
# StateHoliday object
# SchoolHoliday int64
sql = '''
create table {} (
Store int,
DayOfWeek int,
Date LVARCHAR,
Sales int,
Customers int,
Open int,
Promo int,
StateHoliday char(6),
SchoolHoliday int
); '''.format(table_name)
stmt = IfxPy.exec_immediate(conn, sql)
sql = '''
INSERT INTO {} (
Store,
DayOfWeek,
Date,
Sales,
Customers,
Open,
Promo,
StateHoliday,
SchoolHoliday )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? );
'''.format(table_name)
stmt = IfxPy.prepare(conn, sql)
c1 = None
c2 = None
c3 = None
c4 = None
c5 = None
c6 = None
c7 = None
c8 = None
c9 = None
IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR)
IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 6, c6, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 7, c7, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
IfxPy.bind_param(stmt, 8, c8, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR)
IfxPy.bind_param(stmt, 9, c9, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER)
# Read the CSV file and insert into the table
with open(csv_file_name, 'r') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
row_count = 0
for row in csv_reader:
row_count += 1
# Convert the List to tuple
tup = tuple(row)
if row_count == 1:
print("Header :", tup)
else:
print(tup)
IfxPy.execute(stmt, tup)
print(f'Rows Inserted is {row_count-1} .')
# SELECT the inserted rows
sql = "SELECT * FROM t1"
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_both(stmt)
rc = 0
while dictionary != False:
rc = rc + 1
# print ("-- Record {0} --".format(rc))
# print ("c1 is : ", dictionary[0])
# print ("c2 is : ", dictionary[1])
# print ("c3 is : ", dictionary[2])
# print ("c4 is : ", dictionary[3])
# print ("c5 is : ", dictionary[4])
# print ("c6 is : ", dictionary[5])
# print ("c7 is : ", dictionary[6])
# print ("c8 is : ", dictionary[7])
# print ("c9 is : ", dictionary[8])
# print (" ")
dictionary = IfxPy.fetch_both(stmt)
print(f'Rows Selected is {rc} .')
IfxPy.close(conn)
print("Done")
