<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Jason,<br>
<br>
MS's Timestamp column is not what you want. It seems to be a
"increase me when this line changes".<br>
<br>
AFAIK you need a date, so that connector can select all changes from
the last processed date/time.<br>
<br>
The same is for CSV, but file's modification timestamp is used
there.<br>
<br>
Regards,<br>
Ivan<br>
<br>
<div class="moz-cite-prefix">On 11/13/2014 03:40 PM, Jason Everling
wrote:<br>
</div>
<blockquote
cite="mid:CAFkZXY7UNsOtMuHfx5wbF7+rthms8n5iN+bpPYygP5xcdDRtLQ@mail.gmail.com"
type="cite">
<div dir="ltr">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
<div><br>
</div>
<div><a moz-do-not-send="true"
href="https://github.com/Evolveum/midpoint/blob/master/samples/resources/databasetable/create-simple-idm-table-sync.mysql">https://github.com/Evolveum/midpoint/blob/master/samples/resources/databasetable/create-simple-idm-table-sync.mysql</a><br>
<div><br>
</div>
<div>
<div>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.)</div>
<div>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.)</div>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Thu, Nov 13, 2014 at 8:37 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">
<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>
<span class="HOEnZb"><font color="#888888">
<div><br>
</div>
<div>JASON</div>
</font></span></div>
<div class="HOEnZb">
<div class="h5">
<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><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 8:59:33 PM
<div>
<div><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"
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>
<div>
<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"
target="_blank">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"
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>
<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>