Linking to 3rd Party Databases

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Linking to 3rd Party Databases

  • Comments 3
  • Likes

Within the Administration screens is the location that allows the System Administrator to create an additional database connection to a 3rd party database. 

Administration -> Advanced Customization -> Tables and Databases


Sage CRM allows us to connect to a different database on a different server.  For example, we may have CRM installed on MS SQL Server but may need to connect to an Oracle database as an example.  This example shows a connection to an Oracle database being made from an install of Sage CRM running on MS SQL Server.

The valid database connections that can be made are:

  • DB2
  • Informix
  • MSSQL
  • ODBC
  • Oracle
  • Sybase

The database connection is defined in the meta data table 'custom_databases'.  The connection is opened when Sage CRM starts.  For example, if you have defined a connection and restart IIS.  The connection will be opened when the first person logs on to Sage CRM.

Once the database has been connected and a link has been created to a table in that database then Sage CRM will have the ability to interact fully with that
table's data.  The meta data description of the table connection is held in 'custom_tables'.  Full CRUD (Create, Read, Update, and Delete) is possible using the standard API objects.  This is true for both the Sage CRM .NET API and the COM API.

The fields of the newly linked table can then customized with the changes in Field Type being held in the meta data table 'custom_edits' and the changes to the field captions held in the table 'custom_captions'.

Features Available

Once the meta data and connection is in place it can be used with List blocks to retrieve records from the Oracle databases.

The record object and QueryObject can also be used to carry our Insert, Delete, View and Edit (CRUD) tasks. Specifically CRM.FindRecord(), record.DeleteRecord(), record.SaveChanges() all work.

You need to be cautious of any SQL that you have written that includes database specific functions getDate() or sysdate().  If you use a QueryObject then that statement will be passed exactly as you have written it and may cause an error.

Oracle Specific Tips

The hardest part of creating a connection is making sure that you have the necessary Oracle Database Client tools correctly installed on your Sage CRM application server (this is the server with IIS on it).  The Oracle Database Client will allow you to connect to the Oracle database server. Once you have installed the database client and tested the connection to the Oracle database server from the Sage CRM application server you should then be able to connect Sage CRM directly.

It should not matter whether the Oracle database is on Linux or on a Windows server as the connection details are managed by the Oracle Database Client.  For example Oracle can be installed on variants such as Red Hat Enterprise Linux or Novell SUSE Enterprise Linux.

Note:

You will have to use the username that is associated with the schema that the table is created in.  For instance if your table is in the SA schema you must use the SA login to connect to this table.

Comments
  • Hello Jeff.

    In the example, ORC62B3_SPARE-DT4 ¿is the Database Name? or ¿is the Oracle Database Instance Name?

    regards

  • Hi Jeff, have you ever encountered a problem with record locks in a scenario like this? Client wants to have CRUD access to an external table and I am concerned that the high volume may cause issues - are the records locked by CRM in the normal way? If I expose the same database and table and link it to a second CRM instance, so that both have access to the same table, how would the record locks be handled, in your opinion?

  • Oh, and to make it more interesting, the one instance is on 7.1 SP1 and the other is on 7.3.