DB Action binding in database publishing

Rhythmyx Technical Discussion
1

Ok maybe I’m just misunderstanding what I’m reading in the implementation guide but I’ve setup my database publishing, which works. EXCEPT I don’t want to use the db.action = “r”. After the row has been created I have a form on the website that will be inserting some values into the row, so I can’t have Rhythmyx delete/replace the row. According to the implementation guide I can use db.action=“n” which “inserts a row for the content if it does not already exist”. SO I assume if the content does exist, it does nothing. Unforunately this doesn’t seem to be the case as I get a primary key violation error, so its trying to create a second row.

Is my assumption correct? If so, any thooughts on whats going on? If I’m wrong, is there a way to do what I"m looking for?

Any and all help is well appreciated.

Shane

2

Shane,

After some investigation, we’ve determined that the documentation for both options is incorrect.

The “n” option always attempts to insert a new row. If the table already contains a row matching the inserted data, the operation fails, as you experienced.

The “r” option replaces the existing data in the specified columns. It does not delete the row before replacing the data. Only the columns specified in the bindings are updated.

So you can fulfill your requirement using the “r” action, and specifying only the columns containing content managed by Percussion CM in the DB publishing Template bindings.

I have corrected the documentation. The corrected content will be included in the next release of Percussion CM System.

RLJII

3

Thanks for the update Robert. Unfortunately the “r” option won’t work for me either because it only does a update. I need the publishing to create the initial records and then update only afterwards. What was described in the guide for “n” would be exactly what I’m looking for. Is there anyway to get the action “n” to work or is there another action that would do that?

Shane

4

Shane,

In my database publishes, I’ve had to build 3 separate templates:

*_prep which removes child table entries in the target database in preparation for updates,
*_n which updates or creates the main and child table rows, and
*_d which deletes rows in both child and main tables when the item is archived.

The _prep and _d templates only define the key columns for selecting against and removing. I’ve fount that if I don’t do this and the number of child table rows decreases between revisions, i have stale child table rows left in the target database.

The _n template uses a binding of $db.action = “r” and the other column bindings are as you’d expect.

This does the job for me. It’s a hack, I know, but it’s the best I could come up with.

– Sam

5

Sorry my last reply to Roberts message was incorrect. The “r” option appears to be creating new records but it appears to delete the record first and then re-creates it. When I specify only specific columns it updates those but columns that i enter values in manually, get overwritten as well. I’ve made sure their not in the bindings or in the XML.

I’ve also just tried the U option to see what it does, and it too is overwritting my manual columns, so I’m getting confused with these options as they all appear to do the same thing. Am I missing something?

SHane

6

True. I’ve had support tickets on this very issue before. And eventually gave up on it. My advice, have your manual columns in another table and have your front-end select against a view that combines the 2.

7

Thanks for the suggestion Sam, unfortunately that won’t work either as I need the content ID of the content item to be inserted into both tables. When the page is generated that content id is also used as part of my form.

8

Well… there’s always the kludge option: load the old record into a binding using $rx.db.get() and output it in the column you don’t want to change.

9

Support has created a ticket and will be looking into this issue.

10

Although Scott is looking into this as well (and hopefully he’ll find a solution for us), I was looking into use the $rx.db.get() option, but of course I’ve run into issues with this as well.

Here’s some basics. I have the following tables:

parent table (contentid)
child table (contentid of parent item, contentid of child items)

The child items are from a slot and I’m currently retrieving those using

$slotcontent=$user.psoSlotTools.getSlotContents($sys.assemblyItem,“cicaPollAnswersSlot”, params)

The value I would need to retrieve is from the Child table, a column called VoteCount. There would be one value per record.

What I’ve specified as the rx.db.get() is the following:


$rx.db.get("CICADB","SELECT VoteCount FROM PollChoices WHERE  PollID='" +
$sys.item.getProperty("rx:sys_contentid").String + "' AND PollChoiceID='" +
$user.psoSlotTools.getSlotPropertyValues($slotcontent,"rx:sys_contentid") + "'")

Unfortunately it’s retrieving both child records votecount (at least that is what I’m assuming its doing), so i get the following exception:

