Query API - Oracle Hibernate issue with table structure

Bryce Roberts's Avatar

Bryce Roberts

12 Feb, 2016 08:47 PM

Hello,

When use the query API with Oracle, the follow is a bug. I suspect it is an underlaying issue with Oracle/Hibernate given the table structure for metadata.

VM Code:
#set($query = $_.query().byContentType("/School Page").sortBy("keywords").sortDirection("desc").execute())

Displayed Error:
An error occurred while rendering asset preview: org.apache.velocity.exception.MethodInvocationException: Invocation of method 'execute' in class com.hannonhill.cascade.velocity.LocatorTool$SearchQuery threw exception org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at velocityTransform-

SQL:
select
   foldercont0_.id as id22_,
   foldercont0_.name as name22_,
   foldercont0_.lockId as lockId22_,
   foldercont0_.isCurrentVersion as isCurren5_22_,
   foldercont0_.versionDate as versionD6_22_,
   foldercont0_.metadataId as metadataId22_,
   foldercont0_.permissionsId as permissi8_22_,
   foldercont0_.workflowId as workflowId22_,
   foldercont0_.isWorkingCopy as isWorki10_22_,
   foldercont0_.parentFolderId as parentF11_22_,
   foldercont0_.relativeOrder as relativ12_22_,
   foldercont0_.cachePath as cachePath22_,
   foldercont0_.isRecycled as isRecycled22_,
   foldercont0_.recycleRecordId as recycle15_22_,
   foldercont0_.workflowComment as workflo16_22_,
   foldercont0_.draftUserId as draftUs17_22_,
   foldercont0_.draftAssetFactoryId as draftAs18_22_,
   foldercont0_.siteId as siteId22_,
   foldercont0_.prevVersionId as prevVer20_22_,
   foldercont0_.nextVersionId as nextVer21_22_,
   foldercont0_.originalCopyId as origina22_22_,
   foldercont0_.workingCopyId as working23_22_,
   foldercont0_.referenceBlockId as referen24_22_,
   foldercont0_.referenceFileId as referen25_22_,
   foldercont0_.referenceFolderId as referen26_22_,
   foldercont0_.referencePageId as referen27_22_,
   foldercont0_.referenceTemplateId as referen28_22_,
   foldercont0_.referenceStylesheetId as referen29_22_,
   foldercont0_.referenceSymlinkId as referen30_22_,
   foldercont0_.draftOriginalId as draftOr31_22_,
   foldercont0_.xmlId as xmlId22_,
   foldercont0_.fileBlobId as fileBlobId22_,
   foldercont0_.displayName as display34_22_,
   foldercont0_.title as title22_,
   foldercont0_.summary as summary22_,
   foldercont0_.teaser as teaser22_,
   foldercont0_.keywords as keywords22_,
   foldercont0_.description as descrip39_22_,
   foldercont0_.author as author22_,
   foldercont0_.startDate as startDate22_,
   foldercont0_.endDate as endDate22_,
   foldercont0_.reviewDate as reviewDate22_,
   foldercont0_.metadataSetId as metadat44_22_,
   foldercont0_.expirationNoticeSent as expirat45_22_,
   foldercont0_.firstExpirationWarningSent as firstEx46_22_,
   foldercont0_.secondExpirationWarningSent as secondE47_22_,
   foldercont0_.expirationFolderId as expirat48_22_,
   foldercont0_.maintainAbsoluteLinks as maintai49_22_,
   foldercont0_.structuredDataDefinitionId as structu50_22_,
   foldercont0_.pageConfigurationSetId as pageCon51_22_,
   foldercont0_.structuredDataId as structu52_22_,
   foldercont0_.pageStructuredDataVersion as pageStr53_22_,
   foldercont0_.shouldBeIndexed as shouldB54_22_,
   foldercont0_.shouldBePublished as shouldB55_22_,
   foldercont0_.lastDatePublished as lastDat56_22_,
   foldercont0_.lastPublishedBy as lastPub57_22_,
   foldercont0_.contentTypeId as content58_22_,
   foldercont0_.folderIsRoot as folderI59_22_,
   foldercont0_.folderPublishMergeFolderId as folderP60_22_,
   foldercont0_.folderWorkflowInheritDefs as folderW61_22_,
   foldercont0_.folderWorkflowRequire as folderW62_22_,
   foldercont0_.fileRewriteFileContents as fileRew63_22_,
   foldercont0_.byteLength as byteLength22_,
   foldercont0_.blockIndexedFolderId as blockIn65_22_,
   foldercont0_.blockIndexedContentTypeId as blockIn66_22_,
   foldercont0_.blockType as blockType22_,
   foldercont0_.indexBlockType as indexBl68_22_,
   foldercont0_.blockFeedURL as blockFe69_22_,
   foldercont0_.blockIndexTypesBitmask as blockIn70_22_,
   foldercont0_.blockIndexContentBitmask as blockIn71_22_,
   foldercont0_.blockIndexOtherBitmask as blockIn72_22_,
   foldercont0_.blockSortOption as blockSo73_22_,
   foldercont0_.blockSortOrder as blockSo74_22_,
   foldercont0_.blockDepthOfIndex as blockDe75_22_,
   foldercont0_.blockIncludePageXML as blockIn76_22_,
   foldercont0_.blockMaxRenderedEntities as blockMa77_22_,
   foldercont0_.blockTwitterAccountName as blockTw78_22_,
   foldercont0_.blockTwitterSearchString as blockTw79_22_,
   foldercont0_.blockTwitterQueryType as blockTw80_22_,
   foldercont0_.blockTwitterDefaultStyle as blockTw81_22_,
   foldercont0_.blockTwitterExcludeJquery as blockTw82_22_,
   foldercont0_.templateStylesheetId as templat83_22_,
   foldercont0_.templateTargetId as templat84_22_,
   foldercont0_.symlinkLink as symlink85_22_,
   1 as formula0_,
   1 as formula2_,
   1 as formula3_,
   1 as formula4_,
   foldercont0_.assetType as assetType22_
