getting error when selecting a legacy Oracle table with composite key in the domain

JWTeunisse

New member
Getting strange error in the select statement when connecting to a legacy Oracle table with a composite key. Hybernate tries to select a column users_id while the column name is user_id.
Here's the domain class UserGroup followed by the error message.

package cvh_beheer

class UserGroup implements Serializable {
// String id
String userid_type
String userid
String grouping_type
String groupid
Date valid_from
Date valid_until
String toString() { return userid+': '+groupid}

static constraints = {
}
// mapping naar schema coadmin en tabel USER_TO_GROUP
static mapping = {
table 'COADMIN.USER_TO_GROUP'
version false
id column:'USERID_TYPE'
id column:'USER_ID'
id column:'GROUPING_TYPE'
id column:'GROUP_ID'
id composite:['userid_type', 'userid', 'grouping_type', 'groupid'], generator:'assigned'
sort id: "asc"
columns {
userid_type column: 'USER_ID_TYPE'
userid column: 'USER_ID'
grouping_type column: 'GROUPING_TYPE'
groupid column: 'GROUP_ID'
valid_from column: 'VALID_FROM', type: 'date'
valid_until column: 'VALID_UNTIL', type: 'date'
}
}

static belongsTo= [users:Users]

/* JWT uitgezet
def beforeInsert = {
laatste_update_datum = new Date()
creation_datum = newDate()
}
def beforeUpdate = {
laatste_update_datum = new Date()
}
*/
}

Error message:
Error 500: Executing action
  • of controller [cvh_beheer.UserGroupController] caused exception: could not execute query; SQL [select this_.USER_ID_TYPE as USER1_77_0_, this_.USER_ID as USER2_77_0_, this_.GROUPING_TYPE as GROUPING3_77_0_, this_.GROUP_ID as GROUP4_77_0_, this_.users_id as users5_77_0_, this_.VALID_FROM as VALID6_77_0_, this_.VALID_UNTIL as VALID7_77_0_ from COADMIN.USER_TO_GROUP this_ order by this_.USER_ID_TYPE asc, this_.USER_ID asc, this_.GROUPING_TYPE asc, this_.GROUP_ID asc]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
    Servlet: grails
    URI: /CVH_Beheer/grails/userGroup/list.dispatch
    Exception Message: ORA-00904: "THIS_"."USERS_ID": invalid identifier
    Caused by: ORA-00904: "THIS_"."USERS_ID": invalid identifier
    Class: UserGroupController
    At Line: [13]

    Version of grails is:
    * Grails version: 1.3.3
    * Groovy version: 1.7.3
    * JVM version: 1.5.0_14
    * Controllers: 7
    * Domains: 7
    * Services: 7
    * Tag Libraries: 21

    Installed Plugins

    * logging - 1.3.3
    * core - 1.3.3
    * urlMappings - 1.3.3
    * codecs - 1.3.3
    * i18n - 1.3.3
    * filters - 1.3.3
    * groovyPages - 1.3.3
    * servlets - 1.3.3
    * controllers - 1.3.3
    * dataSource - 1.3.3
    * navigation - 1.1.1
    * tomcat - 1.3.3
    * yui - 2.7.0.1
    * scaffolding - 1.3.3
    * mimeTypes - 1.3.3
    * domainClass - 1.3.3
    * bubbling - 2.1.2
    * grailsUi - 1.2-SNAPSHOT
    * hibernate - 1.3.3
    * services - 1.3.3
    * converters - 1.3.3
    * validation - 1.3.3

    Any help or suggestions to solve this or is it an Grails error?

    thanks,
    Jan Willem Teunisse (Netherlands)
 

WillK

New member
This looks like you created the UserGroup domain model more like a database table than an object, not positive without seeing the Users domain model, but that
might be what is going wrong. for example the beginning of the UserGroup domain object should look more like this:

class UserGroup implements Serializable {
// String id
String userid_type
Users user
String grouping_type
Group group
Date valid_from
Date valid_until
String toString() { return userid+': '+groupid}


where the only changes I made reflect id's from other tables/domain objects.
Hopefully this helps if you haven't already solved the issue.
 
Top