Topics

Transaction Processing in blended database environment

Tom Worthen
 

Our application uses Pervasive/Action and MS-SQL databases. Some programs only involve one of those databases but we have more and more programs that are using both. It is my understanding that with Transaction Processing only the main source is used to determine the transaction.

 

Does anyone have experience with transaction processing in a blended database environment?

 

If the main table is MS-SQL and there are Pervasive table updates, or the reverse, and the transaction has to roll back, does it roll back both databases?

 

Is there a best practice for this type of situation?

 

Looking for feedback and information.

 

Thanks,

Tom

Steven Blank
 

Tom,


In Options > Settings > Environment, on the Multi User tab, if Line 2, ISAM Transactions = Yes, then the transaction will be rolled back in Pervasive as well; else, not.


Steve Blank


On 5/15/2020 6:53 AM, Tom Worthen wrote:

Our application uses Pervasive/Action and MS-SQL databases. Some programs only involve one of those databases but we have more and more programs that are using both. It is my understanding that with Transaction Processing only the main source is used to determine the transaction.

 

Does anyone have experience with transaction processing in a blended database environment?

 

If the main table is MS-SQL and there are Pervasive table updates, or the reverse, and the transaction has to roll back, does it roll back both databases?

 

Is there a best practice for this type of situation?

 

Looking for feedback and information.

 

Thanks,

Tom

Tom Worthen
 

Steve,

 

Are you indicating if that flag is set that it will roll back both databases? Does the same hold true if the main table is Pervasive with links/updates to an MS-SQL database, that they would both be rolled back?

 

I presume you have had experience with this so shouldn’t question your knowledge but just wanting to make sure. J

 

Tom

 

 

From: main@magicu-l.groups.io [mailto:main@magicu-l.groups.io] On Behalf Of Steven Blank
Sent: Friday, May 15, 2020 11:51 AM
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] Transaction Processing in blended database environment

 

Tom,

 

In Options > Settings > Environment, on the Multi User tab, if Line 2, ISAM Transactions = Yes, then the transaction will be rolled back in Pervasive as well; else, not.

 

Steve Blank

 

On 5/15/2020 6:53 AM, Tom Worthen wrote:

Our application uses Pervasive/Action and MS-SQL databases. Some programs only involve one of those databases but we have more and more programs that are using both. It is my understanding that with Transaction Processing only the main source is used to determine the transaction.

 

Does anyone have experience with transaction processing in a blended database environment?

 

If the main table is MS-SQL and there are Pervasive table updates, or the reverse, and the transaction has to roll back, does it roll back both databases?

 

Is there a best practice for this type of situation?

 

Looking for feedback and information.

 

Thanks,

Tom

Steven Blank
 

Tom, 
My specific experience in this regard was with Oracle on an HP9000 and Btrieve on NetWare4, and Magic v7.11h on a PC. And yes, both databases would either be committed or rolled-back together. It was pretty amazing, if I do say so myself. 
In a project for 3-Com’s US Robotics division in SLC, I wrote a transaction processor that read finished goods transactions from Oracle, blew through the related BOMs, relieved inventory of component parts and received the finished goods into inventory, all in Btrieve. When complete, the process closed the loop by marking the assembly line transaction created in Oracle as having been received. 
It had to be scalable and bulletproof. I recall testing at one point by simply pulling the plug on the PC to see if it could rollback and recover properly on restart. It did. 
I don’t recall any caveats or gotchas, just a shit-ton of testing. 
Steve Blank


On May 18, 2020, at 9:17 AM, Tom Worthen <tworthen@...> wrote:



Steve,

 

Are you indicating if that flag is set that it will roll back both databases? Does the same hold true if the main table is Pervasive with links/updates to an MS-SQL database, that they would both be rolled back?

 

I presume you have had experience with this so shouldn’t question your knowledge but just wanting to make sure. J

 

Tom

 

 

From: main@magicu-l.groups.io [mailto:main@magicu-l.groups.io] On Behalf Of Steven Blank
Sent: Friday, May 15, 2020 11:51 AM
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] Transaction Processing in blended database environment

 

Tom,

 

In Options > Settings > Environment, on the Multi User tab, if Line 2, ISAM Transactions = Yes, then the transaction will be rolled back in Pervasive as well; else, not.

 

Steve Blank

 

On 5/15/2020 6:53 AM, Tom Worthen wrote:

Our application uses Pervasive/Action and MS-SQL databases. Some programs only involve one of those databases but we have more and more programs that are using both. It is my understanding that with Transaction Processing only the main source is used to determine the transaction.

 

Does anyone have experience with transaction processing in a blended database environment?

 

If the main table is MS-SQL and there are Pervasive table updates, or the reverse, and the transaction has to roll back, does it roll back both databases?

 

Is there a best practice for this type of situation?

 

Looking for feedback and information.

 

Thanks,

Tom