Unexpected exception while assembling one or more items: Problem when evaluating expression “$rx.db.get(“CICADB”,“SELECT VoteCount FROM PollChoices WHERE PollID=’” + $sys.item.getProperty(“rx:sys_contentid”).String + “’ AND PollChoiceID=’” + $user.psoSlotTools.getSlotPropertyValues($slotcontent,“rx:sys_contentid”) + “’”);” for variable “$getVoteCount”: Conversion failed when converting the varchar value ‘[33640, 33641]’ to data type int.

My question is, is my assumption correct? If so, what can I do to retrieve only the one value at a time? My guess would be to go through a loop or something but I"m not sure how to do that in bindings.

11

Yeah… You are asking your database engine to convert the string “[33640, 33641]” into a single number with the intention, I assume, of just getting 1 row of result. Instead, you should get several rows at once.

You’ll want an SQL query that evaluates to something like:

SELECT VoteCount FROM PollChoices WHERE  PollID=12345 AND PollChoiceID IN ( 33640, 33641 ) order by PollChoiceId

The easiest way to do that will be to use Java’s Array’s toString() method (which you’re already using even thought you may not realize it) and String’s replaceAll() method.

Try this code instead:

$rx.db.get("CICADB","SELECT VoteCount FROM PollChoices WHERE  PollID=" +
$sys.item.getProperty("rx:sys_contentid").String + " AND PollChoiceID in (" +
$user.psoSlotTools.getSlotPropertyValues($slotcontent,"rx:sys_contentid").String.replaceAll('[\[\]]', '') + ") order by PollChoiceId")

I think I have the escaping correct in that regex, but if it bugs out, try ‘[\[\]]’ too. Also, you may have to use toString() instead of String… I’m not sure if velocity maps that method for all objects or just ones it recognizes.

I’m anticipating the following issues with this strategy though:
[ol]
[li]You’ll need to sort the results of the query in the exact same order as the slotted content items will appear so that you can bind the correct values. Try $tools.sorter.sort($slotItems, “rx:sys_contentid”) - you’ll need to create the appropriate array of items, though, and you can use $user.psoSlotTools.getSlotContents() for that.
[/li][li]You may have trouble with related items that are removed from the item relationships not being removed from the target database.
[/li][/ol]

12

Sam is right - you are using .String in one case and default datatype for contentid (int) in another - convert all to String.

13

First off Happy New Year everyone.

I’ve giving this a break for a while as it was giving me headaches, I’ve now come back to this. I’ve been trying to hard to figure this out on my own, reading documentation but something is just not clicking for me with this one.

So I’ve simplified my SQL query to the following:

$getVoteCount = $rx.db.get(“CICADB”,“SELECT VoteCount FROM PollChoices WHERE PollID=” +
$sys.item.getProperty(“rx:sys_contentid”).String + " ORDER BY PollChoiceID")

Which is working fine and pulling the correct data. The issue is that it is returning results like the following:

{VoteCount=1}

So of course when it tries to insert, its inserting {VoteCount=1} into the database. How can I remove the {VoteCount= } part and just leave the number?

I’ve tried $getVoteCount.replaceAll(‘VoteCount’,’’) but it tells me it can’t find the method. I’ve only used the replaceAll function in the $sys.item… function so I’m not sure where all I can use it. Do I need to convert the $getVoteCount to a string? I’ve tried rx.string.mapToString($getVoteCount) but that wouldn’t work either (which I didn’t expect it too).

Any help or suggestions you could give me would be very much appreciated.

Shane

14

Hi Shane

As the rx.db.get returns a list you need to get the first item in the list. Each item in the list uses the column names in the SQL query as properties. So to get your value you do:

$getVoteCount[0].VoteCount

Cheers
James

15

Thanks James, that would make sense. So based on that information plus the information from Sam, I’m starting to understand how this is working exactly (i think). So the values for getVoteCount are placed in a list (or an array essentially) so I have to loop through that to get each of the values. Unfortunately what I assumed would happen, happened, it only takes the last value and inserts that because it finishs the loop (not sure if that makes sense).

