def run_test_197(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if conn:
      try:
          rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
      except:
          pass
      rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
      rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")

      print "Test first index table:"
      if (server.DBMS_NAME[0:3] == 'IDS'):
        result = ibm_db.statistics(conn,None,config.user,"index_test",True)
      else:
        result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
        
      row = ibm_db.fetch_tuple(result)
      ## skipping table info row. statistics returns informtation about table itself for informix ###
      if (server.DBMS_NAME[0:3] == 'IDS'):
        row = ibm_db.fetch_tuple(result)
      print row[2]  # TABLE_NAME
      print row[3]  # NON_UNIQUE
      print row[5]  # INDEX_NAME
      print row[8]  # COLUMN_NAME

      try:
          rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
      except:
          pass
      rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
      rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")

      print "Test second index table:"
      if (server.DBMS_NAME[0:3] == 'IDS'):
        result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
      else:
        result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
      row = ibm_db.fetch_tuple(result)
      ### skipping table info row. statistics returns informtation about table itself for informix ###
      if (server.DBMS_NAME[0:3] == 'IDS'):
        row = ibm_db.fetch_tuple(result)
      print row[2]  # TABLE_NAME
      print row[3]  # NON_UNIQUE
      print row[5]  # INDEX_NAME
      print row[8]  # COLUMN_NAME

      print "Test non-existent table:"
      if (server.DBMS_NAME[0:3] == 'IDS'):
        result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
      else:
        result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
      row = ibm_db.fetch_tuple(result)
      if row:
        print "Non-Empty"
      else:
        print "Empty"
    else:
      print 'no connection: ' + ibm_db.conn_errormsg()
Ejemplo n.º 2
0
    def run_test_042(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        #if ({}['EMPNO'] != None):
        #  result = ibm_db.exec_immediate(conn, "select photo_format, picture, length(picture) from emp_photo where photo_format='jpg' and empno='" + {}['EMPNO'] + "'")
        #  row = ibm_db.fetch_array(result)
        #  if row:
        #    # We'll be outputting a
        #    header('Content-type: image/' + row[0])
        #    header('Content-Length: ' + row[2])
        #    print row[1]
        #  else:
        #    print ibm_db.error()
        #  continue
        #else:
        result = ibm_db.exec_immediate(
            conn,
            "select EMPNO, PHOTO_FORMAT from emp_photo where photo_format='jpg'"
        )
        row = ibm_db.fetch_tuple(result)
        while (row):
            print(
                "<a href='test_042.php?EMPNO=%s' target=_blank>%s (%s)</a><br>"
                % (row[0], row[0], row[1]))
            row = ibm_db.fetch_tuple(result)
Ejemplo n.º 3
0
def check_audit_col(src_db, schema, view):
    # check if there are audit columns in PDA and BMSIW views
    # src_db - either PDA or DB2
    aud_col_list = []

    if src_db == 'DB2':
        sql = f"SELECT COLNAME, COLNO FROM IDAA.BMSIW_COLUMNS WHERE SCHEMA = '{schema}' AND "\
                f"VIEWNAME = '{view}' ORDER BY COLNO DESC FETCH FIRST 3 ROWS ONLY;"
    elif src_db == 'PDA':
        sql = f"SELECT COLNAME, COLNO FROM IDAA.PDA_REL_COLUMNS WHERE CREATOR = '{schema}' AND "\
                f"NAME = '{view}' ORDER BY COLNO DESC FETCH FIRST 3 ROWS ONLY;"

    else:
        #print('ERROR: Data not found!')
        return aud_col_list

    stmt = ibm_db.exec_immediate(conn, sql)
    res = ibm_db.fetch_tuple(stmt)

    while res:
        aud_col_list.append(res[0])
        res = ibm_db.fetch_tuple(stmt)
    aud_col_list.reverse()

    return aud_col_list
Ejemplo n.º 4
0
 def select_sql(self,SQL):
     stmt = ibm_db.exec_immediate(self.connection,SQL)
     row = ibm_db.fetch_tuple(stmt)
     log.info("execute sql {} {}".format(self.host,self.database))
     while ( row ):
         yield row
         row = ibm_db.fetch_tuple(stmt)
Ejemplo n.º 5
0
    def _read_routines_(self):
        # SQL bodied routines
        stmt = ibm_db.prepare(self._conn_, DBQueries.read_routines)
        ibm_db.execute(stmt, ())
        tpl = ibm_db.fetch_tuple(stmt)
        while tpl:
            specificname, routineschema, routinename, text, rtype, remarks = tpl
            text = os.linesep.join([s for s in text.splitlines() if s])
            r = DBRoutine(specificname, routineschema, routinename, text,
                          rtype, remarks)
            self._routines_[(tpl[0], tpl[1], tpl[2])] = r
            tpl = ibm_db.fetch_tuple(stmt)

        # external routines
        stmt = ibm_db.prepare(self._conn_, DBQueries.read_external_routines)
        ibm_db.execute(stmt, ())
        tpl = ibm_db.fetch_tuple(stmt)
        while tpl:
            specificname, routineschema, routinename, routinetype, typename, length, scale,\
                language, parameter_style, deterministic, external_action, fenced, threadsafe,\
                implementation, remarks, codepage, sql_data_access = tpl
            r = DBRoutineExternal(specificname, routineschema, routinename,
                                  routinetype, typename, length, scale,
                                  language, parameter_style, deterministic,
                                  external_action, fenced, threadsafe,
                                  implementation, remarks, codepage,
                                  sql_data_access)
            self._routines_[(tpl[0], tpl[1], tpl[2])] = r
            tpl = ibm_db.fetch_tuple(stmt)
        self._read_external_routine_params_()
Ejemplo n.º 6
0
def getBatRes(conn, sql, commitnum=500):
    ''' Call fetch_tuple: '''
    num = 0
    tbname = 'TEST'
    stmt = db2.exec_immediate(conn, sql)
    res = db2.fetch_tuple(stmt)
    while res != False:
        insql = "insert into %s values('%s','%s','%s','%s','%s','%s',\
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
                '%s','%s','%s','%s','%s',%s,%s,%s,%s,%s,'%s',\
                %s, %s, %s, %s)" \
                %(tbname, res[0],res[1],res[2],res[3],res[4],res[5],\
                  res[6],res[7],res[8],res[9],res[10],res[11],\
                  res[12],res[13],res[14],\
                  res[15],res[16],res[17],res[18],res[19],res[20],\
                  res[21] or 'Null',\
                  res[22] or 'Null',res[23] or 'Null',\
                  res[24] or 'Null',res[25] or 'Null',res[26] or 'Null', \
                  res[27] or 'Null',res[28] or 'Null',res[29] or 'Null',\
                  res[30] or 'Null')
        ret = insertRec(conn, insql)
        if ret == True:
            num += 1
        if num % commitnum == 0:
            print 'current records:%d, transcation commit.' %num
            db2.commit(conn)
            
        res = db2.fetch_tuple(stmt)
    db2.commit(conn)
    return num
    def run_test_6561(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

            stmt = ibm_db.exec_immediate(
                conn,
                "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
            )
            statement = "SELECT count(id) FROM animals"
            result = ibm_db.exec_immediate(conn, statement)
            if ((not result) and ibm_db.stmt_error()):
                print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))

            row = ibm_db.fetch_tuple(result)
            while (row):
                for i in row:
                    print(i)
                row = ibm_db.fetch_tuple(result)

            ibm_db.rollback(conn)
            ibm_db.close(conn)

        else:
            print("Connection failed.")
