Document #526, Integrating External SQL Data With GoldMine®
This document will familiarize you with the options and methods available to coordinate changes in SQL-based GoldMine databases with other SQL-based resources. The ability of GoldMine 4.0’s Enterprise Edition to generate and access SQL data enables the integration of GoldMine’s data with the tables of other applications. Properly configured, your SQL server can transfer data between GoldMine’s SQL tables and other SQL databases with little or no administrative effort.
Note: This information is provided as is and cannot be elaborated on or supported by GoldMine Technical Support.
Further information is available from the resources listed on page five of this document. The operations described herein may adversely affect your database and/or database server. Only database administrators and those familiar with advanced SQL server administration should attempt to implement these features. As with any operation that could affect your entire database, it is suggested that you backup your data before altering the configuration of your database server.
Scenarios
This document attempts to give a brief synopsis of some of the different ways in which a DBA might take advantage of the advanced features of MSSQL Server. Several different methods are available to coordinate separate datasets and each is best suited for a different type of scenario. The examples in this document are taken from an MS-SQL 6.5 server.
The concepts involved, however, can be applied to any database server, although you may need to consult your database server’s documentation for necessary vendor-specific modifications.
· A corporation uses GoldMine Enterprise Edition in their Sales department. Management would like to have an independent table that tracks the dollar amount of sales completed by each representative for comparison purposes. In this case, a process must be automated to copy and/or analyze the pertinent GoldMine data and place it in the SQL table used by the team managers. This can be accomplished by creating SQL objects that update/insert records when certain conditions are satisfied (i.e., whenever a sale is completed.) These objects are called Triggers.
· Corporate headquarters wishes to review the data entered into GoldMine by the sales office, but corporate headquarters does not currently use GoldMine. The two offices are geographically separated to the point that real-time access of the GoldMine database is too slow to be feasible. Corporate headquarters would like their SQL server to automatically download updated or new records from the GoldMine database from time to time to create a similar database there. The process best suited to this scenario is Replication.
· A custom application is being developed to manipulate data in the GoldMine database. The application will create profiles in GoldMine to record shipping data and immediately update another application’s database on a separate SQL server with this information. The developer needs a method of updating both of these servers synchronously with a minimal chance of data loss due to network or system failures. A reliable way to accomplish this is by using
Distributed Transactions to alter both resources simultaneously.
Permissions
Users created on an SQL server must be assigned permissions to perform specific types of actions on the SQL data.
Users created in SQL for use by GoldMine are normally assigned permissions to perform SELECT, INSERT, UPDATE, and DELETE actions. Enabling another application to read GoldMine data simply requires that the application login to the database server as a user with only SELECT permissions on the GoldMine tables.
Note: Any modification to GoldMine data must be recorded in the Tlogs for synchronization to properly function. If another application modifies GoldMine’s data, it may not properly create Tlog entries unless it was explicitly designed to do so. It is not recommended that other applications be given INSERT, UPDATE, and DELETE rights to GoldMine tables unless absolutely necessary.
SQL Query Tools
Views and Triggers must be created with an SQL statement. Sending an SQL statement to your database server can be accomplished with the query software supplied with your database server software.
To run the query software supplied with MSSQL 6.5, select Start>>Programs>>Microsoft SQL Server 6.5>>ISQL\w. To run the query software supplied with MSSQL 7.0, select Start>>Programs>>Microsoft SQL Server 7.0>>Query Analyzer. If this option is not available on your Start Menu, the component may not have been installed. Refer to your SQL Server’s documentation for details on installing a query tool.
Views
In addition to regulating permissions for non-GoldMine applications, you may also wish to regulate the data that can be accessed for security reasons. This can be accomplished with views. A view is a subset of a table that can be accessed as if it were an actual table. A user’s permissions can be set to allow them to read the section of the table defined by the view, but deny them any kind of access to the underlying table. In this manner, access to GoldMine data can be restricted to specific rows and columns of each table. The following statement will create a view:
CREATE VIEW my_view
AS
SELECT company, contact, state
FROM contact1
WHERE state = ‘CA’ or state = ‘NV’ or state = ‘AZ’
Selecting all records from this view (SELECT * FROM my_view) would display the company, contact, and state for every record in California, Nevada, and Arizona. In this manner, record and field level security can be implemented by providing an application permissions to my_view but denying it access to contact1.
Triggers
Triggers are commands that only execute when a specified table is modified in a certain way. They can be used to enforce business rules, maintain a log of edits to a database or automate nearly any operation that can be performed on your database server. This document attempts to illustrate the basic concept behind triggers, but for more information you may wish to review the documentation provided with your database server.
The statement used to create the trigger defines the conditions that must be met and the actions that will be performed.
The individual parts of the statement are broken down below:
Triggers are based on three main properties; the table they are attached to, the action(s) they respond to (INSERT, UPDATE, and/or DELETE), and the action(s) they perform (sql_statements). In the following example a trigger will be created to total the dollar amount of each user’s completed sales and update another SQL table to be accessed by the company’s management:
CREATE TRIGGER commission ON ContHist --Define the trigger with a name and table
FOR INSERT
AS
UPDATE saleslist
SET rep_sales = convert(int, rep_sales) + convert(int, duration) --Add the newly inserted sale to the user’s total
FROM inserted, saleslist
WHERE userid = rep --Join the tables
AND EXISTS (SELECT * from inserted where rectype like “S%”) --Limit the trigger to Sale activities only
The two tables being acted upon by the trigger are saleslist and inserted. Saleslist is the table to be accessed by management, and contains two columns: rep and rep_sales. Inserted is a table that is only available from within a trigger. It contains only the new rows/records created by the INSERT statement that activated the trigger. In this example, the inserted table will contain the sale record inserted into ContHist by GoldMine. The SET statement adds the duration field from ContHist (which contains the sale’s dollar amount) to the pre-existing total in the saleslist table.
The WHERE statement will match up the userid in ContHist with the sales rep’s name in saleslist so the correct record is updated. The final line of the sample trigger (a continuation of the WHERE statement) verifies that the inserted record is a Sale. The trigger will not run if the inserted record is not a Sale.
In the scenario presented, the saleslist table could be reset at the beginning of each week. During the week, the table could be accessed through an application or a select statement to produce a running weekly sales total for each sales representative. The trigger could also be configured to update another table containing monthly totals or calculate commissions for the current pay period on the fly.
Note: Modification of GoldMine data with triggers without also updating or adding the related Tlogs is not recommended, as it will seriously hinder synchronization. Tlogs are discussed in further detail later in this document under the heading “Custom Applications”.
Replication
Replication is a method of duplicating data from one database server to another. Data can be marked for replication, and when requested, transmitted and added to another server’s database. You may want to think of Replication as your database server’s version of Synchronization. Both will track new or changed records, collect the data needed by a specific site, and then transfer the records through some sort of connection. However, Replication is not a substitute for GoldMine’s Synchronization. It will not create Tlogs for the records like Synchronization will, and therefore any records imported by Replication will not be recognized by Synchronization. In the second scenario detailed above, Replication could be used to copy GoldMine data to an office that does not use GoldMine. Once replicated, it could be accessed by another application or through SQL statements, allowing the office to monitor and utilize the contact data generated at the site-using GoldMine.
Some database servers allow Replication to occur bi-directionally. Bi-directional replication allows all sites involved to update the replicated data and exchange it with others. It is not recommended that you use bi-directional Replication with GoldMine since this would create records in GoldMine without Tlogs. Data should only be replicated in one direction: out of GoldMine. If more than one site must be able to update GoldMine data, GoldMine must be implemented at all sites and GoldMine’s built-in Synchronization must be used.
To set up Replication, you must refer to your database server’s documentation. Each database server has its own brand of Replication, and the steps to configure it may be very in-depth, depending on your situation and desired result. In general, the Database Administrator must provide the SQL server with the following information: what data is to be replicated, the method used to transmit the data, and the conditions that will start the replication process. Both sites/offices involved must be properly configured for replication to occur.
Custom Applications
In some cases, a situation may require the flexibility and power that only a custom application can provide. Currently the newest and most popular solution for accessing or manipulating an SQL database is ActiveX Data Objects (ADO). ADO is installed with the Microsoft Data Access Components (MDAC) and uses Microsoft’s OLE DB to communicate with any type of data source. ADO is implemented as an ActiveX DLL, and can be used in any programming language that supports COM and Automation DLLs.
Using ADO in a custom application is as simple as implementing its objects and methods in the program’s code. Running stored procedures, sending SQL queries, and modifying data can all be easily accomplished. However, when altering GoldMine data you must be conscious of how your changes will affect any synchronization of the modified data. As discussed earlier in this document, directly altering GoldMine records will not create or update records in the Tlogs, which are necessary to synchronize the new or changed data. GoldMine’s own DLL (GM4S32.DLL) can be used to guarantee proper record creation and Tlog generation. Although it requires more effort, someone who is familiar with the structure of the GoldMine tables should be able to emulate how GoldMine handles these files, and create Tlog records with their application. Resources are available to help developers and database administrators utilize the DLL, these include:
¨ GoldMine Reference Manual: (ftp://ftp.goldminesw.com/public/manuals/gm4-ref.pdf)
Chapter 11: “Database Structures”
Appendix II: “Using GM4S32 for Database Access and Sync Log Updates”
¨ The GoldMine Newsgroups: For more information, select Help>>Newsgroups in GoldMine and/or see Factsback Document #529 (ftp://ftp.goldminesw.com/public/faxback/tech/529.zip)
¨ The BR4 Utility: (http://www.redstonesoftbase.com)
1: It may be necessary to download the latest version of the GoldMine Reference Manual to obtain Appendix II.
2: Programming and development issues cannot be supported over the phone by GoldMine Technical Support.
3: The BR4 utility is a freeware utility supplied by the Redstone Softbase Company.
Distributed Transactions
In the third scenario described in the Overview it is necessary to update multiple database servers at once with a custom application. The most reliable way to accomplish this is with a distributed transaction -- a transaction that affects multiple database servers. If a distributed transaction fails on one or more servers, it will be aborted on the others to maintain consistency between the servers. A custom application written using distributed transactions will accept commands from a user and then pass it off to the distributed transaction service, which in turn, passes the command to the specified servers. There are two main advantages to using distributed transactions:
1. Once data has been passed to the distributed transaction service, application control is returned to the user, which allows the application to be more responsive because it doesn’t need to wait until the transaction is complete on all of the systems involved before proceeding.
2. Using distributed transactions guarantees an all-or-nothing transaction. If the transaction fails on one server, it will be undone on the others. Data will only be altered if it can be altered on all the resources involved. For a business that relies on the consistency of its data this is a very important feature. Also, a distributed transaction service stores the commands passed to it in a buffer, so even in the case of a momentary network or power failure the service will continue where it left off when connection is re-established.
Different distributed transaction services are available, depending on the database server being used. In Oracle 8, Advanced Replication will allow an application to perform distributed transactions. Advanced Replication must be enabled and setup on all the servers involved. Once it is configured, multiple servers can be updated in the same transaction, and
Advanced Replication will distribute these commands among the servers. Similarly, Microsoft Distributed Transaction Coordinator (MS DTC) will perform the same function for MS SQL servers. MS DTC must be installed on every client machine that will run the application and every server machine that will be affected by it. The most obvious use of MS
DTC is to coordinate the data entered on two or more SQL servers, but other resources are also capable of responding to DTC commands. Consult your software’s documentation for details. Also, the Microsoft Developers Network (html://msdn.microsoft.com) is an excellent source of information on implementing MS DTC in your application.
Copyright (c) 2002 FrontRange Solutions Inc.
All rights reserved. You may use this document for personal and informational (non-commercial) purposes, provided that the copyright notice and all other notices and permissions appear in all copies, the document is not copied or posted on any network computer or broadcast in any media and modifications are not made to the document. Use for any other purpose is expressly prohibited by law, and may result in civil or criminal penalties.
The information contained in this document is provided “as is” without warranty of any kind. To the maximum extent permitted by applicable law, FrontRange Solutions disclaims all warranties, either express or implied, including warranties for quality, accuracy, merchantability, fitness for a particular purpose, title and non-infringement; and in no event shall FrontRange Solutions or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of profits or data or special damages, even if FrontRange Solutions or its suppliers have been advised of the possibility of such damages.