[midPoint] - Execute stored procedure in SQLScript resource

[Rodrigo Yanis]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20160907/5cf333c2/attachment.htm>