Ejemplo n.º 8
0
def fetch_all(stmt):
	t_list = []
	t_row = ibm_db.fetch_tuple(stmt)
	while t_row:
		t_list.append(t_row)
		t_row = ibm_db.fetch_tuple(stmt)
	return t_list
Ejemplo n.º 9
0
def get_station_data(conn, station):

    result = ibm_db.exec_immediate(conn, "SELECT DATE_,ENTRIES,EXITS FROM RIDES_PER_STATION WHERE STATION = \'"
                                   + station + "\'")

    # Creating empty lists for dataframe
    dates = []
    entries = []
    exits = []

    # Parsing info from db2 into pandas dataframe
    row = ibm_db.fetch_tuple(result)
    while row:
        dates.append(row[0])
        entries.append(row[1])
        exits.append(row[2])
        row = ibm_db.fetch_tuple(result)
    ibm_db.close(conn)

    data = pd.DataFrame(data={'DATE': dates, 'ENTRIES': entries, 'EXITS': exits})

    pandemic_date = date(2020, 3, 13)
    for i in data.index:
        current = data.loc[i, 'DATE']
        data.loc[i, 'WEEKDAY'] = 'WEEKDAY' if current.isoweekday() < 6 else 'WEEKEND'
        data.loc[i, 'PANDEMIC'] = 'PRE' if current <= pandemic_date else "POST"

    data.sort_values(by='DATE', inplace=True)
    return data
Ejemplo n.º 10
0
    def call(self, itool):
        """Call xmlservice with accumulated input XML.

        Args:
          itool  - iToolkit object

        Returns:
          xml
        """
        if isinstance(self.uid, str):
            conn = ibm_db.connect(self.db2, self.uid, self.pwd)
        else:
            conn = self.uid
        # sql = "call " + self.lib + ".iPLUG512K(?,?,?,?)"
        sql = "call " + self.lib + ".iPLUGR512K(?,?,?)"
        stmt = ibm_db.prepare(conn, sql)
        ipc = self.ipc
        ctl = self.ctl
        xml_in = itool.xml_in()
        xml_out = ""
        ibm_db.bind_param(stmt, 1, ipc, ibm_db.SQL_PARAM_INPUT)
        ibm_db.bind_param(stmt, 2, ctl, ibm_db.SQL_PARAM_INPUT)
        ibm_db.bind_param(stmt, 3, xml_in, ibm_db.SQL_PARAM_INPUT)
        # ibm_db.bind_param(stmt, 4, xml_out, ibm_db.SQL_PARAM_OUTPUT)
        result = ibm_db.execute(stmt)
        if (result):
            row = ibm_db.fetch_tuple(stmt)
            while (row):
                for i in row:
                    xml_out += i
                row = ibm_db.fetch_tuple(stmt)
        ibm_db.close(conn)
        return xml_out
Ejemplo n.º 11
0
 def run_test_142(self):
   sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
   
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   if conn:
     stmt = ibm_db.prepare(conn, sql)
   
     weight = 200.05
     mass = 2.0
     
     ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
     ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
   
     result = ibm_db.execute(stmt) 
     if ( result ):
       row = ibm_db.fetch_tuple(stmt)
       while ( row ):
         #row.each { |child| print child }
         for i in row:
           print i
         row = ibm_db.fetch_tuple(stmt)
     ibm_db.close(conn)
   else:
     print "Connection failed."
Ejemplo n.º 12
0
def qtask6():
    if request.method == 'POST':
        latitude_one = float(request.form.get('latitudeValueOne'))
        longitude_one = float(request.form.get('longitudeValueOne'))
        latitude_two = float(request.form.get('latitudeValueTwo'))
        longitude_two = float(request.form.get('longitudeValueTwo'))
        depth_from = float(request.form.get('depthFrom'))
        depth_to = float(request.form.get('depthTo'))
        if latitude_one > latitude_two:
            latitude_one, latitude_two = latitude_two, latitude_one
            print('one')

        if longitude_one > longitude_two:
            longitude_one, longitude_two = longitude_two, longitude_one
            print('two')

        list_of_data = []
        sql = "select l.place, q.time, q.id, q.depth, q.mag, q.nst, q.error FROM Q q INNER JOIN L l on l.id = q.id  where (l.latitude between '" + str(
            latitude_one) + "' and '" + str(
                latitude_two) + "') and (l.longitude between '" + str(
                    longitude_one) + "' and '" + str(
                        longitude_two) + "' ) and q.depth between '" + str(
                            depth_from) + "' and '" + str(depth_to) + "'"
        print(sql)
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_tuple(stmt)
        # print(result)
        while result:
            list_of_data.append(result)
            result = ibm_db.fetch_tuple(stmt)
        print(list_of_data)
        return render_template('qtask1.html', data=list_of_data)
