search

go-goracle / goracle

Go database/sql driver for connecting to Oracle Database, using the ODPI-C library
273 stars 43 forks source link

Support AQ #186

Closed sinys15 closed 5 years ago

sinys15 commented 5 years ago

Hi, Could you please provide some functions for comfort work with Advanced Queue in Go? I'm interested in UDT (User Defined Type) and SYS.AQ$_JMS_TEXT_MESSAGE (for move Java project to Go) data types.

tgulacsi commented 5 years ago

https://docs.oracle.com/cd/B14117_01/server.101/b10785/jm_exmpl.htm shows it with pl/sql.

Show what you have, and I will try to help.

On Wed, Jul 31, 2019 at 7:05 AM +0200, "sinys15" notifications@github.com wrote:

Could you please provide some functions for comfort work with Advanced Queue in Go?

I'm interested in UDT and SYS.AQ$_JMS_TEXT_MESSAGE (for move Java project to Go) data types.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

cjbj commented 5 years ago

@sinys15 There is an example of PL/SQL usage in the context of node-oracledb at: https://github.com/oracle/node-oracledb/blob/v3.1.2/doc/api.md#aq

@tgulacsi we recently added native support to node-oracledb 4: https://oracle.github.io/node-oracledb/doc/api.html#aq and updated the AQ API in cx_Oracle to match: https://cx-oracle.readthedocs.io/en/latest/aq.html Yell out if you want to talk about the implementation.

sinys15 commented 5 years ago

@tgulacsi

https://docs.oracle.com/cd/B14117_01/server.101/b10785/jm_exmpl.htm shows it with pl/sql.

The part is showing enqueue example for sys.aq$_jms_text_message: Example 16-8 Enqueuing Through the Oracle JMS Administrative Interface

The part is showing dequeue example for sys.aq$_jms_bytes_message (not the same sys.aq$_jms_text_message but just show the example): Example 16-3 Dequeuing and Retrieving JMS Bytes Message Data

@cjbj node.js, python - is fine but I need it in Go. It is already realized in Java a long time ago. Example Dequeue from one of Java project:

// Dequeue 
Message message = queueConsumer.receive(queueReceiveTimeout);
TextMessage txtMsg = (TextMessage) message;

SourceMessage sourceMsg = new SourceMessage()
        .withId(txtMsg.getJMSMessageID())
        .withAppId(txtMsg.getStringProperty("JMSXAppID"))
        .withType(txtMsg.getJMSType())
        .withData(txtMsg.getText());

Enqueue and Dequeue will be enough, Queue Subscribers is not in priority.

sinys15 commented 5 years ago

@tgulacsi Hi, I see your sketches in Queue.go and it's cool to get some code very soon. Did you translate this code from other projects like @cjbj put references or did you do it from zero?

I just want to note several moments: 1) https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_AQ.html#GUID-56E78CA6-3EB0-44C9-AEB7-F13A5A077D73 Absent constants for WAIT parameter (we offten use it in our projects), it can be constants FOREVER, NO_WAIT or value in seconds. 2) // Dequeues messages into the given slice. func (Q Queue) Dequeue(messages []Message) (int, error) { Oracle separate enqueue and dequeue operations for single and batch messages. It looks like: DBMS_AQ.Enqueue and DBMS_AQ.Dequeue functions for single message and DBMS_AQ.ENQUEUE_ARRAY / DBMS_AQ.DEQUEUE_ARRAY for set of messages. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_AQ.html#GUID-A0E01434-EF7A-425D-BDC5-55262E98010B I do not insist but perhaps it would be better to repeat the Oracle way because %_ARRAY functions have specific parameters.

tgulacsi commented 5 years ago

It is what the underlying ODPI-C exposes. Plus, I didn't want to have separate functions for one and for many enq/deq.

1) If you check what are the numeric values of those constants, I can add them.

2) Of course it uses different underlying functions based on the length of the slice: if you use a 1 length messages slice, it uses enqOne / deqOne, otherwise the Many version.

As I don't have too much free time right now, the "queue" branch contains a "proposal". If you can write tests for it that would help a lot!

sinys15 commented 5 years ago

If you check what are the numeric values of those constants, I can add them.

In dbms_aq it looks like FOREVER CONSTANT BINARY_INTEGER := -1; FOREVER_UNLESS_SHUTDOWN CONSTANT BINARY_INTEGER := -2; NO_WAIT CONSTANT BINARY_INTEGER := 0; but how it looks in ODPI-C I don't know.

FOREVER_UNLESS_SHUTDOWN - undocumented, support is not required FOREVER - as far I remember it is default

cjbj commented 5 years ago

Regarding constants, search for "AQ" and "advanced queuing" in https://github.com/oracle/odpi/blob/master/include/dpi.h The same values were used in node-oracledb: https://oracle.github.io/node-oracledb/doc/api.html#oracledbconstantsaq

sinys15 commented 5 years ago

@cjbj

declare
  x BINARY_INTEGER := dbms_aq.FOREVER;
begin
  dbms_output.put_line('result is ' || to_char(x));
end;
/

result is -1

but in your links it is

define DPI_DEQ_WAIT_FOREVER ((uint32_t) -1)

and oracledb.AQ_DEQ_WAIT_FOREVER | 4294967295 | Wait forever if no message is available

it looks like just big value in seconds, it is not original -1 value, but of course it will work and looks like wait infinity (not really).

tgulacsi commented 5 years ago

