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()