zaccarey
2009-09-28T14:11:00Z
Hi,

Apologies if this is addressed elsewhere - but I have searched thoroughly.

I'm currently evaluating a demo version of genius connect.

I'm trying to find examples of geniusconnect being used to (two-way) synchronize data between an Outlook contacts folder and a normalized MySQL database - one in which contacts and companies are held in separate tables. So far, my attempts at writing Stored Procedures to handle this have met with only limited success.

Incidentally, the examples provided to synchronize between Outlook and a non-normalized table work fine. They're just not very useful to me.

Any help appreciated.
Administrator
2009-09-28T14:34:00Z
Hello,

what is exactly the problem/error?

We have examples with stored procedures only for MS SQL Server.

Here the steps to implement 2-way sync with custom SP's:
-create a View in DB (join the tables)
http://support.geniusconnect.com/topics/14- 

-map the Outlook Folder to the View
-create stored procedures for insert/update/delete
sp results:
success : number of rows affected = 1
(any other number of affected rows will result in warning/error in GeniusConnect)

error: raise an error in your sp
-enter the SP's in GeniusConnect (use insert parameters button for field tags)

exact syntax of sp's dependes on your datamodel, database system and version


-optional: create DB Detection stored procedure
zaccarey
2009-09-28T16:12:00Z
>what is exactly the problem/error?
The errors vary depending on exactly what i put into the SP, but usually relate to geniusconnect refusing (probably quite legitimately) to establish a primary key between the MySQL data and the contacts folder

>We have examples with stored procedures only for MS SQL Server.

I think these would be very useful to me as I suspect they're fairly generic - but I can only find a DELETE example. What would the INSERT and UPDATE SPs look like for MS SQL Server on a generic, normalized contacts database?

>Here the steps to implement 2-way sync with custom SP's:
Thanks. With regards to these steps, unless I use SPs, I can only update one of the view's underlying tables at any one time.
Administrator
2009-09-28T17:12:00Z
"I can only update one of the view's underlying tables at any one time."
If your database supports instead of triggers, you don't have to use SP's, you have to implement the triggers.

insert example:
CREATE PROCEDURE [spMyInsert]
(
	@gc_id,
	@gc_name varchar (200)  ,
	@gc_address  text ,
	Etc… ,

) AS
BEGIN

//Insert parent records
Insert into Contact (gc_id ,gc_name  , etc…) VALUES(@gc_id, @gc_name, etc..)

SET NOCOUNT ON //number of affected rows must be 1

//insert child records
Insert into ContactAddress (gc_id ,gc_address  , etc…) VALUES(@gc_id, gc_address  , etc..)


END


zaccarey
2009-09-28T17:30:00Z
Thanks, I'll scrutinize this later and see if/where my code deviates from it.
zaccarey
2009-09-30T01:44:00Z
I'm still pretty confused :-(

Disregarding GeniusConnect and Outlook for a moment, here's a simplified example showing how MySQL tables might be constructed so as to interact with a Stored Procedure. Perhaps someone can help me adapt the tables and/or procedure to work with Outlook and GC...
-- Tables

DROP TABLE IF EXISTS contact;

CREATE TABLE contact(
contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname 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;

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)
);

-- Procedure

DROP PROCEDURE IF EXISTS inserter;
DELIMITER |
CREATE PROCEDURE inserter(gc_firstname VARCHAR(64),gc_company_name VARCHAR(64),gc_job_title CHAR(64))
BEGIN
DECLARE cntct,cmpny INT DEFAULT 1;
INSERT INTO contact
(firstname,created)
VALUES
(gc_firstname,NULL)
ON DUPLICATE KEY UPDATE contact_id=LAST_INSERT_ID(contact_id),dummy = dummy+1;

SET cntct = LAST_INSERT_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;

-- Example

CALL inserter('Steve','Apple','CEO');

CALL inserter('Tony','Apple','VP');

SELECT c.contact_id
     , c.firstname
     , co.company_id
     , co.company_name
     , 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;
+------------+-----------+------------+--------------+-----------+
| contact_id | firstname | company_id | company_name | job_title |
+------------+-----------+------------+--------------+-----------+
|          1 | Steve     |          1 | Apple        | CEO       |
|          2 | Tony      |          1 | Apple        | VP        |
+------------+-----------+------------+--------------+-----------+

Administrator
2009-09-30T10:43:00Z
Hello,

what is exactly the error/waring in GeniusConnect?
what is your SQL Statement (Mandatory keys dialog) to return the auto generated prim. keys to GeniusConnect after insert?

zaccarey
2009-09-30T13:18:00Z
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 ;
Administrator
2009-09-30T20:57:00Z
Hello,

if you run your update stored procedure in some query tool, what is the result message / error?
zaccarey
2009-09-30T21:07:00Z
Hi, thanks for persisting with this. It doesn't give an error. It just updates the wrong contact - but I think I've forgotten to re-set 'cntct'!
zaccarey
2009-10-01T00:12:00Z
I amended the 'updater' procedure, adding this line;

SET cntct = gc_contact_id;

And now it all works (although it is a bit crude) :-)

The next step is to add another level of sophistication. But this may take a while...

For instance, 'v_main' (above) portrays a many-to-many relationship (company_id>-<contact_id), maintained in the underlying `company_contact` table. However, from Outlook/GC's perspective, `contact_id` is the only key defined as Primary. How might one go about writing a DELETE statement for this? If I assign company_id as a PK (keeping query = "SELECT NULL") then the alert '... Items has empty primary key(s)...' will appear.
Administrator
2009-10-01T11:03:00Z
You can use composite primary keys in GeniusConnect , just mark company key as primary and give it a SQL statement to get a value for it. But you don't need that for deleting. The delete SP will delete record from the link table (many to many), and then record from Contact table. If it is last contact linking to the company delete also the company.

zaccarey
2009-10-01T15:53:00Z
OK. I think I've fixed all that now.

I think my confusion arose from the fact that, in SQL Commands(advanced) the 'Insert parameter' button for the Delete procedure only inserts PRIMARY mandatory keys. I took this to mean that these were the ONLY keys that could be used but in fact you can use ALL mandatory keys, whether or not they've been marked as PRIMARY - you just have to type them in by hand.

As you know, I'm using the demo version of your software. Is it actually possible to test a DELETE procedure using the demo version? I mean, if I delete a record from Outlook, how will MySQL find out about it!?!
Administrator
2009-10-01T16:50:00Z
No, Demo/free version and private version has no support for deleting, you need Business or higher license.