db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2350) Use of XML values in the action statement of a trigger throw exceptions.
Date Thu, 12 Jul 2007 23:41:04 GMT

     [ 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.


Mime
View raw message