AnsweredAssumed Answered

How to call stored procedure in DB connector

Question asked by Hardik Modi on Nov 9, 2016
Latest reply on May 29, 2017 by Vijayabaskar Balakrishnan

Like • Show 2 Likes2
Comment • 6

we are having a generic database connector for SQL server. since we are using a lot more operations we went with generic DB connector.

During the create account operation my stored procedure is failing, with error message,

Error code = -1

com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

syntax i am using to call stored procedure is,

CALL [dbo].[JE_SaveUser] (UserId = '${UserId}', UserName = '${UserName}', RoleId = '${RoleId}', ApprovalLevel = '${ApprovalLevel}', Status = '${Status}', CompanyIds = '${CompanyIds}', LoginUserId = '${LoginUserId}', Email = '${Email}', EntityId = '${EntityId}' )

this stored procedure works fine, outside of connector.

any pointers would be greatly appreciated.RSA Identity Governance & Lifecycle Client/Partner Community#afx, #img,#via

2 people also have this question

Outcomes

Daniel Cinnamon

Nov 10, 2016 12:04 AM

What if you try using EXEC instead of CALL?

Recently I had a similar issue also where the AFX connector was expecting some value to be returned from the stored procedure, and since my particular SP didn't return anything, we had a similar error.

So my AFX connector looked like this:

EXEC sp_name(parm1,2,3);

select 'success';

This basically just forced a return value.

Actions

Hardik Modi @ Daniel Cinnamon on Nov 10, 2016 1:47 PM

Tried using EXEC without success.

Actions

Ryan Godfrey Jan 13, 2017 3:35 PM

Hardik,

Did you have any success with this? I am running into the same problem. I keep getting an invalid column index error with not much more info to investigate with.

Actions

Santosh Kumar

Jan 17, 2017 6:21 AM

Assuming, Storied procedure UPDATEEMP is already there in database, and now you wants to call this procedure from Connector capability,

Way to provide command for any capability is :

CALL SOLQA2_DBA.HOT_PCK_IDM.UPDATEEMP(p_sqlcode,p_sqlerrm,p_employee_code,p_abbrev,p_employee_type,p_contract,p_skill_level,p_sales_team,
p_equip_checked_out,p_area_type,p_geog_area_code,p_trx_location_origin,p_employee_manager_code,p_employee_area_type,p_employee_geog_area_code,
p_expiration_date,p_wrong_passwd_counter,p_first_name,p_last_name)

(No semicolon required at the end)

AFX Connector supports calling procedures from the capabilities. If it is referring to declaring procedure from AFX then it’s not supported, to call the procedure as mentioned above it should be present in the DB.

Actions

John Landry

Mar 2, 2017 11:50 AM

The CALL command works for me too when I create an Oracle account and grant connect to it by calling a procedure directly from the connector

Oracle connector - create account capability - Command code

CALL NEW_ACCOUNT (${Account}, ${Password})

The procedure body is

create or replace
PROCEDURE NEW_ACCOUNT
(v_username IN VARCHAR2,
v_password IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'create user '||v_username||' identified by '||v_password;
EXECUTE IMMEDIATE 'grant connect to '||v_username;
END;

Actions

Vijayabaskar Balakrishnan

May 29, 2017 6:54 AM

Hardik Modi Did you resolve this error?

I tried with call UserGroupAssign "${Account}","${GroupID}" for SQL Connector. Getting the same error:

Actions

6 Replies

Daniel Cinnamon Nov 10, 2016 12:04 AM
Mark Correct
Correct Answer
What if you try using EXEC instead of CALL?

Recently I had a similar issue also where the AFX connector was expecting some value to be returned from the stored procedure, and since my particular SP didn't return anything, we had a similar error.

So my AFX connector looked like this:
EXEC sp_name(parm1,2,3);
select 'success';

This basically just forced a return value.
Like • Show 1 Like1
Actions
- Hardik Modi @ Daniel Cinnamon on Nov 10, 2016 1:47 PM
  Mark Correct
  Correct Answer
  Tried using EXEC without success.
  Like • Show 0 Likes0
  Actions
Ryan Godfrey Jan 13, 2017 3:35 PM
Mark Correct
Correct Answer
Hardik,

Did you have any success with this? I am running into the same problem. I keep getting an invalid column index error with not much more info to investigate with.
Like • Show 0 Likes0
Actions
Santosh Kumar Jan 17, 2017 6:21 AM
Mark Correct
Correct Answer
Assuming, Storied procedure UPDATEEMP is already there in database, and now you wants to call this procedure from Connector capability,

Way to provide command for any capability is :

CALL SOLQA2_DBA.HOT_PCK_IDM.UPDATEEMP(p_sqlcode,p_sqlerrm,p_employee_code,p_abbrev,p_employee_type,p_contract,p_skill_level,p_sales_team,
p_equip_checked_out,p_area_type,p_geog_area_code,p_trx_location_origin,p_employee_manager_code,p_employee_area_type,p_employee_geog_area_code,
p_expiration_date,p_wrong_passwd_counter,p_first_name,p_last_name)

(No semicolon required at the end)
AFX Connector supports calling procedures from the capabilities. If it is referring to declaring procedure from AFX then it’s not supported, to call the procedure as mentioned above it should be present in the DB.
Like • Show 0 Likes0
Actions
John Landry Mar 2, 2017 11:50 AM
Mark Correct
Correct Answer
The CALL command works for me too when I create an Oracle account and grant connect to it by calling a procedure directly from the connector

Oracle connector - create account capability - Command code
CALL NEW_ACCOUNT (${Account}, ${Password})

The procedure body is
create or replace
PROCEDURE NEW_ACCOUNT
(v_username IN VARCHAR2,
v_password IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'create user '||v_username||' identified by '||v_password;
EXECUTE IMMEDIATE 'grant connect to '||v_username;
END;
Like • Show 0 Likes0
Actions
Vijayabaskar Balakrishnan May 29, 2017 6:54 AM
Mark Correct
Correct Answer
Hardik Modi Did you resolve this error?

I tried with call UserGroupAssign "${Account}","${GroupID}" for SQL Connector. Getting the same error:
Like • Show 0 Likes0
Actions

How to call stored procedure in DB connector

Outcomes

Related Content

Recommended Content