That FOREVER is uint32(-1) = uint32(0) - 1 = 2^32 - 1 = 4294967295 because it cycles from 0 to the max number.

Either way, a big number is just perfect for waiting forever...

sinys15 commented 5 years ago

I see but is this parameter uint32 data type or why it can't be honest int32 = -1?

tgulacsi commented 5 years ago

7b411ab passes the simple TestQueue test in queue_test.go.

sinys15 commented 5 years ago

Looks good! But what about more complicated data types? From simple

create or replace type MY_ARRAY AS TABLE OF varchar2(1000)
/

create or replace type MY_STRUCT AS OBJECT (
    Field_NUM  number,
    Field_VCHR varchar2(100),
    Field_ARR  MY_ARRAY
)
/

   ...
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'MY_QUEUE_TABLE', queue_payload_type => 'MY_STRUCT');
  ...

To more complicated sys.aq$_jms_text_message data type with methods.

I don't know how to work with Object Types with goracle.

tgulacsi commented 5 years ago

They're there, just not tested thoroughly yet. See TestObject... tests!

On Wed, Aug 7, 2019 at 6:22 AM +0200, "sinys15" notifications@github.com wrote:

Looks good! But what about more complicated data types?

From simple create or replace type MY_ARRAY AS TABLE OF varchar2(1000) /

create or replace type MY_STRUCT AS OBJECT ( Field_NUM number, Field_VCHR varchar2(100), Field_ARR MY_ARRAY ) /

... DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'MY_QUEUE_TABLE', queue_payload_type => 'MY_STRUCT'); ...

To more complicated sys.aq$_jms_text_message data type with methods.

I don't know how to work with Object Types with goracle.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

tgulacsi commented 5 years ago

I've started a TestQueueObject test, but

  1. the nested array would need a nested table, which I don't know how ti specify to DBMS_AQADM.CREATE_QUEUE_TABLE
  2. Something is fishy with the object creation, as now it exists with "ORA-03135: connection lost contact". Maybe we have to attach the object to the connection somehow? I'll have to check that cx_Oracle how does this.
tgulacsi commented 5 years ago 
OCI error ORA-03135: connection lost contact
Process ID: 15178
Session ID: 190 Serial number: 13335 (dpiQueue_enqOne / enqueue message)

I'd love to get some help from @anthony-tuininga or @cjbj - I don't know what am I doing wrong.

Get the object type, create an object, fill its arguments, then Enqueue a message:

        oTyp, err := goracle.GetObjectType(conn, qTypName)
        if err != nil {
                t.Fatal(err)
        }
        defer oTyp.Close()
        obj, err := oTyp.NewObject()
        if err != nil {
                t.Fatal(err)
        }
        defer obj.Close()
        if err = obj.Set("F_DT", time.Now()); err != nil {
                t.Error(err)
        }
        if err = obj.Set("F_VC20", "árvíztűrő tükörfúrógép"); err != nil {
                t.Error(err)
        }
        if err = obj.Set("F_NUM", 3.14); err != nil {
                t.Error(err)
        }
        t.Log("obj:", obj)
        if err = q.Enqueue([]goracle.Message{goracle.Message{Object: obj}}); err != nil {
                t.Fatal("enqueue:", err)
        }

With DPI_LOG_LEVEL=28:

ODPI [12250] 2019-08-15 17:35:13.592: fn start dpiConn_getObjectType(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.635: fn end dpiConn_getObjectType(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.635: fn start dpiObjectType_getInfo(0x2491fa0)
ODPI [12250] 2019-08-15 17:35:13.635: fn end dpiObjectType_getInfo(0x2491fa0) -> 0
ODPI [12250] 2019-08-15 17:35:13.635: fn start dpiObjectType_getAttributes(0x2491fa0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectType_getAttributes(0x2491fa0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x2492100)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x2492100) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x2492160)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x2492160) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x24921c0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x24921c0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiConn_newQueue(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiConn_newQueue(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiQueue_getEnqOptions(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiQueue_getEnqOptions(0x2492220) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiEnqOptions_getTransformation(0x2484f50)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiEnqOptions_getTransformation(0x2484f50) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiEnqOptions_getVisibility(0x2484f50)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiEnqOptions_getVisibility(0x2484f50) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiQueue_getDeqOptions(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiQueue_getDeqOptions(0x2492220) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiDeqOptions_getTransformation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiDeqOptions_getTransformation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiDeqOptions_getCondition(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getCondition(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getConsumerName(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getConsumerName(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getCorrelation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getCorrelation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getMode(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getMode(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getMsgId(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getMsgId(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getNavigation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getNavigation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getVisibility(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getVisibility(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getWait(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getWait(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiConn_getObjectType(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiConn_getObjectType(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_getInfo(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_getInfo(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_getAttributes(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_getAttributes(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d830)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d830) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d8b0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d8b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d930)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d930) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_createObject(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_createObject(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.677: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.678: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.689: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.689: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiConn_newMsgProps(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiConn_newMsgProps(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiMsgProps_setPriority(0x249da00)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiMsgProps_setPriority(0x249da00) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiMsgProps_setPayloadObject(0x249da00)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiMsgProps_setPayloadObject(0x249da00) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiQueue_enqOne(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.724: OCI error ORA-03135: connection lost contact
Process ID: 15178
Session ID: 190 Serial number: 13335 (dpiQueue_enqOne / enqueue message)
tgulacsi commented 5 years ago

Ok, that was a strange error: the object's F_vc20 was filled with more than its length, and that materialized in such an error. Fixing that, the object is queued successfully!