def run_test_150(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 staff")
row = IfxPy.fetch_assoc(result)
while ( row ):
#print "%5d " % row['ID']
#print "%-10s " % row['NAME']
#print "%5d " % row['DEPT']
#print "%-7s " % row['JOB']
#print "%5d " % row['YEARS']
#print "%15s " % row['SALARY']
#print "%10s " % row['COMM']
if (row['YEARS'] == None):
row['YEARS'] = 0
if (row['COMM'] == None):
row['COMM'] = ''
print("%5d %-10s %5d %-7s %5s %15s %10s " % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM']))
row = IfxPy.fetch_assoc(result)
def run_test_157a(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
print("Starting...")
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
result = IfxPy.exec_immediate(conn, sql)
try:
i = 2
row = IfxPy.fetch_assoc(result, i)
while (row):
if (server.DBMS_NAME[0:3] == 'Inf'):
print("%-5d %-16s %-32s %10s" %
(row['id'], row['name'], row['breed'],
row['weight']))
else:
print("%-5d %-16s %-32s %10s" %
(row['ID'], row['NAME'], row['BREED'],
row['WEIGHT']))
i = i + 2
row = IfxPy.fetch_assoc(result, i)
except:
print("SQLSTATE: %s" % IfxPy.stmt_error(result))
print("Message: %s" % IfxPy.stmt_errormsg(result))
print("DONE")
def run_test_158(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 staff WHERE id < 50")
output = ''
row = IfxPy.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = IfxPy.fetch_assoc(result)
result2 = IfxPy.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = IfxPy.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = IfxPy.fetch_assoc(result2)
result3 = IfxPy.exec_immediate(conn,"SELECT * FROM employee WHERE 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')")
row3 = IfxPy.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=IfxPy.fetch_tuple(result3)
print output
def run_test_155(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
serverinfo = IfxPy.server_info(conn)
result = IfxPy.exec_immediate(
conn,
"select * from employee where 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')"
)
i = 0
row = IfxPy.fetch_assoc(result)
while (row):
i += 1
if (serverinfo.DBMS_NAME[0:3] == 'Inf'):
if (row['midinit'] == None):
row['midinit'] = ''
print "%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['empno'], row['firstnme'], row['midinit'], row['lastname'], row['workdept'], \
row['phoneno'], row['hiredate'], row['job'], row['edlevel'], row['sex'], \
row['birthdate'], row['salary'], row['bonus'], row['comm'])
row = IfxPy.fetch_assoc(result)
else:
if (row['MIDINIT'] == None):
row['MIDINIT'] = ''
print "%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['EMPNO'], row['FIRSTNME'], row['MIDINIT'], row['LASTNAME'], row['WORKDEPT'], \
row['PHONENO'], row['HIREDATE'], row['JOB'], row['EDLEVEL'], row['SEX'], \
row['BIRTHDATE'], row['SALARY'], row['BONUS'], row['COMM'])
row = IfxPy.fetch_assoc(result)
print "%d record(s) selected." % i
def run_test_152(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 project")
row = IfxPy.fetch_assoc(result)
while (row):
#printf("%6s ",row['PROJNO'])
#printf("%-24s ",row['PROJNAME'])
#printf("%3s ",row['DEPTNO'])
#printf("%6s",row['RESPEMP'])
#printf("%7s ",row['PRSTAFF'])
#printf("%10s ",row['PRSTDATE'])
#printf("%10s ",row['PRENDATE'])
#printf("%6s",row['MAJPROJ'])
#puts ""
if (row['MAJPROJ'] == None):
row['MAJPROJ'] = ''
print("%6s %-24s %3s %6s%7s %10s %10s %6s" %
(row['PROJNO'], row['PROJNAME'], row['DEPTNO'],
row['RESPEMP'], row['PRSTAFF'], row['PRSTDATE'],
row['PRENDATE'], row['MAJPROJ']))
row = IfxPy.fetch_assoc(result)
def run_test_157(self):
conn = IfxPy.connect(config.ConnStr + 'ENABLESCROLLABLECURSORS=1',
config.user, config.password)
server = IfxPy.server_info(conn)
if conn:
sql = "SELECT id, name, breed, weight FROM animals ORDER BY breed"
if (server.DBMS_NAME[0:3] != 'Inf'):
result = IfxPy.exec_immediate(conn, sql, {
IfxPy.SQL_ATTR_CURSOR_TYPE:
IfxPy.SQL_CURSOR_KEYSET_DRIVEN
})
else:
result = IfxPy.exec_immediate(
conn, sql,
{IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_STATIC})
i = 2
row = IfxPy.fetch_assoc(result, i)
while (row):
if (server.DBMS_NAME[0:3] == 'Inf'):
print "%-5d %-16s %-32s %10s\n" % (
row['id'], row['name'], row['breed'], row['weight'])
else:
print "%-5d %-16s %-32s %10s\n" % (
row['ID'], row['NAME'], row['BREED'], row['WEIGHT'])
i = i + 2
row = IfxPy.fetch_assoc(result, i)
def run_test_133(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
if (not conn):
print "Connection failed."
return 0
IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF)
print "Starting test ..."
res = ''
sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
try:
stmt = IfxPy.prepare(conn, sql)
res = IfxPy.execute(
stmt, (128, 'hacker of human and technological nature',
'Wez the ruler of all things PECL', 88.3))
stmt = IfxPy.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = IfxPy.execute(stmt, (128, ))
row = IfxPy.fetch_assoc(stmt)
for i in row:
print i
IfxPy.rollback(conn)
print "Done"
except:
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
try:
stmt = IfxPy.prepare(
conn, "SELECT breed, name FROM animals WHERE id = ?")
res = IfxPy.execute(stmt, (128, ))
row = IfxPy.fetch_assoc(stmt)
if (row):
for i in row:
print i
print res
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
except:
print "An Exception is not expected"
print "SQLSTATE: %s" % IfxPy.stmt_error(stmt)
print "Message: %s" % IfxPy.stmt_errormsg(stmt)
IfxPy.rollback(conn)
print "Done"
def run_test_159(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 name,job from staff")
i = 1
row = IfxPy.fetch_assoc(result)
while ( row ):
#printf("%3d %10s %10s\n",i, row['NAME'], row['JOB'])
print "%3d %10s %10s" % (i, row['NAME'], row['JOB'])
i += 1
row = IfxPy.fetch_assoc(result)
def run_test_159a(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 prstdate,prendate from project")
i = 1
row = IfxPy.fetch_assoc(result)
while ( row ):
#printf("%3d %10s %10s\n",i, row['PRSTDATE'], row['PRENDATE'])
print("%3d %10s %10s" % (i, row['PRSTDATE'], row['PRENDATE']))
i += 1
row = IfxPy.fetch_assoc(result)
def run_test_153(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 org")
row = IfxPy.fetch_assoc(result)
while (row):
#printf("%4d ",row['DEPTNUMB'])
#printf("%-14s ",row['DEPTNAME'])
#printf("%4d ",row['MANAGER'])
#printf("%-10s",row['DIVISION'])
#printf("%-13s ",row['LOCATION'])
#puts ""
print "%4d %-14s %4d %-10s%-13s " % (
row['DEPTNUMB'], row['DEPTNAME'], row['MANAGER'],
row['DIVISION'], row['LOCATION'])
row = IfxPy.fetch_assoc(result)
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_151(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 sales")
row = IfxPy.fetch_assoc(result)
while (row):
#printf("%-10s ",row['SALES_DATE'])
#printf("%-15s ",row['SALES_PERSON'])
#printf("%-15s ",row['REGION'])
#printf("%4s",row['SALES'])
#puts ""
if (row['SALES'] == None):
row['SALES'] = ''
print("%-10s %-15s %-15s %4s" %
(row['SALES_DATE'], row['SALES_PERSON'], row['REGION'],
row['SALES']))
row = IfxPy.fetch_assoc(result)
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_066(self):
conn = IfxPy.connect(config.ConnStr, config.user, config.password)
server = IfxPy.server_info(conn)
result = IfxPy.tables(conn, None, config.user.lower(), 'animals')
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = IfxPy.fetch_object(result)
# JS: check .lower() functionatily
class Row:
pass
data = IfxPy.fetch_assoc(result)
while (data):
row = Row()
row.table_schem = data['TABLE_SCHEM']
row.table_name = data['TABLE_NAME']
row.table_type = data['TABLE_TYPE']
row.remarks = data['REMARKS']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
# row = IfxPy.fetch_object(result)
data = IfxPy.fetch_assoc(result)
result = IfxPy.tables(conn, None, config.user.lower(), 'animal_pics')
# row = IfxPy.fetch_object(result)
data = IfxPy.fetch_assoc(result)
while (data):
row = Row()
row.table_schem = data['TABLE_SCHEM']
row.table_name = data['TABLE_NAME']
row.table_type = data['TABLE_TYPE']
row.remarks = data['REMARKS']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
data = IfxPy.fetch_assoc(result)
result = IfxPy.tables(conn, None, config.user.lower(), 'anime_cat')
# row = IfxPy.fetch_object(result)
data = IfxPy.fetch_assoc(result)
while (data):
row = Row()
row.table_schem = data['TABLE_SCHEM']
row.table_name = data['TABLE_NAME']
row.table_type = data['TABLE_TYPE']
row.remarks = data['REMARKS']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
# row = IfxPy.fetch_object(result)
data = IfxPy.fetch_assoc(result)
IfxPy.free_result(result)
IfxPy.close(conn)
def run_test_156(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 staff")
row = IfxPy.fetch_assoc(result)
count = 1
while (row):
if (row['YEARS'] == None):
row['YEARS'] = ''
if (row['COMM'] == None):
row['COMM'] = ''
print row['ID'], row['NAME'], row['JOB'], row['YEARS'], row[
'SALARY'], row['COMM']
row = IfxPy.fetch_assoc(result)
result2 = IfxPy.exec_immediate(
conn,
"select * from department where substr(deptno,1,1) in ('A','B','C','D','E')"
)
row2 = IfxPy.fetch_assoc(result2)
while (row2):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
'ADMRDEPT'], row2['LOCATION']
row2 = IfxPy.fetch_assoc(result2)
#__END__
#__LUW_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__ZOS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__SYSTEMI_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
#__IDS_EXPECTED__
#10 Sanders Mgr 7 18357.50
#20 Pernal Sales 8 18171.25 612.45
#30 Marenghi Mgr 5 17506.75
#40 OBrien Sales 6 18006.00 846.55
#50 Hanes Mgr 10 20659.80
#60 Quigley Sales 16808.30 650.25
#70 Rothman Sales 7 16502.83 1152.00
#80 James Clerk 13504.60 128.20
#90 Koonitz Sales 6 18001.75 1386.70
#100 Plotz Mgr 7 18352.80
#110 Ngan Clerk 5 12508.20 206.60
#120 Naughton Clerk 12954.75 180.00
#130 Yamaguchi Clerk 6 10505.90 75.60
#140 Fraye Mgr 6 21150.00
#150 Williams Sales 6 19456.50 637.65
#160 Molinare Mgr 7 22959.20
#170 Kermisch Clerk 4 12258.50 110.10
#180 Abrahams Clerk 3 12009.75 236.50
#190 Sneider Clerk 8 14252.75 126.50
#200 Scoutten Clerk 11508.60 84.20
#210 Lu Mgr 10 20010.00
#220 Smith Sales 7 17654.50 992.80
#230 Lundquist Clerk 3 13369.80 189.65
#240 Daniels Mgr 5 19260.25
#250 Wheeler Clerk 6 14460.00 513.30
#260 Jones Mgr 12 21234.00
#270 Lea Mgr 9 18555.50
#280 Wilson Sales 9 18674.50 811.50
#290 Quill Mgr 10 19818.00
#300 Davis Sales 5 15454.50 806.10
#310 Graham Sales 13 21000.00 200.30
#320 Gonzales Sales 4 16858.20 844.00
#330 Burke Clerk 1 10988.00 55.50
#340 Edwards Sales 7 17844.00 1285.00
#350 Gafney Clerk 5 13030.50 188.00
#A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 None
#B01 PLANNING 000020 A00 None
#C01 INFORMATION CENTER 000030 A00 None
#D01 DEVELOPMENT CENTER A00 None
#D11 MANUFACTURING SYSTEMS 000060 D01 None
#D21 ADMINISTRATION SYSTEMS 000070 D01 None
#E01 SUPPORT SERVICES 000050 A00 None
#E11 OPERATIONS 000090 E01 None
#E21 SOFTWARE SUPPORT 000100 E01 None
raise Exception(
'{0} not in SQL Matrix for version {1} - bailing out.\n Please comment out the call using the sql statement in the collect() function.'
.format(sql_name, _self.version))
sql = _self.sql_matrix[_self.version][sql_name]
try:
stat = IfxPy.exec_immediate(_self.connection, sql)
except Exception, e:
_self.print_error(
"Could not execute SQL statement - are we connected? {0}".
format(e))
_self.disconnect()
_self.connect()
if not _self.connection is None:
stat = IfxPy.exec_immediate(_self.connection, sql)
if not _self.connection is None:
res = IfxPy.fetch_assoc(stat)
else:
_self.print_error("Seems like we're not connected to the DB.")
return
records = []
while res:
row = {}
for key in res.keys():
row[key] = res[key]
records.append(row)
res = IfxPy.fetch_assoc(stat)
IfxPy.free_result(stat)
IfxPy.free_stmt(stat)
return records
def get_uptime_and_mode_info(_self):
def cargarRecibo(**Kwargs):
#CONEXION DEFAULT
IFX_HOST = IFX_CEN_HOST
IFX_SERVER = IFX_CEN_SERVER
IFX_SERVICE = IFX_CEN_SERVICE
IFX_DB = IFX_CEN_DB
#COMUN
IFX_USER = '******'
IFX_PASS = '******'
vSede = Kwargs['pSede']
if vSede == 'VTA':
#CONEXION VILLETA
IFX_HOST = IFX_VTA_HOST
IFX_SERVER = IFX_VTA_SERVER
IFX_SERVICE = IFX_VTA_SERVICE
IFX_DB = IFX_VTA_DB
elif vSede == 'VMI':
#CONEXION VALLEMI
IFX_HOST = IFX_VMI_HOST
IFX_SERVER = IFX_VMI_SERVER
IFX_SERVICE = IFX_VMI_SERVICE
IFX_DB = IFX_VMI_DB
#CADENA DE CONEXION
ConStr = "SERVER=%s;DATABASE=%s;HOST=%s;SERVICE=%s;UID=%s;PWD=%s;" % (
IFX_SERVER, IFX_DB, IFX_HOST, IFX_SERVICE, IFX_USER, IFX_PASS)
#print(ConStr)
try:
# netstat -a | findstr 9088
conn = IfxPy.connect(ConStr, "", "")
except Exception as e:
print('ERROR: Falla de conexion INFORMIX')
print(e)
quit()
#TIPO DE RECIBO
vTipo = Kwargs['pTipo']
sql = ''
if vTipo in ('NOR', 'AGU'):
#CONSULTA SQL
sql = """
SELECT * ,
TO_CHAR(sjhst.ddma_emis,'%d/%m/%Y') as fecha,
fnc_nombre_mes(mmes) as nombre_mes
FROM
sjdoc, sjhst, sjsit, sjcol, sjdiv, outer(sjdpt), outer(sjsec),
pscon, sjtem, pscco, sjpag, outer(sjsba), outer(sjcar)
where 1 = 1
AND sjdoc.lega = sjhst.lega
AND sjhst.lega = {pLegajo}
AND sjhst.mmes = {pMes}
AND sjhst.aano = {pAnho}
and sjhst.lega = sjsit.lega and sjhst.conl = sjcol.conl
and sjhst.ccos = pscco.ccos and sjhst.conc = pscon.conc
and sjsit.tipo_empl = sjtem.tipo_empl
and sjsit.divi = sjdiv.divi and sjsit.depa = sjdpt.depa
and sjsit.secc = sjsec.secc and sjsit.cent_pago = sjpag.cent_pago
and sjsit.carg = sjcar.carg and pscon.tipo = '{pTipo}'
and sjcol.patr_empl = 'E' and sjsit.foli = sjsit.foli_baja
and sjsit.lega = sjsba.lega and sjsit.mone = sjsba.mone
and sjsba.ddma_vige = (SELECT max(ddma_vige)
FROM sjsba b
WHERE b.lega = sjsba.lega
AND b.ddma_vige <= sjhst.ddma_emis)
and sjhst.foli = sjhst.foli_baja
and sjhst.conl not in (select conc from sjcoc)
and sjhst.conl not in (select conl from sjcoc)
order by sjhst.lega ASC, sjhst.conl ASC, sjhst.dbcr ASC
""".format(**Kwargs)
#.format(Kwargs['pCedula'],Kwargs['pLegajo'],Kwargs['pMes'],Kwargs['pAnho'])
#print(sql)
#AGUINALDO PRELIMINAR
elif vTipo == 'AGU2':
sql = """select * ,
TO_CHAR(sjliq_tmp.ddma_emis,'%d/%m/%Y') as fecha,
fnc_nombre_mes(mmes) as nombre_mes
FROM
sjdoc, sjliq_tmp, sjsit, sjcol, sjdiv, outer(sjdpt), outer(sjsec),
pscon, sjtem, pscco, sjpag, outer(sjsba), outer(sjcar)
where 1 = 1
AND sjdoc.lega = sjliq_tmp.lega
AND sjliq_tmp.lega = {pLegajo}
AND sjliq_tmp.mmes = {pMes}
AND sjliq_tmp.aano = {pAnho}
AND sjcol.conl = 211 --{pTipo}
and sjliq_tmp.lega = sjsit.lega and sjliq_tmp.conl = sjcol.conl
and sjliq_tmp.ccos = pscco.ccos and 'LIQ' = pscon.conc
and sjsit.tipo_empl = sjtem.tipo_empl
and sjsit.divi = sjdiv.divi and sjsit.depa = sjdpt.depa
and sjsit.secc = sjsec.secc and sjsit.cent_pago = sjpag.cent_pago
and sjsit.carg = sjcar.carg and pscon.tipo = 'NOR'
and sjcol.patr_empl = 'E' and sjsit.foli = sjsit.foli_baja
and sjsit.lega = sjsba.lega and sjsit.mone = sjsba.mone
and sjsba.ddma_vige = ( SELECT max(ddma_vige)
FROM sjsba b
WHERE b.lega = sjsba.lega
AND b.ddma_vige <= sjliq_tmp.ddma_emis)
--and sjliq_tmp.foli = sjliq_tmp.foli_baja
order by sjliq_tmp.lega ASC, sjliq_tmp.conl ASC, sjliq_tmp.dbcr ASC
""".format(**Kwargs)
else:
sql = """
select * ,
TO_CHAR(sjhst.ddma_emis,'%d/%m/%Y') as fecha,
fnc_nombre_mes(mmes) as nombre_mes
FROM
sjdoc, sjhst, sjsit, sjcol, sjdiv, outer(sjdpt), outer(sjsec),
pscon, sjtem, pscco, sjpag, outer(sjsba), outer(sjcar)
where 1 = 1
AND sjdoc.lega = sjhst.lega
AND sjhst.lega = {pLegajo}
AND sjhst.mmes = {pMes}
AND sjhst.aano = {pAnho}
AND sjcol.conl = {pTipo}
and sjhst.lega = sjsit.lega and sjhst.conl = sjcol.conl
and sjhst.ccos = pscco.ccos and sjhst.conc = pscon.conc
and sjsit.tipo_empl = sjtem.tipo_empl
and sjsit.divi = sjdiv.divi and sjsit.depa = sjdpt.depa
and sjsit.secc = sjsec.secc and sjsit.cent_pago = sjpag.cent_pago
and sjsit.carg = sjcar.carg and pscon.tipo = 'NOR'
and sjcol.patr_empl = 'E' and sjsit.foli = sjsit.foli_baja
and sjsit.lega = sjsba.lega and sjsit.mone = sjsba.mone
and sjsba.ddma_vige = ( SELECT max(ddma_vige)
FROM sjsba b
WHERE b.lega = sjsba.lega
AND b.ddma_vige <= sjhst.ddma_emis)
and sjhst.foli = sjhst.foli_baja
order by sjhst.lega ASC, sjhst.conl ASC, sjhst.dbcr ASC
""".format(**Kwargs)
#.format(Kwargs['pCedula'],Kwargs['pLegajo'],Kwargs['pMes'],Kwargs['pAnho'])
# print(sql)
stmt = IfxPy.exec_immediate(conn, sql)
dic = IfxPy.fetch_assoc(stmt)
#print(dictionary)
lista = []
while dic != False:
lista.append(dic)
dic = IfxPy.fetch_assoc(stmt)
#print(lista)
return lista
def ifxEmpleados(**Kwargs):
####################################################################
#IFXPY NECESITA QUE ESTEN DEFINIDOS LOS CLIENTES INFORMIX CLIENT SDK
####################################################################
#CENTRAL
IFX_CEN_HOST = '10.130.10.250'
IFX_CEN_SERVER = 'ol_informix1170'
IFX_CEN_SERVICE = '22767'
IFX_CEN_DB = 'pl4sjasu'
#VILLETA
IFX_VTA_HOST = '192.100.100.8'
IFX_VTA_SERVER = 'ol_platino'
IFX_VTA_SERVICE = '1530'
IFX_VTA_DB = 'pl4sjpvi'
#VALLEMI
IFX_VMI_HOST = '192.168.100.7'
IFX_VMI_SERVER = 'ol_informix1171'
IFX_VMI_SERVICE = '22767'
IFX_VMI_DB = 'pl4sjvalle'
#CONEXION DEFAULT
IFX_HOST = IFX_CEN_HOST
IFX_SERVER = IFX_CEN_SERVER
IFX_SERVICE = IFX_CEN_SERVICE
IFX_DB = IFX_CEN_DB
#COMUN
IFX_USER = '******'
IFX_PASS = '******'
vSede = Kwargs['pSede']
if vSede == 'VTA':
#CONEXION VILLETA
IFX_HOST = IFX_VTA_HOST
IFX_SERVER = IFX_VTA_SERVER
IFX_SERVICE = IFX_VTA_SERVICE
IFX_DB = IFX_VTA_DB
elif vSede == 'VMI':
#CONEXION VALLEMI
IFX_HOST = IFX_VMI_HOST
IFX_SERVER = IFX_VMI_SERVER
IFX_SERVICE = IFX_VMI_SERVICE
IFX_DB = IFX_VMI_DB
#CADENA DE CONEXION
ConStr = "SERVER=%s;DATABASE=%s;HOST=%s;SERVICE=%s;UID=%s;PWD=%s;" % (
IFX_SERVER, IFX_DB, IFX_HOST, IFX_SERVICE, IFX_USER, IFX_PASS)
#print(ConStr)
try:
# netstat -a | findstr 9088
conn = IfxPy.connect(ConStr, "", "")
except Exception as e:
print('ERROR: Falla de conexion INFORMIX')
print(e)
quit()
#CONSULTA SQL
sql = """
SELECT trim(s.lega) AS legajo, trim(s.nomb) ||' '|| trim(s.apel)||' ['||trim(s.lega)||']'AS full_name
FROM sjsit s
ORDER BY 2
ASC
""".format(**Kwargs)
stmt = IfxPy.exec_immediate(conn, sql)
dic = IfxPy.fetch_assoc(stmt)
#print(dictionary)
lista = []
while dic != False:
lista.append(dic)
dic = IfxPy.fetch_assoc(stmt)
# print(lista)
return lista
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 cargarAsistencia(**Kwargs):
vSede = Kwargs['pSede']
#CONEXION DEFAULT
IFX_HOST = IFX_CEN_HOST
IFX_SERVER = IFX_CEN_SERVER
IFX_SERVICE = IFX_CEN_SERVICE
IFX_DB = IFX_CEN_DB
#COMUN
IFX_USER = '******'
IFX_PASS = '******'
if vSede == 'VTA':
#CONEXION VILLETA
IFX_HOST = IFX_VTA_HOST
IFX_SERVER = IFX_VTA_SERVER
IFX_SERVICE = IFX_VTA_SERVICE
IFX_DB = IFX_VTA_DB
#CADENA DE CONEXION
ConStr = "SERVER=%s;DATABASE=%s;HOST=%s;SERVICE=%s;UID=%s;PWD=%s;" % (
IFX_SERVER, IFX_DB, IFX_HOST, IFX_SERVICE, IFX_USER, IFX_PASS)
#print(ConStr)
try:
# netstat -a | findstr 9088
conn = IfxPy.connect(ConStr, "", "")
except Exception as e:
print('ERROR: Falla de conexion INFORMIX')
print(e)
quit()
# Select records
sql = """
SELECT ashst.ddma_emis as fec_emision,
((CASE WHEN LEN(TRIM(ashst.hora_sali)) > 0 THEN
TO_DATE(ashst.hora_sali,"%Y-%m-%d %H:%M")
ELSE NULL END -
(CASE WHEN LEN(TRIM(ashst.hora_entr)) > 0 THEN
TO_DATE(ashst.hora_entr,"%Y-%m-%d %H:%M")
ELSE NULL END
))::INTERVAL SECOND(6) TO SECOND)::VARCHAR(12)::INT/3600 as hora_trab,
sjsit.lega, sjsit.nomb, sjsit.apel, sjsit.divi,
sjsit.depa, sjsit.secc, sjsit.foli, sjsit.foli_baja,
ashst.lega, ashst.hora_tipo, ashst.turn, ashst.ddma_emis,
CASE WHEN LEN(TRIM(ashst.hora_entr)) > 0 THEN
TO_DATE(ashst.hora_entr,"%Y-%m-%d %H:%M")
ELSE NULL
END AS hora_entr,
CASE WHEN LEN(TRIM(ashst.hora_sali)) > 0 THEN
TO_DATE(ashst.hora_sali,"%Y-%m-%d %H:%M")
ELSE NULL
END AS hora_sali,
ashst.tipo_entr,
ashst.ccos as ccos2, sjdiv.nomb_divi, sjdpt.nomb_depa,
sjsec.nomb_secc, ashot.nomb_hora_tipo,
astur.hora_ent1, astur.hora_sal1, pscco.nomb_ccos,
ashst.conl,ashst.moti_ause,
CASE WEEKDAY(ashst.ddma_emis)
WHEN 0 THEN 'Do'
WHEN 1 THEN 'Lu'
WHEN 2 THEN 'Ma'
WHEN 3 THEN 'Mi'
WHEN 4 THEN 'Ju'
WHEN 5 THEN 'Vi'
WHEN 6 THEN 'Sá'
ELSE '..'
END as ccos
FROM sjsit, OUTER(sjdiv), OUTER(sjdpt), OUTER(sjsec),
ashst, ashot, OUTER(astur), OUTER pscco , OUTER(sjcol),
sjpag, sjdoc
WHERE 1 = 1
and ashst.lega = {pLegajo}
and ashst.ddma_emis >=TO_DATE("{pFechaDesde}","%Y-%m-%d")
and ashst.ddma_emis <=TO_DATE("{pFechaHasta}","%Y-%m-%d")
and sjdoc.lega = ashst.lega
and sjsit.lega = ashst.lega
and sjsit.divi = sjdiv.divi
and sjsit.depa = sjdpt.depa
and sjsit.secc = sjsec.secc
and sjsit.cent_pago = sjpag.cent_pago
and sjsit.foli = sjsit.foli_baja
and ashst.hora_tipo = ashot.hora_tipo
and ashst.turn = astur.turn
and ashst.ccos = pscco.ccos
and ashst.foli = ashst.foli_baja
and ashst.conl = sjcol.conl
ORDER BY 1
""".format(**Kwargs)
#.format(Kwargs['pCedula'],Kwargs['pLegajo'],Kwargs['pMes'],Kwargs['pAnho'])
#print(sql)
stmt = IfxPy.exec_immediate(conn, sql)
dic = IfxPy.fetch_assoc(stmt)
#print(dic)
lista = []
while dic != False:
lista.append(dic)
dic = IfxPy.fetch_assoc(stmt)
# print(lista)
return lista
