<div dir="ltr">Ivan,<div><br></div><div>Yes indeed, it's working now. My comment was merely an observation regarding the Groovy syntax related to the following line in the CreateScript.groovy file that the Scripted SQL resource uses in our setup, originally, we were attempting to perform the call with an incorrect syntax: <span style="font-size:x-small">sql.call("<b>{</b>? = call STORED_PROCEDURE(?, ?)<b>}</b>",[Sql.VARCHAR, attributes?.get("pidm")?.get(</span><wbr style="font-size:x-small"><span style="font-size:x-small">0), Sql.VARCHAR])</span></div><div>Proper syntax is:<span style="font-size:x-small"> </span><span style="font-size:x-small">sql.call("? = call STORED_PROCEDURE(?, ?)",[Sql.VARCHAR, attributes?.get("pidm")?.get(</span><wbr style="font-size:x-small"><span style="font-size:x-small">0), Sql.VARCHAR])</span></div>Note the absence of the enclosures {} in the second line.<br>As this might be obvious to most Groovy devs, this gave us quite the headache since there's confusing information published regarding this method.<div><br></div><div>Please let me know if I can help you any further.</div><div>Thanks,</div></div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><br></div><div dir="ltr"><font face="arial, helvetica, sans-serif"><b>Rodrigo Yanis.</b><br><img src="http://www.identicum.com/img/favicon.ico">Identicum S.A.<br></font>Jorge Newbery 3226<br>Tel: +54 (11) 4824-9971<font face="arial, helvetica, sans-serif"><br><a href="mailto:ryanis@identicum.com" target="_blank"><font color="#0b5394">ryanis@identicum.com</font></a><br><a href="http://www.identicum.com/" target="_blank"><font color="#0b5394">www.identicum.com</font></a></font></div></div></div></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">2016-09-08 5:09 GMT-03:00 Ivan Noris <span dir="ltr"><<a href="mailto:Ivan.Noris@evolveum.com" target="_blank">Ivan.Noris@evolveum.com</a>></span>:<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 Rodrigo,<br></div><div>so it now works as I understand.<br></div><div><br></div><div>Regarding your comment with regard to Groovy/SQL: I'm not a developer, so if you describe the problem and workaround/solution in a few sentences (so that even I can understand :) ), I can put it to our wiki as a note. Or I can take what you already sent here.<br></div><div><br></div><div>Thanks & 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"><b>From: </b>"Rodrigo Yanis" <<a href="mailto:ryanis@identicum.com" target="_blank">ryanis@identicum.com</a>><br><b>To: </b>"midPoint General Discussion" <<a href="mailto:midpoint@lists.evolveum.com" target="_blank">midpoint@lists.evolveum.com</a>><br><b>Sent: </b>Wednesday, September 7, 2016 3:09:14 PM<br><b>Subject: </b>Re: [midPoint] - Execute stored procedure in SQLScript resource<div><div class="h5"><br><div><br></div><div dir="ltr">Hello Ivan,<div><br></div><div>Indeed that was one of the points of failure - we mapped the icfs name attribute to the name attribute on midpoint, and UID attribute to the attribute we desired to map as inbound from our database. This solved the problem from the midPoint perspective.</div><div><br></div><div>Something else worth commenting, when the SQL call is performed in Groovy, do not use '{' and '}' as enclosure tokens for the DML statement as this seems to break the syntax. (This is at least valid for the sql.call method).</div><div><br></div><div>Thanks,</div></div><div class="gmail_extra"><br clear="all"><div><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><br></div><div dir="ltr"><span style="font-family:arial,helvetica,sans-serif" face="arial, helvetica, sans-serif"><b>Rodrigo Yanis.</b><br><img src="http://www.identicum.com/img/favicon.ico">Identicum S.A.<br></span>Jorge Newbery 3226<br>Tel: +54 (11) 4824-9971<span style="font-family:arial,helvetica,sans-serif" face="arial, helvetica, sans-serif"><br><a href="mailto:ryanis@identicum.com" target="_blank"><span style="color:#0b5394" color="#0b5394">ryanis@identicum.com</span></a><br><a href="http://www.identicum.com/" target="_blank"><span style="color:#0b5394" color="#0b5394">www.identicum.com</span></a></span><br></div></div></div></div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">2016-08-31 5:32 GMT-03:00 Ivan Noris <span dir="ltr"><<a href="mailto:ivan.noris@evolveum.com" target="_blank">ivan.noris@evolveum.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<p>Rodrigo,</p>
<p>a wild guess: your outbound mappings in the resource are not
setting the mandatory attributes, i.e. icfs:name, when adding new
account. Could this be the case?<br>
</p>
Ivan<div><div><br>
<br>
<div>On 08/30/2016 09:48 PM, Rodrigo Yanis
wrote:<br>
</div>
</div></div><blockquote><div><div>
<div dir="ltr">Hello everyone,
<div><br>
</div>
<div>I'm currently trying to perform a call on a stored
procedure in my Oracle DB through a SQLScript resource. The
use case is as follows:</div>
<div>1. The resource is assigned to the user X</div>
<div>2. Resource performs a call to a stored procedure on the
database, sending one of the user attributes as a parameter
for the stored procedure to do its magic</div>
<div>3. Stored procedure outputs a value, and its written into a
different attribute on my midPoint user.</div>
<div><br>
</div>
<div>I've tried to do this by modifying the CreateScript in the
midPoint examples, here:</div>
<div><a href="https://github.com/Evolveum/midpoint/blob/master/samples/resources/scriptedsql/CreateScript.groovy" target="_blank">https://github.com/Evolveum/<wbr>midpoint/blob/master/samples/<wbr>resources/scriptedsql/<wbr>CreateScript.groovy</a><br>
</div>
<div><br>
</div>
<div>The script I've come up with is the following:</div>
<div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span style="font-size:xx-small" size="1"><a href="http://log.info" target="_blank">log.info</a>("Entering
"+action+" Script");<br>
def sql = new Sql(connection);<br>
try {<br>
sql.call("{? = call STORED_PROCEDURE(?,
?)}",[Sql.VARCHAR, attributes?.get("pidm")?.get(<wbr>0),
Sql.VARCHAR])<br>
{<br>
alias -> return alias<br>
};<br>
}<br>
catch(all) {<br>
log.error("Exception in Stored Procedure
execution");<br>
}<br>
return "errorInCall";</span></blockquote>
</div>
<div>Stored procedure logic is out of the scope of this case.</div>
<div><br>
</div>
<div>I've configured the resource so it maps the "pidm"
attribute i'm requesting in my script to the user attribute in
midPoint. I've also mapped "alias" as an inbound attribute to
my attribute in midPoint.</div>
<div><br>
</div>
<div>Does this scenario make any sense to you? Is this being
approached correctly? Is there a way to perform a "set
attribute" on the script itself?</div>
<div><br>
</div>
<div>Right now I'm getting the following error in step 1. of the
use case I described:</div>
<div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span style="font-size:xx-small" size="1">Error
executing changes for (account (default) on
<a>resource:e1054471-b559-4f13-<wbr>bc32-aec273c51e46(Banner</a>
Personas)): Couldn't add object. Schema violation: Schema
violation during processing shadow: shadow: null
(OID:null): Attempt to add shadow without any attributes:
shadow:null(null)<br>
</span><span style="font-size:xx-small" size="1">com.evolveum.midpoint.util.<wbr>exception.SchemaException:
Couldn't add object. Schema violation: Schema violation
during processing shadow: shadow: null (OID:null): Attempt
to add shadow without any attributes: shadow:null(null)</span></blockquote>
<div><br>
</div>
<div>Thanks in advance,</div>
</div>
<div>
<div>
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr"><br>
</div>
<div dir="ltr"><span style="font-family:arial,helvetica,sans-serif" face="arial,
helvetica, sans-serif"><b>Rodrigo
Yanis.</b><br>
<img src="http://www.identicum.com/img/favicon.ico">Identicum
S.A.<br>
</span>Jorge Newbery 3226<br>
Tel: +54 (11) 4824-9971<span style="font-family:arial,helvetica,sans-serif" face="arial, helvetica, sans-serif"><br>
<a href="mailto:ryanis@identicum.com" target="_blank"><span style="color:#0b5394" color="#0b5394">ryanis@identicum.com</span></a><br>
<a href="http://www.identicum.com/" target="_blank"><span style="color:#0b5394" color="#0b5394">www.identicum.com</span></a></span><br></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<br>
</div></div><pre>______________________________<wbr>_________________
midPoint mailing list
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><span><span style="color:#888888" color="#888888">
</span></span></pre><span><span style="color:#888888" color="#888888">
</span></span></blockquote><span><span style="color:#888888" color="#888888">
<br>
<pre>--
Ivan Noris
Senior Identity Engineer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre>
</span></span></div>
<br>______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br></blockquote></div><br></div>
<br>______________________________<wbr>_________________<br>midPoint mailing list<br><a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a><br><a href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/<wbr>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>Ivan Noris<br>Senior Identity Engineer<br><a href="http://evolveum.com" target="_blank">evolveum.com</a><span name="x"></span><br></div></div></div></div></div><br>______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br></blockquote></div><br></div>