Ejemplo n.º 13
0
def task1():
    if request.method == 'POST':
        latitude = request.form.get('latitudeValue')
        longitude = request.form.get('longitudeValue')
        list_of_data = []
        sql = "SELECT *,\
        (\
            (\
                (\
                    acos(\
                        sin((" + latitude + " * 0.01745329251))\
                        *\
                        sin((latitude * 0.01745329251)) +\
                        cos((" + latitude + " * 0.01745329251))\
                        *\
                        cos((latitude * 0.01745329251)) \
                        * \
                        cos(((" + longitude + " - longitude) * 0.01745329251))) \
                ) * 57.2957795131\
            ) * 60 * 1.1515 \
        )\
        as distance FROM eqi where mag > 6  ORDER BY Distance asc LIMIT 1"

        # print(sql)
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_tuple(stmt)
        # print(result)
        while result:
            list_of_data.append(result)
            result = ibm_db.fetch_tuple(stmt)
        # print(list_of_data)
        return render_template('eqmagabove6.html',
                               data=list_of_data,
                               lat=latitude,
                               long=longitude)
Ejemplo n.º 14
0
def task5():
    if request.method == 'POST':
        latitude_one = float(request.form.get('latitudeValueOne'))
        longitude_one = float(request.form.get('longitudeValueOne'))
        latitude_two = float(request.form.get('latitudeValueTwo'))
        longitude_two = float(request.form.get('longitudeValueTwo'))
        if latitude_one > latitude_two:
            latitude_one, latitude_two = latitude_two, latitude_one
            print('one')

        if longitude_one > longitude_two:
            longitude_one, longitude_two = longitude_two, longitude_one
            print('two')

        list_of_data = []
        sql = "(SELECT *\
        FROM latlong where (latitude between '" + str(
            latitude_one) + "' and '" + str(
                latitude_two) + "') and (longitude between " + str(
                    longitude_one) + " and " + str(longitude_two) + " )) "
        print(sql)
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_tuple(stmt)
        # print(result)
        while result:
            list_of_data.append(result)
            result = ibm_db.fetch_tuple(stmt)
        # print(list_of_data)
        return render_template('task5.html',
                               data=list_of_data,
                               latitude_one=latitude_one,
                               longitude_one=longitude_one,
                               latitude_two=latitude_two,
                               longitude_two=longitude_two)
  def run_test_158(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
      ibm_db.set_option(conn, op, 1)

    result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
    
    output = ''
    row = ibm_db.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 = ibm_db.fetch_assoc(result)
      
    result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
    row2 = ibm_db.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 = ibm_db.fetch_assoc(result2)
    
    result3 = ibm_db.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 = ibm_db.fetch_tuple(result3)
    while ( row3 ):
        output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
        row3=ibm_db.fetch_tuple(result3)
    print(output)
    def run_test_022(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)

            ibm_db.autocommit(conn, 0)
            ac = ibm_db.autocommit(conn)
            if ac != 0:
                print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
                #continue

            ibm_db.exec_immediate(
                conn,
                "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")

            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)

            ibm_db.rollback(conn)

            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)
            ibm_db.close(conn)
        else:
            print("Connection failed.")
Ejemplo n.º 17
0
 def run_test_020(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   if conn:
       
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
     
     ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
     ac = ibm_db.autocommit(conn)
     if ac != 0:
       print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
       #continue 
     
     ibm_db.exec_immediate(conn, "DELETE FROM animals")
     
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
      
     ibm_db.rollback(conn)
      
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
     ibm_db.close(conn)
   else:
     print "Connection failed."
Ejemplo n.º 18
0
def populateTableDump(inputConn, tableName):
    sql = 'SELECT * FROM E0015DB.' + tableName + ' WHERE COMPANY_CODE = \'MLF\' FETCH FIRST ' + str(
        determineRowCount(conn, tableName)) + ' ROWS ONLY'
    stmt = ibm_db.exec_immediate(inputConn, sql)
    dictionary = ibm_db.fetch_tuple(stmt)
    tableList = []
    while dictionary != False:
        currentRow = list(dictionary)
        for i in range(len(currentRow)):
            try:
                currentRow[i] = currentRow[i].strip()
            except:
                pass

            try:
                if i == 3 or i == 6:
                    currentRow[i] = currentRow[i].replace('\x9f', '*')

                if i == 4 or i == 5:
                    currentRow[i] = currentRow[i].strftime('%Y-%m-%d')
            except:
                pass
        tableList.append(currentRow)
        dictionary = ibm_db.fetch_tuple(stmt)
    return tableList
    def run_test_020(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:

            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)

            ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
            ac = ibm_db.autocommit(conn)
            if ac != 0:
                print("Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test")
                #continue

            ibm_db.exec_immediate(conn, "DELETE FROM animals")

            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)

            ibm_db.rollback(conn)

            stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
            res = ibm_db.fetch_tuple(stmt)
            rows = res[0]
            print(rows)
            ibm_db.close(conn)
        else:
            print("Connection failed.")
Ejemplo n.º 20
0
def get_legacy_view_name(pda_schema, pda_view):
    sql = f"SELECT OLD_SCHEMA, OLD_VIEW_NAME FROM IDAA.PDA_VIEW_MAP WHERE NEW_SCHEMA = '{pda_schema}' AND NEW_VIEW_NAME = '{pda_view}' FETCH FIRST 1 ROW ONLY;"
    stmt = ibm_db.exec_immediate(conn, sql)
    tuple = ibm_db.fetch_tuple(stmt)
    while tuple:
        return tuple[0], tuple[1]
        tuple = ibm_db.fetch_tuple(stmt)
