Application Note for QuNect ODBC for Quick Base

Synching a SQL Server table in Quick Base

The following stored procedure will synch a table from SQL Server to Quick Base. After running this stored procedure the table in Quick Base will be up to date with the table in SQL Server. Records that have been deleted on SQL Server will only be deleted in Quick Base if the @deleteOrphanRecords is equal to 1. If you set the @deleteOrphanRecords equal to 1 then there must be a checkbox field in the Quick Base table called Orphan. This checkbox field is used to efficently determine which Quick Base records need to be deleted. This stored procedure only modifies the Quick Base table, not the corresponding SQL Server table. It has a required parameter: SQLTableName. The SQLTableName should be the name of the table in SQL Server that you want to synch over to Quick Base. Another required parameter is the dbid of the Quick Base table to synch information to. In addition you'll need to set the required parameter QDBAppName to the name of the Quick Base application. You can find the DBID of a Quick Base table by reading the How to Find the DBID of a Quick Base Table application note. You'll also need to identify a field in the SQL Server table that changes whenever a modification is made to any other field in the same SQL Server record. This field must be a datetime field. A typical name for this kind of field is Date Modified. This SQL Server field's name should become the value of the required parameter called SQLDateModifiedFieldName. Next you'll need to identify a field in the corresponding Quick Base table that is also a Date / Time field. The name of this field should become the value of the required parameter QuickBaseDateModifiedFieldName. Every Quick Base table comes with five built-in fields. One of these is a Date / Time called Date Modified. This is a read-only field and only Quick Base modifies the value of this field when other field values change. The parameter QuickBaseDateModifiedFieldName should never be set to the name of this field. You must use another user created Date / Time field. Another pair of field names need to be specified. The key field in SQL Server SQLKey and the copy of this key field in Quick Base QDBKey. They need to be of the same type of course. Also the key field of the Quick Base table should be set to QDBKey. Then you'll need to specify two comma delimited lists of fields names. QuickBaseFieldList and SQLFieldList. These two field lists need to have the same number of fields in them and they have to have matching field types. They should not contain the fields defined previously as SQLKey and QDBKey. Field names within these comma separated lists will have to be double quote enclosed.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Claude von Roesgen Copyright 2008
-- Create date: 01/11/2008
-- Description:	Synchronize a table from SQL Server to Quick Base.
--				This stored procedure depends on the existance of a linked server called
--				Quick Base. To create this linked server run the following SQL:
--
--					EXEC sp_addlinkedserver 
--					@server = 'Quick Base', 
--					@provider = 'MSDASQL',
--					@srvproduct = 'QuNect',
--					@provstr = 'DSN=Quick Base via QuNect;UID=claude;PWD=YourPassword' 
--					GO
--
--              After you have your linked server in place you're ready to run the stored 
--				procedure below. Both the SQL Server table and the Quick Base table must preexist.
--				Only the records that have been modified since the last
--				sync will be imported. Records that have been deleted on SQL Server will only
--				be deleted in Quick Base if the @deleteOrphanRecords is equal to 1.
--				You must double quote enclose field names within the @SQLFieldList and @QuickBaseFieldList parameters.
--				For example you must refer to Date Created as "Date Created". 
--				Since field names are easily changed in Quick Base you may want to refer to the fields 
--				by their field identifiers instead of their field labels. The field identifiers never
--				change, even when the field name is changed.
-- =============================================
CREATE PROCEDURE [dbo].[SyncSQLServerQuickBaseTable]
@SQLTableName VarChar(255),
@QDBAppName VarChar(255),
@dbid VarChar(255),
@SQLDateModifiedFieldName VarChar(255),
@QuickBaseDateModifiedFieldName VarChar(255),
@SQLKey VarChar(255),
@QDBKey VarChar(255),
@SQLFieldList VarChar(8000),
@QuickBaseFieldList VarChar(8000),
@deleteLocalOrphanRecords bit = 1
AS
BEGIN
	BEGIN
		SET ANSI_NULLS ON
		SET QUOTED_IDENTIFIER ON

		-- Find the latest date modified of the Quick Base records
		-- and then do an inner join with SQL Server records to see which records have
		-- changed on the SQL Server server since the last synch. Delete those records 
		-- in Quick Base and then refetch them from SQL Server and put them back into Quick Base.
		DECLARE @insertSQL VarChar(8000)
		SET @insertSQL = '
		DECLARE @maxQDBDateModified datetime
		SELECT @maxQDBDateModified = maxQDB FROM OPENQUERY(Quick Base, ''SELECT TOP 1 "'+@QuickBaseDateModifiedFieldName+'" as maxQDB FROM "'+@dbid+'" ORDER BY "'+@QuickBaseDateModifiedFieldName+'" DESC'')
		print ''Looking for SQL records modified on or after: '' 
		SET @maxQDBDateModified = ISNULL(@maxQDBDateModified, ''1900-1-1'')
		print @maxQDBDateModified
		INSERT INTO Quick Base."' + @QDBAppName + '".."'+@dbid+'" ("' + @QDBKey + '",' + @QuickBaseFieldList + ') SELECT "' + @SQLKey + '",' + @SQLFieldList + ' FROM "' + @SQLTableName + '"
		WHERE "'+@SQLDateModifiedFieldName+'" >= @maxQDBDateModified
		'
		PRINT @insertSQL
		EXEC(@insertSQL)
	END
	-- Now look for all orphaned records using an outer join
	-- and delete the orphans.
	DECLARE @deleteSQL VarChar(8000)
	if @deleteLocalOrphanRecords = 1		
	BEGIN
            -- Mark all records in Quick Base as orphans
            PRINT 'Marking all records as orphans'
            SET @deleteSQL = 'SELECT * FROM OPENQUERY(Quick Base, ''UPDATEOQ "'+@dbid+'" SET Orphan = 1'')'
            EXEC(@deleteSQL)
            -- Now mark all records in Quick Base that are also in SQL Server as not orphans
            PRINT 'Selectively marking records as non-orphans'
            SET @deleteSQL = 'INSERT INTO Quick Base."' + @QDBAppName + '".."'+@dbid+'" ("' + @QDBKey + '", Orphan) SELECT "' + @SQLKey + '", 0 FROM "' + @SQLTableName + '"'
            EXEC(@deleteSQL)
            -- Now delete all records in Quick Base marked as orphans
            SET @deleteSQL = 'SELECT * FROM OPENQUERY(Quick Base, ''DELETEOQ FROM "'+@dbid+'" WHERE Orphan = 1'')'
            EXEC(@deleteSQL)
	END
END