[midPoint] - Execute stored procedure in SQLScript resource

[Ivan Noris]

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 

----- Original Message -----

> 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 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
> 

> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint

-- 
Ivan Noris 
Senior Identity Engineer 
evolveum.com 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20160908/ff0256fb/attachment.htm>