This is the fourth article in a series that discusses creating a new database connection to the Sage X3 database using the standard Sage CRM features.
I discussed in the previous article "Extending the Sage X3 Integration (Part 3)" how to define the connection to the database.
This article needs to consider the connection to the database tables or view.
There are a few observations that need to be made.
- Sage X3 requires a SQL collation method of Latin1_General_BIN.
Sage CRM installation do not inherit the default database Server collation, it creates its own collation on the CRM database. These collations are:
- For English (EN) Installations = Latin1_General_CI_AS
- For Germany (DE) Installations = Latin1_General_CI_AS
- For Spanish (ES) Installations = Modern_Spanish_CI_AS
- For French (FR) Installations = French_CI_AS
- For Chinese (CS) Installations = Chinese_PRC_CI_AS
Queries to Sage X3 Database structures are case sensitive
e.g.
[code language="sql"]
select * from demo.bpcustomer;
[/code]
will return the error "Invalid object name 'demo.bpcustomer'."
whereas
[code language="sql"]
select * from DEMO.BPCUSTOMER;
[/code]
will return data correctly.
But
[code language="sql"]
select bpcname_0 from DEMO.BPCUSTOMER;
[/code]
will return the error "Invalid column name 'bpcname_0'"
whereas
[code language="sql"]
select BPCNAM_0 from DEMO.BPCUSTOMER;
[/code]
will return data correctly.
Queries to Database data are also case sensitive
e.g.
[code language="sql"]
select * from DEMO.BPCUSTOMER where BPCNAM_0 like 'COMP%'
[/code]
will not return data but
[code language="sql"]
select * from DEMO.BPCUSTOMER where BPCNAM_0 like 'Comp%'
[/code]
will return data.
This case-sensitivity in the Sage X3 database presents challenge for the Sage CRM which does not expect database structures to be case-sensitive.
The workaround is a simple trick. Instead of connecting directly to a database table we would connect to a view that has aliased the columns in lowercase. This is especially important for any columns that will be used to restrict the rows returned e.g. those columns used in searches and where data needs to be linked by context.
For example it might be that you want to connect from Sage CRM to one of the table in the Sage X3 Inventory module. This could be the STOSER table that describes the Serial Numbers or it could be to the BPSUPPLIER table in the Common Data module of Sage X3.
For example we could create a view like this
[code language="sql"]
CREATE VIEW [dbo].[CRMSuppliers]
AS
SELECT BPSNUM_0 AS bpsnum_0, BPSNAM_0 AS bpsnam_0, BPSSHO_0 AS bpssho_0, BPSREM_0 AS bpsrem_0
FROM DEMO.BPSUPPLIER
[/code]
Note.
Sage X3 may have multiple Schemas or folders. This example has simplified the view for Sage CRM.
You can see above that all the field names included in the view have been aliased to lowercase. Once you have done this you can connect into the database in CRM and connect to the table with the view.
Once you have created the connection to the database you can create the connection to the view.
Click on the 'New Table Connection'
Once you have finished creating a connection to views in the external database these will appear in the list of Tables and Databases.
The definitions of the tables can be found in the Metadata table custom_tables and can be viewed using Microsoft SQL Server Management Studio
[code language="sql"]
select * from Custom_Tables where Bord_Name = 'CRMSuppliers'
[/code]
Note:
Using a connection to a view provides us with a number of advantages. The view can amalgamate data from multiple tables and perform manipulation of the data so that it would be more suitable for use in the Sage CRM interface. Views are ideal structures for Sage CRM to use to access Sage X3 data as you will only use these for viewing data. No Insert, Update or Delete action should be carried out against the Sage X3 tables.
The next article will further discuss views as the basis of the connection to Sage X3
Creating a new database connection to Sage X3