JSR-170 Retrurning All Revisions

Rhythmyx Technical Discussion
1

I’m trying to pull all items from a new content type (aeaTestimonials). The JSR-170 query returning all revisions when I ask for particular fields.

For example, the following returns only the latest revision.

SELECT rx:sys_contentid, rx:sys_contentstartdate, rx:brand, rx:sys_revision FROM rx:aeaTestimonial

However, if I ask for either “rx:quotesource”, a local field, or “rx:body”, a shared field, or both…

SELECT rx:sys_contentid, rx:sys_contentstartdate, rx:brand, rx:sys_revision, rx:body, rx:quotesource FROM rx:aeaTestimonial

…then all revisions are returned.

Any ideas?

2

I’ve done some quick testing and the problem appears to be caused by including in your query fields that are backed by CLOB or BLOB fields. This would generally be fields using the sys_editlive control (Ephox), or fields containing file uploads.

Can you verify if this matches the behavior you are seeing?

3

Through one iteration, I did use an EditLive control for the rx:body. Replacing the field with a TextArea or with a EditBox did not change the output.

I did manage to correct the problem, but it took completely deleting the content type and recreating it. There is no difference in the structure between what I deleted and what I recreated.

At the moment, I’m blaming squirrels.

4

Actually, changing the control will not change the data-type of the column used in the database. Changing the size of a text field from “max” to something specific (i.e. 255) will change the backend type from a CLOB to a varchar based field.

5

Yep. I should have been more specific. Each time I changed the control, I also changed made sure to change the size of the text field. I didn’t use “max” for any of my attempts, but used a specific value instead.

It is good to know that “max” will change it to a CLOB data type. It makes sense, but I doubt I would have realized it right away.

Thanks, Jay.

6

Just an FYI for you detail oriented folks. The actual cutoff is 4000. If the field size is this value or less, you get a VARCHAR, otherwise a CLOB for the DB column data type.