What I need to have happen is similar to the user.psoSlotTools.getSlotProprtyValues() where it inserts 1 value, then moves or creates a second record (row) for the next value.

At this point I’m not even sure if I can do this through regular bindings or if I have to create a new class or something. Thoughts anyone?

Shane

16

Not exactly, Shane, but close.

All database queries return an array of rows. Even if your query results in only 1 row, or even 0 rows, you’ll still get an array.

Every row is essentially a Map<String,String> java object, where the key values in the map are the column names. Even if you’re querying only 1 column, you’ll still get a map object for the row.

Every value in the map begins life as a String object, even number columns. If you want a number, integer, floar or whatever, you’ll have to parse the value.

So… if you have the command:
#set( $resultTable = $db.get(’’, ‘select foo, bar from baz’) )

You’ll get a result table which in JavaScript Object Notation (JSON) looks something like (assuming the tables in your head are as weird as the ones in mine):

[
{ foo=“The quick brown fox”, bar=“jumps over the lazy dog.” },
{ foo=“Every good boy”, bar=“deserve fudge.” },
{ foo=“Good deeds are”, bar=“ever bearing fruit.” }
]

[ and ] denote the array object and , delimits the elements in it.
{ and } denote the map objects with = denoting the key-value pairs and , delimiting the pairs.

This is still not answering your question tho.

You’re only getting one value in your result table because your query only returns 1 row, and that row only has 1 column.

17

Shane,

See the discussion of database publishing in the Version 6.7 Rhythmyx Implementation Guide, specifically “Defining Bindings to Publish Local Child Data”, p. 382. We revised the content on database publishing to clarify the implementation. The template implementation works the same way in Version 6.5.2 (which your profile indicates is the version you are currently using).

RLJII

18

Finally got it working. Thanks Sam for the explanation, thats pretty much what I had in my head, I just couldn’t explain it. Thanks Robert for your suggestion as well. After going back to the implementation guide, the answer was right there, all i had to do was just read

$rx.asmhelper.mapValues($getVoteCount,‘VoteCount’)

Originally when I had read the guide I wasn’t getting data from the database so I kind of skipped that section, so my bad. But thanks to everyone for the information you did provide, it did help me quite a lot and your explaination were a little more then what is in the guide so that helps me a lot as well as I try to learn this stuff.

Shane

19

[QUOTE=Rushing;15692]…In my database publishes, I’ve had to build 3 separate templates:

*_prep which removes child table entries in the target database in preparation for updates,
*_n which updates or creates the main and child table rows, and
*_d which deletes rows in both child and main tables when the item is archived.

The _prep and _d templates only define the key columns for selecting against and removing. I’ve fount that if I don’t do this and the number of child table rows decreases between revisions, i have stale child table rows left in the target database.

The _n template uses a binding of $db.action = “r” and the other column bindings are as you’d expect.

This does the job for me. It’s a hack, I know, but it’s the best I could come up with.[/QUOTE]

I’ve been having a look at this in 6.7 and have created a “_prep” template which is used to delete the rows ($db.action=‘d’) in order to remove any stale rows. I have two content lists in my edition with the same query, but the first one calls the _prep template and the second one uses my regular ‘publish’ template.

Looking at the publishing logs in the web based GUI I see that it logs the _prep template first and then the regular publish one, but if I turn SQL statement debugging on through log4j.xml and monitor my server log I get SQL UPDATE statements first followed by DELETE statements - meaning that I’m left with nothing in my published table!

Have you experienced this? It seems that the CMS prepares all the database statements in advance and then during the ‘committing’ phase of publishing, runs them in an order that doesn’t tie up with the actual publishing log order that is visible through the publishing logs GUI.

What I don’t want to do is have two editions configured - one for the ‘prep’ template and then one edition to repopulate the table and then run them one after another other.

I’d be interested to see if you ran into this problem at all?

20

You’re probably using a single content list to run all 3 templates. Don’t do this. Same issue you’re having, I had myself for a while until I stopped trying to make all 3 templates run on a single clist.

Instead, have 3 content lists each with 3 different priorities… I like to use 10 for _prep, 100 for _n and 1000 for _d.