[opencms-dev] SQL Server and CONCAT Error

[Georgi Naplatanov]

Hello, Richard, may OpenCms with JPA driver will a good options for.
Performance loss on PostgreSQL compared to native SQL driver is not so big.

You can download compiled OpenCms war file with jpa driver from

http://os.oles.biz/opencms/jpa-driver/opencms-jpa-20081003.war.

Another option is to rewrite query according MS SQL  server rules.

For example JPQL may use "locate" function as equivalent expression
ORACLE use || oprator

JPQL query is

C_READ_RESOURCE_OUS=\
SELECT DISTINCT \
	${C_RELATIONS_SELECT_ATTRIBS} \
FROM \
	CmsDAO${PROJECT}ResourceRelations T_CmsDAO${PROJECT}ResourceRelations \
WHERE \
    T_CmsDAO${PROJECT}ResourceRelations.m_relationType = ? \
    AND LOCATE(T_CmsDAO${PROJECT}ResourceRelations.m_relationTargetPath,
?) <> -1


My be SQL query for MS SQL should seems like this
# MS SQL specific because of missing concat function: use concat operator +
C_READ_RESOURCE_OUS=\
SELECT DISTINCT \
 ${C_RELATIONS_SELECT_ATTRIBS} \
FROM \
  CMS_${PROJECT}_RESOURCE_RELATIONS \
WHERE \
  CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TYPE = ? \
  AND ? LIKE (CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH || '%')

Best regards
Georgi

PS: Vahe Momjyan had the same problem, but didn't post for query
rewriting or status on JPA driver with MS SQL server.

opencms . wrote:
> Hey all
> 
> I've worn out Google looking for a solution to my problem and this is my
> last resort. I have everything installed and running except I can't see
> context menu's within the workplace. I've narrowed it down to the fact
> CONCAT doesn't exist for SQL Server. I have tried modifying the
> query.properties file within the opencms.jar file to include the following.
> 
> # MS SQL specific because of missing concat function: use concat operator +
> C_READ_RESOURCE_OUS=\
> SELECT DISTINCT \
>   ${C_RELATIONS_SELECT_ATTRIBS} \
> FROM \
>   CMS_${PROJECT}_RESOURCE_RELATIONS \
> WHERE \
>   CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TYPE = ? \
>   AND ? LIKE (CMS_${PROJECT}_RESOURCE_RELATIONS.RELATION_TARGET_PATH + '%')
> 
> Which has been suggested by a few people. But as soon as I stick the .jar
> file back into WEB-INF\lib I can't start opencms back up. Do I have to do
> anything special when creating the .jar file? Does someone have a working
> .jar file for SQL Server that they can e-mail me? Any help on the matter
> would be greatly appreciated.
> 
> Thanks. Richard.
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev