Re: [cx-oracle-users] Query Result Change Notification

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

On 17/11/12 9:17 AM, Yann Cézard wrote:
> Le 16/11/2012 14:20, Glen Walker a écrit :
>> Hi,
>>
>> 2012/11/15 Yann Cézard<yann.cezard@un...>:
>> > I have recently discovered the Oracle "Database Change Notification"
>> > functionnality, which is really useful for one of my project, and looking at
>> > some docs to understand more about it, I found about QRCN (Query Result Change
>> > Notification) which might responds to my needs even more than DCN.
>> >
>> > Searching the web about QRCN and cx_Oracle, I found this really useful message
>> > which was posted one year and a half ago on this same list :
>> > (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>>
>> > So now I was wondering if there are any plan to introduce the QRCN feature in
>> > cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
>> > suggested code modification ? Or is such a functionnality is include under
>> > another name in cx_Oracle ?
>>
>> As luck would have it a project I am working on would also benefit from QRCN.
>> After reading the message from 2011-04 a few days ago I decided to implement
>> this myself and contribute it to the project. I have now finished the update
>> and need to know the best way to submit a pull request or patch. I have just
>> joined the mailing list, so perhaps a list regular could point me in the right
>> direction - I see that cx_Oracle is on Bitbucket:
>>    https://bitbucket.org/anthony_tuininga/cx_oracle/
>> as well as Sourceforge:
>>    http://sourceforge.net/projects/cx-oracle/
>>
>> Regards,
>> Glen
>>
> Great, that's very good news !
>
> I will test as soon as it is available. Thanks a lot !
>
> Best regards,
>
> Yann

Hi,

I've submitted a pull request with my changes to support query result 
set change notification. You can find it on Bitbucket 
<https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if 
you don't want to wait. Sample code below.

Kind regards,
Glen

import time
import cx_Oracle

def EventToString(event):
     if event == cx_Oracle.EVENT_NONE:
         return 'None'
     elif event == cx_Oracle.EVENT_STARTUP:
         return 'Startup'
     elif event == cx_Oracle.EVENT_SHUTDOWN:
         return 'Shutdown'
     elif event == cx_Oracle.EVENT_SHUTDOWN_ANY:
         return 'ShutdownAny'
     elif event == cx_Oracle.EVENT_DEREG:
         return 'Dereg'
     elif event == cx_Oracle.EVENT_OBJCHANGE:
         return 'ObjChange'
     elif event == cx_Oracle.EVENT_QUERYCHANGE:
         return 'QueryChange'
     return 'Unknown'

def OperationToString(operation):
     operations = []
     if operation & cx_Oracle.OPCODE_INSERT:
         operations.append("insert")
     if operation & cx_Oracle.OPCODE_UPDATE:
         operations.append("update")
     if operation & cx_Oracle.OPCODE_DELETE:
         operations.append("delete")
     if operation & cx_Oracle.OPCODE_ALTER:
         operations.append("alter")
     if operation & cx_Oracle.OPCODE_DROP:
         operations.append("drop")
     if operation & cx_Oracle.OPCODE_ALLOPS:
         operations.append("all operations")
     if operation & cx_Oracle.OPCODE_ALLROWS:
         operations.append("all rows")
     return ", ".join(operations)

def onChange(message):
print "=" * 60
     print 'onChange subscription id %d' % (message.subscription.id, )

     print "Message type:", message.type, EventToString(message.type)
     print "Message database name:", message.dbname
     if message.tables is not None:
         print "Message tables:"
         for table in message.tables:
             print "--> Table Name:", table.name
             print "--> Table Operation:", table.operation, 
OperationToString(table.operation)
             if table.rows is not None:
                 print "--> Table Rows:"
                 for row in table.rows:
                     print "--> --> Row RowId:", row.rowid
                     print "--> --> Row Operation:", row.operation, 
OperationToString(row.operation)
                     print "-" * 60
             print "=" * 60
     if message.queries is not None:
         print "Message queries:"
         for query in message.queries:
             print "--> Query ID:", query.id
             print "--> Query Operation:", query.operation, 
EventToString(query.operation)
             if query.tables is not None:
                 print "--> Query tables:"
                 for table in query.tables:
                     print "--> --> Table Name:", table.name
                     print "--> --> Table Operation:", table.operation, 
OperationToString(table.operation)
                     if table.rows is not None:
                         print "--> --> Table Rows:"
                         for row in table.rows:
                             print "--> --> --> Row RowId:", row.rowid
                             print "--> --> --> Row Operation:", 
row.operation, OperationToString(row.operation)
                             print "-" * 60
             print "=" * 60

print 'Connecting'

dsn = cx_Oracle.makedsn(...)
connection = cx_Oracle.connect(user=username, password=password, 
dsn=dsn, threaded=True, events=True)

subscription = connection.subscribe(callback=onChange, 
operations=cx_Oracle.OPCODE_ALLOPS,

qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN,cqqos=cx_Oracle.SUBSCR_CQ_QOS_QUERY,
         rowids=True)

print "Subscription:", subscription
print "--> Connection:", subscription.connection
print "--> Callback:", subscription.callback
print "--> Namespace:", subscription.namespace
print "--> Protocol:", subscription.protocol
print "--> Port:", subscription.port
print "--> Timeout:", subscription.timeout
print "--> Operations:", subscription.operations
print "--> QOS:", subscription.qos
print "--> CQ QOS:", subscription.cqqos
print "--> Rowids:", subscription.rowids
print "--> Id:", subscription.id

queryId1 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT <= 3')
print "Query ID 1:", queryId1

queryId2 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT > 3')
print "Query ID 2:", queryId2

print 'Waiting'
try:
     time.sleep(6000)
except KeyboardInterrupt:
     pass

print 'Unsubscribing'
subscription = None

print 'Disconnecting'
connection.close()