What is CallableStatement? How you can call stored procedure to pass IN parameter?

Question

I am using a stored procedure as a source in my Informatica mapping, and I have defined the SQL query in the source qualifier as

exec dbo.GET_ATTRIBUTES($$fromDate, $$toDate)

where $$fromDate and $$toDate are mapping parameters I have defined in a parameter file. I have tried a number of different ways of going about this and none seem to work, as the SQL query fails to validate.

So, my question boils down to this, is there a way to call a stored procedure while passing in two mapping parameters?

Thanks in advance

Answer 1

CallableStatement is an interface under namespace java.sql.CallableStatement. This is usually initialized by Connection.prepareCall(java.lang.String), that returns the CallbaleStatement. Which used to call SP (stored procedures) from DB.

Sample syntax goes like this,

String insertStoreProc = "{call SP_NAME(?,?)}";
callableStatement = dbConnection.prepareCall(insertStoreProc);
callableStatement.setInt(1, "VALUE_FOR_PARAM1");
callableStatement.setDate(2, "VALUE_FOR_PARAM1");
callableStatement.executeUpdate();

Hope this helps