Ejemplo n.º 21
0
def report_changes_for_contexts(schema, csvFile, outputcsv):
    writer = print_legend(csvFile, outputcsv)
    # Execute query to find all suffixes being replicated
    sql = (
        "select eid, dn_trunc "
        "from {}.ldap_entry "
        "where eid in "
        "(select peid "
        "from {}.ldap_entry as l, {}.OBJECTCLASS as o "
        "where l.eid=o.eid "
        "and o.OBJECTCLASS='IBM-REPLICAGROUP')"
    ).format(schema, schema, schema)
    logger.debug("Executing SQL: {}".format(sql))
    stmt = ibm_db.exec_immediate(conn, sql)
    result = ibm_db.fetch_tuple(stmt)
    while (result):
        eid, context = result[0], result[1]
        logger.debug("eid: {} context: {}".format(eid, format))
        tablename = "REPLCHG{}".format(eid)
        ret_data1 = get_changes(conn, schema, tablename, 0)  # Successful changes
        if not ret_data1:
            logger.info("No replication data found for successful changes, perhaps no replication setup for {}?"
                        .format(context))
            write_or_print_data(writer, context, None, None, None, outputcsv)
        else:
            maxChangeID = get_latest_update(conn, schema, tablename)
            ret_data2 = get_changes(conn, schema, tablename, 1)  # Oldest Pending changes
            write_or_print_data(writer, context, ret_data1, maxChangeID, ret_data2, outputcsv)
        result = ibm_db.fetch_tuple(stmt)
  def run_test_158(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
      ibm_db.set_option(conn, op, 1)

    result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
    
    output = ''
    row = ibm_db.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 = ibm_db.fetch_assoc(result)
      
    result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
    row2 = ibm_db.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 = ibm_db.fetch_assoc(result2)
    
    result3 = ibm_db.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 = ibm_db.fetch_tuple(result3)
    while ( row3 ):
        output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
        row3=ibm_db.fetch_tuple(result3)
    print(output)
Ejemplo n.º 23
0
    def query_db(self, query, args=()):
        """Submits database query.
    
        Examples:
    
        for user in query_db('select * from users'):
            print user['username'], 'has the id', user['user_id']
        
        for user in query_db('select * from users where username = ?', [the_username]):
            print user['username'], 'has the id', user['user_id']

        Returns list
        list = list of rows, where each row is represented using tuple
        """
        rows = []
        if self._conn:
            log.debug("Running query\n" + query)
            log.debug("Query params: " + pprint.pformat(args))
            stmt = ibm_db.prepare(self._conn, query)
    
            for i, param in enumerate(args):
                ibm_db.bind_param(stmt, i, param)
    
            ibm_db.execute(stmt)
            if re.search('create|insert|update|delete', query, re.I):
                return rows

            row = ibm_db.fetch_tuple(stmt)
            while (row):
                rows.append(row)
                row = ibm_db.fetch_tuple(stmt)

        return rows
    def run_test_146(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        if conn:
            name = "Peaches"
            second_name = "Rickety Ride"
            weight = 0

            print("Values of bound parameters _before_ CALL:")
            print("  1: %s 2: %s 3: %d\n" % (name, second_name, weight))

            stmt, name, second_name, weight = ibm_db.callproc(
                conn, 'match_animal', (name, second_name, weight))

            if stmt is not None:
                print("Values of bound parameters _after_ CALL:")
                print("  1: %s 2: %s 3: %d\n" % (name, second_name, weight))

                if (server.DBMS_NAME[0:3] != 'IDS'):
                    print("Results:")
                    row = ibm_db.fetch_tuple(stmt)
                    while (row):
                        print("  %s, %s, %s" %
                              (row[0].strip(), row[1].strip(), row[2]))
                        row = ibm_db.fetch_tuple(stmt)
 def run_test_022(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   if conn:
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.autocommit(conn, 0)
     ac = ibm_db.autocommit(conn)
     if ac != 0:
       print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
       #continue
       
     ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
       
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.rollback(conn)
     
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
     ibm_db.close(conn)
   else:
     print("Connection failed.")
Ejemplo n.º 26
0
    def getModel(self, spcItem):
        spcId = spcItem["SPCID"]
        sites = spcItem["SITE"]
        groupingKeys = spcItem["GROUPING_KEYS"]
        sql = self.getQuerySQL(spcItem)

        if self._debug:
            print sql

        rets = {}
        for site in sites:
            conn = self.getDBConnection(site)
            stmt = ibm_db.exec_immediate(conn, sql)
            mtypeTuple = ibm_db.fetch_tuple(stmt)
            mtypes = Set([])
            while mtypeTuple != False:
                mtypes.add(mtypeTuple[0])
                mtypeTuple = ibm_db.fetch_tuple(stmt)
            rets[site] = mtypes
            if self._debug:
                print site, mtypes
        
        # union all the results
        uRet = Set([])
        for ret in rets.itervalues():
            uRet = uRet.union(ret)
        
        return list(uRet)
    def run_test_143(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

        insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
        select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'

        if conn:
            stmt = ibm_db.prepare(conn, insert1)

            animal = None
            ibm_db.bind_param(stmt, 1, animal)

            if ibm_db.execute(stmt):
                stmt = ibm_db.exec_immediate(conn, select)
                row = ibm_db.fetch_tuple(stmt)
                while (row):
                    #row.each { |child| print child }
                    for i in row:
                        print(i)
                    row = ibm_db.fetch_tuple(stmt)

            ibm_db.rollback(conn)
        else:
            print("Connection failed.")
Ejemplo n.º 28
0
def get_db_list(admin_db, user):
    global global_db_list
    db_list = []
    tLock.acquire()
    print("db_list has acquired a lock")
    # Get password
    str = open('C:/Users/sxk11/PycharmProjects/Safari_1/test.txt', 'r').read()
    conn = ibm_db.connect('ADM1P', 'tu01945', str)
    sql = 'SELECT * FROM DB2ADM1P.UDB_DB_NAMES ' \
          'where ' \
          'db_all_chk_ind = \'Y\' ' \
          'and db_alias in (\'LSS1D\', \'EDT5D\', \'EDT6D\', \'DBI1D\') '
    # 'db_status = \'D\' '\
    stmt = ibm_db.prepare(conn, sql)
    ibm_db.execute(stmt)
    tuple = ibm_db.fetch_tuple(stmt)
    while tuple != False:
        print("The ID is : ", tuple[0])
        print("The alias name is : ", tuple[3])
        db_name = tuple[2]
        db_list.append(db_name)
        tuple = ibm_db.fetch_tuple(stmt)
    print(db_list)

    tLock.release()
    print("db_list has completed lock released")
    global_db_list = db_list
Ejemplo n.º 29
0
def comment(id):
    conn = connect.connection()
    if "mail" in session:
        prevProjects = set()
        sql_stmt = "select * from projekt where kennung = %s" % id
        prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
        if prevPorjectsResult is not None:
            row = ibm_db.fetch_tuple(prevPorjectsResult)
            prevProjects.add(row)
            while row:
                row = ibm_db.fetch_tuple(prevPorjectsResult)
                if row:
                    prevProjects.add(row)

        if request.method == "POST":
            comment = request.form["comment"]
            anonymousid = request.form["anonymousid"]
            mail = session["mail"]
            if anonymousid == 'yes':
                sql_stmt1 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','privat')"
                prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt1)
            else:
                sql_stmt2 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','oeffentlich')"
                prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt2)
            return redirect(url_for("projectdetails", id=id))

        return render_template('comment.html', prevProjects=prevProjects)

    else:
        return redirect(url_for("login"))
Ejemplo n.º 30
0
def project():
    if "mail" in session:

        conn = connect.connection()

        openProjects = set()
        sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email  where  ersteller = '%s' and status='offen'" % \
                   session["mail"]
        openProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
        if openProjectsResult is not None:
            row = ibm_db.fetch_tuple(openProjectsResult)
            openProjects.add(row)
            while row:
                row = ibm_db.fetch_tuple(openProjectsResult)
                if row:
                    openProjects.add(row)

        closeProjects = set()
        sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email  where  ersteller = '%s' and status='geschlossen'" % \
                   session["mail"]
        closeProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
        if closeProjectsResult is not None:
            row = ibm_db.fetch_tuple(closeProjectsResult)
            closeProjects.add(row)
            while row:
                row = ibm_db.fetch_tuple(closeProjectsResult)
                if row:
                    closeProjects.add(row)

        return render_template('projects.html', openProjects=openProjects, closeProjects=closeProjects,
                               ses_email=session["mail"])
    else:
        return redirect(url_for("login"))
  def run_test_143(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

    insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
    select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
    
    if conn:
      stmt = ibm_db.prepare(conn, insert1)
    
      animal = None
      ibm_db.bind_param(stmt, 1, animal)
    
      if ibm_db.execute(stmt):
        stmt = ibm_db.exec_immediate(conn, select)
        row = ibm_db.fetch_tuple(stmt)
        while ( row ):
          #row.each { |child| print child }
          for i in row:
            print(i)
          row = ibm_db.fetch_tuple(stmt)

      ibm_db.rollback(conn)
    else:
      print("Connection failed.")
    def run_test_197(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info( conn )

        if conn:
            try:
                rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
            except:
                pass
            rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
            rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")

            print("Test first index table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn,None,config.user,"index_test",True)
            else:
                result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
            row = ibm_db.fetch_tuple(result)
            ## skipping table info row. statistics returns informtation about table itself for informix ###
            if (server.DBMS_NAME[0:3] == 'IDS'):
                row = ibm_db.fetch_tuple(result)
            print(row[2])  # TABLE_NAME
            print(row[3])  # NON_UNIQUE
            print(row[5])  # INDEX_NAME
            print(row[8])  # COLUMN_NAME

            try:
                rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
            except:
                pass
            rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
            rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")

            print("Test second index table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
            else:
                result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
            row = ibm_db.fetch_tuple(result)
            ### skipping table info row. statistics returns informtation about table itself for informix ###
            if (server.DBMS_NAME[0:3] == 'IDS'):
                row = ibm_db.fetch_tuple(result)
            print(row[2])  # TABLE_NAME
            print(row[3])  # NON_UNIQUE
            print(row[5])  # INDEX_NAME
            print(row[8])  # COLUMN_NAME

            print("Test non-existent table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
            else:
                result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
            row = ibm_db.fetch_tuple(result)
            if row:
                print("Non-Empty")
            else:
                print("Empty")
        else:
            print('no connection: ' + ibm_db.conn_errormsg())
Ejemplo n.º 33
0
    def run_test_064(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        create = 'CREATE SCHEMA AUTHORIZATION t'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.tables(conn, None, 't')
        else:
            result = ibm_db.tables(conn, None, 'T')

        for i in range(0, ibm_db.num_fields(result)):
            print "%s, " % ibm_db.field_name(result, i)
        print
        print

        i = 0
        row = ibm_db.fetch_tuple(result)
        while (row):
            ibm_db.num_fields(result)
            if (i < 4):
                print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n"
            i = i + 1
            row = ibm_db.fetch_tuple(result)

        ibm_db.free_result(result)

        ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
Ejemplo n.º 34
0
  def run_test_064(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    create = 'CREATE SCHEMA AUTHORIZATION t'
    try:
      result = ibm_db.exec_immediate(conn, create)
    except:
      pass
    
    create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create)
    except:
      pass
    
    create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create)
    except:
      pass
    
    create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create)
    except:
      pass
    
    create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create)
    except:
      pass
    
    if (server.DBMS_NAME[0:3] == 'IDS'):
        result = ibm_db.tables(conn, None, 't')
    else:
        result = ibm_db.tables(conn, None, 'T')
    
    for i in range(0, ibm_db.num_fields(result)):
      print("%s, " % ibm_db.field_name(result, i))
    print()
    print()
  
    i = 0
    row = ibm_db.fetch_tuple(result)
    while ( row ):
      ibm_db.num_fields(result)
      if (i < 4):
        print(", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n")
      i = i + 1
      row = ibm_db.fetch_tuple(result)

    ibm_db.free_result(result)

    ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
Ejemplo n.º 35
0
 def execute(self,sql):
     self._stmt = ibm_db.exec_immediate(self._conn,sql)
     self._result = ibm_db.fetch_tuple(self._stmt)
     self.result_list = []
     while(self._result):
         self.result_list.append(self._result)
         self._result = ibm_db.fetch_tuple(self._stmt)
     return self.result_list
Ejemplo n.º 36
0
 def queryTuples( self, _sql ):
   stmt = ibm_db.exec_immediate( self.connection, _sql )
   tuples = []
   tup = ibm_db.fetch_tuple( stmt )
   while tup != False:
     tuples.append( tup )
     tup = ibm_db.fetch_tuple( stmt )
   return tuples
Ejemplo n.º 37
0
def connect_db2():
    for (dbtype, customer, dbname, dbuser, dbpass, dbalias, sid, hostname,
         dbport, usertunn, hostunn, portunn, umbralert, ip) in data_sql_db2:
        print(dbtype, customer, dbalias, ip)
        if customer[:1] != '#':
            try:
                # Create connection to remote DB2
                con = ibm_db.connect(
                    "DATABASE=" + sid + ";HOSTNAME=" + hostname +
                    ";PROTOCOL=TCPIP;UID=" + dbuser + ";PWD=" + dbpass +
                    ";PORT=" + dbport, "", "")
            except:
                # Connection error handling
                data_error = traceback.format_exc().splitlines()
                list_connection_fail.append({
                    "CLIENTE": customer,
                    "ALIAS": dbalias,
                    "SID": sid,
                    "ERROR": str(data_error[-1])
                })
        # TODO make error handling for connection
            else:
                try:
                    # Execute query to get space
                    stmt = ibm_db.exec_immediate(
                        con,
                        "select DB_STORAGE_PATH,FS_TOTAL_SIZE,STO_PATH_FREE_SIZE,FS_USED_SIZE from sysibmadm.SNAPSTORAGE_PATHS"
                    )
                except:
                    # Query error handling
                    data_error = traceback.format_exc().splitlines()
                    list_connection_fail.append({
                        "CLIENTE": customer,
                        "ALIAS": dbalias,
                        "SID": sid,
                        "ERROR": str(data_error[-1])
                    })
            # TODO make error handling for query
                else:
                    # Fetch first result
                    result = ibm_db.fetch_tuple(stmt)
                    inserts = []
                    while (result):
                        # TODO whatever needed with the result
                        inserts.append("('" + customer + "','" + dbtype +
                                       "','" + time_i + "','" + sid + "','" +
                                       dbname + "','" + str(result[0]) + "'," +
                                       str(float(result[1]) / 1000.0) + "," +
                                       str(float(result[2]) / 1000.0) + "," +
                                       str(float(result[3]) / 1000.0) +
                                       ",0,0,0,0,0)")
                        result = ibm_db.fetch_tuple(stmt)

                    stm = "INSERT INTO espacios_stats values " + ','.join(
                        inserts) + ";"
                    cur_sql_db2.execute(stm)
                    cnx_1.commit()
    return
  def run_test_065(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    create = 'CREATE SCHEMA AUTHORIZATION t'
    try:
      result = ibm_db.exec_immediate(conn, create) 
    except:
      pass
    
    create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create) 
    except:
      pass
    
    create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create) 
    except:
      pass
      
    create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create) 
    except:
      pass
    
    create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
    try:
      result = ibm_db.exec_immediate(conn, create) 
    except:
      pass
    
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.tables(conn, None, '%', "t3")
    else:
      result = ibm_db.tables(conn, None, '%', "T3")
    
    columns = ibm_db.num_fields(result)
    
    for i in range(0, columns):
      print "%s, " % ibm_db.field_name(result, i)
    print "\n\n"
   
    row = ibm_db.fetch_tuple(result) 
    while ( row ):
      final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , ";
      row = ibm_db.fetch_tuple(result)

    print final
    
    ibm_db.free_result(result)

    ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
    ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
Ejemplo n.º 39
0
    def run_test_065(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        create = 'CREATE SCHEMA AUTHORIZATION t'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.tables(conn, None, '%', "t3")
        else:
            result = ibm_db.tables(conn, None, '%', "T3")

        columns = ibm_db.num_fields(result)

        for i in range(0, columns):
            print("%s, " % ibm_db.field_name(result, i))
        print("\n\n")

        row = ibm_db.fetch_tuple(result)
        while (row):
            final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "
            row = ibm_db.fetch_tuple(result)

        print(final)

        ibm_db.free_result(result)

        ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
Ejemplo n.º 40
0
def get_pda_schema_list():
    sql = f"SELECT DISTINCT TRIM(NEW_SCHEMA) AS PDA_SCHEMA FROM IDAA.PDA_VIEW_MAP ORDER BY 1"
    stmt = ibm_db.exec_immediate(conn, sql)
    tuple = ibm_db.fetch_tuple(stmt)
    pda_schemas = []
    while tuple:
        pda_schemas.append(tuple[0])
        tuple = ibm_db.fetch_tuple(stmt)
    return pda_schemas
Ejemplo n.º 41
0
def determineRowCount(inputConn, tableName):
    sqlCount = 'SELECT COUNT(*) FROM E0015DB.' + tableName + ' WHERE COMPANY_CODE = \'MLF\''
    stmt = ibm_db.exec_immediate(inputConn, sqlCount)
    dictionary = ibm_db.fetch_tuple(stmt)
    rowCount = 0
    while dictionary != False:
        rowCount = int(dictionary[0])
        dictionary = ibm_db.fetch_tuple(stmt)
    return rowCount
Ejemplo n.º 42
0
def get_pda_view_list(schema):
    sql = f"SELECT DISTINCT TRIM(NEW_VIEW_NAME) AS PDA_VIEW FROM IDAA.PDA_VIEW_MAP WHERE NEW_SCHEMA = '{schema}'ORDER BY 1"
    stmt = ibm_db.exec_immediate(conn, sql)
    tuple = ibm_db.fetch_tuple(stmt)
    pda_views = []
    while tuple:
        pda_views.append(tuple[0])
        tuple = ibm_db.fetch_tuple(stmt)
    return pda_views
Ejemplo n.º 43
0
def main():
    conn = None
    stmt = None
    if not conf.has_option('passwd'):
        conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8'))
    try:
        conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'), 
                                                                                              conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '')
        stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8'))
        try:
            result = ibm_db.fetch_tuple(stmt)
        except:
            rows = ibm_db.num_rows(stmt)
            if rows != -1:
                print u'Обработано строк %d' % rows
            else:
                print u'Команда выполнена'
            return
        if result:
            column_conv = []
            head = u''
            underline=u''
            for i in xrange(len(result)):
                if i != 0:
                    head += u'|'
                    underline += u'+'
                name = ibm_db.field_name(stmt, i)
                size = ibm_db.field_display_size(stmt, i)
                if len(name) > size:
                    size = len(name)
                if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size:
                    size = len(u'NULL')
                type_field = ibm_db.field_type(stmt, i)
                if type_field == 'float' or type_field == 'real' or type_field == 'decimal':
                    column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float})
                elif type_field == 'int' or type_field == 'bigint':
                    column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int})
                else:
                    column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert})
                head += name.center(size)
                underline += u'-' * size
            print head
            print underline
            while( result ):
                print conv(result, column_conv)
                result = ibm_db.fetch_tuple(stmt)
        else:
            print u'Результата не возвращено'
    except Exception as e:
        print >> sys.stderr, e
        sys.exit(-1)
    finally:
        if stmt:
            ibm_db.free_result(stmt)
        if conn:
            ibm_db.close(conn)
  def run_test_046(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format FROM emp_photo") 
    else:
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(picture) FROM emp_photo")
    row = ibm_db.fetch_tuple(result)
    while ( row ):
      if row[1] != 'xwd':
        print("<a href='test_046.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a><br>" % (row[0], row[1], row[0], row[1], row[2]))
      row = ibm_db.fetch_tuple(result)
Ejemplo n.º 45
0
def connectBD2():
	# 连接数据库
    # conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '')
    conn = ibm_db.connect(conn_str, '', '')
    # 获取数据库服务器信息
    server = ibm_db.server_info(conn)
    # 查询数据库sql
    sql ="select * from emp"
    if conn:
        result = ibm_db.exec_immediate(conn, sql)
        onerow = ibm_db.fetch_tuple(result)
        while ( onerow ):
            print(onerow)
            onerow = ibm_db.fetch_tuple(result)
 def run_test_130(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   if conn:
     stmt = ibm_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
   
     if ibm_db.execute(stmt):
       row = ibm_db.fetch_tuple(stmt)
       while ( row ):
         for i in row:
           print i
           row = ibm_db.fetch_tuple(stmt)
   else:
     print "Connection failed."
Ejemplo n.º 47
0
 def get_source_data(self, querysql, queue):
     """ Call fetch_row """
     num = 0
     conn = self.get_conn()
     try:
         stmt = ibm_db.exec_immediate(conn, querysql)
         result = ibm_db.fetch_tuple(stmt)
         while result:
             queue.put(result)
             num += 1
             result = ibm_db.fetch_tuple(stmt)
     except Exception as e:
         print e.args
     return num
 def run_test_131(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   if conn:
     stmt = ibm_db.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?" )
   
     if ibm_db.execute(stmt, (0,)):
       row = ibm_db.fetch_tuple(stmt)
       while ( row ):
         #row.each { |child| print child }
         for i in row:
           print(i)
         row = ibm_db.fetch_tuple(stmt)
   else:
     print("Connection failed.")
Ejemplo n.º 49
0
def executeSelect(statement):
    connection = ibm_db.pconnect(url, '', '')
    statement = ibm_db.prepare(connection, statement)
    ibm_db.execute(statement)
    data = ibm_db.fetch_tuple(statement)
    result = []     
    
    while (data):
        result.append(data)
        data = ibm_db.fetch_tuple(statement)
    
    ibm_db.free_stmt(statement)
    ibm_db.close(connection)
    
    return result
  def run_test_047(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format from emp_photo")
    else:
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(PICTURE) from emp_photo")
    row = ibm_db.fetch_tuple(result)
    while ( row ):
      if (row[1] == 'gif'):
        print "<img src='test_047.php?EMPNO=%s&FORMAT=%s'><br>\n" % (row[0], row[1])
      if (row[1] != 'xwd'):
        print "<a href='test_047.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a>\n<br>" % (row[0], row[1], row[0], row[1], row[2])
      row = ibm_db.fetch_tuple(result)
Ejemplo n.º 51
0
  def run_test_021(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
      
    if conn:
      stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
      res = ibm_db.fetch_tuple(stmt)
      rows = res[0]
      print rows
        
      ibm_db.autocommit(conn, 0)
      ac = ibm_db.autocommit(conn)
      if ac != 0:
        print "Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test"
        #continue
        
      ibm_db.exec_immediate(conn, "DELETE FROM animals")
        
      stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
      res = ibm_db.fetch_tuple(stmt)
      rows = res[0]
      print rows
        
      ibm_db.commit(conn)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
      res = ibm_db.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 = ibm_db.prepare(conn, insert)
      if stmt:
        for animal in animals:
          result = ibm_db.execute(stmt, animal)
      ibm_db.commit(conn)
      ibm_db.close(conn)
    else:
      print "Connection failed."
Ejemplo n.º 52
0
def summation(q, txtBefore, txtAfter):
    sum_str = q[2]
    # Connect to DB
    conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
    if conn is None:
        raise Usage(ibm_db.conn_errormsg())
    ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
    # Set isolation level
    ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
    # Prepare statement
    sum_stmt = ibm_db.prepare(conn, sum_str)
    if sum_stmt == False:
        raise Usage("Failed to prepare sum query")
    # Execute statement
    if ibm_db.execute(sum_stmt) == False:
        raise Usage("Failed to execute the sum query")
    sum = ibm_db.fetch_tuple(sum_stmt)
    ibm_db.commit(conn)
    # Print result set to output file
    try:
        f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
        f.write(txtBefore + str(sum) + txtAfter)
        f.close()
    except IOError, e:
        raise Usage("Failed to manipulate output.txt.\n")
 def run_test_260(self):
     conn = ibm_db.connect(config.database, config.user, config.password)
     
     if conn:
       stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
       
       row = ibm_db.fetch_tuple(stmt)
       while ( row ):
           for i in row:
               print(i)
           row = ibm_db.fetch_tuple(stmt)
       
       ibm_db.close(conn)
       
     else:
       print("Connection failed.")
Ejemplo n.º 54
0
def experiment(query_str,g):
    # generate nb of parameters for query
    matchList  = re.findall('\?', query_str)
    nbParams   = len(matchList)
    if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)")
    # Connect to DB
    conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','')
    if conn is None: raise Usage(ibm_db.conn_errormsg())
    # Prepare statement
    query_stmt   = ibm_db.prepare(conn, query_str)
    if (query_stmt == False): raise Usage("Failed to prepare query")
    # Execute statement
    for i in range(NBQUERIES): 
        if (nbParams == 0): 
            if ibm_db.execute(query_stmt) == False:
                raise Usage("Failed to execute the query")
        else:
            t = g.getWrite(i)
            l = list(t)
            u = [l[j] for j in range(len(l)) if j in ATTLIST]
            if ibm_db.execute(query_stmt, tuple(u)) == False:
                raise Usage("Failed to execute the query") 
        nbtuples = 0
        while (ibm_db.fetch_tuple(query_stmt) != False):
            nbtuples += 1
        print "Query"+str(i)+": "+str(nbtuples)+" fetched."
    # Disconnect from DB
    status = ibm_db.close(conn)
    if status == False: raise Usage("Failed to close db connection.\n") 
Ejemplo n.º 55
0
Archivo: svtp.py Proyecto: svrist/itu
def summation(q):
    sum_str = q[2]
    # Connect to DB
    start=time.time()
    #sys.stderr.write("Start sum %s\n"%(start))
    for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
        #sys.stderr.write("Sum %d"%n)
        conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
        if conn is None: raise Usage(ibm_db.conn_errormsg())
        ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
        # Set isolation level
        ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
        # Prepare statement
        sum_stmt   = ibm_db.prepare(conn, sum_str)
        if (sum_stmt == False): raise Usage("Failed to prepare sum query")
        # Execute statement
        if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
        sum= ibm_db.fetch_tuple(sum_stmt)
        ibm_db.commit(conn)
        # Print result set to output file
        try:
            f = open(OUTPUT_FILE_PATH, 'a')
            f.write(str(sum)+'\n')
            # f.close
        except IOError, e:
            raise Usage("Failed to manipulate sum.sql.\n")
        finally:
Ejemplo n.º 56
0
  def run_test_040(self): 
    conn = ibm_db.connect(config.database, config.user, config.password)

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
      
    # Drop the test table, in case it exists
    drop = 'DROP TABLE animals'
    try:
      result = ibm_db.exec_immediate(conn, drop)
    except:
      pass
      
    # Create the test table
    create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
    result = ibm_db.exec_immediate(conn, create)
      
    insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
      
    ibm_db.exec_immediate(conn, insert)
      
    stmt = ibm_db.exec_immediate(conn, "select * from animals")
    
    onerow = ibm_db.fetch_tuple(stmt)
     
    for element in onerow:
      print element

    ibm_db.rollback(conn)
 def run_test_044(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   result = ibm_db.exec_immediate(conn, "select * from sales")
    
   row = ibm_db.fetch_tuple(result)
   while ( row ):
     #printf("%-10s ",row[0])
     #printf("%-15s ",row[1])
     #printf("%-15s ",row[2])
     #printf("%4s",row[3])
     #print
     if (row[3] == None):
       row = row[0:3] + (' ',)
     print("%-10s %-15s %-15s %4s" % (row[0], row[1], row[2], row[3]))
     row = ibm_db.fetch_tuple(result)
Ejemplo n.º 58
0
def get_xml_by_id(conn, articleid):
    if conn:
        sql = "SELECT FULLTEXT FROM ARTICLE WHERE ID = " + str(articleid) + ";"
        print("get_xml_by_id_sql", articleid)
        stmt = ibm_db.exec_immediate(conn, sql)
        result = ibm_db.fetch_tuple(stmt)
        return etree.fromstring(result[0].encode("utf-8").replace("encoding=\"UTF-16\"", "encoding=\"UTF-8\""))
Ejemplo n.º 59
0
 def queryDict( self, _sql ):
   stmt = ibm_db.exec_immediate( self.connection, _sql )
   dictionary = []
   item = ibm_db.fetch_assoc( stmt )
   while item != False:
     dictionary.append( item )
     item = ibm_db.fetch_tuple( stmt )
   return dictionary