from
   cxml_foldercontent foldercont0_
left outer join
   cxml_site site1_
      on foldercont0_.siteId=site1_.id
where
   foldercont0_.isCurrentVersion=1
   and foldercont0_.isWorkingCopy=0
   and foldercont0_.isRecycled=0
   and (
      assetType in (
         ?
      )
   )
   and foldercont0_.contentTypeId=?
   and site1_.name=?
   and (
      assetType='BLO'
      or assetType='SYM'
      or foldercont0_.shouldBeIndexed=1
   )
   and (
      foldercont0_.startDate is null
      or foldercont0_.startDate<=?
   )
   and (
      foldercont0_.endDate is null
      or foldercont0_.endDate>=?
   )
order by
   foldercont0_.keywords desc

SQL Error:
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB

  1. Support Staff 1 Posted by Tim on 15 Feb, 2016 03:38 PM

    Tim's Avatar

    Hi Bryce,

    Thanks for the report. I was able to confirm this behavior as well and it appears to affect the other fields that are CLOB types in the database (teaser, description, summary). I've gone ahead and filed an issue in our project. I don't have an ETA for a fix at the moment, but I can let you know once we've implemented a fix.

    Let me know if you have any questions in the meantime.

    Thanks

  2. Ryan Griffith closed this discussion on 04 Mar, 2016 10:11 PM.

Discussions are closed to public comments.
If you need help with Cascade CMS please start a new discussion.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac

 

26 Aug, 2016 01:19 PM
25 Aug, 2016 03:02 PM
25 Aug, 2016 12:50 PM
24 Aug, 2016 08:43 PM
24 Aug, 2016 07:20 PM
21 Aug, 2016 01:20 PM