You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Matthias Moeser <ma...@ish.com.au> on 2008/08/04 09:09:26 UTC
MS SQL Server: Select Distinct on text datatype
Hi!
We are using Cayenne with Microsoft SQL Server. We get a
com.microsoft.sqlserver.jdbc.SQLServerException: "The text data type
cannot be selected as DISTINCT because it is not comparable."
The problem is that the following query is not allowed in MS SQL Server:
SELECT DISTINCT column FROM table;
where "column" is of datatype "text". So i think that cayenne does
somewhere such a DISTINCT query on a "text" datatype column.
The datatype "text" in MS SQL server is a variable-length data with a
maximum length of 2^31 - 1 (2,147,483,647) characters, Varchar() can
have only maximum 8000 character.
What can I do? I need the "text" datatype.
Thank you
Matthias
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at
org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.jav
a:228)
at
org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.ja
va:450)
Caused by: java.lang.Exception: The text data type cannot be selected as
DISTINCT because it is not comparable.
com.microsoft.sqlserver.jdbc.SQLServerException: The text data type
cannot be selected as DISTINCT because it is not comparable.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Un
known Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrepare
dStatement(Unknown Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.
doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
Source)
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unk
nown Source)
at
org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.j
ava:73)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryActi
on.java:58)
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 5, 2008, at 9:55 PM, Lachlan Deck wrote:
> Are you +1 on providing these with accessors?
Actually no, as there is lots of private state there. I want to hide
as much as possible. I think we are exposing too much stuff at this
level already. If you have a specific adapter customization need,
let's discuss it (or maybe just send a patch to dev list, so the
intention is clear). I still don't understand what are you trying to
achieve with all the proposed changes.
Andrus
Re: MS SQL Server: Select Distinct on text datatype
Posted by Lachlan Deck <la...@gmail.com>.
On 06/08/2008, at 11:32 AM, Andrus Adamchik wrote:
> On Aug 5, 2008, at 9:24 PM, Lachlan Deck wrote:
>
>> Was just thinking that there didn't seem to be any hook available
>> to createSqlString() (which calls the above method) to ensure
>> suppressingDistinct is true for this case.
>
> Sure. I am +1 on such change if we find that overriding it in a
> subclass for a different adapter is indeed helpful.
Actually it appears to me that perhaps the bit we want to subclass is
dbRelationshipAdded(?) which sets forcingDistinct to true. However,
the various variables in SelectTranslator have no getters/setters. Are
you +1 on providing these with accessors?
with regards,
--
Lachlan Deck
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 5, 2008, at 9:24 PM, Lachlan Deck wrote:
> Was just thinking that there didn't seem to be any hook available to
> createSqlString() (which calls the above method) to ensure
> suppressingDistinct is true for this case.
Sure. I am +1 on such change if we find that overriding it in a
subclass for a different adapter is indeed helpful.
Andrus
Re: MS SQL Server: Select Distinct on text datatype
Posted by Lachlan Deck <la...@gmail.com>.
On 06/08/2008, at 10:40 AM, Andrus Adamchik wrote:
>
> On Aug 5, 2008, at 8:27 PM, Lachlan Deck wrote:
>
>> Any objections to adding (or making) isUnsupportedForDistinct non-
>> static? i.e., assuming that SelectTranslator can be overridden.
>
> I don't have any objections per se, but can you elaborate how that
> can help in your situation?
Was just thinking that there didn't seem to be any hook available to
createSqlString() (which calls the above method) to ensure
suppressingDistinct is true for this case.
with regards,
--
Lachlan Deck
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 5, 2008, at 8:27 PM, Lachlan Deck wrote:
> Any objections to adding (or making) isUnsupportedForDistinct non-
> static? i.e., assuming that SelectTranslator can be overridden.
I don't have any objections per se, but can you elaborate how that can
help in your situation?
Andrus
Re: MS SQL Server: Select Distinct on text datatype
Posted by Lachlan Deck <la...@gmail.com>.
Hi there,
On 05/08/2008, at 6:49 AM, Andrus Adamchik wrote:
> Andrey, you are spot on with the UNSUPPORTED_DISTINCT_TYPES.
Any objections to adding (or making) isUnsupportedForDistinct non-
static? i.e., assuming that SelectTranslator can be overridden.
> 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 <ma...@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)
>>>
>>>
>>>
>
with regards,
--
Lachlan Deck
Re: MS SQL Server: Select Distinct on text datatype
Posted by Lachlan Deck <la...@gmail.com>.
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.
Thanks Andrus.
with regards,
--
Lachlan Deck
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <ma...@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)
>>>>
>>>>
>>>>
>>
>
>
Re: MS SQL Server: Select Distinct on text datatype
Posted by Matthias Moeser <ma...@ish.com.au>.
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 <ma...@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.visitDeclaredPropert
>>> ies(PersistentDescriptor.java:338)
>>> [java] at
>>> org.apache.cayenne.reflect.PersistentDescriptor.visitProperties
>>> (PersistentDescriptor.java:371)
>>> [java] at
>>> org.apache.cayenne.reflect.LazyClassDescriptorDecorator.visitPropert
>>> ies(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)
>>>
>>>
>>>
>
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <ma...@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)
>>
>>
>>
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrey Razumovsky <ra...@gmail.com>.
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 <ma...@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)
>
>
>
Re: MS SQL Server: Select Distinct on text datatype
Posted by Matthias Moeser <ma...@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)
Re: MS SQL Server: Select Distinct on text datatype
Posted by Lachlan Deck <la...@gmail.com>.
Hi there,
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?
> 2008/8/4, Matthias Moeser <ma...@ish.com.au>:
>>
>> We are using Cayenne with Microsoft SQL Server. We get a
>> com.microsoft.sqlserver.jdbc.SQLServerException: "The text data
>> type cannot
>> be selected as DISTINCT because it is not comparable."
>>
>> The problem is that the following query is not allowed in MS SQL
>> Server:
>>
>> SELECT DISTINCT column FROM table;
>>
>> where "column" is of datatype "text". So i think that cayenne does
>> somewhere such a DISTINCT query on a "text" datatype column.
>>
>> The datatype "text" in MS SQL server is a variable-length data with a
>> maximum length of 2^31 - 1 (2,147,483,647) characters, Varchar()
>> can have
>> only maximum 8000 character.
>>
>> What can I do? I need the "text" datatype.
>>
>> Thank you
>> Matthias
>>
>>
>> org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
>> at
>> org.mortbay.jetty.bio.SocketConnector
>> $Connection.run(SocketConnector.jav
>> a:228)
>> at
>> org.mortbay.thread.BoundedThreadPool
>> $PoolThread.run(BoundedThreadPool.ja
>> va:450)
>> Caused by: java.lang.Exception: The text data type cannot be
>> selected as
>> DISTINCT because it is not comparable.
>> com.microsoft.sqlserver.jdbc.SQLServerException: The text data type
>> cannot be selected as DISTINCT because it is not comparable.
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Un
>> known Source)
>> at
>> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
>> Source)
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrepare
>> dStatement(Unknown Source)
>> at
>> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement
>> $PrepStmtExecCmd.
>> doExecute(Unknown Source)
>> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown
>> Source)
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
>> Source)
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
>> Source)
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
>> Source)
>> at
>> com
>> .microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unk
>> nown Source)
>> at
>> org
>> .apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.j
>> ava:73)
>> at
>> org
>> .apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryActi
>> on.java:58)
>>
with regards,
--
Lachlan Deck
Re: MS SQL Server: Select Distinct on text datatype
Posted by Andrey Razumovsky <ra...@gmail.com>.
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
2008/8/4, Matthias Moeser <ma...@ish.com.au>:
>
> Hi!
>
> We are using Cayenne with Microsoft SQL Server. We get a
> com.microsoft.sqlserver.jdbc.SQLServerException: "The text data type cannot
> be selected as DISTINCT because it is not comparable."
>
> The problem is that the following query is not allowed in MS SQL Server:
>
> SELECT DISTINCT column FROM table;
>
> where "column" is of datatype "text". So i think that cayenne does
> somewhere such a DISTINCT query on a "text" datatype column.
>
> The datatype "text" in MS SQL server is a variable-length data with a
> maximum length of 2^31 - 1 (2,147,483,647) characters, Varchar() can have
> only maximum 8000 character.
>
> What can I do? I need the "text" datatype.
>
> Thank you
> Matthias
>
>
> org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> at
> org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.jav
> a:228)
> at
> org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.ja
> va:450)
> Caused by: java.lang.Exception: The text data type cannot be selected as
> DISTINCT because it is not comparable.
> com.microsoft.sqlserver.jdbc.SQLServerException: The text data type
> cannot be selected as DISTINCT because it is not comparable.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Un
> known Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrepare
> dStatement(Unknown Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.
> doExecute(Unknown Source)
> at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown
> Source)
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unk
> nown Source)
> at
> org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.j
> ava:73)
> at
> org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryActi
> on.java:58)
>