ToracleEvent problems

[joet]

I'm using a TOracleEvent to pass an alert on an insert trigger in ORacle 9i 9.2. It is a 2 node RAC cluster. I'm geeting some sporadic results. Sometimes I receive the alert sometimes I don't. Does anyone have any thoughts on why this might be?
I'm using direct Oracle access 4.0.6 with C++ builder 6

Thanks,

Joe

 

[joet]

the two transactions I discussed in my previous email are about 6 seconds apart, one successful the other not, do I need to do anything special in my service to queue these transactions if they are comming that quick? Could it be busy handling one transaction and miss another?

 

[Marco Kalter]

You can indeed miss alerts if it takes too long to process them. If you need guaranteed delivery, use AQ instead.

 

[joet]

Is that a parameter I can I do that with DOA?

 

[Marco Kalter]

AQ = Advanced Queuing. It is implemented in Oracle through the dbms_aq and dbms_aqadm packages. The Oracle "PL/SQL Packages and Types Reference" describes these packages (and their types) in detail.

Direct Oracle Access 4.0 supports AQ through the TOracleQueue component.

 

[joet]

When trying to compile the example showing AQ support I get the following error. Any help? A TOracleObejct require the Direct ORacle Access Obecjt version.

 

[joet]

I have the standard version, I just purchased the object version, is there an upgrade available? How can I upgrade my standard edition to object edition? Do I have to uninstall and resinstall or is there an easier method?

Thanks,

Joe

 

[Marco Kalter]

You can simply install the object version. This will replace the standard version.

 

[joet]

thanks Marco,

But you didnt really answer all my questions. Is there an upgrade path from the non object version to the object version? Or am I required to purchase a new version?

Thanks,

Joe

 

[Marco Kalter]

Sorry, I missed that. There is indeed a standard-to-object upgrade available. It costs USD 50 per license.

 

[joet]

I was in a hurry and bought the object version seperately. can you halpe me get a partial refund please?

Thanks,

Joe

 

[Marco Kalter]

Sure, I will send you an e-mail shortly.

 

[joet]

I have defined the following object. When I dequeue it and try to get the data via the TOraclequeue->Payload->GetAttr(); It hangs my service. IS there some step I'm missing? I get the MessageID ok but any data accessed via getattr causes issues. I noticed a queuetype but I can't seem to set that. does it default to qtObject?

create type feature_package_type as object
(
app_feature_id number(10),
feature_id number(10),
app_id varchar2(7),
service_no_id varchar2(7),
service_no varchar2(20)
);

fprintf(ptr, "Calling Dequeue Function%s \n", DateTimeToStr(Now()));
OracleQueue1->Dequeue();
fprintf(ptr, "Message received: %s \n" , OracleQueue1->MessageProperties->MsgId);
fprintf(ptr, "App feature_id = %s \n", OracleQueue1->Payload->GetAttr("app_feature_id"));

 

[Marco Kalter]

Did you set TOracleQueue.Threaded to True? In that case the Dequeue call will merely spawn a thread, and the OnThreadDequeued event handler will be called when a message has been dequeued.

The QueueType property reflects the type of the queue. You cannot set it, since it depends on the QueueName property.

 

[joet]

Marco,

Yes I have tried setting the Threaded property to true. My onThreadDequeued evnt handler looks like this.

fprintf(ptr, "Message received: %s \n", Sender->MessageProperties->MsgId);
fprintf(ptr, "%s \n", Sender->Payload->GetAttr("app_feature_id"));

this causes the service to hang. If I remove the line fprintf(ptr, "%s \n", Sender->Payload->GetAttr("app_feature_id")); it works fine. I've included the code I used to define my object, create and start my queue and to queue up a sample message. What am I doing wrong?

create type feature_package_type as object
(
app_feature_id number(10),
feature_id number(10),
app_id varchar2(7),
service_no_id varchar2(7),
service_no varchar2(20)
);

begin
dbms_aqadm.create_queue_table(queue_table => 'fea_package_queue_table',
queue_payload_type => 'feature_package_type');
dbms_aqadm.create_queue(queue_name => 'feature_package_queue',
queue_table => 'fea_package_queue_table');
dbms_aqadm.start_queue(queue_name => 'feature_package_queue');
commit;
end;

DECLARE
queueopts DBMS_AQ.ENQUEUE_OPTIONS_T;
msgprops DBMS_AQ.MESSAGE_PROPERTIES_T;
msgid RAW(16);
my_msg feature_package_type;
BEGIN
my_msg := feature_package_type(1234567890,1234567,'123abc','abc123','2486564210');

DBMS_AQ.ENQUEUE ('feature_package_queue',
queueopts,
msgprops,
my_msg,
msgid);
END;
Commit;

 

[Marco Kalter]

You will have to debug this to find out if there is an exception. The GetAttr function will not hang.

 

[joet]

Marco,

This code works fine in an application. It only causes issues when I try to include it in a windows service. Can you see if you can reproduce this issue on your end?

 

[joet]

did you ever have a chance to look at this? this works fine in an application but when used as a windows service it hangs! also any progress on the situation where you lose a database connection and want to reconnect without recreating the queue?

Thanks,

Joe

 

[Marco Kalter]

I have not been able to reproduce this. Like I said, the GetAttr function will not hang.