5. Helpful Examples
5.1 c-treeACE SQL Microsoft SQL Server Integration
1. Start c-treeACE SQL as a Windows service. If both c-treeACE SQL and SQL Server are on the same machine, they will use a shared memory protocol. Since Windows Vista, both Microsoft SQL Server and c-treeACE SQL must be started as Windows services to establish a Named Pipe connection.
2. Set up an ODBC “System Data Source”. The “User Data Source” type is not applicable for a linked database.
3. Create the “account” table in the c-treeACE SQL database.
create table "admin"."account" ( "id" integer not null, "person_id" integer, "balance" float (8), "obs" varchar (128), primary key ("id")
);
insert into "admin"."account" values('1','1','99.23','None'); insert into "admin"."account" values('2','2','12.11',NULL);
insert into "admin"."account" values('3','1','73.34','Secondary'); insert into "admin"."account" values('4','3','155.84','Primary'); insert into "admin"."account" values('5','3','12.19',NULL);
insert into "admin"."account" values('6','4','0.18','None'); commit work;
4. Set up c-treeACE SQL as a Linked Server in Microsoft SQL Server. Using the Microsoft SQL Server Management Studio (SSMS), execute the following steps.
a. In the “Object Explorer”, right click on “Server Objects / Linked Servers” and select the “New Linked Server” option.
b. Enter a “Linked Server name”, select the “OLE DB Provider for ODBC drivers” as the provider, “product name” and the “System Data Source” name created in item 2.
c. Click the “Security” option and add a map to the remote (c-tree) authentication. After clicking “Add”, select the authentication option on the “Local Login” and enter your c-treeACE SQL User ID and Password in the “Remote User” and “Remote Password” boxes.
d. Click the “Server Options” page, enable “RPC” and “RPC Out” options, and confirm. e. Right click on the “CTREESQL” linked server and select the “Test Connection” option. f. Check that the “account” c-treeACE SQL table is present in the linked server.
5. Query a c-treeACE SQL table in SSMS. Execute the following query in SSMS.
select * from OPENQUERY(CTREESQL, 'select * from account where id > 2') select * from CTREESQL..admin.account where id > 2
6. Create Microsoft SQL data. Execute the following commands.
[id] [int] NOT NULL,
[name] [char](32) NOT NULL,
CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED (
[id] ASC )
) ON [PRIMARY] GO
insert into person values(1, 'Mary') insert into person values(2, 'Rick') insert into person values(3, 'Jack') insert into person values(4, 'Julia')
7. Execute a join between Microsoft SQL and c-treeACE SQL tables with the following query.
select p.name, a.id, a.balance, a.obs
from person p, OPENQUERY(CTREESQL, 'select * from account') a where a.person_id = p.id
order by p.name
select p.name, a.id, a.balance, a.obs from person p, CTREESQL..admin.account a where a.person_id = p.id
order by p.name
8. Create a view with a table in Microsoft SQL Server and another in c-treeACE SQL with the following commands.
CREATE VIEW [dbo].[account_view] AS
SELECT p.name, a.id, a.balance, a.obs FROM dbo.person AS p INNER JOIN
OPENQUERY(CTREESQL, 'select * from account') AS a ON p.id = a.person_id
The view can be executed as any ordinary SQL Server statement:
select * from account_view where name = 'Mary'
9. Create a c-treeACE SQL table in SSMS. Execute the following commands.
exec ('create table "admin"."holiday" ( "id" integer not null,
"description" varchar (32), "hol_month" integer not null, "hol_day" integer not null, primary key ("id")
)') at CTREESQL GO
exec ('insert into "admin"."holiday" values (1, ''Christmas'', 12, 25)') at CTREESQL
exec ('insert into "admin"."holiday" values (2, ''New Year'', 1, 1)') at CTREESQL
GO
select * from OPENQUERY(CTREESQL, 'select * from holiday')
10. Create a similar table in Microsoft SQL Server. Execute the following commands.
CREATE TABLE [dbo].[holiday]( [id] [int] NOT NULL,
[description] [varchar](32) NULL, [hol_month] [int] NOT NULL,
[hol_day] [int] NOT NULL,
CONSTRAINT [PK_holiday] PRIMARY KEY CLUSTERED (
[id] ASC )
) ON [PRIMARY] GO
insert into holiday values(1, 'Christmas', 12, 25) GO
insert into holiday values(2, 'New Year', 1, 2) GO
select * from holiday
11. Create queue table for “holiday” in Microsoft SQL Server. This table will store the modifications to be replicated to the “linked server”. Execute the following commands.
select * into holiday_queue from holiday where 1 = 2 GO
alter table holiday_queue add action char(1) GO
alter table holiday_queue add prev_id integer GO
12. Create triggers for “holiday” in Microsoft SQL Server. To create triggers for Insert, Update and Delete operations to populate the “holiday_queue” table, execute the following commands.
CREATE TRIGGER holidayINS ON holiday AFTER INSERT
AS
INSERT holiday_queue SELECT *, 'I', NULL FROM inserted GO
CREATE TRIGGER holidayDEL ON holiday AFTER DELETE
AS
INSERT holiday_queue SELECT *, 'D', id
FROM deleted GO
CREATE TRIGGER holidayUPD ON holiday AFTER UPDATE
AS
INSERT holiday_queue
SELECT *, 'U', (select id from deleted) FROM inserted
GO
13. Create a Stored Procedure to sync “linked server” table. To create stored procedures that reads the “holiday_queue” rows and execute the actions in the “linked server” table, execute the following commands.
--- -- This stored procedure retrieves the actions queued -- and "replicates" the modifications in the linked -- server
--- CREATE PROCEDURE usp_sync_linkedsrv
AS
DECLARE @err_message nvarchar(255)
--- -- holiday replication -- --- DECLARE @id int
DECLARE @description varchar(32) DECLARE @hol_month int
DECLARE @hol_day int DECLARE @action char(1) DECLARE @previd int
-- declare cursor for reading all the holiday events DECLARE holiday_queue_cursor CURSOR FOR
SELECT * FROM holiday_queue FOR UPDATE
-- open cursor
OPEN holiday_queue_cursor
-- retrieve the data from the cursor FETCH FROM holiday_queue_cursor
WHILE @@FETCH_STATUS = 0 BEGIN
IF @action = 'I'
-- process the INSERT event
INSERT CTREESQL..admin.holiday ( id, description, hol_month, hol_day ) VALUES ( @id, @description, @hol_month, @hol_day ) ELSE BEGIN IF @action = 'U' -- process the UPDATE event
UPDATE CTREESQL..admin.holiday SET id = @id, description = @description, hol_month = @hol_month, hol_day = @hol_day WHERE id = @previd ELSE BEGIN
-- process the DELETE event
IF @action = 'D'
DELETE CTREESQL..admin.holiday WHERE id = @previd
ELSE BEGIN
SET @err_message = 'Invalid action: ' + @action RAISERROR (@err_message,10, 1)
END END
END
-- remove the current event from the queue
DELETE FROM holiday_queue WHERE CURRENT OF holiday_queue_cursor -- retrieve the next event
FETCH NEXT FROM holiday_queue_cursor
INTO @id, @description, @hol_month, @hol_day, @action, @previd END -- close cursor CLOSE holiday_queue_cursor -- deallocate cursor DEALLOCATE holiday_queue_cursor GO
14. Create a Job to execute the linked server table sync. To create and schedule a Job for calling the “usp_sync_linkedsrv” stored procedure created in the previous item to replicate the table changes from the Microsoft SQL Server to c-treeACE SQL every 10 seconds, execute the following commands.
exec msdb.dbo.sp_add_job
@job_name = 'CTREESQL replication', @enabled=1
GO
exec msdb.dbo.sp_add_jobstep
@job_name = 'CTREESQL replication',
@step_name = 'Check for changes to be replicated', @subsystem = 'TSQL',
@command = 'exec dbo.usp_sync_linkedsrv', @database_name = 'ctreeTest'
GO
exec msdb.dbo.sp_add_schedule
@schedule_name = 'CTREESQL replication schedule', @enabled = 1,
@freq_interval = 1, @freq_type = 4,
@freq_subday_interval = 10 GO
exec msdb.dbo.sp_attach_schedule
@job_name = 'CTREESQL replication',
@schedule_name = 'CTREESQL replication schedule' GO
exec msdb.dbo.sp_add_jobserver
@job_name = 'CTREESQL replication', @server_name = 'ENRICO-PC'
GO