Hello!
I'm trying to make a bulk insert with parameters in the 'select from' clause:
Code:
insert into DominioCuestionario
(idDominioCuestionario,idDominioMaestro,idCuestionario,descripcion,formato)
select :idNuevo,d.idDominioMaestro,:idCuest,d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
But on application startup, the following errors show:
Code:
2007-06-27 16:39:59,687 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - <AST>:3:16: unexpected AST node: :
<AST>:3:16: unexpected AST node: :
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1387)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:553)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:482)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:253)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:363)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:327)
Code:
org.hibernate.QueryException: [b]number of select types did not match those for insert[/b] [
insert into DominioCuestionario (idDominioCuestionario,idDominioMaestro, idCuestionario, descripcion, formato)
select :idNuevo,d.idDominioMaestro,:idCuest,d.descripcion,d.formato,d.idDominioMaestro,d.idDominioMaestro from es.cpdata.gq.model.DominioMaestro d where (d.idDominioMaestro = :idMaest1)
]
at org.hibernate.hql.ast.tree.IntoClause.validateTypes(IntoClause.java:92)
at org.hibernate.hql.ast.tree.InsertStatement.validate(InsertStatement.java:34)
at org.hibernate.hql.ast.HqlSqlWalker.postProcessInsert(HqlSqlWalker.java:535)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.insertStatement(HqlSqlBaseWalker.java:491)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:253)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:363)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:327)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:825)
etc
Clearly, if you read the insert statement, there are five columns to be inserted, and five columns in the select. I tried this, just for fun:
Code:
insert into DominioCuestionario (idDominioCuestionario,idDominioMaestro, idCuestionario, descripcion, formato)
select d.idDominioMaestro,d.idDominioMaestro,d.idDominioMaestro,d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
that is, removing the parameters from the select and replacing them with DominioMaestro field values, and no errors were shown on startup.
If I try:
Code:
insert into DominioCuestionario
(idDominioCuestionario,idDominioMaestro,idCuestionario,descripcion,formato)
select ':idNuevo',d.idDominioMaestro,':idCuest',d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
it now agrees that i'm selecting five columns, but correctly complains that the column types do not match the mapped properties (as they should be of type Long)
So, what can i do?
Hibernate version: 3.1.2 + Spring 1.2.7 + Tomcat 4.1
Mapping documents:Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class
name="es.cpdata.gq.model.DominioMaestro"
table="GQ_DOMINIO_MAESTRO"
select-before-update="false"
dynamic-update="false"
dynamic-insert="false"
optimistic-lock="version"
>
<id
name="idDominioMaestro"
column="ID_DOMINIO_MAESTRO"
type="java.lang.Long"
>
<generator class="sequence">
<param name="sequence">SEQ_GQ_DEFINICION</param>
</generator>
</id>
<property
name="descripcion"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="DESCRIPCION"
length="300"
not-null="false"
unique="false"
/>
<property
name="formato"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="FORMATO"
length="1000"
not-null="false"
unique="false"
/>
<!-- exported bi-directional one-to-many association to RespMaestro -->
<set name="respMaestroGroup"
table="gq_resp_maestro"
lazy="false"
fetch="select"
inverse="true"
cascade="save-update"
sort="unsorted"
>
<key>
<column name="ID_DOMINIO_MAESTRO"/>
</key>
<one-to-many
class="es.cpdata.gq.model.RespMaestro"
/>
</set>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class
name="es.cpdata.gq.model.DominioCuestionario"
table="GQ_DOMINIO_CUESTIONARIO"
select-before-update="false"
dynamic-update="false"
dynamic-insert="false"
optimistic-lock="version"
>
<id
name="idDominioCuestionario"
column="ID_DOMINIO_CUESTIONARIO"
type="java.lang.Long"
>
<generator class="assigned">
</generator>
</id>
<property
name="idDominioMaestro"
type="java.lang.Long"
update="true"
insert="true"
access="property"
column="ID_DOMINIO_MAESTRO"
length="22"
not-null="false"
unique="false"
/>
<property
name="descripcion"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="DESCRIPCION"
length="200"
not-null="false"
unique="false"
/>
<property
name="formato"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="FORMATO"
length="1000"
not-null="false"
unique="false"
/>
<property
name="idCuestionario"
type="java.lang.Long"
update="true"
insert="true"
access="property"
column="ID_CUESTIONARIO"
length="22"
not-null="false"
unique="false"
/>
<!-- imported bi-directional many-to-one association to Cuestionario -->
<many-to-one
name="cuestionario"
class="es.cpdata.gq.model.Cuestionario"
not-null="false"
outer-join="auto"
insert="false"
update="false"
>
<column name="ID_CUESTIONARIO" />
</many-to-one>
<!-- exported bi-directional one-to-many association to RespCuest -->
<set name="respCuestGroup"
table="gq_resp_cuest"
lazy="false"
fetch="select"
inverse="true"
cascade="save-update"
sort="unsorted"
>
<key>
<column name="ID_DOMINIO_CUESTIONARIO"/>
</key>
<one-to-many
class="es.cpdata.gq.model.RespCuest"
/>
</set>
</class>
<query name="insertarDominioCuestionario">
insert into DominioCuestionario (idDominioCuestionario,idDominioMaestro, idCuestionario, descripcion, formato)
select :idNuevo,d.idDominioMaestro,:idCuest,d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
<!--
select d.idDominioMaestro,d.idDominioMaestro,d.idDominioMaestro,d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
select :idNuevo,d.idDominioMaestro,:idCuest,d.descripcion,d.formato from DominioMaestro d where (d.idDominioMaestro = :idMaest1)
or (d.idDominioMaestro in (select a.idDomMaestroAsociado from RespMaestro a where a.idDominioMaestro = :idMaest2))-->
</query>
</hibernate-mapping>
Name and version of the database you are using: Oracle 10g
[/code]