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
