Calling a stored procedure with JDBC application

[#1]

Post Date: April 17, 2012
Posted By: Doug Bulkley

• Question
• Answer

Question

Is it possible to use a stored procedure with an IdentityIQ JDBC application instead of direct SQL commands?

Answer

Yes. Let's take the following simple example.

1) I currently have a working JDBC application defined, which connects to SQL Server, where the SQL field has the following value:

select * from people

2) I need to create my stored procedure inside the database the JDBC application is connecting to:
    Note: The following will vary depending on database vendor. Please speak with your database administrator for assistance here.

CREATE PROCEDURE myTestSelect
AS 
    SET NOCOUNT ON;
    SELECT * FROM JDOE.PEOPLE;
RETURN
GO

3) Edit the JDBC application xml inside identityIQ and add the following attribute:

      <entry key="usePrepareCall" value="true"/>

4) Edit the JDBC application xml inside identityIQ and change the SQL attribute value to the name of your stored procedure:

      <entry key="SQL" value="myTestSelect"/>

5) Test the changes using the "iiq console" command like so to ensure the proper data is returned and the stored procedure is operating as expected:

      connectorDebug "name of your application here" iterate

6) If you have an error with your connectorDebug test, most likely there is an issue with the stored procedure itself. Please speak with your database administrator for assistance.

[raymond_cheung]

Hi, I tried to use this method but I am getting this error... unable to create iterator sailpoint.connector.ConnectorException: Stored procedure 'sp_IIQ_*****_*****' not found

[daniel_neubert]

Hi Raymond.

You probably want to double check:

- does the database schema you are connecting to within your JDBC application have "EXECUTE" privileges on the stored procedure

=> validate using database client (SQL client)

- in case your procedure is located outside the schema you are connecting to you will have to fully qualify it like: <owner>.<procedure_name>

Generally it's always a good idea to check the functionality with the help of a database client before implementing the application.

Good luck!

Daniel

[RahulKannam]

My use case is also similar where we are using Postgres functions in order to get the schema. Any help would be much appreciated!

[Eric_Mendes_CISSP]

@RahulKannam : This post is from 2012... all currently supported versions of IdentityIQ (and ISC) support utilizing a stored procedure for querying data with the JDBC connector out-of-the-box and within the GUI, so no need to go into the debug pages. 

[RahulKannam]

@Eric_Mendes_CISSP , Thank you for the clarification also could you please provide me any technical white papers where I can configure stored procedures for the query or how to use them?

[Eric_Mendes_CISSP]

@RahulKannam My pleasure! I'm not aware of any technical white papers that specifically detail using stored procedures within the JDBC connector because it's going to depend on the database you're interfacing with since they each have their own syntax for invoking the stored procedure/function. For example, MySQL has the concept of a stored procedure, which gets invoked via the CALL statement. In this case, you'd check the box "Use Stored Procedure" and enter "CALL testStoredProcQuery();" w/o quotes. I just tested in my lab, and that works as expected.

Looks like you're attempting to invoke a Postgres function, which I believe is invoked via the "SELECT" command, so it would be like this: "SELECT testStoredProcQuery();". Let me know if that works for you. 

Cheers!