[midPoint] - Execute stored procedure in SQLScript resource

[Rodrigo Yanis]

Ivan,

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: sql.call("*{*? = call STORED_PROCEDURE(?, ?)*}*",[Sql.VARCHAR,
attributes?.get("pidm")?.get(0), Sql.VARCHAR])
Proper syntax is: sql.call("? = call STORED_PROCEDURE(?, ?)",[Sql.VARCHAR,
attributes?.get("pidm")?.get(0), Sql.VARCHAR])
Note the absence of the enclosures {} in the second line.
As this might be obvious to most Groovy devs, this gave us quite the
headache since there's confusing information published regarding this
method.

Please let me know if I can help you any further.
Thanks,


*Rodrigo Yanis.*
Identicum S.A.
Jorge Newbery 3226
Tel: +54 (11) 4824-9971
ryanis at identicum.com
www.identicum.com

2016-09-08 5:09 GMT-03:00 Ivan Noris <Ivan.Noris at evolveum.com>:

> Hi Rodrigo,
> so it now works as I understand.
>
> 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.
>
> Thanks & regards,
> Ivan
>
> ------------------------------
>
> *From: *"Rodrigo Yanis" <ryanis at identicum.com>
> *To: *"midPoint General Discussion" <midpoint at lists.evolveum.com>
> *Sent: *Wednesday, September 7, 2016 3:09:14 PM
> *Subject: *Re: [midPoint] - Execute stored procedure in SQLScript resource
>
>
> Hello Ivan,
>
> 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.
>
> 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).
>
> Thanks,
>
>
> *Rodrigo Yanis.*
> Identicum S.A.
> Jorge Newbery 3226
> Tel: +54 (11) 4824-9971
> ryanis at identicum.com
> www.identicum.com
>
> 2016-08-31 5:32 GMT-03:00 Ivan Noris <ivan.noris at evolveum.com>:
>
>> Rodrigo,
>>
>> 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?
>> Ivan
>>
>>
>> On 08/30/2016 09:48 PM, Rodrigo Yanis wrote:
>>
>> Hello everyone,
>>
>> 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:
>> 1. The resource is assigned to the user X
>> 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
>> 3. Stored procedure outputs a value, and its written into a different
>> attribute on my midPoint user.
>>
>> I've tried to do this by modifying the CreateScript in the midPoint
>> examples, here:
>> https://github.com/Evolveum/midpoint/blob/master/samples/
>> resources/scriptedsql/CreateScript.groovy
>>
>> The script I've come up with is the following:
>>
>>> log.info("Entering "+action+" Script");
>>> def sql = new Sql(connection);
>>> try {
>>>         sql.call("{? = call STORED_PROCEDURE(?, ?)}",[Sql.VARCHAR,
>>> attributes?.get("pidm")?.get(0), Sql.VARCHAR])
>>>         {
>>>                 alias -> return alias
>>>         };
>>> }
>>> catch(all) {
>>>         log.error("Exception in Stored Procedure execution");
>>> }
>>> return "errorInCall";
>>
>> Stored procedure logic is out of the scope of this case.
>>
>> 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.
>>
>> 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?
>>
>> Right now I'm getting the following error in step 1. of the use case I
>> described:
>>
>>> Error executing changes for (account (default) on
>>> resource:e1054471-b559-4f13-bc32-aec273c51e46(Banner 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)
>>> com.evolveum.midpoint.util.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)
>>
>>
>> Thanks in advance,
>>
>> *Rodrigo Yanis.*
>> Identicum S.A.
>> Jorge Newbery 3226
>> Tel: +54 (11) 4824-9971
>> ryanis at identicum.com
>> www.identicum.com
>>
>>
>> _______________________________________________
>> midPoint mailing listmidPoint at lists.evolveum.comhttp://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>> --
>> Ivan Noris
>> Senior Identity Engineerevolveum.com
>>
>>
>> _______________________________________________
>> midPoint mailing list
>> midPoint at lists.evolveum.com
>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>
>>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
>
>
> --
> Ivan Noris
> Senior Identity Engineer
> evolveum.com
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20160908/de623636/attachment.htm>