Post Date: April 17, 2012
Posted By: Doug Bulkley
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.
