cancel
Showing results for 
Search instead for 
Did you mean: 

Database Interface Question

Former Member
0 Kudos

Hello,

i'm using PB 12.5.2 Build 5006 Classic and ORACLE 11 connecting with ORA11 Driver.

I have another app inserting rows in an interface table of my app. I want to process these rows asap.

I could use a timer and do selects on this table and if i find rows process them but that solution has many drawbacks.

i wonder if there are other approaches, i have to admit i cant think of any.

Every suggetion is welcome!

Thanks in advance

Christian

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

A timer is probably the way to go but interferes with the running of your app. You could put the timer and table reading on another thread using SharedObjectRegister() etc. What happens then depends on what you have to do - process it in the background or do something to the interface to notify the user that action is required.

It's a shame the threading capabilities of PB are implemented in such an 'obscure' fashion because if you can get to grips with it, it works well. I used this technique years ago (PB8) to interface to a real time stock market feed.

HTH Paul

Answers (7)

Answers (7)

Former Member
0 Kudos

Re : Database Interface Question


Hello Christian,


The best solution is to use an Insert Trigger. There are two advantages. Once it gets operational, it is live and will work fine all the time. It is best to leave such works to the database. Secondly, You don't have to re-code the same when you change the software platform.


If  you are particular to use the code in PB then Timer is the best solution. Also it is advisable to put a flag in the rows that are processed (that is if you are not emptying the table after all rows are processed).


Cheers..jai

Former Member
0 Kudos

Thank you all for your suggestions!

I try to elaborate it a little more, which in english is not so easy for me

The app i'm working on is for accounting. The interface has 2 tables, one for the customer/vendor data, like acoount number, name, address and so on. The processing of these rows is quite simple. The values have to be checked against other tables, additional data has to be selected and in the end a row has to be inserted/updated in 2 tables.

The second table is for accounting transaction data, here the processing is much more complicated. There are a lot of dependencies between the values in a row and also in the other rows that belong to the transaction. Several inserts and updates in the end if all the values are correct.

As i said, i allready have that logic in a nvo in pb. I will try the using a timer but in a different thread that.

Former Member
0 Kudos

Hi Christian:

It sounds like a good model. Please let me know if you run into any issues and I can follow up.

Cheers...Bob

Former Member
0 Kudos

Hi Christian:


Can you elaborate on what processing you're doing on the interface table row?

Cheers...Bob

Former Member
0 Kudos

You can use Oracle AQ to have it send messages to your app.  Not exactly for the faint of heart, but very solid.

http://docs.oracle.com/cd/E11882_01/server.112/e11013/aq_intro.htm

Former Member
0 Kudos

Do you remove the row from the interface table once it's been processed?

As Manuel suggested, insert trigger is the best approach. But since you already have the code in your second app in place, then I think timer is the second best.

If you don't remove the row from the interface table, then a combination of trigger and timer might be a good idea. You create another table with the row inserted indicator. The trigger will increment the value by 1 everytime a new row is inserted, and your app decrements the value by 1 everytime a row has been processed.  0 mean no new row.

Former Member
0 Kudos

If these applications run on the same machine, you could post a windows message from the inserting app to the other one.  The same concept could be used across the network with a pipe (or similar).

Former Member
0 Kudos

Hi Christian,

There are many approaches, but the best option depends on what your needs are and how/where you need to process those rows.

The first idea that comes to my mind is an INSERT TRIGGER. This would allow you to process the rows as soon as they are inserted in the database. You'll have to put your processing logic in the database.

HTH,

Manuel Marte

Former Member
0 Kudos

i have allready a function in my app to process these rows and i would like to keep the processing in the client/app.

thanks for the suggestion though.

Former Member
0 Kudos

Since you already have the functionality created on your application, a timer might be a good option. There are drawbacks to this approach, but they might even out by the fact that you already have most the logic coded. If you take this option you'd want to create a "monitor" with a separate thread to be looking into the table for new rows and trigger the function when you find them.

Another option I can think of is to publish you function as a webservice. You can then use an INSERT TRIGGER and consume the webservice from your Oracle code whenever a new row is inserted.