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

Like • Show 0 Likes0
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

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)

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

Pradeep Kadambar

Mar 1, 2018 9:31 AM

Try running the commands in a SQL editor like Squirrel and make it works as intended.
Turn DEBUG on for the AFX connector in question and see the actual SQL query. Try to execute the same in th SQL editor.

Actions

Santosh Sajjan @ Pradeep Kadambar on Mar 1, 2018 10:17 AM

It's working fine in SQL developer and DEBUG mode showing same input.

Actions

Pradeep Kadambar

@ Santosh Sajjan on Mar 1, 2018 7:11 PM

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)

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)

Actions

Santosh Sajjan Mar 10, 2018 10:45 AM

Pradeep Kadambar thanks for replay. the special character after query causing issue.

Actions

4 Replies

Pradeep Kadambar Mar 1, 2018 9:31 AM
Mark Correct
Correct Answer
1. Try running the commands in a SQL editor like Squirrel and make it works as intended.
2. Turn DEBUG on for the AFX connector in question and see the actual SQL query. Try to execute the same in th SQL editor.
Like • Show 0 Likes0
Actions
- Santosh Sajjan @ Pradeep Kadambar on Mar 1, 2018 10:17 AM
  Mark Correct
  Correct Answer
  It's working fine in SQL developer and DEBUG mode showing same input.
  Like • Show 0 Likes0
  Actions
  - Pradeep Kadambar @ Santosh Sajjan on Mar 1, 2018 7:11 PM
    Unmark Correct
    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)
    Like • Show 1 Like1
    Actions
Santosh Sajjan Mar 10, 2018 10:45 AM
Mark Correct
Correct Answer
Pradeep Kadambar thanks for replay. the special character after query causing issue.
Like • Show 0 Likes0
Actions

AFX Oracle Connector - Throwing invalid index column error

Outcomes

Related Content

Recommended Content