ActiveObject issues with MSSQL Server when using string fields of unlimited length

[Mibex_Software]

Hi,

I have a Stash add-on using ActiveObjects with entities that have string fields of unlimited length. Here's an example:

@Accessor(COMMENT_TEXT_COLUMN)
@StringLength(StringLength.UNLIMITED)
public String getCommentText()

This all (for other databases) works fine, except for MSSQL Server, where when I try to create an entity I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: varbinary is incompatible with ntext
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:113) ~[na:na]
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:299) ~[na:na]

When I try to execute a query which uses this entity, I get the following error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The ntext data type cannot be selected as DISTINCT because it is not comparable.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc-4.0.2206.jar:na]
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285) ~[sqljdbc-4.0.2206.jar:na]

I've seen various issues about the use of varchar, nvarchar, ntext and how they all get mapped in ActiveObjects to MSSQL server data types. My question is: what are the best practices here? Am I not allowed to use strings of unlimited length together with MSSQL server with ActiveObjects?

Any help is much appreciated.

Thanks,

Michael

[Mibex_Software · Accepted solution]

I was able to find a solution for this now. See here for reference: https://ecosystem.atlassian.net/browse/AO-681