def testRoundTripObject(self):
"test inserting and then querying object with all data types"
self.cursor.execute("truncate table TestClobs")
self.cursor.execute("truncate table TestNClobs")
self.cursor.execute("truncate table TestBlobs")
self.cursor.execute("insert into TestClobs values " \
"(1, 'A short CLOB')")
self.cursor.execute("insert into TestNClobs values " \
"(1, 'A short NCLOB')")
self.cursor.execute("insert into TestBlobs values " \
"(1, utl_raw.cast_to_raw('A short BLOB'))")
self.connection.commit()
self.cursor.execute("select CLOBCol from TestClobs")
clob, = self.cursor.fetchone()
self.cursor.execute("select NCLOBCol from TestNClobs")
nclob, = self.cursor.fetchone()
self.cursor.execute("select BLOBCol from TestBlobs")
blob, = self.cursor.fetchone()
typeObj = self.connection.gettype(u"UDT_OBJECT")
obj = typeObj.newobject()
obj.NUMBERVALUE = 5
obj.STRINGVALUE = "A string"
obj.FIXEDCHARVALUE = "Fixed str"
obj.NSTRINGVALUE = "A NCHAR string"
obj.NFIXEDCHARVALUE = "Fixed N"
obj.RAWVALUE = b"Raw Value"
obj.INTVALUE = 27
obj.SMALLINTVALUE = 13
obj.REALVALUE = 184.875
obj.DOUBLEPRECISIONVALUE = 1.375
obj.FLOATVALUE = 23.75
obj.DATEVALUE = datetime.date(2017, 5, 9)
obj.TIMESTAMPVALUE = datetime.datetime(2017, 5, 9, 9, 41, 13)
obj.TIMESTAMPTZVALUE = datetime.datetime(1986, 8, 2, 15, 27, 38)
obj.TIMESTAMPLTZVALUE = datetime.datetime(1999, 11, 12, 23, 5, 2)
obj.BINARYFLOATVALUE = 14.25
obj.BINARYDOUBLEVALUE = 29.1625
obj.CLOBVALUE = clob
obj.NCLOBVALUE = nclob
obj.BLOBVALUE = blob
subTypeObj = self.connection.gettype("UDT_SUBOBJECT")
subObj = subTypeObj.newobject()
subObj.SUBNUMBERVALUE = 23
subObj.SUBSTRINGVALUE = "Substring value"
obj.SUBOBJECTVALUE = subObj
self.cursor.execute("insert into TestObjects (IntCol, ObjectCol) " \
"values (4, :obj)", obj = obj)
self.cursor.execute("""
select IntCol, ObjectCol, ArrayCol
from TestObjects
where IntCol = 4""")
self.__TestData(4, (5, 'A string', 'Fixed str ', 'A NCHAR string',
'Fixed N ', b'Raw Value', 27, 13, 184.875, 1.375, 23.75,
14.25, 29.1625, cx_Oracle.Timestamp(2017, 5, 9, 0, 0, 0),
cx_Oracle.Timestamp(2017, 5, 9, 9, 41, 13),
cx_Oracle.Timestamp(1986, 8, 2, 15, 27, 38),
cx_Oracle.Timestamp(1999, 11, 12, 23, 5, 2),
'A short CLOB', 'A short NCLOB', b'A short BLOB',
(23, 'Substring value'), None), None)
self.connection.rollback()
def testFetchData(self):
"test fetching objects"
self.cursor.execute("""
select
IntCol,
ObjectCol,
ArrayCol
from TestObjects
order by IntCol""")
self.assertEqual(
self.cursor.description,
[('INTCOL', cx_Oracle.NUMBER, 10, None, 9, 0, 0),
('OBJECTCOL', cx_Oracle.OBJECT, None, None, None, None, 1),
('ARRAYCOL', cx_Oracle.OBJECT, None, None, None, None, 1)])
self.__TestData(1, (1, 'First row', 'First ', 2, 5, 12.5,
cx_Oracle.Timestamp(2007, 3, 6, 0, 0, 0),
cx_Oracle.Timestamp(2008, 9, 12, 16, 40),
(11, 'Sub object 1'), [(5, 'first element'),
(6, 'second element')]),
[5, 10, None, 20])
self.__TestData(2, None, [3, None, 9, 12, 15])
self.__TestData(3, (3, 'Third row', 'Third ', 4, 10, 43.25,
cx_Oracle.Timestamp(2007, 6, 21, 0, 0, 0),
cx_Oracle.Timestamp(2007, 12, 13, 7, 30, 45),
(13, 'Sub object 3'), [(10, 'element #1'),
(20, 'element #2'),
(30, 'element #3'),
(40, 'element #4')]), None)
def setUp(self):
super().setUp()
self.raw_data = []
self.data_by_key = {}
for i in range(1, 11):
time_tuple = (2002, 12, 9, 0, 0, 0, 0, 0, -1)
time_in_ticks = time.mktime(time_tuple) + i * 86400
date_value = oracledb.TimestampFromTicks(int(time_in_ticks))
str_value = str(i * 50)
fsecond = int(str_value + "0" * (6 - len(str_value)))
date_col = oracledb.Timestamp(date_value.year, date_value.month,
date_value.day, date_value.hour,
date_value.minute, i * 2, fsecond)
if i % 2:
time_in_ticks = time.mktime(time_tuple) + i * 86400 + 86400
date_value = oracledb.TimestampFromTicks(int(time_in_ticks))
str_value = str(i * 125)
fsecond = int(str_value + "0" * (6 - len(str_value)))
nullable_col = oracledb.Timestamp(
date_value.year, date_value.month, date_value.day,
date_value.hour, date_value.minute, i * 3, fsecond)
else:
nullable_col = None
data_tuple = (i, date_col, nullable_col)
self.raw_data.append(data_tuple)
self.data_by_key[i] = data_tuple
def setUp(self):
TestEnv.BaseTestCase.setUp(self)
self.rawData = []
self.dataByKey = {}
for i in range(1, 11):
timeTuple = (2002, 12, 9, 0, 0, 0, 0, 0, -1)
timeInTicks = time.mktime(timeTuple) + i * 86400
dateValue = cx_Oracle.TimestampFromTicks(int(timeInTicks))
strValue = str(i * 50)
fsecond = int(strValue + "0" * (6 - len(strValue)))
dateCol = cx_Oracle.Timestamp(dateValue.year, dateValue.month,
dateValue.day, dateValue.hour, dateValue.minute,
i * 2, fsecond)
if i % 2:
timeInTicks = time.mktime(timeTuple) + i * 86400 + 86400
dateValue = cx_Oracle.TimestampFromTicks(int(timeInTicks))
strValue = str(i * 125)
fsecond = int(strValue + "0" * (6 - len(strValue)))
nullableCol = cx_Oracle.Timestamp(dateValue.year,
dateValue.month, dateValue.day, dateValue.hour,
dateValue.minute, i * 3, fsecond)
else:
nullableCol = None
tuple = (i, dateCol, nullableCol)
self.rawData.append(tuple)
self.dataByKey[i] = tuple
def testFetchData(self):
"test fetching objects"
self.cursor.execute(u"""
select
IntCol,
ObjectCol,
ArrayCol
from TestObjects
order by IntCol""")
self.assertEqual(
self.cursor.description,
[(u'INTCOL', cx_Oracle.NUMBER, 10, None, 9, 0, 0),
(u'OBJECTCOL', cx_Oracle.OBJECT, None, None, None, None, 1),
(u'ARRAYCOL', cx_Oracle.OBJECT, None, None, None, None, 1)])
self.__TestData(
1,
(1, u'First row', u'First ', u'N First Row', u'N First ', 2,
5, 12.5, 25.25, 50.125, cx_Oracle.Timestamp(2007, 3, 6, 0, 0, 0),
cx_Oracle.Timestamp(2008, 9, 12, 16, 40), u'Short CLOB value',
u'Short NCLOB Value', 'Short BLOB value',
(11, 'Sub object 1'), [(5, 'first element'),
(6, 'second element')]), [5, 10, None, 20])
self.__TestData(2, None, [3, None, 9, 12, 15])
self.__TestData(
3,
(3, u'Third row', u'Third ', u'N Third Row', u'N Third ', 4,
10, 43.25, 86.5, 192.125, cx_Oracle.Timestamp(
2007, 6, 21, 0, 0,
0), cx_Oracle.Timestamp(2007, 12, 13, 7, 30,
45), u'Another short CLOB value',
u'Another short NCLOB Value', 'Yet another short BLOB value',
(13, 'Sub object 3'), [(10, 'element #1'), (20, 'element #2'),
(30, 'element #3'),
(40, 'element #4')]), None)
def testBindInOutSetInputSizes(self):
"test binding in/out with set input sizes defined"
vars = self.cursor.setinputsizes(value = cx_Oracle.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
begin
:value := :value + 5.25;
end;""",
value = cx_Oracle.Timestamp(2002, 12, 12, 10, 0, 0))
self.assertEqual(vars["value"].getvalue(),
cx_Oracle.Timestamp(2002, 12, 17, 16, 0, 0))
def test_1411_bind_in_out_set_input_sizes(self):
"1411 - test binding in/out with set input sizes defined"
bind_vars = self.cursor.setinputsizes(value=oracledb.DATETIME)
self.cursor.execute("""
begin
:value := :value + 5.25;
end;""",
value=oracledb.Timestamp(2002, 12, 12, 10, 0, 0))
self.assertEqual(bind_vars["value"].getvalue(),
oracledb.Timestamp(2002, 12, 17, 16, 0, 0))
def testBindInOutVarDirectSet(self):
"test binding in/out with cursor.var() method"
var = self.cursor.var(cx_Oracle.DB_TYPE_TIMESTAMP)
var.setvalue(0, cx_Oracle.Timestamp(2002, 12, 9, 6, 0, 0))
self.cursor.execute("""
begin
:value := :value + 5.25;
end;""",
value = var)
self.assertEqual(var.getvalue(),
cx_Oracle.Timestamp(2002, 12, 14, 12, 0, 0))
def test_1413_bind_in_out_var_direct_set(self):
"1413 - test binding in/out with cursor.var() method"
var = self.cursor.var(oracledb.DATETIME)
var.setvalue(0, oracledb.Timestamp(2002, 12, 9, 6, 0, 0))
self.cursor.execute("""
begin
:value := :value + 5.25;
end;""",
value=var)
self.assertEqual(var.getvalue(),
oracledb.Timestamp(2002, 12, 14, 12, 0, 0))
def testBindDate(self):
"test binding in a date"
self.cursor.execute("""
select * from TestDates
where DateCol = :value""",
value=cx_Oracle.Timestamp(2002, 12, 13, 9, 36, 0))
self.assertEqual(self.cursor.fetchall(), [self.dataByKey[4]])
def test_1400_bind_date(self):
"1400 - test binding in a date"
self.cursor.execute("""
select * from TestDates
where DateCol = :value""",
value=oracledb.Timestamp(2002, 12, 13, 9, 36, 0))
self.assertEqual(self.cursor.fetchall(), [self.data_by_key[4]])
def test_1403_bind_date_after_string(self):
"1403 - test binding in a date after setting input sizes to a string"
self.cursor.setinputsizes(value=15)
self.cursor.execute("""
select * from TestDates
where DateCol = :value""",
value=oracledb.Timestamp(2002, 12, 14, 12, 0, 0))
self.assertEqual(self.cursor.fetchall(), [self.data_by_key[5]])
def testBindDateAfterString(self):
"test binding in a date after setting input sizes to a string"
self.cursor.setinputsizes(value=15)
self.cursor.execute("""
select * from TestDates
where DateCol = :value""",
value=cx_Oracle.Timestamp(2002, 12, 14, 12, 0, 0))
self.assertEqual(self.cursor.fetchall(), [self.dataByKey[5]])
def testBindTimestamp(self):
"test binding in a timestamp"
self.cursor.setinputsizes(value = cx_Oracle.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
select * from TestTimestamps
where TimestampCol = :value""",
value = cx_Oracle.Timestamp(2002, 12, 14, 0, 0, 10, 250000))
self.assertEqual(self.cursor.fetchall(), [self.dataByKey[5]])
def test_2602_bind_out_set_input_sizes(self):
"2602 - test binding out with set input sizes defined"
bind_vars = self.cursor.setinputsizes(value=oracledb.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
begin
:value := to_timestamp('20021209', 'YYYYMMDD');
end;""")
self.assertEqual(bind_vars["value"].getvalue(),
oracledb.Timestamp(2002, 12, 9))
def testBindOutSetInputSizes(self):
"test binding out with set input sizes defined"
vars = self.cursor.setinputsizes(value = cx_Oracle.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
begin
:value := to_timestamp('20021209', 'YYYYMMDD');
end;""")
self.assertEqual(vars["value"].getvalue(),
cx_Oracle.Timestamp(2002, 12, 9))
def test_2600_bind_timestamp(self):
"2600 - test binding in a timestamp"
self.cursor.setinputsizes(value=oracledb.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
select * from TestTimestamps
where TimestampCol = :value""",
value=oracledb.Timestamp(2002, 12, 14, 0, 0, 10,
250000))
self.assertEqual(self.cursor.fetchall(), [self.data_by_key[5]])
def test_1410_bind_out_set_input_sizes(self):
"1410 - test binding out with set input sizes defined"
bind_vars = self.cursor.setinputsizes(value=oracledb.DATETIME)
self.cursor.execute("""
begin
:value := to_date(20021209, 'YYYYMMDD');
end;""")
self.assertEqual(bind_vars["value"].getvalue(),
oracledb.Timestamp(2002, 12, 9))
def test_1408_bind_in_out_date_array_by_var(self):
"1408 - test binding in/out a date array (with arrayvar)"
array = self.cursor.arrayvar(oracledb.DATETIME, 10, 100)
original_data = [r[1] for r in self.raw_data]
array.setvalue(0, original_data)
self.cursor.execute("""
begin
pkg_TestDateArrays.TestInOutArrays(:num_elems, :array);
end;""",
num_elems=5,
array=array)
self.assertEqual(array.getvalue(),
[ oracledb.Timestamp(2002, 12, 17, 2, 24, 0),
oracledb.Timestamp(2002, 12, 18, 4, 48, 0),
oracledb.Timestamp(2002, 12, 19, 7, 12, 0),
oracledb.Timestamp(2002, 12, 20, 9, 36, 0),
oracledb.Timestamp(2002, 12, 21, 12, 0, 0) ] + \
original_data[5:])
def testBindInOutDateArrayByVar(self):
"test binding in/out a date array (with arrayvar)"
array = self.cursor.arrayvar(cx_Oracle.DATETIME, 10, 100)
originalData = [r[1] for r in self.rawData]
array.setvalue(0, originalData)
self.cursor.execute("""
begin
pkg_TestDateArrays.TestInOutArrays(:numElems, :array);
end;""",
numElems=5,
array=array)
self.assertEqual(array.getvalue(),
[ cx_Oracle.Timestamp(2002, 12, 17, 2, 24, 0),
cx_Oracle.Timestamp(2002, 12, 18, 4, 48, 0),
cx_Oracle.Timestamp(2002, 12, 19, 7, 12, 0),
cx_Oracle.Timestamp(2002, 12, 20, 9, 36, 0),
cx_Oracle.Timestamp(2002, 12, 21, 12, 0, 0) ] + \
originalData[5:])
def testBindOutVar(self):
"test binding out with cursor.var() method"
var = self.cursor.var(cx_Oracle.DB_TYPE_TIMESTAMP)
self.cursor.execute("""
begin
:value := to_date('20021231 12:31:00',
'YYYYMMDD HH24:MI:SS');
end;""",
value = var)
self.assertEqual(var.getvalue(),
cx_Oracle.Timestamp(2002, 12, 31, 12, 31, 0))
def test_1412_bind_out_var(self):
"1412 - test binding out with cursor.var() method"
var = self.cursor.var(oracledb.DATETIME)
self.cursor.execute("""
begin
:value := to_date('20021231 12:31:00',
'YYYYMMDD HH24:MI:SS');
end;""",
value=var)
self.assertEqual(var.getvalue(),
oracledb.Timestamp(2002, 12, 31, 12, 31, 0))
def test_3506_datetime(self):
"3506 - test binding dates/intervals as scalar JSON values"
data = [
datetime.datetime.today(),
datetime.datetime(2004, 2, 1, 3, 4, 5),
datetime.datetime(2020, 12, 2, 13, 29, 14),
datetime.timedelta(8.5),
datetime.datetime(2002, 12, 13, 9, 36, 0),
oracledb.Timestamp(2002, 12, 13, 9, 36, 0),
datetime.datetime(2002, 12, 13)
]
self.__bind_scalar_as_json(data)
def test_2304_fetch_data(self):
"2304 - test fetching objects"
self.cursor.execute("alter session set time_zone = 'UTC'")
self.cursor.execute("""
select
IntCol,
ObjectCol,
ArrayCol
from TestObjects
order by IntCol""")
expected_value = [
('INTCOL', oracledb.DB_TYPE_NUMBER, 10, None, 9, 0, 0),
('OBJECTCOL', oracledb.DB_TYPE_OBJECT, None, None, None, None, 1),
('ARRAYCOL', oracledb.DB_TYPE_OBJECT, None, None, None, None, 1)
]
self.assertEqual(self.cursor.description, expected_value)
expected_value = (1, 'First row', 'First ', 'N First Row',
'N First ', b'Raw Data 1', 2, 5, 12.125, 0.5,
12.5, 25.25, 50.125,
oracledb.Timestamp(2007, 3, 6, 0, 0, 0),
oracledb.Timestamp(2008, 9, 12, 16, 40),
oracledb.Timestamp(2009, 10, 13, 17, 50),
oracledb.Timestamp(2010, 11, 14, 18,
55), 'Short CLOB value',
'Short NCLOB Value', b'Short BLOB value',
(11, 'Sub object 1'), [(5, 'first element'),
(6, 'second element')])
self.__test_data(1, expected_value, [5, 10, None, 20])
self.__test_data(2, None, [3, None, 9, 12, 15])
expected_value = (3, 'Third row', 'Third ', 'N Third Row',
'N Third ', b'Raw Data 3', 4, 10, 6.5, 0.75, 43.25,
86.5, 192.125,
oracledb.Timestamp(2007, 6, 21, 0, 0, 0),
oracledb.Timestamp(2007, 12, 13, 7, 30, 45),
oracledb.Timestamp(2017, 6, 21, 23, 18, 45),
oracledb.Timestamp(2017, 7, 21, 8, 27,
13), 'Another short CLOB value',
'Another short NCLOB Value',
b'Yet another short BLOB value',
(13, 'Sub object 3'), [(10, 'element #1'),
(20, 'element #2'),
(30, 'element #3'),
(40, 'element #4')])
self.__test_data(3, expected_value, None)
def testBindOutDateArrayByVar(self):
"test binding out a date array (with arrayvar)"
array = self.cursor.arrayvar(cx_Oracle.DATETIME, 6, 100)
self.cursor.execute("""
begin
pkg_TestDateArrays.TestOutArrays(:numElems, :array);
end;""",
numElems = 6,
array = array)
self.assertEqual(array.getvalue(),
[ cx_Oracle.Timestamp(2002, 12, 13, 4, 48, 0),
cx_Oracle.Timestamp(2002, 12, 14, 9, 36, 0),
cx_Oracle.Timestamp(2002, 12, 15, 14, 24, 0),
cx_Oracle.Timestamp(2002, 12, 16, 19, 12, 0),
cx_Oracle.Timestamp(2002, 12, 18, 0, 0, 0),
cx_Oracle.Timestamp(2002, 12, 19, 4, 48, 0) ])
def test_1409_bind_out_date_array_by_var(self):
"1409 - test binding out a date array (with arrayvar)"
array = self.cursor.arrayvar(oracledb.DATETIME, 6, 100)
self.cursor.execute("""
begin
pkg_TestDateArrays.TestOutArrays(:num_elems, :array);
end;""",
num_elems=6,
array=array)
self.assertEqual(array.getvalue(), [
oracledb.Timestamp(2002, 12, 13, 4, 48, 0),
oracledb.Timestamp(2002, 12, 14, 9, 36, 0),
oracledb.Timestamp(2002, 12, 15, 14, 24, 0),
oracledb.Timestamp(2002, 12, 16, 19, 12, 0),
oracledb.Timestamp(2002, 12, 18, 0, 0, 0),
oracledb.Timestamp(2002, 12, 19, 4, 48, 0)
])
