test_5400_cursor_execute_async.py

# -----------------------------------------------------------------------------
# Copyright (c) 2023, 2024, Oracle and/or its affiliates.
#
# This software is dual-licensed to you under the Universal Permissive License
# (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
# 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
# either license.
#
# If you elect to accept the software under the Apache License, Version 2.0,
# the following applies:
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# -----------------------------------------------------------------------------

"""
5400 - Module for testing the cursor execute() method with asyncio
"""

importcollections
importunittest

importoracledb
importtest_env


@unittest.skipUnless(
test_env.get_is_thin(), "asyncio not supported in thick mode"
)
classTestCase(test_env.BaseAsyncTestCase):
asyncdeftest_5400(self):
"5400 - test executing a statement without any arguments"
result=awaitself.cursor.execute("begin null; end;")
self.assertIsNone(result)

asyncdeftest_5401(self):
"5401 - test executing a None statement with bind variables"
cursor=self.conn.cursor()
withself.assertRaisesFullCode("DPY-2001"):
awaitcursor.execute(None, x=5)

asyncdeftest_5402(self):
"5402 - test executing a statement with args and empty keyword args"
simple_var=self.cursor.var(oracledb.NUMBER)
args= [simple_var]
kwargs= {}
result=awaitself.cursor.execute(
"begin :1 := 25; end;", args, **kwargs
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 25)

asyncdeftest_5403(self):
"5403 - test executing a statement with keyword arguments"
simple_var=self.cursor.var(oracledb.NUMBER)
result=awaitself.cursor.execute(
"begin :value := 5; end;", value=simple_var
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 5)

asyncdeftest_5404(self):
"5404 - test executing a statement with a dictionary argument"
simple_var=self.cursor.var(oracledb.NUMBER)
dict_arg=dict(value=simple_var)
result=awaitself.cursor.execute(
"begin :value := 10; end;", dict_arg
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 10)

asyncdeftest_5405(self):
"5405 - test executing a statement with both a dict and keyword args"
simple_var=self.cursor.var(oracledb.NUMBER)
dict_arg=dict(value=simple_var)
withself.assertRaisesFullCode("DPY-2005"):
awaitself.cursor.execute(
"begin :value := 15; end;", dict_arg, value=simple_var
 )

asyncdeftest_5406(self):
"5406 - test executing a statement and then changing the array size"
awaitself.cursor.execute("select IntCol from TestNumbers")
self.cursor.arraysize=5
self.assertEqual(len(awaitself.cursor.fetchall()), 10)

asyncdeftest_5407(self):
"5407 - test that subsequent executes succeed after bad execute"
sql="begin raise_application_error(-20000, 'this); end;"
withself.assertRaisesFullCode("DPY-2041"):
awaitself.cursor.execute(sql)
awaitself.cursor.execute("begin null; end;")

asyncdeftest_5408(self):
"5408 - test that subsequent fetches fail after bad execute"
withself.assertRaisesFullCode("ORA-00904"):
awaitself.cursor.execute("select y from dual")
withself.assertRaisesFullCode("DPY-1003"):
awaitself.cursor.fetchall()

asyncdeftest_5409(self):
"5409 - test executing a statement with an incorrect named bind"
sql="select * from TestStrings where IntCol = :value"
withself.assertRaisesFullCode("DPY-4008", "ORA-01036"):
awaitself.cursor.execute(sql, value2=3)

asyncdeftest_5410(self):
"5410 - test executing a statement with named binds"
awaitself.cursor.execute(
"""
 select *
 from TestNumbers
 where IntCol = :value1 and LongIntCol = :value2
 """,
value1=1,
value2=38,
 )
self.assertEqual(len(awaitself.cursor.fetchall()), 1)

asyncdeftest_5411(self):
"5411 - test executing a statement with an incorrect positional bind"
sql="""
 select *
 from TestNumbers
 where IntCol = :value and LongIntCol = :value2"""
withself.assertRaisesFullCode("DPY-4009", "ORA-01008"):
awaitself.cursor.execute(sql, [3])

asyncdeftest_5412(self):
"5412 - test executing a statement with positional binds"
awaitself.cursor.execute(
"""
 select *
 from TestNumbers
 where IntCol = :value and LongIntCol = :value2
 """,
 [1, 38],
 )
self.assertEqual(len(awaitself.cursor.fetchall()), 1)

asyncdeftest_5413(self):
"5413 - test executing a statement after rebinding a named bind"
statement="begin :value := :value2 + 5; end;"
simple_var=self.cursor.var(oracledb.NUMBER)
simple_var2=self.cursor.var(oracledb.NUMBER)
simple_var2.setvalue(0, 5)
result=awaitself.cursor.execute(
statement, value=simple_var, value2=simple_var2
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 10)

