def test_sql_execute_conn_set(): key = 'jrntesje' conn = 'conn-label' element = ET.fromstring(iSqlQuery(key, QUERY, {'conn': conn}).xml_in()) assert(element.tag == 'sql') assert(len(element.attrib) == 1) assert('var' in element.attrib) assert(element.attrib['var'] == key) assert(element.text == '\n') children = tuple(iter(element)) assert(len(children) == 1) element = children[0] assert(element.tag == 'query') assert(element.text == QUERY) assert(len(element.attrib) == 3) assert('conn' in element.attrib) assert(element.attrib['conn'] == conn) assert('var' in element.attrib) assert(element.attrib['var'] == key)
def itoolkit_run_sql(sql): conn = dbi.connect() db_itransport = DatabaseTransport(conn) itool = iToolKit() itool.add(iSqlQuery('query', sql, {'error': 'on'})) itool.add(iSqlFetch('fetch')) itool.add(iSqlFree('free')) itool.call(db_itransport) command_output = itool.dict_out('fetch') rc = IBMi_COMMAND_RC_UNEXPECTED out = '' err = '' if 'error' in command_output: command_error = command_output['error'] if 'joblog' in command_error: rc = IBMi_COMMAND_RC_ERROR err = command_error['joblog'] else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_JOBLOG err = "iToolKit result dict does not have key 'joblog', the output is %s" % command_output else: rc = IBMi_COMMAND_RC_SUCCESS out = command_output['row'] return rc, out, err
def test_sql_query(): key = 'dmmyeozq' element = ET.fromstring(iSqlQuery(key, QUERY).xml_in()) assert(element.tag == 'sql') assert(len(element.attrib) == 1) assert('var' in element.attrib) assert(element.attrib['var'] == key) assert(element.text == '\n') children = tuple(iter(element)) assert(len(children) == 1) element = children[0] assert(element.tag == 'query') assert(element.text == QUERY) assert(len(element.attrib) == 2) assert('error' in element.attrib) assert(element.attrib['error'] == 'fast') assert('var' in element.attrib) assert(element.attrib['var'] == key)
def test_sql_prepare_error_off(): key = 'vqsjrnkv' error = 'off' element = ET.fromstring(iSqlQuery(key, QUERY, {'error': error}).xml_in()) assert(element.tag == 'sql') assert(len(element.attrib) == 1) assert('var' in element.attrib) assert(element.attrib['var'] == key) assert(element.text == '\n') children = tuple(iter(element)) assert(len(children) == 1) element = children[0] assert(element.tag == 'query') assert(element.text == QUERY) assert(len(element.attrib) == 2) assert('error' in element.attrib) assert(element.attrib['error'] == error) assert('var' in element.attrib) assert(element.attrib['var'] == key)
def test_sql_execute_options_set(): key = 'qeficmfx' options = 'options-label' element = ET.fromstring(iSqlQuery(key, QUERY, {'options': options}).xml_in()) assert(element.tag == 'sql') assert(len(element.attrib) == 1) assert('var' in element.attrib) assert(element.attrib['var'] == key) assert(element.text == '\n') children = tuple(iter(element)) assert(len(children) == 1) element = children[0] assert(element.tag == 'query') assert(element.text == QUERY) assert(len(element.attrib) == 3) assert('options' in element.attrib) assert(element.attrib['options'] == options) assert('var' in element.attrib) assert(element.attrib['var'] == key)
def test_sql_execute_stmt_set(): key = 'miecefin' stmt = 'stmt-label' element = ET.fromstring(iSqlQuery(key, QUERY, {'stmt': stmt}).xml_in()) assert(element.tag == 'sql') assert(len(element.attrib) == 1) assert('var' in element.attrib) assert(element.attrib['var'] == key) assert(element.text == '\n') children = tuple(iter(element)) assert(len(children) == 1) element = children[0] assert(element.tag == 'query') assert(element.text == QUERY) assert(len(element.attrib) == 3) assert('stmt' in element.attrib) assert(element.attrib['stmt'] == stmt) assert('var' in element.attrib) assert(element.attrib['var'] == key)
def itoolkit_run_sql(conn, sql): out_list = [] rc = '' try: itransport = DatabaseTransport(conn) itool = iToolKit() itool.add(iSqlQuery('query', sql, {'error': 'on'})) itool.add(iSqlFetch('fetch')) itool.add(iSqlFree('free')) itool.call(itransport) command_output = itool.dict_out('fetch') out = '' if 'error' not in command_output: rc = IBMi_COMMAND_RC_SUCCESS out = command_output['row'] if isinstance(out, dict): out_list.append(out) elif isinstance(out, list): out_list = out else: command_error = command_output['error'] if 'joblog' in command_error: rc = IBMi_COMMAND_RC_ERROR else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_JOBLOG if 'Row not found' in command_error: rc = 0 # treat as success but also indicate the Row not found message in stderr except Exception as e_db_connect: raise Exception(str(e_db_connect)) return rc, interpret_return_code(rc), out_list
def itoolkit_run_sql(sql, db): if db.strip() != '': try: conn = dbi.connect(database='{db_pattern}'.format(db_pattern=db)) except Exception as e: out_list = [] err = "%s, most likely the database does not exist on the system" % str( e) rc = IBMi_COMMAND_RC_UNEXPECTED return rc, out_list, err else: conn = dbi.connect() itransport = DatabaseTransport(conn) itool = iToolKit() itool.add(iSqlQuery('query', sql, {'error': 'on'})) itool.add(iSqlFetch('fetch')) itool.add(iSqlFree('free')) itool.call(itransport) command_output = itool.dict_out('fetch') out_list = [] out = '' err = '' if 'error' in command_output: command_error = command_output['error'] if 'joblog' in command_error: rc = IBMi_COMMAND_RC_ERROR err = command_error['joblog'] else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_JOBLOG err = "iToolKit result dict does not have key 'joblog', the output is %s" % command_output if 'Row not found' in err: rc = 0 # treat as success but also indicate the Row not found message in stderr else: rc = IBMi_COMMAND_RC_SUCCESS out = command_output['row'] if isinstance(out, dict): out_list.append(out) elif isinstance(out, list): out_list = out return rc, out_list, err
def itoolkit_sql_callproc(self, sql): itransport = DatabaseTransport(self.conn) itool = iToolKit(iparm=1) itool.add(iSqlQuery('query', sql)) itool.add(iSqlFree('free')) itool.call(itransport) command_output = itool.dict_out('query') out = '' err = '' if 'success' in command_output: rc = ibmi_util.IBMi_COMMAND_RC_SUCCESS out = str(command_output) else: rc = ibmi_util.IBMi_COMMAND_RC_ERROR err = str(command_output) return rc, out, err
def itoolkit_sql_callproc(sql, db): if db.strip() != '': try: conn = dbi.connect(database='{db_pattern}'.format(db_pattern=db)) except Exception as e: out_list = [] err = "%s, most likely the database does not exist on the system" % str( e) rc = IBMi_COMMAND_RC_UNEXPECTED return rc, out_list, err else: conn = dbi.connect() itransport = DatabaseTransport(conn) itool = iToolKit(iparm=1) itool.add(iSqlQuery('query', sql, {'error': 'on'})) itool.add(iSqlFree('free')) itool.call(itransport) command_output = itool.dict_out('query') out = '' err = '' if 'success' in command_output: rc = IBMi_COMMAND_RC_SUCCESS out = command_output['success'] elif 'error' in command_output: command_error = command_output['error'] if 'joblog' in command_error: rc = IBMi_COMMAND_RC_ERROR err = command_error['joblog'] else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_JOBLOG err = "iToolKit result dict does not have key 'joblog', the output is %s" % command_output else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_ERROR err = "iToolKit result dict does not have key 'error', the output is %s" % command_output return rc, out, err
def itoolkit_run_sql(sql): conn = dbi.connect() # itransport = iDB2Call(conn) itransport = DatabaseTransport(conn) itool = iToolKit() itool.add(iSqlQuery('query', sql, {'error': 'on'})) itool.add(iSqlFetch('fetch')) itool.add(iSqlFree('free')) itool.call(itransport) command_output = itool.dict_out('fetch') rc = IBMi_COMMAND_RC_UNEXPECTED out_list = [] out = '' err = '' if 'error' in command_output: command_error = command_output['error'] if 'joblog' in command_error: rc = IBMi_COMMAND_RC_ERROR err = command_error['joblog'] else: # should not be here, must xmlservice has internal error rc = IBMi_COMMAND_RC_ITOOLKIT_NO_KEY_JOBLOG err = "iToolKit result dict does not have key 'joblog', the output is %s" % command_output if 'Row not found' in err: rc = 0 # treat as success but also indicate the Row not found message in stderr else: rc = IBMi_COMMAND_RC_SUCCESS out = command_output['row'] if isinstance(out, dict): out_list.append(out) elif isinstance(out, list): out_list = out return rc, out_list, err