[GROOVY-5996] Sql#executeInsert does not return list of auto-generated keys

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.6
• Fix Version/s: 2.3.2
• Component/s: SQL processing
• Labels: breaking executeInsert jdbc oracle, sql,
• Environment: Windows 7; Java 1.6; Groovy 2.0.6; JDBC; Oracle 11g
• Assignee: Paul King
• Reporter: Alexander Hawley
• Created: 13/Feb/13 12:46
• Updated: 28/Sep/14 18:26
• Resolved: 13/Jul/14 22:14

Description

When using Sql#executeInsert, the documentation states the return value should be a list of auto-generated column values. In my script, the return value does not contain the correct generated ID.

This might be an Oracle problem.

http://groovy.codehaus.org/gapi/groovy/sql/Sql.html#executeInsert%28java.lang.String%29

The suggested approach to getting the generated ID is something like as follows.

def result = database.executeInsert(...)
def generated_id = result[0][0]

My insert code as follows.

def result = database.executeInsert(
    """\
        insert
          into buy_in (
            rid
            ,last_name
            ,first_name
            ,mi
            ,dob
            ,prog_co
            ,sex
            ,ssn
            ,ss_claim_no
            ,created_by
            ,created_on
            ,modified_by
            ,modified_on
          )
          values (
            :id
            ,:last_name
            ,:first_name
            ,:middle_initial
            ,to_date(:birthday, 'yyyy-mm-dd hh24:mi:ss')
            ,:program_county
            ,:sex
            ,:ssn
            ,:claim
            ,:created_by
            ,to_date(:created_on, 'yyyy-mm-dd hh24:mi:ss')
            ,:modified_by
            ,to_date(:modified_on, 'yyyy-mm-dd hh24:mi:ss')
          )
    """
    ,[
        id: 'foo'
        ,last_name: ''
        ,first_name: ''
        ,middle_initial: ''
        ,birthday: new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date())
        ,program_county: ''
        ,sex: ''
        ,ssn: ''
        ,claim: ''
        ,created_by: ''
        ,created_on: new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date())
        ,modified_by: ''
        ,modified_on: new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date())
    ]
)

My return value is as follows.

result.dump()
=> <java.util.ArrayList@3811f796 elementData=[[oracle.sql.ROWID@3811f758], null, null, null, null, null, null, null, null, null] size=1 modCount=1>

to_json(result)
=> [
  [
    {
      "data": [
        65,
        65,
        67,
        77,
        65,
        78,
        65,
        65,
        76,
        65,
        65,
        76,
        101,
        115,
        57,
        65,
        65,
        70
      ]
    }
  ]
]

Any ideas?

Thanks.

-AH