def test_1118_rollback_on_del(self):
"1118 - connection rolls back before destruction"
connection = base.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
other_connection = base.get_connection()
other_cursor = other_connection.cursor()
other_cursor.execute("insert into TestTempTable (IntCol) values (1)")
del other_cursor
del other_connection
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 0)
def test_1121_ctx_mgr_close(self):
"1121 - test context manager - close"
connection = base.get_connection()
with connection:
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
cursor.execute("insert into TestTempTable (IntCol) values (1)")
connection.commit()
cursor.execute("insert into TestTempTable (IntCol) values (2)")
self.assertRaises(oracledb.InterfaceError, connection.ping)
connection = base.get_connection()
cursor = connection.cursor()
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 1)
def test_1104_autocommit(self):
"1104 - test use of autocommit"
connection = base.get_connection()
cursor = connection.cursor()
other_connection = base.get_connection()
other_cursor = other_connection.cursor()
cursor.execute("truncate table TestTempTable")
cursor.execute("insert into TestTempTable (IntCol) values (1)")
other_cursor.execute("select IntCol from TestTempTable")
rows = other_cursor.fetchall()
self.assertEqual(rows, [])
connection.autocommit = True
cursor.execute("insert into TestTempTable (IntCol) values (2)")
other_cursor.execute("select IntCol from TestTempTable order by IntCol")
rows = other_cursor.fetchall()
self.assertEqual(rows, [(1,), (2,)])
def test_2708_visibility_mode_commit(self):
"2708 - test enqueue visibility option - ENQ_ON_COMMIT"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.visibility = oracledb.ENQ_ON_COMMIT
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = base.get_connection()
deq_options = other_connection.deqoptions()
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
message_id = other_connection.deq(self.book_queue_name, deq_options,
props, book)
self.assertTrue(message_id is None)
self.connection.commit()
message_id = other_connection.deq(self.book_queue_name, deq_options,
props, book)
self.assertTrue(message_id is not None)
def test_2712_delivery_mode_same_persistent_buffered(self):
"2712 - test enqueue/dequeue delivery modes the same"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.deliverymode = oracledb.MSG_PERSISTENT_OR_BUFFERED
enq_options.visibility = oracledb.ENQ_IMMEDIATE
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = base.get_connection()
deq_options = other_connection.deqoptions()
deq_options.deliverymode = oracledb.MSG_PERSISTENT_OR_BUFFERED
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_IMMEDIATE
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
other_connection.deq(self.book_queue_name, deq_options, props, book)
results = (book.TITLE, book.AUTHORS, book.PRICE)
other_connection.commit()
self.assertEqual(results, self.book_data[0])
def __connect_and_drop(self):
"""Connect to the database, perform a query and drop the connection."""
connection = base.get_connection(threaded=True)
cursor = connection.cursor()
cursor.execute("select count(*) from TestNumbers")
count, = cursor.fetchone()
self.assertEqual(count, 10)
def test_2804_enq_and_deq_visibility(self):
"2804 - test visibility option for enqueue and dequeue"
queue = self.__get_and_clear_raw_queue()
# first test with ENQ_ON_COMMIT (commit required)
queue.enqOptions.visibility = oracledb.ENQ_ON_COMMIT
props1 = self.connection.msgproperties(payload="A first message")
props2 = self.connection.msgproperties(payload="A second message")
queue.enqMany([props1, props2])
other_connection = base.get_connection()
other_queue = other_connection.queue(RAW_QUEUE_NAME)
other_queue.deqOptions.wait = oracledb.DEQ_NO_WAIT
other_queue.deqOptions.visibility = oracledb.DEQ_ON_COMMIT
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 0)
self.connection.commit()
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 2)
other_connection.rollback()
# second test with ENQ_IMMEDIATE (no commit required)
queue.enqOptions.visibility = oracledb.ENQ_IMMEDIATE
other_queue.deqOptions.visibility = oracledb.DEQ_IMMEDIATE
queue.enqMany([props1, props2])
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 4)
other_connection.rollback()
messages = other_queue.deqMany(5)
self.assertEqual(len(messages), 0)
def test_1108_change_password_negative(self):
"1108 - test changing password to an invalid value"
connection = base.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
new_password = "******" * 150
self.assertRaises(oracledb.DatabaseError, connection.changepassword,
base.get_main_password(), new_password)
def __deq_in_thread(self, results):
connection = base.get_connection()
books_type = connection.gettype(self.book_type_name)
book = books_type.newobject()
options = connection.deqoptions()
options.wait = 10
props = connection.msgproperties()
if connection.deq(self.book_queue_name, options, props, book):
results.append((book.TITLE, book.AUTHORS, book.PRICE))
connection.commit()
def test_1123_closed_connection_attributes(self):
"1123 - test closed connection attribute values"
connection = base.get_connection()
connection.close()
attr_names = ["current_schema", "edition", "external_name",
"internal_name", "stmtcachesize"]
if base.get_client_version() >= (12, 1):
attr_names.append("ltxid")
for name in attr_names:
self.assertRaises(oracledb.InterfaceError, getattr, connection,
name)
def test_1107_change_password(self):
"1107 - test changing password"
connection = base.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
sys_random = random.SystemRandom()
new_password = "".join(sys_random.choice(string.ascii_letters) \
for i in range(20))
connection.changepassword(base.get_main_password(), new_password)
cconnection = oracledb.connect(base.get_main_user(), new_password,
base.get_connect_string())
connection.changepassword(new_password, base.get_main_password())
def __deq_in_thread(self, results):
connection = base.get_connection(threaded=True)
queue = connection.queue(RAW_QUEUE_NAME)
queue.deqOptions.wait = 10
queue.deqOptions.navigation = oracledb.DEQ_FIRST_MSG
while len(results) < len(RAW_PAYLOAD_DATA):
messages = queue.deqMany(5)
if not messages:
break
for m in messages:
results.append(m.payload.decode(connection.encoding))
connection.commit()
def test_1101_app_context(self):
"1101 - test use of application context"
namespace = "CLIENTCONTEXT"
app_context_entries = [
( namespace, "ATTR1", "VALUE1" ),
( namespace, "ATTR2", "VALUE2" ),
( namespace, "ATTR3", "VALUE3" )
]
connection = base.get_connection(appcontext=app_context_entries)
cursor = connection.cursor()
for namespace, name, value in app_context_entries:
cursor.execute("select sys_context(:1, :2) from dual",
(namespace, name))
actual_value, = cursor.fetchone()
self.assertEqual(actual_value, value)
def test_1109_parse_password(self):
"1109 - test connecting with password containing / and @ symbols"
connection = base.get_connection()
if self.is_on_oracle_cloud(connection):
self.skipTest("passwords on Oracle Cloud are strictly controlled")
sys_random = random.SystemRandom()
chars = list(sys_random.choice(string.ascii_letters) for i in range(20))
chars[4] = "/"
chars[8] = "@"
new_password = "".join(chars)
connection.changepassword(base.get_main_password(), new_password)
try:
arg = "%s/%s@%s" % (base.get_main_user(), new_password,
base.get_connect_string())
oracledb.connect(arg)
finally:
connection.changepassword(new_password, base.get_main_password())
def test_1125_transaction_begin(self):
"1125 - test begin, prepare, cancel transaction"
connection = base.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
connection.begin(10, 'trxnId', 'branchId')
self.assertEqual(connection.prepare(), False)
connection.begin(10, 'trxnId', 'branchId')
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (1, 'tesName')""")
self.assertEqual(connection.prepare(), True)
connection.cancel()
connection.rollback()
cursor.execute("select count(*) from TestTempTable")
count, = cursor.fetchone()
self.assertEqual(count, 0)
def test_1103_attributes(self):
"1103 - test connection end-to-end tracing attributes"
connection = base.get_connection()
if base.get_client_version() >= (12, 1) \
and not self.is_on_oracle_cloud(connection):
sql = "select dbop_name from v$sql_monitor " \
"where sid = sys_context('userenv', 'sid')" \
"and status = 'EXECUTING'"
self.__verify_attributes(connection, "dbop", "oracledb_dbop", sql)
sql = "select sys_context('userenv', 'action') from dual"
self.__verify_attributes(connection, "action", "oracledb_Action", sql)
sql = "select sys_context('userenv', 'module') from dual"
self.__verify_attributes(connection, "module", "oracledb_Module", sql)
sql = "select sys_context('userenv', 'client_info') from dual"
self.__verify_attributes(connection, "clientinfo", "oracledb_cinfo",
sql)
sql = "select sys_context('userenv', 'client_identifier') from dual"
self.__verify_attributes(connection, "client_identifier",
"oracledb_cid", sql)
def test_1113_connect_with_handle(self):
"1113 - test creating a connection using a handle"
connection = base.get_connection()
cursor = connection.cursor()
cursor.execute("truncate table TestTempTable")
int_value = random.randint(1, 32768)
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (:val, null)""", val=int_value)
connection2 = oracledb.connect(handle = connection.handle)
cursor = connection2.cursor()
cursor.execute("select IntCol from TestTempTable")
fetched_int_value, = cursor.fetchone()
self.assertEqual(fetched_int_value, int_value)
cursor.close()
self.assertRaises(oracledb.DatabaseError, connection2.close)
connection.close()
cursor = connection2.cursor()
self.assertRaises(oracledb.DatabaseError, cursor.execute,
"select count(*) from TestTempTable")
def test_2709_visibility_mode_immediate(self):
"2709 - test enqueue visibility option - ENQ_IMMEDIATE"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.visibility = oracledb.ENQ_IMMEDIATE
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = base.get_connection()
deq_options = other_connection.deqoptions()
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_ON_COMMIT
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
other_connection.deq(self.book_queue_name, deq_options, props, book)
results = (book.TITLE, book.AUTHORS, book.PRICE)
other_connection.commit()
self.assertEqual(results, self.book_data[0])
def test_2715_enqueue_transformation(self):
"2715 - test enqueue transformation"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
expectedPrice = book.PRICE + 5
enq_options = self.connection.enqoptions()
enq_options.transformation = "%s.transform1" % self.connection.username
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
self.connection.commit()
other_connection = base.get_connection()
deq_options = other_connection.deqoptions()
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_IMMEDIATE
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
other_connection.deq(self.book_queue_name, deq_options, props, book)
otherPrice = book.PRICE
self.assertEqual(otherPrice, expectedPrice)
def test_2713_delivery_mode_different(self):
"2713 - test enqueue/dequeue delivery modes different"
self.__clear_books_queue()
books_type = self.connection.gettype(self.book_type_name)
book = books_type.newobject()
book.TITLE, book.AUTHORS, book.PRICE = self.book_data[0]
enq_options = self.connection.enqoptions()
enq_options.deliverymode = oracledb.MSG_BUFFERED
enq_options.visibility = oracledb.ENQ_IMMEDIATE
props = self.connection.msgproperties()
self.connection.enq(self.book_queue_name, enq_options, props, book)
other_connection = base.get_connection()
deq_options = other_connection.deqoptions()
deq_options.deliverymode = oracledb.MSG_PERSISTENT
deq_options.navigation = oracledb.DEQ_FIRST_MSG
deq_options.visibility = oracledb.DEQ_IMMEDIATE
deq_options.wait = oracledb.DEQ_NO_WAIT
books_type = other_connection.gettype(self.book_type_name)
book = books_type.newobject()
props = other_connection.msgproperties()
message_id = other_connection.deq(self.book_queue_name, deq_options,
props, book)
self.assertTrue(message_id is None)
def test_1124_ping(self):
"1124 - test connection ping"
connection = base.get_connection()
connection.ping()
def test_1112_exception_on_close(self):
"1112 - confirm an exception is raised after closing a connection"
connection = base.get_connection()
connection.close()
self.assertRaises(oracledb.InterfaceError, connection.rollback)
def test_1116_version(self):
"1116 - connection version is a string"
connection = base.get_connection()
self.assertTrue(isinstance(connection.version, str))
def test_1120_string_format(self):
"1120 - test string format of connection"
connection = base.get_connection()
expected_value = "<cx_Oracle.Connection to %s@%s>" % \
(base.get_main_user(), base.get_connect_string())
self.assertEqual(str(connection), expected_value)
def test_1100_all_args(self):
"1100 - connection to database with user, password, TNS separate"
connection = base.get_connection()
self.__verify_args(connection)
def test_3000_subscription(self):
"3000 - test Subscription for insert, update, delete and truncate"
# skip if running on the Oracle Cloud, which does not support
# subscriptions currently
if self.is_on_oracle_cloud():
message = "Oracle Cloud does not support subscriptions currently"
self.skipTest(message)
# truncate table in order to run test in known state
self.cursor.execute("truncate table TestTempTable")
# expected values
table_operations = [
oracledb.OPCODE_INSERT, oracledb.OPCODE_UPDATE,
oracledb.OPCODE_INSERT, oracledb.OPCODE_DELETE,
oracledb.OPCODE_ALTER | oracledb.OPCODE_ALLROWS
]
row_operations = [
oracledb.OPCODE_INSERT, oracledb.OPCODE_UPDATE,
oracledb.OPCODE_INSERT, oracledb.OPCODE_DELETE
]
rowids = []
# set up subscription
data = SubscriptionData(5)
connection = base.get_connection(threaded=True, events=True)
sub = connection.subscribe(callback=data.CallbackHandler,
timeout=10,
qos=oracledb.SUBSCR_QOS_ROWIDS)
sub.registerquery("select * from TestTempTable")
connection.autocommit = True
cursor = connection.cursor()
# insert statement
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (1, 'test')""")
cursor.execute("select rowid from TestTempTable where IntCol = 1")
rowids.extend(r for r, in cursor)
# update statement
cursor.execute("""
update TestTempTable set
StringCol = 'update'
where IntCol = 1""")
cursor.execute("select rowid from TestTempTable where IntCol = 1")
rowids.extend(r for r, in cursor)
# second insert statement
cursor.execute("""
insert into TestTempTable (IntCol, StringCol)
values (2, 'test2')""")
cursor.execute("select rowid from TestTempTable where IntCol = 2")
rowids.extend(r for r, in cursor)
# delete statement
cursor.execute("delete TestTempTable where IntCol = 2")
rowids.append(rowids[-1])
# truncate table
cursor.execute("truncate table TestTempTable")
# wait for all messages to be sent
data.condition.acquire()
data.condition.wait(10)
# verify the correct messages were sent
self.assertEqual(data.table_operations, table_operations)
self.assertEqual(data.row_operations, row_operations)
self.assertEqual(data.rowids, rowids)
# test string format of subscription object is as expected
fmt = "<cx_Oracle.Subscription on <cx_Oracle.Connection to %s@%s>>"
expected = fmt % (base.get_main_user(), base.get_connect_string())
self.assertEqual(str(sub), expected)
#!/usr/bin/env python
import sys
from base import get_connection
connection = get_connection()
channel = connection.channel()
channel.queue_declare(queue = 'hello')
message = ' '.join(sys.argv[1:]) or 'Hello World!'
channel.basic_publish(
exchange = '',
routing_key = 'hello',
body = message
)
print ' [x] Sent %r' % (message,)
connection.close()
