[
https://issues.apache.org/jira/browse/DERBY-2350?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali updated DERBY-2350:
----------------------------------
I took a look at this issue. I agree with the suggestion on the mentioned wiki
that a good longterm solution in this area which would also fix this bug is to
change the underlying trigger implementation to not be dependent on jdbc to get
the old and new values for the
trigger actions. My initial take is that such a change would be most
appropriate for a new
release not a bug fix in an existing release. It would be nice to get XML
working in triggers in 10.2 and 10.3 in the meantime, even if the solution is
not the best.
The base problem for the bug seems to be that by default we use the jdbc
getObject() call in the generated call to the old and new values. From
comments in
CreateTriggerNode.java we generate "internal" sql of the following form for all
the
datatypes:
/*
** Generate something like this:
**
** cast (org.apache.derby.iapi.db.Factory::
** getTriggerExecutionContext().getNewRow().
** getObject(<colPosition>) AS DECIMAL(6,2))
**
** Column position is used to avoid the wrong column being
** selected problem (DERBY-1258) caused by the case insensitive
** JDBC rules for fetching a column by name.
**
** The cast back to the SQL Domain may seem redundant
** but we need it to make the column reference appear
** EXACTLY like a regular column reference, so we need
** the object in the SQL Domain and we need to have the
** type information. Thus a user should be able to do
** something like
**
** CREATE TRIGGER ... INSERT INTO T length(Column), ...
*/
The problem is that the jdbc being called does not support getObject on an XML
object
and thus throws the exception.
I spent some time, and will post an example patch, looking at generating
different code
for XML. What I was looking at was generating the following in the case of XML
rather
than the above:
XMLPARSE(DOCUMENT
CAST (org.apache.derby.iapi.db.Factory::
getTriggerExecutionContext().getNewRow().
getString(<colPosition>) AS CLOB)
PRESERVE WHITESPACE)
The patch I will post has this change and when I run the junit TriggerTest test
it gets past the getObject() error but then gets the following which I have not
yet figured out. I believe
the parsing done by the XMLPARSE generated for the old/new reference is
working, so
not quite sure where this error is coming from.
...E
Time: 84.552
There was 1 error:
1) testTypesInActionStatement(org.apache.derbyTesting.functionTests.tests.lang.T
riggerTest)java.sql.SQLException: Values assigned to XML columns must be well-fo
rmed DOCUMENT nodes.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExcepti
onFactory.java:45)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Tra
nsactionResourceImpl.java:391)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Transa
ctionResourceImpl.java:346)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnectio
n.java:1572)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChil
d.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement
.java:1293)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedP
reparedStatement.java:1652)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPrep
aredStatement.java:299)
at org.apache.derbyTesting.functionTests.tests.lang.TriggerTest.actionTypesI
nsertTest(TriggerTest.java:580)
at org.apache.derbyTesting.functionTests.tests.lang.TriggerTest.actionTypeTe
st(TriggerTest.java:445)
at org.apache.derbyTesting.functionTests.tests.lang.TriggerTest.testTypesInA
ctionStatement(TriggerTest.java:427)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java
:64)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorI
mpl.java:43)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:95)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
Caused by: ERROR 2200L: Values assigned to XML columns must be well-formed DOCUM
ENT nodes.
at org.apache.derby.iapi.error.StandardException.newException(StandardExcept
ion.java:280)
at org.apache.derby.iapi.types.XML.normalize(XML.java:386)
at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(DataTypeDescript
or.java:505)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(Normali
zeResultSet.java:330)
at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Norma
lizeResultSet.java:189)
at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWri
teResultSet.java:125)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.ja
va:496)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPrepare
dStatement.java:370)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Gener
icTriggerExecutor.java:173)
at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(St
atementTriggerExecutor.java:80)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Trigg
erEventActivator.java:278)
at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Insert
ResultSet.java:1163)
at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.ja
va:497)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPrepare
dStatement.java:370)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement
.java:1203)
... 26 more
FAILURES!!!
Tests run: 3, Failures: 0, Errors: 1
> Use of XML values in the action statement of a trigger throw exceptions.
> ------------------------------------------------------------------------
>
> Key: DERBY-2350
> URL: https://issues.apache.org/jira/browse/DERBY-2350
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Daniel John Debrunner
>
> A trigger like, where V, V1 and V2 are columns of type XML will thrown an
> exception when fired.
> CREATE TRIGGER AIS AFTER INSERT ON T_MAIN
> REFERENCING NEW_TABLE AS N
> FOR EACH STATEMENT
> INSERT INTO T_ACTION_STATEMENT(A, V1, ID, V2)
> SELECT 'I', V, ID, V FROM N
> ERROR 38000: The exception 'java.sql.SQLException: An attempt was made to get
> a data value of type 'java.lang.Object' from a data value of type 'XML'.' was
> thrown while evaluating an expression.
> Most likely because triggers are implementing using VTIs and hence JDBC
> ResultSets and XML is not supported through JDBC yet.
> TriggerTest shows this issue, see the comment with the bug number to
> reproduce.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