simple_var=self.cursor.var(oracledb.NATIVE_FLOAT)
simple_var2=self.cursor.var(oracledb.NATIVE_FLOAT)
simple_var2.setvalue(0, 10)
result=awaitself.cursor.execute(
statement, value=simple_var, value2=simple_var2
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 15)

asyncdeftest_5414(self):
"5414 - test executing a PL/SQL statement with duplicate binds"
simple_var=self.cursor.var(oracledb.NUMBER)
simple_var.setvalue(0, 5)
result=awaitself.cursor.execute(
"""
 begin
 :value := :value + 5;
 end;
 """,
value=simple_var,
 )
self.assertIsNone(result)
self.assertEqual(simple_var.getvalue(), 10)

asyncdeftest_5415(self):
"5415 - test executing a PL/SQL statement with duplicate binds"
simple_var=self.cursor.var(oracledb.NUMBER)
simple_var.setvalue(0, 5)
awaitself.cursor.execute(
"begin :value := :value + 5; end;", [simple_var]
 )
self.assertEqual(simple_var.getvalue(), 10)

asyncdeftest_5416(self):
"5416 - test executing a statement with an incorrect number of binds"
statement="begin :value := :value2 + 5; end;"
var=self.cursor.var(oracledb.NUMBER)
var.setvalue(0, 5)
withself.assertRaisesFullCode("DPY-4010", "ORA-01008"):
awaitself.cursor.execute(statement)
withself.assertRaisesFullCode("DPY-4010", "ORA-01008"):
awaitself.cursor.execute(statement, value=var)
withself.assertRaisesFullCode("DPY-4008", "ORA-01036"):
awaitself.cursor.execute(
statement, value=var, value2=var, value3=var
 )

asyncdeftest_5417(self):
"5417 - change in size on subsequent binds does not use optimised path"
awaitself.cursor.execute("truncate table TestTempTable")
data= [(1, "Test String #1"), (2, "ABC"*100)]
forrowindata:
awaitself.cursor.execute(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:1, :2)
 """,
row,
 )
awaitself.conn.commit()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
self.assertEqual(awaitself.cursor.fetchall(), data)

asyncdeftest_5418(self):
"5418 - test that dml can use optimised path"
data_to_insert= [(i+1, f"Test String #{i+1}") foriinrange(3)]
awaitself.cursor.execute("truncate table TestTempTable")
forrowindata_to_insert:
withself.conn.cursor() ascursor:
awaitcursor.execute(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:1, :2)
 """,
row,
 )
awaitself.conn.commit()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable order by IntCol"
 )
self.assertEqual(awaitself.cursor.fetchall(), data_to_insert)

asyncdeftest_5419(self):
"5419 - test calling execute() with invalid parameters"
sql="insert into TestTempTable (IntCol, StringCol1) values (:1, :2)"
withself.assertRaisesFullCode("DPY-2003"):
awaitself.cursor.execute(sql, "These are not valid parameters")

asyncdeftest_5420(self):
"5420 - test calling execute() with mixed binds"
awaitself.cursor.execute("truncate table TestTempTable")
self.cursor.setinputsizes(None, None, str)
data=dict(val1=1, val2="Test String 1")
withself.assertRaisesFullCode("DPY-2006"):
awaitself.cursor.execute(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:1, :2)
 returning StringCol1 into :out_var
 """,
data,
 )

asyncdeftest_5421(self):
"5421 - test binding by name with double quotes"
data= {'"_value1"': 1, '"VaLue_2"': 2, '"3VALUE"': 3}
awaitself.cursor.execute(
'select :"_value1" + :"VaLue_2" + :"3VALUE" from dual',
data,
 )
 (result,) =awaitself.cursor.fetchone()
self.assertEqual(result, 6)

asyncdeftest_5422(self):
"5422 - test executing a statement with different input buffer sizes"
sql="""
 insert into TestTempTable (IntCol, StringCol1, StringCol2)
 values (:int_col, :str_val1, :str_val2) returning IntCol
 into :ret_data"""
values1= {"int_col": 1, "str_val1": '{"a", "b"}', "str_val2": None}
values2= {"int_col": 2, "str_val1": None, "str_val2": '{"a", "b"}'}
values3= {"int_col": 3, "str_val1": '{"a"}', "str_val2": None}

awaitself.cursor.execute("truncate table TestTempTable")
ret_bind=self.cursor.var(oracledb.DB_TYPE_VARCHAR, arraysize=1)
self.cursor.setinputsizes(ret_data=ret_bind)
awaitself.cursor.execute(sql, values1)
self.assertEqual(ret_bind.values, [["1"]])

ret_bind=self.cursor.var(oracledb.DB_TYPE_VARCHAR, arraysize=1)
self.cursor.setinputsizes(ret_data=ret_bind)
awaitself.cursor.execute(sql, values2)
self.assertEqual(ret_bind.values, [["2"]])

ret_bind=self.cursor.var(oracledb.DB_TYPE_VARCHAR, arraysize=1)
self.cursor.setinputsizes(ret_data=ret_bind)
awaitself.cursor.execute(sql, values3)
self.assertEqual(ret_bind.values, [["3"]])

asyncdeftest_5423(self):
"5423 - test using rowfactory"
awaitself.cursor.execute("truncate table TestTempTable")
awaitself.cursor.execute(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (1, 'Test 1')
 """
 )
