Synthesis AG - SyncML Discussion Forum  

Go Back   Synthesis AG - SyncML Discussion Forum > Synthesis SyncML Products (english) > SyncML Servers (Linux, Windows, MacOS X)

Reply
 
Thread Tools Display Modes
  #1  
Old 2007-07-03, 16:55
brady4747 brady4747 is offline
Junior Member
 
Join Date: 2007-06
Posts: 23
Default Datastore sql statement questions

I am working on modifying the default syncserv_odbc.xml that came with the pro server trial to work with preexisting mysql db. I am wondering how to tailor the sql statements(selectdatasql etc> for my db which is somewhat normalized. The company info is all in separate table from the contacts table which is good db design I guess since more often than not you have more than one person for each company. Of course I know how to use joins and everything to get all the data but did not know how that would work with %N and other variables and the fieldmap. I guess I just need some guidance on using joins for relational data in the sql statements and fieldmap. OR since I am using mysql 5 should I just create a view, although I don't know if that would be updateable though. here is the hypothetical simplified table structure of contacts and company:

CREATE TABLE `contacts` (
`idcontact` int(10) NOT NULL auto_increment,
`idcompany` int(10) default NULL,
`contact_first_name` varchar(20) default NULL,
`contact_last_name` varchar(20) default NULL,
`contact_email` varchar(45) default NULL,
`contact_fax` varchar(10) default NULL,
`contact_business_phone` varchar(17) default NULL,
`contact_home_phone` varchar(10) default NULL,
`contact_mobile_phone` varchar(10) default NULL,
`contact_pager_phone` varchar(10) default NULL,
`contact_website` varchar(45) default NULL,
`contact_birthdate` date default NULL,
`contact_sex` char(1) default NULL,
`contact_street_number` varchar(7) default NULL,
`contact_street_name` varchar(45) default NULL,
`contact_city` varchar(30) default NULL,
`contact_state` char(2) default NULL,
`contact_zip` varchar(5) default NULL,
PRIMARY KEY (`idcontact`)
) ENGINE=MyISAM


CREATE TABLE `company` (
`idcompany` int(10) NOT NULL auto_increment,
`company_name` varchar(45) default NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`created` timestamp NOT NULL default '0000-00-00 00:00:00',
`company_street_number` varchar(6) default NULL,
`company_street_name` varchar(30) default NULL,
`company_city` varchar(30) default NULL,
`company_state` char(2) default NULL,
`company_zip` varchar(5) default NULL,
`company_phone` varchar(10) default NULL,
`company_fax` varchar(10) default NULL,
`company_website` varchar(255) default NULL,
`company_login` varchar(20) default NULL,
`company_password` varchar(20) default NULL,
`company_path` varchar(45) default NULL,
PRIMARY KEY (`idcompany`)
) ENGINE=MyISAM

As you can tell, only the idcompany is stored for each contact, not the entire company name.

Thanks
Reply With Quote
  #2  
Old 2007-07-05, 19:38
brady4747 brady4747 is offline
Junior Member
 
Join Date: 2007-06
Posts: 23
Default Re: Datastore sql statement questions

OK, I succesfully put the JOIN in the selectdatasql:

Quote:
SELECT %N FROM contacts LEFT JOIN company on contacts.idcompany = company.idcompany WHERE idcontact=%k AND folderkey=%f
and put the company.company_name in the fieldmap for ORG_NAME and it syncs to device fine. I guess I need to set the ORG_NAME to r instead of rw though in case of changes on client
Reply With Quote
  #3  
Old 2007-07-05, 21:37
brady4747 brady4747 is offline
Junior Member
 
Join Date: 2007-06
Posts: 23
Default Re: Datastore sql statement questions

I have been reading the docs on this. Would the array tag (9.19.17 SySync Config Reference) better handle the db relationship between contacts and company like above?
Reply With Quote
  #4  
Old 2007-07-05, 23:20
brady4747 brady4747 is offline
Junior Member
 
Join Date: 2007-06
Posts: 23
Default Re: Datastore sql statement questions

Talking to myself again, I promise I am not crazy Just posting in case it helps someone else and in case I forget.

I also was able to get the company name from the related table by using:

Quote:
<map name="company_name" references="ORG_NAME" type="string" mode="r"
keyfield="idcompany"
readblobsql="SELECT company_name FROM company WHERE idcompany=%K"/>
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +2. The time now is 03:50.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.