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.
