[midPoint] Get the generated email address back to database

[Ivan Noris]

Hi Jason, 

generally you should modify the JDBC URL template only because of different formats for different SQL database (e.g. MySQL, PostgreSQL, Oracle, SQL Server) or the JDBC parameters (like encoding). 
The parameters %h, %p, %d etc. are automatically copied from the other connector properties. 

My JDBC URL template for SQL server (scripted SQL connector) is: 
<icfi:jdbcUrlTemplate> jdbc:sqlserver://%h:%p;database=%d </icfi:jdbcUrlTemplate> 

For my MySQL database (scripted SQL connector) it's for example somehow more complicated (because of the database I'm using): 

<icfi:jdbcUrlTemplate> jdbc:mysql://%h:%p/%d?noAccessToProcedureBodies=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8 </icfi:jdbcUrlTemplate> 

Hope this helps, 
Ivan 

----- Original Message -----

> From: "Jason Everling" <jeverling at bshp.edu>
> To: "midPoint General Discussion" <midpoint at lists.evolveum.com>
> Sent: Wednesday, November 12, 2014 6:29:25 PM
> Subject: Re: [midPoint] Get the generated email address back to database

> What is the jdbcURLTemplate for SQL Server when using the database table sync

> <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;databaseName=%d;</icscdbtable:jdbcUrlTemplate>

> Where to put username/password because sql server has to be something like or
> is the below correct?

> jdbc:sqlserver://%h:%p;databaseName=%d;user=%user;password=%password

> JASON

> On Wed, Nov 12, 2014 at 10:41 AM, Jason Everling < jeverling at bshp.edu >
> wrote:

> > I am going about this the hard way, I was just thinking about this, I could
> > create another database on the sql server and actually write a sql server
> > integration services script to sync the information that I am putting in
> > the
> > CSV file and instead of syncing from the csv file I could sync from that
> > database that would only have 1 table!
> 

> > This should be do-able!
> 

> > JASON
> 

> > On Wed, Nov 12, 2014 at 10:28 AM, Jason Everling < jeverling at bshp.edu >
> > wrote:
> 

> > > Oh Ok, I don't really want to provision or update anything else in the
> > > database, that would require a lot of joins, we have a table basically
> > > for
> > > each attribute, name table, address table, email table, email2 table,
> > > address2 table, status table for more or less employeeType, department
> > > table
> > > and so on. Each table's unique key is soc_sec which is the student id and
> > > is
> > > the employeeNumber in midpoint.
> > 
> 

> > > Would a correlation still be needed if the groovy sql statement is
> > > already
> > > matching employeeNumber using the update statement when using the simple
> > > method?
> > 
> 

> > > JASON
> > 
> 

> > > On Wed, Nov 12, 2014 at 9:45 AM, Ivan Noris < Ivan.Noris at evolveum.com >
> > > wrote:
> > 
> 

> > > > Hi Jason,
> > > 
> > 
> 

> > > > depending on direction(s) you wish to use, you may need the -sync
> > > > version
> > > > of
> > > > the resource AND the sync Groovy script - if you wish to use the
> > > > connector
> > > > as bi-directional.
> > > 
> > 
> 

> > > > If you want to do it only for provisioning (and keeping CSV for
> > > > import/recon/livesync), you don't need the sync Groovy script.
> > > 
> > 
> 

> > > > Be adwised that the -simple resource sample does NOT contain any
> > > > correlation
> > > > expression configuration. Which is mandatory if you wish to use it as
> > > > proposed by me yesterday (the "dirty" part).
> > > 
> > 
> 

> > > > Regards,
> > > 
> > 
> 
> > > > Ivan
> > > 
> > 
> 

> > > > > From: "Jason Everling" < jeverling at bshp.edu >
> > > > 
> > > 
> > 
> 
> > > > > To: "midPoint General Discussion" < midpoint at lists.evolveum.com >
> > > > 
> > > 
> > 
> 
> > > > > Sent: Wednesday, November 12, 2014 3:46:04 PM
> > > > 
> > > 
> > 
> 
> > > > > Subject: Re: [midPoint] Get the generated email address back to
> > > > > database
> > > > 
> > > 
> > 
> 

> > > > > Yes, I cannot use the DBTable Connector because of how are database
> > > > > is,
> > > > > there
> > > > > are so many tables that require joins which is why I went with the
> > > > > CSV
> > > > > file,
> > > > > easier to dump then scp over to the midpoint host.
> > > > 
> > > 
> > 
> 

> > > > > I am leaning more toward the ScriptedSQL after reviewing it, the
> > > > > update
> > > > > sample script should be fine, I just need to remove everything and
> > > > > just
> > > > > leave the staement with email and edit the SQL statement.
> > > > 
> > > 
> > 
> 

> > > > > Would I use the Sync sample script or just the localhost simple? Not
> > > > > really
> > > > > syncing the info, just pushing the info back.
> > > > 
> > > 
> > 
> 

> > > > > JASON
> > > > 
> > > 
> > 
> 

> > > > > On Tue, Nov 11, 2014 at 1:13 PM, Ivan Noris < ivan.noris at evolveum.com
> > > > > >
> > > > > wrote:
> > > > 
> > > 
> > 
> 

