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