>>* >/Hi, />//>/I've successfuly implemented a sync flow between an ldap source >>and a />/rest api and would like to now try importing from a relational db. >>/>//>/How would I go about importing a user-group hierarchy similar to one >>/>/found in an ldap directory? The examples don't mention how to handle >>/>/N-N relations so I'd appreciate if anyone can provide some pointers. >>/>//>/Perhaps I'll need 3 tasks, one for importing user details, one for >>/>/groups and one for the user-group relationship? />// *>* >>You need at least 2 tasks: one for users, the other for groups. For *>* >>groups, you need to create a SQL query that will concatenate members in *>* >>a single field. You will then be able to manager them as an array. *>* >>-- *>* >>Clément OUDOT *>* >>Consultant en logiciels libres, Expert infrastructure et sécurité *>* >>Savoir-faire Linux *>* >>87, rue de Turbigo - 75003 PARIS *>* >>Blog:http://sflx.ca/coudot <http://sflx.ca/coudot> *>>>* Thanks Clément, *>* I've created the query accordingly but now when I'm running the tool I get an *>* error saying one of the sql maps is not found. I know the Ibatis mapping is being loaded. *>* sql-map-config.xml: *>* <select id="getUser" resultClass="java.util.HashMap" *>* parameterClass="java.util.Map"> select id as Id, mail as Mail, (STUFF( *>* (Select ';' + Convert(Nvarchar(50),groupid) from UserGroup ug where *>* ug.userId = u.id <http://u.id> <http://u.id <http://u.id/>> For XML PATH ('')), 1,1,'')) as *>* memberOf from dbo.[User] as u where u.id <http://u.id> <http://u.id <http://u.id/>> LIKE #mail# *>* </select> <select id="checkUserForClean" *>* resultClass="java.util.HashMap" parameterClass="java.util.Map"> Select *>* mail FROM [User] WHERE mail LIKE #mail# </select> <select *>* id="getUserList" resultClass="java.util.HashMap"> SELECT mail FROM *>* [User] </select> </sqlMap> *>* Database connection: *>* <databaseConnection> <name>local-sql-server</name> *>* <url>jjdbc:sqlserver://.\SQLEXPRESS2014;databaseName=LSCSample;integratedSecurity=true;</url <sqlserver://.\SQLEXPRESS2014;databaseName=LSCSample;integratedSecurity=true;</url>> *>* <username>lsc</username> <password>lsc</password> *>* <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver> *>* </databaseConnection> *>* Database source service: *>* <databaseSourceService> <name>MySyncTask-src</name> <connection *>* reference="local-sql-server"/> *>* <requestNameForList>getUserList</requestNameForList> *>* <requestNameForObject>getUser</requestNameForObject> *>* <requestNameForClean>checkUserForClean</requestNameForClean> *>* </databaseSourceService> *>* ERROR: *>* com.ibatis.sqlmap.client.SqlMapException: There is no statement named *>* getUserList in this SqlMap. * >> Maybe there are characters in the getUser statement that are not >> >> compatible with XML format, then the file is not read at all. >> I don't know how to work with SQLserver, maybe someone on this list has >> >> already tried it. >> -- >> Clément OUDOT>> Consultant en logiciels libres, Expert infrastructure >> et sécurité>> Savoir-faire Linux>> 87, rue de Turbigo - 75003 PARIS>> Blog: >> http://sflx.ca/coudot
Turns out I had set the configuration in the sql-map-config.xml file instead of a file inside sql-map-config.d folder. Everything is working now, including the groups being separated by the delimiter as you suggested. Thanks! On Mon, Apr 18, 2016 at 5:41 PM, Gustavo Castelo <[email protected]> wrote: > > >* Hi, > *>>* I've successfuly implemented a sync flow between an ldap source and a > *>* rest api and would like to now try importing from a relational db. > *>>* How would I go about importing a user-group hierarchy similar to one > *>* found in an ldap directory? The examples don't mention how to handle > *>* N-N relations so I'd appreciate if anyone can provide some pointers. > *>>* Perhaps I'll need 3 tasks, one for importing user details, one for > *>* groups and one for the user-group relationship? > *> > >>You need at least 2 tasks: one for users, the other for groups. For > >>groups, you need to create a SQL query that will concatenate members in > >>a single field. You will then be able to manager them as an array. > > > >>-- >>Clément OUDOT>>Consultant en logiciels libres, Expert infrastructure > >>et sécurité>>Savoir-faire Linux>>87, rue de Turbigo - 75003 PARIS>>Blog: > >>http://sflx.ca/coudot > > > Thanks Clément, > > > I've created the query accordingly but now when I'm running the tool I get an > > error saying one of the sql maps is not found. I know the Ibatis mapping is > being loaded. > > > sql-map-config.xml: > > > <select id="getUser" resultClass="java.util.HashMap" > parameterClass="java.util.Map"> > > select > id as Id, > mail as Mail, > (STUFF( (Select ';' + Convert(Nvarchar(50),groupid) from > UserGroup ug where ug.userId = u.id For XML PATH ('')), 1,1,'')) as memberOf > from dbo.[User] as u > where u.id LIKE #mail# > </select> > > <select id="checkUserForClean" resultClass="java.util.HashMap" > parameterClass="java.util.Map"> > Select mail > FROM [User] > WHERE mail LIKE #mail# > </select> > > <select id="getUserList" resultClass="java.util.HashMap"> > SELECT mail > FROM [User] > </select> > > </sqlMap> > > > Database connection: > > <databaseConnection> > <name>local-sql-server</name> > > <url>jjdbc:sqlserver://.\SQLEXPRESS2014;databaseName=LSCSample;integratedSecurity=true;</url> > <username>lsc</username> > <password>lsc</password> > <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver> > </databaseConnection> > > > Database source service: > > <databaseSourceService> > <name>MySyncTask-src</name> > <connection reference="local-sql-server"/> > <requestNameForList>getUserList</requestNameForList> > <requestNameForObject>getUser</requestNameForObject> > <requestNameForClean>checkUserForClean</requestNameForClean> > </databaseSourceService> > > > ERROR: > > com.ibatis.sqlmap.client.SqlMapException: There is no statement named > getUserList in this SqlMap. > > > > > <http://sflx.ca/coudot> > > -- Gustavo Castelo AcrossGadget Unipessoal Lda +351 91 1182566
_______________________________________________________________ Ldap Synchronization Connector (LSC) - http://lsc-project.org lsc-users mailing list [email protected] http://lists.lsc-project.org/listinfo/lsc-users
