Community
Showing results for 
Search instead for 
Do you mean 
Reply

Logically Connecting SQL DB to ACT SQL DB

Copper Contributor
Posts: 51
Country: United States

Logically Connecting SQL DB to ACT SQL DB

My client has elected to use ACT for managing all contact information for their company.  I am developing an extensive transaction processing and knowledge management application that requires many tables (up to about a hundred) at this point and many, many of these tables join to tables that will be found in ACT, especially Contact and Company. 

 

SQL server will allow two DB to be treated as one logical DB.  Has anyone had experience doing this when one DB is the ACT SQL DB?  Also, the agreement that ACT users have to sign in exchange for the SA password has strong prohibitions against modifying anything within the ACT DB.  I worry that implementing this logical merger may require setting permissions and properties inside the ACT DB. 

 

Alternatively, has anyone had experience implementing this architecture with a merge/replication approach?  Does ACT support publishing or subscribing to updates to either DB?

 

Any experience will be greatly appreciated.

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Logically Connecting SQL DB to ACT SQL DB

You can READ the ACT! data via ODBC/OLEDB... but you MUST use the ACT! SDK to write to the ACT! data.

 

See: http://blog.glcomputing.com.au/2009/07/connecting-to-act-by-sage-data.html

 

If you need help, we create products that sync other databases with ACT!

Copper Contributor
Posts: 51
Country: United States

Re: Logically Connecting SQL DB to ACT SQL DB

Thanks, Mike,

 

This isn't quite what I was looking for.  I have seen through this forum some of the rich capabilities for connecting to the ACT! DB using ODBC.  The syntax looks straightforward.  I am confident that I can accomplish logical cross-DB inserts and queries associated with a business task.  The hole in this logic happens if someone deletes an ACT Contact record which is the parent record to records in one (or more) tables in the 'other' DB.  In normal DB design this deletion would not be allowed to take place without first deleting the child records but if the databases are both physically and logically separate then this deletion from the ACT table can take place leaving orphaned records in the related tables.

 

In the original post I described two approaches that I would use when different SQL Server databases have a logical connection.  I am trying to see if I can use either of these with ACT as one of the DBs.

 

The alternative will be doing my own merge replication routines where I'll probably maintian shadow tables in the non--ACT DB to mirror selected columns (maybe just primary keys) of the ACT tables.  I would then have to periodically validate these records against the ACT tables to detect and respond to deletions.

 

Thanks again.  All ideas are welcome.

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Logically Connecting SQL DB to ACT SQL DB

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Logically Connecting SQL DB to ACT SQL DB

When you talk about SQL database merger- are you talking about executing distributed queries against multiple SQL databases i.e. - Select * from ACT7.ACTshema.Contacts join otherDB.schema.table etc...?

 

In your specific example you aren't writing to the ACT! DB necessarily, but you want to enforce cascading CUD actions across the multiple DB's.  Normally using distributed queries you'd have to do this using triggers on the "parent" DB table which would prevent the deletion (or reconstitute the record) if the cascaded delete across the other tables failed.  

 

I'm not sure as to what licensing/agreement is allowed with the SA password tool, though I would say that my experience is that adding permissions or database objects (like UDFs or Triggers) in SQL tend to destabilize ACT! so I wouldn't expect that to be allowed via the licensing.  There are technical functions that I believe specifically look at schema and DB objects and dispose of nonconforming objects.

 

With the SA tool you should be able to execute distributed Select statements but updating or inserting data (or deleting data for that matter)  via this interface is not allowed as it bypasses the business object layer and causes data consistency problems in the ACT! application.

 

That being said there are approaches to something like what you've described beyond porting data into ACT! - there's a control on devnet where you can query an external DB and display items from that result set in the control (I believe the control implements a data grid, but you could use any control there for binding).  You could implement a Plugin which looks for contact.delete events and interrupts the event in ACT! based on whatever criteria you set (like existing related data in external system).  

 

As a side note: 

Data level relational mapping across systems as an integration practice usually tightly couples systems and since data schemas often change those integrations can be difficult to maintain.  I more loosely coupled approach at a higher level of the stack (Business object to BO, or UI to UI, or even UI to Data) which are generally abstracted and survive upgrades is usually a better approach.

 

Hope this helps. 

 

 

 

 

Copper Contributor
Posts: 51
Country: United States

Re: Logically Connecting SQL DB to ACT SQL DB

Many thanks to both responders.

 

Mike L.: My apologies.  I did not catch in your first response that you mentioned using the SDK instead of ODBC.  I was aware of the SDK capablities from samples posted in this forum and was mixing up my alphabet soup.  I don't anticipate great difficulties being able to execute code logic such as the following:

 

Receive transaction request involving contact

Query ACT DB to see if contact exists

  If found

    Retrieve Contact.ID

  If not found

    Add new contact

    Retrieve new Contact.ID

Insert related records in external DB using Contact.ID as foreign key 

 

Allen,

 

Thanks also for your insightful comments.  You have pegged the issue perfectly.  I was hoping to take advantage of SQL's distributed DB management tools to use messaging to inform each part of the DB of actions taken in the other.  But I agree with you on a couple of important points.  First, our study of the ACT! agreement that accompanies the SA password makes it clear that messing with the ACT! DB directly is dangerous for multiple reasons.  Second, your issue regarding changing data schemas is also an important consideration.  I agree that dealing with this issue in the business objects is the way to go.

 

I am considering one pretty interesting alternative which is to maintain a minimalist copy of key ACT! table columns in the external transaction processing DB coupled with scheduled routines that would detect differences between the copies and the ACT! tables.  The only real concern I have is actually deleting a key record in an ACT! table with logically related records in the external DB.  This is probably really unlikely to happen and my client is going to have to let me know how they want to handle this if it does.  This will be a little complex as I anticipate about two dozen logical relationships to the Contacts table, for example, but dealing with each case isn't hard, it just may have some business rule implications they will have to think through. 

 

Thanks again to both of you.

 

Larry