OK, it seems to be almost working now. My UPDATE statement is still wrong - but that might just be a simple mistake.
So, to recap, these are the steps I've taken so far:
1a. In MySQL, create tables and procedures as shown below.
1b. In MySQL, create a view (v_main) of all the relevant fields, again as shown below.
1c. In MySQL, create 'Insert' and 'Update' procedures, again as shown below. A 'Delete' procedure may also be required.
2a.In GC setup, assign the view to the Outlook contacts folder:
Company(Name) -> company_name
First Name -> firstname
Job Title -> job_title
2b.In GC setup, assign following statements to Mandatory Keys:
+-----------+-----+-------------------------------------+------------------------+
|DB Field |Type |SQL statement |Identity(Auto Increment)|
+-----------+-----+-------------------------------------+------------------------+
|contact_id |P |SELECT MAX(contact_id)+1 FROM contact|No |
|c_created |M |SELECT NULL |No |
|c_modified |M |SELECT NULL |No |
|company_id |M |SELECT NULL |No |
|co_created |M |SELECT NULL |No |
|co_modified|M |SELECT NULL |No |
+-----------+-----+-------------------------------------+------------------------+
2c.In GC setup->Assign Table...->Options...->SQL Commands(Advanced), assign these statements to 'Insert' and 'Update' respectively:
Call inserter('@contact_id','@firstname','@company_name','@job_title');
Call updater('@contact_id','@firstname','@company_name','@job_title');
-- Schema creation statements
-- All to be used in conjunction with a Mandatory Key query of "SELECT MAX(contact_id)+1 FROM contact" or similar
-- 1a. Tables
DROP TABLE IF EXISTS contact;
CREATE TABLE contact(
contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(64),
created TIMESTAMP NOT NULL DEFAULT 0,
modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dummy INT DEFAULT 0
);
DROP TABLE IF EXISTS company;
CREATE TABLE company(
company_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
company_name VARCHAR(64) UNIQUE,
created TIMESTAMP NOT NULL DEFAULT 0,
modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dummy INT DEFAULT 0
);
DROP TABLE IF EXISTS company_contact;
CREATE TABLE company_contact(
company_id INT NOT NULL,
contact_id INT NOT NULL,
job_title VARCHAR(64) NULL,
PRIMARY KEY (company_id,contact_id)
);-- Insert Procedure
-- 1b. View. This is the 'table' that gets assigned to GC
CREATE VIEW v_main AS
SELECT c.contact_id
, c.firstname
, c.created c_created
, c.modified c_modified
, co.company_id
, co.company_name
, co.created co_created
, co.modified co_modified
, cc.job_title
FROM contact c
JOIN company_contact cc
ON cc.contact_id = c.contact_id
JOIN company co ON co.company_id = cc.company_id;
-- 1c. Procedures
DROP PROCEDURE IF EXISTS inserter;
DELIMITER |
CREATE PROCEDURE inserter(gc_contact_id INT(10),gc_firstname VARCHAR(64),gc_company_name VARCHAR(64),gc_job_title CHAR(64))
BEGIN
DECLARE cntct,cmpny INT DEFAULT 1;
INSERT INTO contact
(contact_id,firstname,created)
VALUES
(gc_contact_id,gc_firstname,NULL);
SET cntct = gc_contact_id;
INSERT INTO company
(company_name,created)
VALUES
(gc_company_name,NULL)
ON DUPLICATE KEY UPDATE company_id=LAST_INSERT_ID(company_id),dummy = dummy+1;
SET cmpny = LAST_INSERT_ID();
INSERT INTO company_contact
(company_id,contact_id,job_title)
VALUES
(cmpny,cntct,gc_job_title);
END |
DELIMITER ;
-- Update Procedure. Note: not yet working!!!!
DROP PROCEDURE IF EXISTS updater;
DELIMITER |
CREATE PROCEDURE updater(gc_contact_id INT(10),gc_firstname VARCHAR(64),gc_company_name VARCHAR(64),gc_job_title CHAR(64))
BEGIN
DECLARE cntct,cmpny INT DEFAULT 1;
UPDATE contact SET firstname = gc_firstname WHERE contact_id = gc_contact_id;
INSERT INTO company
(company_name,created)
VALUES
(gc_company_name,NULL)
ON DUPLICATE KEY UPDATE company_id=LAST_INSERT_ID(company_id),dummy = dummy+1;
SET cmpny = LAST_INSERT_ID();
INSERT INTO company_contact
(company_id,contact_id,job_title)
VALUES
(cmpny,cntct,gc_job_title)
ON DUPLICATE KEY UPDATE job_title=gc_job_title;
END |
DELIMITER ;