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