> > > > > > Hi Jason,
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > > Yes, the real database the CSV file is being dumped from, SQL
> > > > > > > Server
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > > 2008 R2, I have already loaded the jdbc driver for SQL Server in
> > > > > > > the
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > > rebuilt war so that is already taken care of,
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > Well, there are several possibilities:
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > 1.
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - use the CSV export from SQL DB
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - create user with email address in midPoint
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - provision to either specific SQL DB table OR LDAP/Active
> > > > > > Directory
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > attribute, from which your SQL DB application will fetch the
> > > > > > attribute.
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > This way you will have no direct connection to SQL DB and you'll
> > > > > > keep
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > most of your current settings. But the SQL DB must fetch the data
> > > > > > from
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > some intermediate table or LDAP/AD attribute.
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > 2.
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - use the CSV export from SQL DB
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - create user with email address in midPoint
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - provision directly to your SQL DB.
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > This is somehow "dirty" as you will provision to the same resource
> > > > > > as
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > you indirectly use as your authoritative resource. For midPoint,
> > > > > > attempt
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > to provision email attribute to the SQL DB will look like "add"
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > operation, but connector will discover existing user record in SQL
> > > > > > DB
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > and modify it instead of adding. This requires 100% correct
> > > > > > correlation
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > expression to automagically convert the add to modify.
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > 3.
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > - use the SQL DB as both authoritative source for your users as
> > > > > > well
> > > > > > as
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > target resource for email address provisioning
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > This is the most clean solution, with no intermediate steps and no
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > magic. You just set the proper resource mappings (outbounds,
> > > > > > inbounds).
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > The option "3" is recomended over the "2".
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > As for your SQL DB: connecting (as either source or target
> > > > > > resource)
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > would require a connector. In midPoint, you have three
> > > > > > possibilities:
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > a) DB Table connector: this is suitable for ONE table (no joins).
> > > > > > Which,
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > I suppose is not your case...
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > b) ScriptedSQL connector: universal scriptable (Groovy) connector.
> > > > > > This
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > can do anything, but you have to write your operations (add,
> > > > > > modify,
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > delete, search etc.). The connector is just a glue that puts the
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > operation scripts to a form usable from midPoint.
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > c) custom SQL connector (must be written in Java)
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > Btw. you don't need to have JDBC drivers bundled in (custom build
> > > > > > of)
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > midpoint. It's completely OK just drop the driver JAR files to
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > appropriate directory of your application server, in my case for
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > example, customer's "/opt/tomcat/lib" contains MySQL and MS SQL
> > > > > > drivers
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > needed for the deployment.
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > Regards,
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > Ivan
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > --
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > Ing. Ivan Noris
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > Senior Identity Management Engineer
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > evolveum.com
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > ___________________________________________
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > "Idem per idem - semper idem Vix."
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > > _______________________________________________
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > midPoint mailing list
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > midPoint at lists.evolveum.com
> > > > > 
> > > > 
> > > 
> > 
> 
> > > > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > > > 
> > > > 
> > > 
> > 
> 

> > > > > CONFIDENTIALITY NOTICE:
> > > > 
> > > 
> > 
> 
> > > > > This e-mail together with any attachments is proprietary and
> > > > > confidential;
> > > > > intended for only the recipient(s) named above and may contain
> > > > > information
> > > > > that is privileged. You should not retain, copy or use this e-mail or
> > > > > any
> > > > > attachments for any purpose, or disclose all or any part of the
> > > > > contents
> > > > > to
> > > > > any person. Any views or opinions expressed in this e-mail are those
> > > > > of
> > > > > the
> > > > > author and do not represent those of the Baptist School of Health
> > > > > Professions. If you have received this e-mail in error, or are not
> > > > > the
> > > > > named
> > > > > recipient(s), you are hereby notified that any review, dissemination,
> > > > > distribution or copying of this communication is prohibited by the
> > > > > sender
> > > > > and to do so might constitute a violation of the Electronic
> > > > > Communications
> > > > > Privacy Act, 18 U.S.C. section 2510-2521. Please immediately notify
> > > > > the
> > > > > sender and delete this e-mail and any attachments from your computer.
> > > > 
> > > 
> > 
> 

> > > > > _______________________________________________
> > > > 
> > > 
> > 
> 
> > > > > midPoint mailing list
> > > > 
> > > 
> > 
> 
> > > > > midPoint at lists.evolveum.com
> > > > 
> > > 
> > 
> 
> > > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > > 
> > > 
> > 
> 

> > > > --
> > > 
> > 
> 
> > > > Ing. Ivan Noris
> > > 
> > 
> 
> > > > Senior Identity Management Engineer
> > > 
> > 
> 
> > > > evolveum.com
> > > 
> > 
> 
> > > > ___________________________________________
> > > 
> > 
> 
> > > > "Idem per idem - semper idem Vix."
> > > 
> > 
> 

> > > > _______________________________________________
> > > 
> > 
> 
> > > > midPoint mailing list
> > > 
> > 
> 
> > > > midPoint at lists.evolveum.com
> > > 
> > 
> 
> > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > 
> > 
> 

> CONFIDENTIALITY NOTICE:
> This e-mail together with any attachments is proprietary and confidential;
> intended for only the recipient(s) named above and may contain information
> that is privileged. You should not retain, copy or use this e-mail or any
> attachments for any purpose, or disclose all or any part of the contents to
> any person. Any views or opinions expressed in this e-mail are those of the
> author and do not represent those of the Baptist School of Health
> Professions. If you have received this e-mail in error, or are not the named
> recipient(s), you are hereby notified that any review, dissemination,
> distribution or copying of this communication is prohibited by the sender
> and to do so might constitute a violation of the Electronic Communications
> Privacy Act, 18 U.S.C. section 2510-2521. Please immediately notify the
> sender and delete this e-mail and any attachments from your computer.

> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint

-- 
Ing. Ivan Noris 
Senior Identity Management Engineer 
evolveum.com 
___________________________________________ 
"Idem per idem - semper idem Vix." 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20141113/f858b8fa/attachment.htm>