awaitself.conn.commit()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
column_names= [col[0] forcolinself.cursor.description]

defrowfactory(*row):
returndict(zip(column_names, row))

self.cursor.rowfactory=rowfactory
self.assertEqual(self.cursor.rowfactory, rowfactory)
self.assertEqual(
awaitself.cursor.fetchall(),
 [{"INTCOL": 1, "STRINGCOL1": "Test 1"}],
 )

asyncdeftest_5424(self):
"5424 - test executing same query after setting rowfactory"
awaitself.cursor.execute("truncate table TestTempTable")
data= [(1, "Test 1"), (2, "Test 2")]
awaitself.cursor.executemany(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:1, :2)
 """,
data,
 )
awaitself.conn.commit()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
column_names= [col[0] forcolinself.cursor.description]
self.cursor.rowfactory=lambda*row: dict(zip(column_names, row))
results1=awaitself.cursor.fetchall()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
results2=awaitself.cursor.fetchall()
self.assertEqual(results1, results2)

asyncdeftest_5425(self):
"5425 - test executing different query after setting rowfactory"
awaitself.cursor.execute("truncate table TestTempTable")
data= [(1, "Test 1"), (2, "Test 2")]
awaitself.cursor.executemany(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:1, :2)
 """,
data,
 )
awaitself.conn.commit()
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
column_names= [col[0] forcolinself.cursor.description]
self.cursor.rowfactory=lambda*row: dict(zip(column_names, row))
awaitself.cursor.execute(
"""
 select IntCol, StringCol
 from TestSTrings
 where IntCol between 1 and 3 order by IntCol
 """
 )
expected_data= [(1, "String 1"), (2, "String 2"), (3, "String 3")]
self.assertEqual(awaitself.cursor.fetchall(), expected_data)

asyncdeftest_5426(self):
"5426 - test setting rowfactory on a REF cursor"
withself.conn.cursor() ascursor:
sql_function="pkg_TestRefCursors.TestReturnCursor"
ref_cursor=awaitcursor.callfunc(
sql_function, oracledb.DB_TYPE_CURSOR, [2]
 )
column_names= [col[0] forcolinref_cursor.description]
ref_cursor.rowfactory=lambda*row: dict(zip(column_names, row))
expected_value= [
 {"INTCOL": 1, "STRINGCOL": "String 1"},
 {"INTCOL": 2, "STRINGCOL": "String 2"},
 ]
self.assertEqual(awaitref_cursor.fetchall(), expected_value)

asyncdeftest_5427(self):
"5427 - test using a subclassed string as bind parameter keys"

classmy_str(str):
pass

awaitself.cursor.execute("truncate table TestTempTable")
keys= {my_str("str_val"): oracledb.DB_TYPE_VARCHAR}
self.cursor.setinputsizes(**keys)
values= {
my_str("int_val"): 5427,
my_str("str_val"): "5427 - String Value",
 }
