AnsweredAssumed Answered

AFX Oracle Connector - Throwing invalid index column error

Question asked by Santosh Sajjan on Mar 1, 2018
Latest reply on Mar 10, 2018 by Santosh Sajjan
  • Comment • 4

Currently using IMG 7.0.0 with AFX oracle Connector Connected to a Oracle 11.1.2 database.

In create account capability we running SQL command 

"insert into tabale1 (user_id, user_name, full_name, peoplesoft_id, AXZ_id, last_set_password) 

values (axzseq_user.NEXTVAL, '${user_name}', '${full_name}', axzseq_user.CURRVAL, axzseq_user.CURRVAL, SYSDATE)" and mapping user_name and full_name. It's caught SQLException: Invalid column index.

 

Same error throwing even when adding account_id to group_id in group_member table having 2 columns for this I am using add account to group capability with command "insert into table group_member (group_id, account_id) values (${group_id}, ${account_id})" and input variable values group_id and account_id.

Pradeep Kadambar
Pradeep Kadambar Employee Mar 1, 2018 7:11 PM
Correct Answer

AFX uses Java PreparedStatement and the following SQL gets translated

 

from

insert into tabale1 (user_id, user_name, full_name, peoplesoft_id, AXZ_id, last_set_password) 
values (axzseq_user.NEXTVAL, '${user_name}', '${full_name}', axzseq_user.CURRVAL, axzseq_user.CURRVAL, SYSDATE)

to

insert into tabale1 (user_id, user_name, full_name, peoplesoft_id, AXZ_id, last_set_password) 
values (axzseq_user.NEXTVAL, '?', '?', axzseq_user.CURRVAL, axzseq_user.CURRVAL, SYSDATE)

 

And '?' is an invalid construct.

 

Change your SQL as shown below and it should work.

insert into tabale1 (user_id, user_name, full_name, peoplesoft_id, AXZ_id, last_set_password) 
values (axzseq_user.NEXTVAL, ${user_name}, ${full_name}, axzseq_user.CURRVAL, axzseq_user.CURRVAL, SYSDATE)
See the reply in context
No one else had this question

Outcomes