ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Appending strings to query
Date Tue, 20 Jun 2006 08:33:13 GMT
Looks like "Missing key in referenced table for referential
constraint" is the core problem.

My guess is that the SQL is fine, but the data is not.

Larry


On 6/19/06, Cristiano Monteiro dos Santos <csantos@polimed.com.br> wrote:
> It's getting clear now.
>
> So the $$ stuff should work transparently in any case, right? If I get it
> right, It's just insert the name of the parametter class attribute between
> the $$, like tb$userid$, and it should get substituted without any other
> intervention. If not, where do I set the value for the $$ variable?
>
> But in my specific case, where I must add the database name and the change
> the table name will it work as well? Here is my insert statement:
>
>   <insert id="abatorgenerated_insert"
> parameterClass="br.com.polimed.ibatis.sim.model.Tb301usuario">
>     insert into $nomeEms$:tb$emsNumeroString$usuario (<static field list
> blablabla>)
>     values (static values list blablabla)
>   </insert>
>
> The parametterClass has the attributes nomeEms (a String) and the
> emsNumeroUsuario, a calculated String attribute. When I execute the
> statement, I receive the following exception:
>
> com.ibatis.dao.client.DaoException: Failed to insert - id
> [informix_tb301usuario.abatorgenerated_insert], parameterObject
> [br.com.polimed.ibatis.sim.model.Tb301usuario@65a77f]. Cause:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> br/com/polimed/ibatis/sim/model/informix_tb301usuario_SqlMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the
> informix_tb301usuario.abatorgenerated_insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> br/com/polimed/ibatis/sim/model/informix_tb301usuario_SqlMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the
> informix_tb301usuario.abatorgenerated_insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
>         at
> com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplate.ja
> va:102)
>         at
> br.com.polimed.ibatis.sim.dao.Tb301usuarioDAOImpl.insert(Tb301usuarioDAOImpl
> .java:31)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
>         at $Proxy1.insert(Unknown Source)
>         at
> br.com.polimed.ibatis.sim.Tb301usuarioDAOImplTest.testInsertTb301usuario(Tb3
> 01usuarioDAOImplTest.java:51)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at junit.framework.TestCase.runTest(TestCase.java:154)
>         at junit.framework.TestCase.runBare(TestCase.java:127)
>         at junit.framework.TestResult$1.protect(TestResult.java:106)
>         at junit.framework.TestResult.runProtected(TestResult.java:124)
>         at junit.framework.TestResult.run(TestResult.java:109)
>         at junit.framework.TestCase.run(TestCase.java:118)
>         at junit.framework.TestSuite.runTest(TestSuite.java:208)
>         at junit.framework.TestSuite.run(TestSuite.java:203)
>         at
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3T
> estReference.java:128)
>         at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:3
> 8)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:460)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:673)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:386)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
> .java:196)
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> br/com/polimed/ibatis/sim/model/informix_tb301usuario_SqlMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the
> informix_tb301usuario.abatorgenerated_insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:91)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDe
> legate.java:442)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.jav
> a:81)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:
> 58)
>         at
> com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplate.ja
> va:100)
>         ... 26 more
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
>         at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3204)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
>         at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
>         at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
>         at com.informix.jdbc.IfxSqli.c(IfxSqli.java:1256)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2195)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2132)
>         at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:378)
>         at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1299)
>         at
> com.informix.jdbc.IfxPreparedStatement.d(IfxPreparedStatement.java:436)
>         at
> com.informix.jdbc.IfxPreparedStatement.execute(IfxPreparedStatement.java:421
> )
>         at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.jav
> a:84)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate
> (GeneralStatement.java:200)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:78)
>         ... 30 more
> Caused by: java.sql.SQLException: ISAM error: no record found.
>         at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
>         ... 42 more
>
> Caused by:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in
> br/com/polimed/ibatis/sim/model/informix_tb301usuario_SqlMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the
> informix_tb301usuario.abatorgenerated_insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:91)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDe
> legate.java:442)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.jav
> a:81)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:
> 58)
>         at
> com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplate.ja
> va:100)
>         at
> br.com.polimed.ibatis.sim.dao.Tb301usuarioDAOImpl.insert(Tb301usuarioDAOImpl
> .java:31)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
>         at $Proxy1.insert(Unknown Source)
>         at
> br.com.polimed.ibatis.sim.Tb301usuarioDAOImplTest.testInsertTb301usuario(Tb3
> 01usuarioDAOImplTest.java:51)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at junit.framework.TestCase.runTest(TestCase.java:154)
>         at junit.framework.TestCase.runBare(TestCase.java:127)
>         at junit.framework.TestResult$1.protect(TestResult.java:106)
>         at junit.framework.TestResult.runProtected(TestResult.java:124)
>         at junit.framework.TestResult.run(TestResult.java:109)
>         at junit.framework.TestCase.run(TestCase.java:118)
>         at junit.framework.TestSuite.runTest(TestSuite.java:208)
>         at junit.framework.TestSuite.run(TestSuite.java:203)
>         at
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3T
> estReference.java:128)
>         at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:3
> 8)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:460)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:673)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:386)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
> .java:196)
> Caused by: java.sql.SQLException: Missing key in referenced table for
> referential constraint (unimedpaulistana:informix.r131_216).
>         at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3204)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
>         at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
>         at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
>         at com.informix.jdbc.IfxSqli.c(IfxSqli.java:1256)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2195)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2132)
>         at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:378)
>         at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1299)
>         at
> com.informix.jdbc.IfxPreparedStatement.d(IfxPreparedStatement.java:436)
>         at
> com.informix.jdbc.IfxPreparedStatement.execute(IfxPreparedStatement.java:421
> )
>         at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.jav
> a:84)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate
> (GeneralStatement.java:200)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:78)
>         ... 30 more
> Caused by: java.sql.SQLException: ISAM error: no record found.
>         at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
>         ... 42 more
>
> Caused by:
> java.sql.SQLException: Missing key in referenced table for referential
> constraint (unimedpaulistana:informix.r131_216).
>         at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3204)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518)
>         at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353)
>         at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269)
>         at com.informix.jdbc.IfxSqli.c(IfxSqli.java:1256)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2195)
>         at com.informix.jdbc.IfxSqli.executeExecute(IfxSqli.java:2132)
>         at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:378)
>         at com.informix.jdbc.IfxStatement.a(IfxStatement.java:1299)
>         at
> com.informix.jdbc.IfxPreparedStatement.d(IfxPreparedStatement.java:436)
>         at
> com.informix.jdbc.IfxPreparedStatement.execute(IfxPreparedStatement.java:421
> )
>         at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.jav
> a:84)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate
> (GeneralStatement.java:200)
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:78)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDe
> legate.java:442)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.jav
> a:81)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:
> 58)
>         at
> com.ibatis.dao.client.template.SqlMapDaoTemplate.insert(SqlMapDaoTemplate.ja
> va:100)
>         at
> br.com.polimed.ibatis.sim.dao.Tb301usuarioDAOImpl.insert(Tb301usuarioDAOImpl
> .java:31)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
>         at $Proxy1.insert(Unknown Source)
>         at
> br.com.polimed.ibatis.sim.Tb301usuarioDAOImplTest.testInsertTb301usuario(Tb3
> 01usuarioDAOImplTest.java:51)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
>         at java.lang.reflect.Method.invoke(Unknown Source)
>         at junit.framework.TestCase.runTest(TestCase.java:154)
>         at junit.framework.TestCase.runBare(TestCase.java:127)
>         at junit.framework.TestResult$1.protect(TestResult.java:106)
>         at junit.framework.TestResult.runProtected(TestResult.java:124)
>         at junit.framework.TestResult.run(TestResult.java:109)
>         at junit.framework.TestCase.run(TestCase.java:118)
>         at junit.framework.TestSuite.runTest(TestSuite.java:208)
>         at junit.framework.TestSuite.run(TestSuite.java:203)
>         at
> org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3T
> estReference.java:128)
>         at
> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:3
> 8)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:460)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRu
> nner.java:673)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.
> java:386)
>         at
> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner
> .java:196)
> Caused by: java.sql.SQLException: ISAM error: no record found.
>         at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
>         at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523)
>         ... 42 more
>
>
> I'm still a little lost.
>
> Regards,
> Cristiano
>
> -----Mensagem original-----
> De: Ted Schrader [mailto:tschrader@gmail.com]
> Enviada em: segunda-feira, 19 de junho de 2006 18:42
> Para: user-java@ibatis.apache.org
> Assunto: Re: Appending strings to query
>
>
> I think you've almost got it.
>
> You only need to consider remapResults="true" when the SQL statement
> will return results.  In other words, you only need it for queries or
> stored procedures, since INSERTs, UPDATEs, and DELETEs don't really
> give back results.
>
> The use of remapResults="true" and the use of $ are two separate
> features of iBATIS.  It just happens that using $ in the SELECT or
> FROM clause may imply the need for remapResults="true".
>
> Now, in your example above, you had:
>
> >> select f1, f2, f3
> >> #databasename#:tb#customerid#user
>
> If your column names aren't going to change, like in this example,
> then you don't need remapResults="true".  If you're going to do
> something more like:
>
> select * from $table$
>
> or
>
> select $columnList$
> from myTable
>
> then you'd need remapResults="true".
>
> Ted
>
> On 19/06/06, Cristiano Monteiro dos Santos <csantos@polimed.com.br> wrote:
> > Hey again, Ted.
> >
> > I believe now I got the point. I have to set my statements with the
> > remapResults="true" and then the $$ "magic" should work transparently,
> > right? But as I see in the documentation and in the XML's DTD, this
> property
> > is only available to <statement>, <select> and <procedure> attributes,
> > right? The problem is that I have to use this in all of my queries.
> >
> > Am I missing something?
> >
> > Thanks,
> > CMS
> >
> > -----Mensagem original-----
> > De: Ted Schrader [mailto:tschrader@gmail.com]
> > Enviada em: segunda-feira, 19 de junho de 2006 17:57
> > Para: user-java@ibatis.apache.org
> > Assunto: Re: Appending strings to query
> >
> >
> > I have a JavaBean property named dbName; in other words I have a
> > getDbName() and a setDbName() method.  The application sets the
> > database name into the bean based on what the user does, then hands
> > the bean over to iBATIS.
> >
> > On 19/06/06, Cristiano Monteiro dos Santos <csantos@polimed.com.br> wrote:
> > > Hi, Ted!
> > >
> > > Thanks for your tip. How do you deal with the $dbName$ variable? You
> > > substitude by yourself this string for another thing? How do you do
> this?
> > I
> > > was cheking the link you sent me, and it has something to do with it, as
> I
> > > see on that page the same kind of usage. How do I pass the value of that
> > > variable to iBATIS?
> > >
> > > Thanks, man!
> > > CMS
> > >
> > > -----Mensagem original-----
> > > De: Ted Schrader [mailto:tschrader@gmail.com]
> > > Enviada em: segunda-feira, 19 de junho de 2006 17:38
> > > Para: user-java@ibatis.apache.org
> > > Assunto: Re: Appending strings to query
> > >
> > >
> > > Hi Cristiano,
> > >
> > > No, it's not too much to expect.  I've had to do something similar,
> > > although probably not as complicated as your situation.
> > >
> > > Here's an example:
> > >
> > > SELECT NUMBER, DESCRIPTION
> > >   FROM $dbName$.ITEMS
> > >
> > > My application spans multiple databases (actually, multiple iSeries
> > > libraries) and this has worked out okay, since all of our ITEMS tables
> > > have identical definitions (not my schema!).
> > >
> > > Note that we have to use the $ symbol, not #.  Also, when using $, be
> > > sure your values are SQL-escaped to avoid injection attacks.  In my
> > > case, the dbName variable is managed internally by the application.
> > >
> > > Also, I recommend you get familiar with the remapResults="true"
> > > setting in case it applies to your situation:
> > >
> >
> http://opensource.atlassian.com/confluence/oss/pages/viewpage.action?pageId=
> > > 3057
> > >
> > > Cheers!
> > >
> > > Ted
> > >
> > >
> > > On 19/06/06, Cristiano Monteiro dos Santos <csantos@polimed.com.br>
> wrote:
> > > > Hi again, guys.
> > > >
> > > > I have another question: is it possible to append string snippets to
> the
> > > > iBATIS's xml queries? I mean, I have several databases, each of then
> > > > containing the same set of tables, following the schema
> > > > #databasename#:tb#customerid#user (not telling if it's right or wrong,
> > > it's
> > > > fact), and I want to execute the queries sometimes in one database,
> > > > sometimes in the other. Like this:
> > > >
> > > > select f1, f2, f3
> > > > #databasename#:tb#customerid#user
> > > >
> > > > Resulting in the following query:
> > > > select f1, f2, f3
> > > > custname:tb123user
> > > >
> > > > Where #databasename# and #customerid# are both attributes of the
> > > > parameterClass. Am I expecting too much?
> > > >
> > > > Thanks!
> > > > Cristiano
> > > >
> > >
> >
>

Mime
View raw message