Sage CRM 7.2 will introduce new Mobile Apps for Windows 8 and the iPhone. Please see the article "What's Coming in Sage CRM 7.2?". As part of preparing for this new way of providing offline content, in an increasingly mobile world, the older style SOLO disconnected client has been dropped.
In Sage CRM v7.1 and earlier the SOLO clients used to use a database called SQL Lite for the offline storage of data. Because synchronization of data on the client and the server with SOLO needed to work with both Oracle and MS SQL Server databases there had to be a common way of handling the primary key values and the allocation of data ranges so that records created in the SOLO client wouldn't conflict with records created on the server.
You may have investigated tables like rep_ranges, rep_deletes and rep_conflicts when working with Sage CRM v7.1. These tables are now redundant along with the tables soloprofileoptions and clientrangesrequired and all these tables have been dropped in Sage CRM 7.2. I will be covering more about the data model changes introduced in Sage CRM v7.2 in a later article.
When Sage CRM v7.1 was installed on MS SQL Server, new IDs for records were generated using a stored procedure called eware_get_identity_id and system tables in the CRM database would store the next id for each table and manage the range information.
But new installs of Sage CRM 7.2 on SQL Server will now use SQL Identities. Because of this we should see significant performance improvements. It is one of the very big advantages of the new version.
You can investigate the use of the identity columns using this example SQL code;
INFORMATION_SCHEMA.COLUMNS AS c JOIN
INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_NAME = c.TABLE_NAME
,c.COLUMN_NAME,'IsIdentity') = 1 AND
t.TABLE_TYPE = 'Base Table'
For systems that are upgraded from Sage CRM v7.1 to Sage CRM 7.2 support for SOLO will be lost and the stored procedure,eware_get_identity_id, used for generating the primary key will be changed. This means that existing upgraded systems will still continue to use the earlier way of maintain primary key integrity.
- There are NO Changes for Sage CRM 7.2 installed on Oracle. There is no equivalent stored procedure in Oracle and Sequences will continue to be used to control the identities.
- SOLO is completely gone in Sage CRM 7.2 and because replication no longer has to be considered the Rep_ranges tables will have gone for Oracle too.
The API objects in both the ASP and .NET environments and in the web services have been changed internally to ensure that inserts will continue to work.
But problems may arise with add-on code that does a direct insert using eware_get_identity_id with the Query object.
This is likely to affect ‘hidden’ or automatic rules within Sage CRM such as
- Validation Rules
- Table Level Scripts
- and some ASP and .NET Application Extensions
For example a component might add some code into a new install of Sage CRM v7.1 that looks like this
Sage CRM v7.1
var strSQL = "DECLARE @ret int";
strSQL += "EXEC @ret=eware_get_identity_id 'cases'";
strSQL += "INSERT cases";
strSQL += "(case_caseid, Case_Description, Case_PrimaryCompanyId)";
strSQL += "VALUES (@ret, 'abc',10)";
var myQuery = CRM.CreateQueryObj(strSQL,"");
BUT in a new install of Sage CRM 7.2 the code should not refer to the eware_get_identity_id stored procedure as primary key values are now managed by identities. The code would need to be changed to look like this
Sage CRM 7.2
var strSQL = "INSERT INTO Cases";
strSQL += "(Case_Description, Case_PrimaryCompanyId)";
strSQL += "VALUES ('abc',10)")";
var myQuery = CRM.CreateQueryObj(strSQL,"");
Note: In .NET the code reference to look for is the use of the Sage.CRM.Data.QuerySelect class.
As I mentioned earlier the ASP &.NET Record object has not been effected.
Upgraded vs New Systems
Upgraded System will still use old eware_get_identity_id mechanism.
Code that has to run on upgraded and new installations may need to work out how to do inserts. If we are creating a component that could be run on an upgraded system on on a new system we should have a way to check in code which primary key system is in use? We can take advantage of a system parameter called "StillGotIdentityCols".
select parm_name, parm_value from custom_sysparams where parm_name = 'StillGotIdentityCols';
In a vanilla install of Sage CRM 7.2 this will be 'Y' in an install upgraded from Sage CRM v7.1 this will be null.
I hope in the future that there may be a way to move an existing system to use SQL identities instead of the eware procedure. As you can see the behaviour is partially governed by a system parameter and I know that we have implementations on SQL Server in which the use of the stored procedure has been replaced by Identities. BUT this is not a trivial matter and I must wait until guidance is officially issued on this.