cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: MS SQL Server: Select Distinct on text datatype
Date Tue, 05 Aug 2008 13:33:34 GMT
Hi Matthias,

maybe you can switch to CLOB column type on Derby.

Andrus

On Aug 4, 2008, at 9:39 PM, Matthias Moeser wrote:

> Hi!
>
> Thank you Andrus and Andrey. I changed the mapping for the columns  
> of datatype "text" from VARCHAR to CLOB (max length 32000) in  
> Cayenne Modeler. Everything is working fine now for MS SQL server.
>
> But I also run the applicaion with embedded Derby, where I use  
> VARCHAR(32000) in the sql code. And now I get a  
> org.apache.derby.impl.jdbc.EmbedSQLException: "An attempt was made  
> to get a data value of type 'VARCHAR' from a data value of type  
> 'CLOB'."
>
> Any idea? Is there a different mapping possible for different  
> database systems? I use an own DriverDataSourceFactory which  
> implements DataSourceFactory to decide which database system to  
> choose and which loads the right DataSourceInfo.
>
> With regards
> Matthias
>
>
> [java] java.sql.SQLException: An attempt was made to get a data  
> value of type 'VARCHAR' from a data value of type 'CLOB'.
>    [java]     at  
> org 
> .apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown  
> Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown  
> Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown  
> Source)
>    [java]     at  
> org 
> .apache 
> .derby.impl.jdbc.EmbedPreparedStatement.dataTypeConversion(Unknown  
> Source)
>    [java]     at  
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setNull(Unknown  
> Source)
>    [java]     at  
> org.apache.cayenne.dba.JdbcAdapter.bindParameter(JdbcAdapter.java:493)
>    [java]     at  
> org 
> .apache 
> .cayenne 
> .access 
> .trans 
> .InsertBatchQueryBuilder.bindParameters(InsertBatchQueryBuilder.java: 
> 63)
>    [java]     at  
> org 
> .apache.cayenne.access.jdbc.BatchAction.runAsBatch(BatchAction.java: 
> 128)
>    [java]     at  
> org 
> .apache 
> .cayenne.access.jdbc.BatchAction.performAction(BatchAction.java:80)
>    [java]     at  
> org 
> .apache 
> .cayenne 
> .access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
>    [java]     at  
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
>    [java] Aug 5, 2008 10:44:39 AM  
> com.caucho.hessian.server.HessianSkeleton invoke
>    [java] WARNING: org.apache.cayenne.CayenneRuntimeException: [v. 
> 3.0-SNAPSHOT Jul 09 2008 01:28:38] Exception processing message  
> org.apache.cayenne.remote.SyncMessage of type flush-cascade-sync
>    [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0- 
> SNAPSHOT Jul 09 2008 01:28:38] Exception processing message  
> org.apache.cayenne.remote.SyncMessage of type flush-cascade-sync
>    [java]     at  
> org 
> .apache 
> .cayenne 
> .remote 
> .service.BaseRemoteService.processMessage(BaseRemoteService.java:205)
>    [java]     at  
> sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
>    [java]     at  
> sun 
> .reflect 
> .DelegatingMethodAccessorImpl 
> .invoke(DelegatingMethodAccessorImpl.java:25)
>    [java]     at java.lang.reflect.Method.invoke(Method.java:585)
>    [java]     at  
> com 
> .caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java: 
> 180)
>    [java]     at  
> com 
> .caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java: 
> 109)
>    [java]     at  
> com.caucho.hessian.server.HessianServlet.service(HessianServlet.java: 
> 396)
>    [java]     at  
> org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
>    [java]     at org.mortbay.jetty.servlet.ServletHandler 
> $CachedChain.doFilter(ServletHandler.java:1097)
>    [java]     at  
> ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
>    [java]     at org.mortbay.jetty.servlet.ServletHandler 
> $CachedChain.doFilter(ServletHandler.java:1088)
>    [java]     at  
> org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java: 
> 360)
>    [java]     at  
> org 
> .mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java: 
> 216)
>    [java]     at  
> org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java: 
> 181)
>    [java]     at  
> org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java: 
> 729)
>    [java]     at  
> org 
> .mortbay 
> .jetty 
> .handler 
> .ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
>    [java]     at  
> org 
> .mortbay 
> .jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
>    [java]     at  
> org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java: 
> 152)
>    [java]     at org.mortbay.jetty.Server.handle(Server.java:324)
>    [java]     at  
> org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java: 
> 505)
>    [java]     at org.mortbay.jetty.HttpConnection 
> $RequestHandler.content(HttpConnection.java:843)
>    [java]     at  
> org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:729)
>    [java]     at  
> org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
>    [java]     at  
> org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
>    [java]     at org.mortbay.jetty.bio.SocketConnector 
> $Connection.run(SocketConnector.java:228)
>    [java]     at org.mortbay.thread.QueuedThreadPool 
> $PoolThread.run(QueuedThreadPool.java:488)
>    [java] Caused by: java.lang.Exception: class  
> org.apache.derby.impl.jdbc.EmbedSQLException An attempt was made to  
> get a data value of type 'VARCHAR' from a data value of type 'CLOB'.
>    [java]     at  
> org 
> .apache 
> .cayenne 
> .remote 
> .service.BaseRemoteService.processMessage(BaseRemoteService.java:204)
>    [java]     ... 25 more
>
>
>
>
>
> On 05/08/2008, at 6:49 AM, Andrus Adamchik wrote:
>
>> Andrey, you are spot on with the UNSUPPORTED_DISTINCT_TYPES. Here  
>> is one caveat (and hopefully a workaround). "text" is a SQL Server  
>> native type; UNSUPPORTED_DISTINCT_TYPES stores abstract JDBC types.  
>> So I think Matthias can simply map his "text" column as a CLOB in  
>> Cayenne, and Cayenne will know to stop using DISTINCT.
>>
>> On a side note, DISTINCT is added implicitly for to-many qualifiers  
>> when Cayenne assumes that duplicates may be returned.
>>
>> Andrus
>>
>>
>>
>> On Aug 4, 2008, at 4:24 AM, Andrey Razumovsky wrote:
>>
>>> I've looked at the code a bit. Turns out there're some
>>> UNSUPPORTED_DISTINCT_TYPES in SelectTranslator class. Probably if  
>>> "text"
>>> type will be added there, this would let go the exceptions. I'll  
>>> advice to
>>> open a JIRA issue about it.
>>> By now you could try use SQLTemplate. It will not generate any  
>>> "distinct"
>>> modifiers.
>>>
>>> By the way, it seems quite strange to me, that Cayenne adds  
>>> ''distinct'
>>> modifiers automatically e.g. when selecting prefetches, even if
>>> SelectQuery.isDistinct returns false (and it does by default).
>>>
>>> 2008/8/4, Matthias Moeser <matthias@ish.com.au>:
>>>>
>>>> Hi,
>>>>
>>>> On 04/08/2008, at 5:49 PM, Lachlan Deck wrote:
>>>>
>>>> On 04/08/2008, at 5:39 PM, Andrey Razumovsky wrote:
>>>>>
>>>>> This is definitely MS SQL's issue, not Cayenne's. Only thing I can
>>>>>> recommend, if you can allow that, select all rows and then seed
>>>>>> out duplicate rows manually
>>>>>>
>>>>>
>>>>> AFAIK we are not purposely turning on the flag for obtaining  
>>>>> distinct
>>>>> rows. (Well to be fair - we don't have the root stack trace from  
>>>>> ROP so
>>>>> can't be certain if we are or not).
>>>>>
>>>>> But AFAIK Cayenne does turn certain queries into select distinct  
>>>>> (e.g.,
>>>>> joins). Is that right?
>>>>>
>>>>
>>>> The following, for example, is when trying to delete a record an  
>>>> commit the
>>>> context.
>>>>
>>>> 17:49:47,728 [AWT-EventQueue-0] ERROR ish.view.components.Button : 
>>>> 165 -
>>>> Failed to perform action
>>>>   [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0- 
>>>> SNAPSHOT Jul
>>>> 09 2008 01:28:38] Remote error. URL - http://localhost:8181/angel-server-cayenne

>>>> ;
>>>> CAUSE - class com.microsoft.sqlserver.jdbc.SQLServerException The  
>>>> text data
>>>> type cannot be selected as DISTINCT because it is not comparable.
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .remote 
>>>> .hessian.HessianConnection.doSendMessage(HessianConnection.java: 
>>>> 151)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne.remote.BaseConnection.sendMessage(BaseConnection.java:73)
>>>>   [java]     at
>>>> org.apache.cayenne.remote.ClientChannel.send(ClientChannel.java: 
>>>> 281)
>>>>   [java]     at
>>>> org 
>>>> .apache.cayenne.remote.ClientChannel.onQuery(ClientChannel.java: 
>>>> 113)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .util 
>>>> .ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java: 
>>>> 317)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .util 
>>>> .ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
>>>>   [java]     at
>>>> org.apache.cayenne.CayenneContext.onQuery(CayenneContext.java:340)
>>>>   [java]     at
>>>> org 
>>>> .apache.cayenne.CayenneContext.performQuery(CayenneContext.java: 
>>>> 328)
>>>>   [java]     at
>>>> ish 
>>>> .oncourse.cayenne.CayenneContext.performQuery(CayenneContext.java: 
>>>> 294)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .util.RelationshipFault.resolveFromDB(RelationshipFault.java:90)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .util 
>>>> .PersistentObjectList 
>>>> .resolvedObjectList(PersistentObjectList.java:301)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .util.PersistentObjectList.isEmpty(PersistentObjectList.java:207)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .ObjectContextDeleteAction 
>>>> .relatedObjects(ObjectContextDeleteAction.java:204)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .ObjectContextDeleteAction 
>>>> .processRules(ObjectContextDeleteAction.java:138)
>>>>   [java]     at
>>>> org.apache.cayenne.ObjectContextDeleteAction.access 
>>>> $0(ObjectContextDeleteAction.java:128)
>>>>   [java]     at
>>>> org.apache.cayenne.ObjectContextDeleteAction 
>>>> $1.visitToMany(ObjectContextDeleteAction.java:110)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne.reflect.BaseToManyProperty.visit(BaseToManyProperty.java: 
>>>> 102)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .reflect 
>>>> .PersistentDescriptor 
>>>> .visitDeclaredProperties(PersistentDescriptor.java:338)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .reflect 
>>>> .PersistentDescriptor.visitProperties(PersistentDescriptor.java: 
>>>> 371)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .reflect 
>>>> .LazyClassDescriptorDecorator 
>>>> .visitProperties(LazyClassDescriptorDecorator.java:161)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .ObjectContextDeleteAction 
>>>> .processDeleteRules(ObjectContextDeleteAction.java:104)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .ObjectContextDeleteAction 
>>>> .deletePersistent(ObjectContextDeleteAction.java:93)
>>>>   [java]     at
>>>> org 
>>>> .apache 
>>>> .cayenne 
>>>> .ObjectContextDeleteAction 
>>>> .performDelete(ObjectContextDeleteAction.java:78)
>>>>   [java]     at
>>>> org 
>>>> .apache.cayenne.CayenneContext.deleteObject(CayenneContext.java: 
>>>> 282)
>>>>
>>>>
>>>>
>>
>
>


Mime
View raw message