awaitself.cursor.execute(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:int_val, :str_val)
 """,
values,
 )
awaitself.cursor.execute(
"select IntCol, StringCol1 from TestTempTable"
 )
self.assertEqual(
awaitself.cursor.fetchall(), [(5427, "5427 - String Value")]
 )

asyncdeftest_5428(self):
"5428 - test using a sequence of parameters other than a list or tuple"

classMySeq(collections.abc.Sequence):
def__init__(self, *data):
self.data=data

def__len__(self):
returnlen(self.data)

def__getitem__(self, index):
returnself.data[index]

values_to_insert= [MySeq(1, "String 1"), MySeq(2, "String 2")]
expected_data= [tuple(value) forvalueinvalues_to_insert]
awaitself.cursor.execute("truncate table TestTempTable")
awaitself.cursor.executemany(
"""
 insert into TestTempTable (IntCol, StringCol1)
 values (:int_val, :str_val)
 """,
values_to_insert,
 )
awaitself.cursor.execute(
"""
 select IntCol, StringCol1
 from TestTempTable
 order by IntCol
 """
 )
self.assertEqual(awaitself.cursor.fetchall(), expected_data)

asyncdeftest_5429(self):
"5429 - test an output type handler with prefetch > arraysize"

deftype_handler(cursor, metadata):
returncursor.var(metadata.type_code, arraysize=cursor.arraysize)

self.cursor.arraysize=2
self.cursor.prefetchrows=3
self.cursor.outputtypehandler=type_handler
awaitself.cursor.execute(
"select level from dual connect by level <= 5"
 )
self.assertEqual(
awaitself.cursor.fetchall(), [(1,), (2,), (3,), (4,), (5,)]
 )

asyncdeftest_5430(self):
"5430 - test setinputsizes() but without binding"
self.cursor.setinputsizes(None, int)
sql="select :1, : 2 from dual"

withself.assertRaisesFullCode("ORA-01008", "DPY-4010"):
awaitself.cursor.execute(sql, [])

asyncdeftest_5431(self):
"5431 - test getting FetchInfo attributes"
type_obj=awaitself.conn.gettype("UDT_OBJECT")
varchar_ratio, _=awaittest_env.get_charset_ratios_async()
test_values= [
 (
"select IntCol from TestObjects",
10,
None,
False,
"INTCOL",
False,
9,
0,
oracledb.DB_TYPE_NUMBER,
oracledb.DB_TYPE_NUMBER,
 ),
 (
"select ObjectCol from TestObjects",
None,
None,
False,
"OBJECTCOL",
True,
None,
None,
type_obj,
oracledb.DB_TYPE_OBJECT,
 ),
 (
"select JsonVarchar from TestJsonCols",
4000,
4000*varchar_ratio,
True,
"JSONVARCHAR",
False,
None,
None,
oracledb.DB_TYPE_VARCHAR,
oracledb.DB_TYPE_VARCHAR,
 ),
 (
"select FLOATCOL from TestNumbers",
127,
None,
False,
"FLOATCOL",
False,
126,
-127,
oracledb.DB_TYPE_NUMBER,
oracledb.DB_TYPE_NUMBER,
 ),
 ]
for (
sql,
display_size,
internal_size,
is_json,
name,
null_ok,
precision,
scale,
typ,
type_code,
 ) intest_values:
awaitself.cursor.execute(sql)
 (fetch_info,) =self.cursor.description
self.assertIsInstance(fetch_info, oracledb.FetchInfo)
self.assertEqual(fetch_info.display_size, display_size)
self.assertEqual(fetch_info.internal_size, internal_size)
self.assertEqual(fetch_info.is_json, is_json)
self.assertEqual(fetch_info.name, name)
self.assertEqual(fetch_info.null_ok, null_ok)
self.assertEqual(fetch_info.precision, precision)
self.assertEqual(fetch_info.scale, scale)
self.assertEqual(fetch_info.type, typ)
self.assertEqual(fetch_info.type_code, type_code)

asyncdeftest_5432(self):
"5432 - test FetchInfo repr() and str()"
awaitself.cursor.execute("select IntCol from TestObjects")
 (fetch_info,) =self.cursor.description
self.assertEqual(
str(fetch_info),
"('INTCOL', <DbType DB_TYPE_NUMBER>, 10, None, 9, 0, False)",
 )
self.assertEqual(
repr(fetch_info),
"('INTCOL', <DbType DB_TYPE_NUMBER>, 10, None, 9, 0, False)",
 )

asyncdeftest_5433(self):
"5433 - test slicing FetchInfo"
awaitself.cursor.execute("select IntCol from TestObjects")
 (fetch_info,) =self.cursor.description
self.assertEqual(fetch_info[1:3], (oracledb.DB_TYPE_NUMBER, 10))

asyncdeftest_5434(self):
"5434 - test async context manager"
expected_value=test_env.get_main_user().upper()
withself.conn.cursor() ascursor:
awaitcursor.execute("select user from dual")
self.assertEqual(awaitcursor.fetchone(), (expected_value,))
asyncwithself.conn.cursor() ascursor:
awaitcursor.execute("select user from dual")
self.assertEqual(awaitcursor.fetchone(), (expected_value,))

asyncdeftest_5435(self):
"5435 - test metadata requiring multiple packets"
values= [f"Test value 5435 - {i}"foriinrange(1, 301)]
columns=", ".join(f"'{v}'"forvinvalues)
query=f"select {columns} from dual"
awaitself.cursor.execute(query)
row=awaitself.cursor.fetchone()
self.assertEqual(row, tuple(values))

asyncdeftest_5436(self):
"5436 - test raising no_data_found in PL/SQL"
withself.assertRaisesFullCode("ORA-01403"):
awaitself.cursor.execute("begin raise no_data_found; end;")


if__name__=="__main__":
test_env.run_test_cases()