<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Jason,<br>
<br>
in MS SQL Server, you probably need to use DateTime type column
instead of Timestamp, and default values (for creation of row) and
triggers (for row update).<br>
<br>
(Just googling, I'm not a MS SQL Server expert...)<br>
<br>
Regards,<br>
Ivan<br>
<br>
<div class="moz-cite-prefix">On 11/13/2014 03:37 PM, Jason Everling
wrote:<br>
</div>
<blockquote
cite="mid:CAFkZXY4wxn+2jMGPq3VpZoe8F+-47PDnoLNY6TM2Gd-sBnXATw@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>Yes, this is the one that worked for me yesterday,</div>
<div><span style="color:rgb(0,0,0);font-family:'times new
roman','new york',times,serif;font-size:16px"><icfi:jdbcUrlTemplate></span><strong
style="color:rgb(0,0,0);font-family:'times new roman','new
york',times,serif;font-size:16px">jdbc:sqlserver://%h:%p;database=%d</strong><span
style="color:rgb(0,0,0);font-family:'times new roman','new
york',times,serif;font-size:16px"><</span><span
style="color:rgb(0,0,0);font-family:'times new roman','new
york',times,serif;font-size:16px">/icfi:jdbcUrlTemplate></span><br>
</div>
<div><span style="color:rgb(0,0,0);font-family:'times new
roman','new york',times,serif;font-size:16px"><br>
</span></div>
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.
<div><br>
</div>
<div>Is what I did,</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>JASON</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Thu, Nov 13, 2014 at 2:17 AM, Ivan
Noris <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:Ivan.Noris@evolveum.com" target="_blank">Ivan.Noris@evolveum.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div style="font-family:times new roman,new
york,times,serif;font-size:12pt;color:#000000">
<div>Hi Jason,<br>
</div>
<div><br>
</div>
<div>unfortunately I have no example for SQL Server and
the synchronization configuration nor detailed
knowledge of SQL Server.<br>
</div>
<div><br>
</div>
<div>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).<br>
</div>
<div><br>
</div>
<div>Regards,<br>
</div>
<div>Ivan<br>
</div>
<div><br>
</div>
<hr>
<blockquote style="border-left:2px solid
#1010ff;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt"><span
class=""><b>From: </b>"Jason Everling" <<a
moz-do-not-send="true"
href="mailto:jeverling@bshp.edu">jeverling@bshp.edu</a>><br>
<b>To: </b>"midPoint General Discussion" <<a
moz-do-not-send="true"
href="mailto:midpoint@lists.evolveum.com">midpoint@lists.evolveum.com</a>><br>
</span><b>Sent: </b>Wednesday, November 12, 2014
8:59:33 PM
<div>
<div class="h5"><br>
<b>Subject: </b>Re: [midPoint] Get the generated
email address back to database<br>
<div><br>
</div>
<div dir="ltr">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,
<div><br>
</div>
<div>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?</div>
<div><br>
</div>
<div>JASON</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Wed, Nov 12, 2014 at
11:29 AM, Jason Everling <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:jeverling@bshp.edu"
target="_blank">jeverling@bshp.edu</a>></span>
wrote:<br>
<blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px
#ccc solid;padding-left:1ex">
<div dir="ltr">What is the jdbcURLTemplate
for SQL Server when using the database
table sync
<div><br>
</div>
<div><icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;databaseName=%d;</icscdbtable:jdbcUrlTemplate><br>
<div><br>
</div>
<div>Where to put username/password
because sql server has to be something
like or is the below correct?</div>
<div><br>
</div>
<div>jdbc:sqlserver://%h:%p;databaseName=%d;user=%user;password=%password<span><span
style="color:#888888"
color="#888888"><br>
<div><br>
</div>
<div>JASON</div>
</span></span></div>
</div>
</div>
<div>
<div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Wed, Nov
12, 2014 at 10:41 AM, Jason Everling
<span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:jeverling@bshp.edu"
target="_blank">jeverling@bshp.edu</a>></span>
wrote:<br>
<blockquote class="gmail_quote"
style="margin:0 0 0
.8ex;border-left:1px #ccc
solid;padding-left:1ex">
<div dir="ltr">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!
<div><br>
</div>
<div>This should be do-able!</div>
<span><span
style="color:#888888"
color="#888888">
<div><br>
</div>
<div>JASON </div>
</span></span></div>
<div>
<div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On
Wed, Nov 12, 2014 at 10:28
AM, Jason Everling <span
dir="ltr"><<a
moz-do-not-send="true"
href="mailto:jeverling@bshp.edu" target="_blank">jeverling@bshp.edu</a>></span>
wrote:<br>
<blockquote
class="gmail_quote"
style="margin:0 0 0
.8ex;border-left:1px
#ccc
solid;padding-left:1ex">
<div dir="ltr">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.
<div><br>
</div>
<div>Would a
correlation still be
needed if the groovy
sql statement is
already matching
employeeNumber using
the update statement
when using the
simple method?</div>
<span><span
style="color:#888888"
color="#888888">
<div><br>
</div>
<div><br>
</div>
<div>JASON</div>
</span></span></div>
<div>
<div>
<div
class="gmail_extra"><br>
<div
class="gmail_quote">On
Wed, Nov 12,
2014 at 9:45 AM,
Ivan Noris <span
dir="ltr"><<a
moz-do-not-send="true" href="mailto:Ivan.Noris@evolveum.com"
target="_blank">Ivan.Noris@evolveum.com</a>></span>
wrote:<br>
<blockquote
class="gmail_quote"
style="margin:0
0 0
.8ex;border-left:1px
#ccc
solid;padding-left:1ex">
<div>
<div
style="font-family:times
new roman,new
york,times,serif;font-size:12pt;color:#000000">
<div>Hi Jason,<br>
</div>
<div><br>
</div>
<div>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.<br>
</div>
<div><br>
</div>
<div>If you
want to do it
only for
provisioning
(and keeping
CSV for
import/recon/livesync),
you don't need
the sync
Groovy script.<br>
</div>
<div><br>
</div>
<div>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).<br>
</div>
<div><br>
</div>
<div>Regards,<br>
</div>
<div>Ivan<br>
</div>
<div><br>
</div>
<hr>
<blockquote
style="border-left:2px
solid
#1010ff;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt"><span><b>From:
</b>"Jason
Everling" <<a
moz-do-not-send="true" href="mailto:jeverling@bshp.edu" target="_blank">jeverling@bshp.edu</a>><br>
<b>To: </b>"midPoint
General
Discussion"
<<a
moz-do-not-send="true"
href="mailto:midpoint@lists.evolveum.com" target="_blank">midpoint@lists.evolveum.com</a>><br>
</span><b>Sent:
</b>Wednesday,
November 12,
2014 3:46:04
PM<br>
<b>Subject: </b>Re:
[midPoint] Get
the generated
email address
back to
database
<div>
<div><br>
<div><br>
</div>
<div dir="ltr">
<div>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.</div>
<div><br>
</div>
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.
<div><br>
</div>
<div>Would I
use the Sync
sample script
or just the
localhost
simple? Not
really syncing
the info, just
pushing the
info back.</div>
<div><br>
</div>
<div>JASON<br>
<div><br>
</div>
<div><br>
</div>
</div>
</div>
<div
class="gmail_extra"><br>
<div
class="gmail_quote">On
Tue, Nov 11,
2014 at 1:13
PM, Ivan Noris
<span
dir="ltr"><<a
moz-do-not-send="true" href="mailto:ivan.noris@evolveum.com"
target="_blank">ivan.noris@evolveum.com</a>></span>
wrote:<br>
<blockquote
class="gmail_quote"
style="margin:0
0 0
.8ex;border-left:1px
#ccc
solid;padding-left:1ex">Hi
Jason,<br>
<span><br>
<br>
> Yes, the
real database
the CSV file
is being
dumped from,
SQL Server<br>
> 2008 R2,
I have already
loaded the
jdbc driver
for SQL Server
in the<br>
> rebuilt
war so that is
already taken
care of,<br>
<br>
</span>Well,
there are
several
possibilities:<br>
<br>
1.<br>
- use the CSV
export from
SQL DB<br>
- create user
with email
address in
midPoint<br>
- provision to
either
specific SQL
DB table OR
LDAP/Active
Directory<br>
attribute,
from which
your SQL DB
application
will fetch the
attribute.<br>
<br>
This way you
will have no
direct
connection to
SQL DB and
you'll keep<br>
most of your
current
settings. But
the SQL DB
must fetch the
data from<br>
some
intermediate
table or
LDAP/AD
attribute.<br>
<br>
2.<br>
- use the CSV
export from
SQL DB<br>
- create user
with email
address in
midPoint<br>
- provision
directly to
your SQL DB.<br>
<br>
This is
somehow
"dirty" as you
will provision
to the same
resource as<br>
you indirectly
use as your
authoritative
resource. For
midPoint,
attempt<br>
to provision
email
attribute to
the SQL DB
will look like
"add"<br>
operation, but
connector will
discover
existing user
record in SQL
DB<br>
and modify it
instead of
adding. This
requires 100%
correct
correlation<br>
expression to
automagically
convert the
add to modify.<br>
<br>
3.<br>
- use the SQL
DB as both
authoritative
source for
your users as
well as<br>
target
resource for
email address
provisioning<br>
<br>
This is the
most clean
solution, with
no
intermediate
steps and no<br>
magic. You
just set the
proper
resource
mappings
(outbounds,
inbounds).<br>
<br>
The option "3"
is recomended
over the "2".<br>
<br>
As for your
SQL DB:
connecting (as
either source
or target
resource)<br>
would require
a connector.
In midPoint,
you have three
possibilities:<br>
<br>
a) DB Table
connector:
this is
suitable for
ONE table (no
joins). Which,<br>
I suppose is
not your
case...<br>
b) ScriptedSQL
connector:
universal
scriptable
(Groovy)
connector.
This<br>
can do
anything, but
you have to
write your
operations
(add, modify,<br>
delete, search
etc.). The
connector is
just a glue
that puts the<br>
operation
scripts to a
form usable
from midPoint.<br>
c) custom SQL
connector
(must be
written in
Java)<br>
<br>
Btw. you don't
need to have
JDBC drivers
bundled in
(custom build
of)<br>
midpoint. It's
completely OK
just drop the
driver JAR
files to<br>
appropriate
directory of
your
application
server, in my
case for<br>
example,
customer's
"/opt/tomcat/lib"
contains MySQL
and MS SQL
drivers<br>
needed for the
deployment.<br>
<br>
Regards,<br>
Ivan<br>
<div>
<div><br>
--<br>
Ing. Ivan
Noris<br>
Senior
Identity
Management
Engineer<br>
<a
moz-do-not-send="true"
href="http://evolveum.com" target="_blank">evolveum.com</a><br>
___________________________________________<br>
"Idem per
idem - semper
idem Vix."<br>
<br>
_______________________________________________<br>
midPoint
mailing list<br>
<a
moz-do-not-send="true"
href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br>
<a
moz-do-not-send="true"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
target="_blank">http://lists.evolveum.com/mailman/listinfo/midpoint</a><br>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<span
style="font-size:small"><span
style="font-size:small"><br>
</span></span>
<div><br>
</div>
</div>
</div>
<span><span
style="font-size:small">CONFIDENTIALITY
NOTICE:<br>
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. </span><br>
<div><br>
</div>
</span><span>_______________________________________________<br>
midPoint
mailing list<br>
<a
moz-do-not-send="true"
href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br>
<a
moz-do-not-send="true"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
target="_blank">http://lists.evolveum.com/mailman/listinfo/midpoint</a><br>
</span></blockquote>
<span>
<div><br>
<div><br>
</div>
</div>
<div><br>
</div>
<div>-- <br>
</div>
<div><span></span> Ing.
Ivan Noris<br>
Senior
Identity
Management
Engineer<br>
<a
moz-do-not-send="true"
href="http://evolveum.com" target="_blank">evolveum.com</a><br>
___________________________________________<br>
"Idem per idem
- semper idem
Vix."<span></span><br>
</div>
</span></div>
</div>
<br>
_______________________________________________<br>
midPoint
mailing list<br>
<a
moz-do-not-send="true"
href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br>
<a
moz-do-not-send="true"
href="http://lists.evolveum.com/mailman/listinfo/midpoint"
target="_blank">http://lists.evolveum.com/mailman/listinfo/midpoint</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<span style="font-size:small" size="2"><br>
<div><br>
</div>
CONFIDENTIALITY NOTICE:<br>
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. </span><br>
<div><br>
</div>
_______________________________________________<br>
midPoint mailing list<br>
<a moz-do-not-send="true"
href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a moz-do-not-send="true"
href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a><br>
</div>
</div>
</blockquote>
<div>
<div class="h5">
<div><br>
<br>
</div>
<div><br>
</div>
<div>-- <br>
</div>
<div><span name="x"></span> Ing. Ivan Noris<br>
Senior Identity Management Engineer<br>
<a moz-do-not-send="true"
href="http://evolveum.com">evolveum.com</a><br>
___________________________________________<br>
"Idem per idem - semper idem Vix."<span
name="x"></span><br>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
midPoint mailing list<br>
<a moz-do-not-send="true"
href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a moz-do-not-send="true"
href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<font size="2"><br>
<br>
CONFIDENTIALITY NOTICE:<br>
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. </font><br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
midPoint mailing list
<a class="moz-txt-link-abbreviated" href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a>
<a class="moz-txt-link-freetext" href="http://lists.evolveum.com/mailman/listinfo/midpoint">http://lists.evolveum.com/mailman/listinfo/midpoint</a>
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Ing. Ivan Noris
Senior Identity Management Engineer
evolveum.com
___________________________________________
"Idem per idem - semper idem Vix."
</pre>
</body>
</html>