[midPoint] Get the generated email address back to database
Ivan Noris
ivan.noris at evolveum.com
Thu Nov 13 17:51:03 CET 2014
Jason,
MS's Timestamp column is not what you want. It seems to be a "increase
me when this line changes".
AFAIK you need a date, so that connector can select all changes from the
last processed date/time.
The same is for CSV, but file's modification timestamp is used there.
Regards,
Ivan
On 11/13/2014 03:40 PM, Jason Everling wrote:
> Here is the error when using a timestamp type column, in the sample in
> github for mysql you are using the timestamp type but it does not work
> for sql server
>
> https://github.com/Evolveum/midpoint/blob/master/samples/resources/databasetable/create-simple-idm-table-sync.mysql
>
> 2014-11-12 13:52:53,089 [PROVISIONING] [midPointScheduler_Worker-6]
> ERROR
> (com.evolveum.midpoint.provisioning.impl.ProvisioningServiceImpl):
> Synchronization error: generic connector framework error: DB error:
> com.microsoft.sqlserver.jdbc.SQLServerException(Implicit conversion
> from data type nvarchar to timestamp is not allowed. Use the CONVERT
> function to run this query.)
> com.evolveum.midpoint.provisioning.ucf.api.GenericFrameworkException:
> DB error: com.microsoft.sqlserver.jdbc.SQLServerException(Implicit
> conversion from data type nvarchar to timestamp is not allowed. Use
> the CONVERT function to run this query.)
>
> On Thu, Nov 13, 2014 at 8:37 AM, Jason Everling <jeverling at bshp.edu
> <mailto:jeverling at bshp.edu>> wrote:
>
> Yes, this is the one that worked for me yesterday,
> <icfi:jdbcUrlTemplate>*jdbc:sqlserver://%h:%p;database=%d*</icfi:jdbcUrlTemplate>
>
> I wish I could do it directly, there wouldn't be a way to access
> the real system database directly, there is literally a table for
> each attribute with the student id or employee number as the
> unique key. For example, there are 3 address tables, 3 name
> tables, 3 status tables, etc.. Joins are needed to get the correct
> information and to match up the data.
>
> Is what I did,
>
> Instead of using a CSV file, I am actually sync'ing to a secondary
> sql database on the sql server. It is basically syncing what I had
> in the CSV directly to a database and I am using a sql server
> integration services package to keep it sync'd with one another.
>
> In midpoint, I removed the CSV connector and now only have the
> database table connector that connects to the secondary database
> on the sql server. Midpoint then generates the username, email
> address, password and such which then gets pushed back to the
> secondary database. The SSIS package on SQL Server runs every
> hour, picks up the changes, and updates the changes to the correct
> tables in the primary database. Also works the opposite, student
> updates address in the student system, gets sync'd to the
> secondary database, midpoints picks this up and changes it in
> midpoint which then gets sync'd to AD.
>
> The only thing I had an issue with was the change_timestamp
> column, if I create a column with the timestamp type in sql server
> midpoint errors out, I had to change it to nvarchar(50) for it to
> work in midpoint but it seems to not be getting used.
>
> JASON
>
> On Thu, Nov 13, 2014 at 2:17 AM, Ivan Noris
> <Ivan.Noris at evolveum.com <mailto:Ivan.Noris at evolveum.com>> wrote:
>
> Hi Jason,
>
> unfortunately I have no example for SQL Server and the
> synchronization configuration nor detailed knowledge of SQL
> Server.
>
> But, as a last resort, I believe that DB configuration with
> some kind of "datetime" column updated automatically when
> content of the row changes should be possible (as in other
> databases).
>
> Regards,
> Ivan
>
> ------------------------------------------------------------------------
>
> *From: *"Jason Everling" <jeverling at bshp.edu
> <mailto:jeverling at bshp.edu>>
> *To: *"midPoint General Discussion"
> <midpoint at lists.evolveum.com
> <mailto:midpoint at lists.evolveum.com>>
> *Sent: *Wednesday, November 12, 2014 8:59:33 PM
>
> *Subject: *Re: [midPoint] Get the generated email address
> back to database
>
> Awesome! I got it working, well i at least have the email
> address and password going back into the database with a
> little dirty work,
>
> So, the only issue I have so far is the change_timestamp
> column for sql server, how should this column be created
> for SQL Server?
>
> JASON
>
> On Wed, Nov 12, 2014 at 11:29 AM, Jason Everling
> <jeverling at bshp.edu <mailto:jeverling at bshp.edu>> wrote:
>
> 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 <mailto: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 <mailto: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
> <mailto: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
> <mailto:jeverling at bshp.edu>>
> *To: *"midPoint General Discussion"
> <midpoint at lists.evolveum.com
> <mailto: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
> <mailto: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 <http://evolveum.com>
>
> ___________________________________________
> "Idem per idem - semper
> idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> <mailto: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
> <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
> --
> Ing. Ivan Noris
> Senior Identity Management Engineer
> evolveum.com <http://evolveum.com>
> ___________________________________________
> "Idem per idem - semper idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> <mailto: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
> <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
> --
> Ing. Ivan Noris
> Senior Identity Management Engineer
> evolveum.com <http://evolveum.com>
> ___________________________________________
> "Idem per idem - semper idem Vix."
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com <mailto: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/066b3c3a/attachment.htm>
More information about the midPoint
mailing list