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)
Beispiel #2
0
    def run_test_SPInOutZero(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        serverinfo = ibm_db.server_info(conn)
        server = serverinfo.DBMS_NAME[0:3]

        if (server == 'IDS'):
            procedure = """CREATE PROCEDURE TEST_OUT_ZERO_INT(OUT O_RETURN INTEGER); LET O_RETURN = 0;END PROCEDURE;"""
        else:
            procedure = """CREATE PROCEDURE TEST_OUT_ZERO_INT(OUT O_RETURN SMALLINT) BEGIN DECLARE C_RET_OK SMALLINT CONSTANT 0; DECLARE C_RET_EXCEPT  SMALLINT CONSTANT -1; SET O_RETURN = C_RET_OK; RETURN; END"""

        if conn:
            try:
                ibm_db.exec_immediate(conn, 'DROP PROCEDURE TEST_OUT_ZERO_INT')
            except:
                pass
            ibm_db.exec_immediate(conn, procedure)
            stmt, ret = ibm_db.callproc(conn, "TEST_OUT_ZERO_INT", (99, ))

            if ret is not None:
                print("{}".format(ret))
            else:
                print("ret is None")
            ibm_db.close(conn)
        else:
            print("Connection failed.")
Beispiel #3
0
def GetStoredProcResult(args_list):
  """Get the results from running the stored proc.

  Args:
    args_list: A list of stored procedure arguments.
  
  Returns:
    actual_value: An int of the stored proc result.
  """
  # initialize the return values
  x_val, y_val = 0, 0
  stored_proc = '%s' % args_list[1]
  if len(args_list) == 2:
    sp_args_tuple = (args_list[0], x_val, y_val)
    return_key = 2
  else:
    sp_args_tuple = (args_list[0], args_list[2], x_val, y_val)
    return_key = 3
 
  username, password = ReadCredentials()
  try:
    conn = ibm_db.connect('db2mond', username, password)
    results_tuple = ibm_db.callproc(conn, stored_proc, sp_args_tuple)
  except Exception, e:
    print str(e)
    print RESULTS_TEMPLATE % ('UNKNOWN', 'could not connect to DB2mond')
    sys.exit(3)
Beispiel #4
0
    def collect(self, conn):
        try:
            # reorgchk tables
            (stmt, param1, param2) = ibm_db.callproc(conn, self.procname, tuple(self.params))
            if stmt:
                result = ibm_db.fetch_assoc(stmt)
                while result != False:
                    if self.add_inst_name:
                        if ('INST' not in result.keys()):
                            result['INST'] = self.server_info['inst_name'].upper()
                        else:
                            result['INST'] = result['INST'].upper()

                    if self.add_db_name:
                        if ('DB' not in result.keys()):
                            result['DB'] = self.server_info['db_name'].upper()
                        else:
                            result['DB'] = result['DB'].upper()

                    self.publish(result)
                    result = ibm_db.fetch_assoc(stmt)
        except Exception:
            self.log.error(traceback.format_exc())        

        return True
Beispiel #5
0
    def run_test_SPInOutBlob(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        serverinfo = ibm_db.server_info( conn )
        server = serverinfo.DBMS_NAME[0:3]

        if (server == 'IDS'):
            procedure = """CREATE PROCEDURE TEST_OUT_BLOB(IN P1 BLOB(100),OUT P2 BLOB(100)); LET P2 = P1;END PROCEDURE;"""
        else:
            procedure = """CREATE PROCEDURE TEST_OUT_BLOB(IN P1 BLOB(100),OUT P2 BLOB(100)) 
                           LANGUAGE SQL
                           DYNAMIC RESULT SETS 0
                           BEGIN
                           SET P2 = P1;
                           END"""

        if conn:
            try:
                ibm_db.exec_immediate(conn, 'DROP PROCEDURE TEST_OUT_BLOB')
            except:
                pass
            ibm_db.exec_immediate(conn, procedure)

            stmt, inparam, outparam = ibm_db.callproc(conn, "TEST_OUT_BLOB",(b'12345678901234567890', b'0'))

            if stmt is not None:
                print("Values of bound parameters _after_ CALL:")
                print("  1: %s  2: %s\n" % (inparam, outparam))
            else:
                print("Error\n")
            ibm_db.close(conn)
        else:
            print("Connection failed.")
Beispiel #6
0
    def call(self, procname, *parms):
        """
        database procedure call

        :param procname: procedure name
        :param params: procedure parameters
        """
        import ibm_db
        return ibm_db.callproc(self._db, procname, *parms)
Beispiel #7
0
    def run_test_146a(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info( conn )

        if conn:
            p1 = bytes('1234', 'utf8')

            print("Values of bound parameters _before_ CALL:")
            print("  1: %s\n" % (p1.decode('utf8')))

            stmt, blobl = ibm_db.callproc(conn, 'out_blob', (p1))

            if stmt is not None:
                print("Values of bound parameters _after_ CALL:")
                print("  1: %s \n" % (blob1))
Beispiel #8
0
    def run_test_spinout_timestamp(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        # Get the server type
        serverinfo = ibm_db.server_info(conn)

        if conn:

            drop = "DROP PROCEDURE PROC_TIMESTAMP"
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass

            # Create the SP with timestamp parameters

            if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
                create = "CREATE PROCEDURE PROC_TIMESTAMP ( IN PAR1 TIMESTAMP, OUT PAR2 TIMESTAMP) BEGIN SET PAR2 = PAR1; END"
            else:
                create = "CREATE PROCEDURE PROC_TIMESTAMP ( IN PAR1 TIMESTAMP, OUT PAR2 TIMESTAMP) BEGIN SET PAR2 = PAR1; END"
            result = ibm_db.exec_immediate(conn, create)

            # call the SP. Expect PAR2 to contain value passed to PAR1
            par1 = "2016-11-14-22.47.29.872688"
            par2 = ""

            print("Values of bound parameters _before_ CALL:")
            print("  1: %s 2: %s\n" % (par1, par2))

            stmt, par1, par2 = ibm_db.callproc(conn, 'proc_timestamp',
                                               (par1, par2))
            if stmt is not None:
                print("Values of bound parameters _after_ CALL:")
                print("  1: %s 2: %s\n" % (par1, par2))

            ibm_db.close(conn)
        else:
            print("Connection failed.")
    def run_test_spinout_timestamp(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        # Get the server type
        serverinfo = ibm_db.server_info( conn )

        if conn:

            drop = "DROP PROCEDURE PROC_TIMESTAMP"
            try:
                result = ibm_db.exec_immediate(conn,drop)
            except:
                pass

            # Create the SP with timestamp parameters

            if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
                create = "CREATE PROCEDURE PROC_TIMESTAMP ( IN PAR1 TIMESTAMP, OUT PAR2 TIMESTAMP) BEGIN SET PAR2 = PAR1; END"
            else:
                create = "CREATE PROCEDURE PROC_TIMESTAMP ( IN PAR1 TIMESTAMP, OUT PAR2 TIMESTAMP) BEGIN SET PAR2 = PAR1; END"
            result = ibm_db.exec_immediate(conn, create)

            # call the SP. Expect PAR2 to contain value passed to PAR1
            par1 = "2016-11-14-22.47.29.872688"
            par2 = ""

            print "Values of bound parameters _before_ CALL:"
            print "  1: %s 2: %s\n" % (par1, par2)

            stmt, par1, par2 = ibm_db.callproc(conn, 'proc_timestamp', (par1, par2))
            if stmt is not None:
                print "Values of bound parameters _after_ CALL:"
                print "  1: %s 2: %s\n" % (par1, par2)

            ibm_db.close(conn)
        else:
            print ("Connection failed.")
  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 test_xml(self, conn):
     return_value = "This is just a test for XML Column. The data gets truncated since we do not "
     stmt, return_value = ibm_db.callproc(conn, 'PROCESSXML',
                                          (return_value, ))
     print("ProcessXML:", return_value.__str__())
 def executeproc(self, proc_name, **args):
     if len(args) > 0:
         ibm_db.callproc(self._conn, proc_name, args)
     else:
         ibm_db.callproc(self._conn, proc_name)
    def run_test_148(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            ##### Set up #####
            serverinfo = ibm_db.server_info(conn)
            server = serverinfo.DBMS_NAME[0:3]
            try:
                sql = "DROP TABLE sptb"
                ibm_db.exec_immediate(conn, sql)
            except:
                pass

            try:
                sql = "DROP PROCEDURE sp"
                ibm_db.exec_immediate(conn, sql)
            except:
                pass

            if (server == 'IDS'):
                sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 CLOB)"
            else:
                sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 BIGINT, c5 CLOB)"

            ibm_db.exec_immediate(conn, sql)

            sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'clob data clob data')"
            ibm_db.exec_immediate(conn, sql)

            if (server == 'IDS'):
                sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 CLOB);
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
            else:
                sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 BIGINT, OUT out5 CLOB)
                 DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END"""
            ibm_db.exec_immediate(conn, sql)
            #############################

            ##### Run the test #####

            out1 = 0
            out2 = 0.00
            out3 = ""
            out4 = 0
            out5 = ""

            stmt, out1, out2, out3, out4, out5 = ibm_db.callproc(
                conn, 'sp', (out1, out2, out3, out4, out5))

            print("out 1:")
            print(out1)
            print("out 2:")
            print(out2)
            print("out 3:")
            print(out3)
            print("out 4:")
            print(out4)
            print("out 5:")
            print(out5)
            #############################
        else:
            print("Connection failed.")
  def run_test_148(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      ##### Set up #####
      serverinfo = ibm_db.server_info( conn )
      server = serverinfo.DBMS_NAME[0:3]
      try:
          sql = "DROP TABLE sptb"
          ibm_db.exec_immediate(conn, sql)
      except:
          pass
      
      try:
          sql = "DROP PROCEDURE sp"
          ibm_db.exec_immediate(conn, sql)
      except:
          pass
      
      if (server == 'IDS'):
        sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 CLOB)"
      else:
        sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 BIGINT, c5 CLOB)"
      
      ibm_db.exec_immediate(conn, sql)
      
      sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'clob data clob data')"
      ibm_db.exec_immediate(conn, sql)
      
      if (server == 'IDS'):
        sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 CLOB);
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
      else:
        sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 BIGINT, OUT out5 CLOB)
                 DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN
                 SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END"""
      ibm_db.exec_immediate(conn, sql)
      #############################

      ##### Run the test #####

      out1 = 0
      out2 = 0.00
      out3 = ""
      out4 = 0
      out5 = ""

      stmt, out1, out2, out3, out4, out5 = ibm_db.callproc(conn, 'sp', (out1, out2, out3, out4, out5))

      print("out 1:")
      print(out1)
      print("out 2:")
      print(out2)
      print("out 3:")
      print(out3)
      print("out 4:")
      print(out4)
      print("out 5:")
      print(out5)
      #############################
    else:
      print("Connection failed.")
Beispiel #15
0
    query += " " + line

# create procedure
ibm_db.exec_immediate(conn, CREATE_PROC)
ibm_db.exec_immediate(conn, "delete from advise_index")

# try to create costtbl
try:
    ibm_db.exec_immediate(conn, DDL_COST_TABLE)
except:
    # assume it exists
    pass

ibm_db.exec_immediate(conn, CLEAN_COST_TABLE)
print("Begin recomending indexes")
ibm_db.callproc(conn, 'EXEC_IN_EXPLAIN_MODE', (fix_query(query), "R"))
print("End recomending indexes")

indexes = []
indexes_sql_stmt = ibm_db.exec_immediate(conn, GET_INDEX_NAMES)
tpl = ibm_db.fetch_tuple(indexes_sql_stmt)
while tpl:
    indexes.append(tpl[0])
    tpl = ibm_db.fetch_tuple(indexes_sql_stmt)

indset = set(indexes)
print("number of suggested indexes %d" % len(indset))
print(indset)
ps = list(power_set(indset))
n = 0
print("Evaluating %d index combinations" % (len(ps)))
Beispiel #16
0
Datei: DB.py Projekt: exedre/e4t
 def call(self,procname,*parms):
     import ibm_db
     return ibm_db.callproc(self._db,procname,*parms)
 def test_xml(self, conn):
   return_value = "This is just a test for XML Column. The data gets truncated since we do not "
   stmt, return_value = ibm_db.callproc(conn, 'PROCESSXML', (return_value,))
   print "ProcessXML:", return_value.__str__()
 def test_varchar(self, conn):
   return_value = ""
   stmt, return_value = ibm_db.callproc(conn, 'PROCESSVAR', (return_value,))
   print "ProcessVAR:", return_value
 def test_int(self, conn):
   return_value = 0
   stmt, return_value = ibm_db.callproc(conn, 'PROCESSINT', (return_value,))
   print "ProcessINT:", return_value
    def run_test_bool_callproc(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        if (not conn):
            print("Could not make a connection.")
            return 0
        server = ibm_db.server_info(conn)

        if (not server.DBMS_NAME.startswith('DB2/')):
            print("Boolean is not supported")
            return 0

        try:
            ibm_db.exec_immediate(conn, "DROP PROCEDURE bool_procparams")
            ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
        except:
            pass

        try:
            ibm_db.exec_immediate(
                conn,
                "CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
            )
        except:
            pass

        try:
            procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
                           RESULT SETS  1
                           LANGUAGE SQL
                           BEGIN
                             DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
                             OPEN c1;
                             set param2 = parm1;
                           END"""

            ibm_db.exec_immediate(conn, procedure)
        except Exception as e:
            print(str(e))
            exit(-1)

        try:
            insert_sql = "INSERT INTO bool_test values(?, ?)"
            stmt = ibm_db.prepare(conn, insert_sql)

            rows = ((True, 'bindparam true'), (False, 'bindparam false'),
                    (None, 'bindparam None'))

            for row in rows:
                ibm_db.bind_param(stmt, 1, row[0])
                ibm_db.bind_param(stmt, 2, row[1])
                ibm_db.execute(stmt)
            stmt = None

            inparam = 11
            outparam = -1
            stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
                                                      (inparam, outparam))
            print("Fetching first result set")
            row = ibm_db.fetch_row(stmt)
            while row:
                row0 = ibm_db.result(stmt, 0)
                row1 = ibm_db.result(stmt, 1)
                print(row0)
                print(row1)
                row = ibm_db.fetch_row(stmt)

            ibm_db.close(conn)
        except Exception as e:
            print("Error:{}".format(str(e)))
Beispiel #21
0
    pass

# If The SQL Statement Could Not Be Executed, Display An Error Message And Exit
if returnCode is False:
    print("\nERROR: Unable to execute the SQL statement specified.")
    conn.closeConnection()
    exit(-1)

# Otherwise, Complete The Status Message
else:
    print("Done!\n")

# Execute The Stored Procedure Just Created
print("Executing the " + spName + " stored procedure ... ", end="")
try:
    resultSet = ibm_db.callproc(dbConnection, spName, spParamValues)
except Exception:
    pass

# If The Stored Procedure Specified Could Not Be Executed, Display An Error Message And Exit
if resultSet is None:
    print("\nERROR: Unable to execute the stored procedure specified.")
    conn.closeConnection()
    exit(-1)

# Otherwise, Complete The Status Message; Then Format And Display The Data Values Returned
else:
    print("Done!\n")

    # Display A Report Header
    print("Salary statistics:")
Beispiel #22
0
def config_IBMHIST(conn, coll_path, arch_path, max_size, arch_cmd, arch_ext):

    print("Configuring IBMHIST settings ...")

    # delete from IBMHIST.TAB_CONFIG
    print("    Deleting configurations from IBMHIST.TAB_CONFIG ...")
    ibm_db.exec_immediate(conn, "DELETE FROM IBMHIST.TAB_CONFIG")

    # rename current collection directory to avoid name conflicts
    stmt = ibm_db.exec_immediate(
        conn, "SELECT PATH FROM IBMHIST.TAB_DIRS WHERE STATUS = 'COLL'")
    while ibm_db.fetch_row(stmt):

        # get orig path
        orig_path = ibm_db.result(stmt, "PATH")

        # get new path by appending current minute and current second
        new_path = orig_path + '_old' + str(
            datetime.datetime.now().minute) + str(
                datetime.datetime.now().second)

        # rename
        print("    Renaming collection directory '%s' to '%s' ..." %
              (orig_path, new_path))
        shutil.move(orig_path, new_path)

        # update IBMHIST.TAB_DIRS
        ibm_db.exec_immediate(
            conn, "UPDATE IBMHIST.TAB_DIRS SET PATH = '%s' WHERE PATH = '%s'" %
            (new_path, orig_path))

    # add data collection path configuration
    print("    Setting COLL_PATH to '%s' ..." % (coll_path))
    assert os.path.exists(
        coll_path), "Data collection path: %s does not exist" % (coll_path)
    stmt, ret_coll_path, retcode = ibm_db.callproc(
        conn, 'IBMHIST.PATH_READABLE_WRITABLE', (coll_path, 0))
    assert retcode is 0 or retcode is None, "Data collection path: %s does not provide fenced external functions read/write access" % (
        coll_path)
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'COLL_PATH', '%s', 'DIRECTORY PATH OF DATA COLLECTION') "
        % (coll_path))

    # add data archival path configuration
    print("    Setting ARCH_PATH to '%s' ..." % (arch_path))
    assert os.path.exists(
        arch_path), "Data archival path: %s does not exist" % (arch_path)
    stmt, ret_arch_path, retcode = ibm_db.callproc(
        conn, 'IBMHIST.PATH_READABLE_WRITABLE', (arch_path, 0))
    assert retcode is 0 or retcode is None, "Data archival path: %s does not provide fenced external functions read/write access" % (
        arch_path)
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_PATH', '%s', 'DIRECTORY PATH OF DATA ARCHIVAL') "
        % (arch_path))

    # add max size configuration
    print("    Setting MAX_SIZE to '%s' bytes ..." % (max_size))
    assert str.isdigit(max_size), "Max size: %s is not an integer" % (max_size)
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'MAX_SIZE', '%s', 'MAX SIZE IN BYTES OF COLLECTION AND ARCHIVAL') "
        % (max_size))

    # add archival command and archival extension
    print("    Setting ARCH_CMD to '%s' and ARCH_EXT to '%s' ..." %
          (arch_cmd, arch_ext))
    # test archival functionality by archiving sql folder
    src, dest = "sql", "arch_test" + arch_ext
    arch_cmd_test = arch_cmd.replace("_src_", src).replace("_dest_", dest)
    if os.path.exists(dest):
        os.remove(dest)
    subprocess.check_call(arch_cmd_test, shell=True)
    assert os.path.exists(
        dest
    ), "Data archival command: %s failed, could not find archival %s " % (
        arch_cmd, dest)
    os.remove(dest)
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_CMD', '%s', 'COMMAND USED TO ARCHIVE HOURLY DIRECTORIES') "
        % arch_cmd)
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_EXT', '%s', 'EXTENSION OF ARCHIVE HOURLY DIRECTORIES') "
        % arch_ext)

    # add task_details.json path configuration
    task_details_path = os.path.realpath("task_details.json")
    print("    Setting TASK_DETAILS_PATH to '%s' ..." % (task_details_path))
    ibm_db.exec_immediate(
        conn,
        "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'TASK_DETAILS_PATH', '%s', 'LOCATION OF task_details.json FILE') "
        % task_details_path)
 def test_varchar(self, conn):
     return_value = ""
     stmt, return_value = ibm_db.callproc(conn, 'PROCESSVAR',
                                          (return_value, ))
     print("ProcessVAR:", return_value)
 def test_int(self, conn):
     return_value = 0
     stmt, return_value = ibm_db.callproc(conn, 'PROCESSINT',
                                          (return_value, ))
     print("ProcessINT:", return_value)
Beispiel #25
0
def parseCall(hdbc, inSQL, local_ns):

    global _hdbc, _hdbi, _connected, _settings

    # Check to see if we are connected first
    if (_connected == False):  # Check if you are connected
        db2_doConnect()
        if _connected == False: return None

    flag_quotes = _settings["quotes"]

    remainder = inSQL.strip()
    procName, procArgs = parseCallArgs(
        remainder[5:])  # Assume that CALL ... is the format

    resultsets = findProc(procName)
    if (resultsets == None): return None

    argvalues = []

    if (len(procArgs) > 0):  # We have arguments to consider
        for arg in procArgs:
            varname = arg
            if (len(varname) > 0):
                if (varname[0] == ":"):
                    checkvar = varname[1:]
                    varvalue = getContents(checkvar, flag_quotes, local_ns)
                    if (varvalue == None):
                        errormsg("Variable " + checkvar + " is not defined.")
                        return None
                    argvalues.append(varvalue)
                else:
                    if (varname.upper() == "NULL"):
                        argvalues.append(None)
                    else:
                        argvalues.append(varname)
            else:
                argvalues.append(None)

    try:

        if (len(procArgs) > 0):
            argtuple = tuple(argvalues)
            result = ibm_db.callproc(_hdbc, procName, argtuple)
            stmt = result[0]
        else:
            result = ibm_db.callproc(_hdbc, procName)
            stmt = result

        if (resultsets != 0 and stmt != None):

            columns, types = getColumns(stmt)
            if (columns == None): return None

            rows = []
            rowlist = ibm_db.fetch_tuple(stmt)
            while (rowlist):
                row = []
                colcount = 0
                for col in rowlist:
                    try:
                        if (types[colcount] in ["int", "bigint"]):
                            row.append(int(col))
                        elif (types[colcount] in ["decimal", "real"]):
                            row.append(float(col))
                        elif (types[colcount] in ["date", "time",
                                                  "timestamp"]):
                            row.append(str(col))
                        else:
                            row.append(col)
                    except:
                        row.append(col)
                    colcount += 1
                rows.append(row)
                rowlist = ibm_db.fetch_tuple(stmt)

            if (_settings["format"] == "array"):
                rows.insert(0, columns)
                if len(procArgs) > 0:
                    allresults = []
                    allresults.append(rows)
                    for x in result[1:]:
                        allresults.append(x)
                    return allresults  # rows,returned_results
                else:
                    return rows
            else:
                df = pandas.DataFrame.from_records(rows, columns=columns)
                return df

        else:
            if len(procArgs) > 0:
                allresults = []
                for x in result[1:]:
                    allresults.append(x)
                return allresults  # rows,returned_results
            else:
                return None

    except Exception as err:
        db2_error()
        return None