HOWTO: re-loading object from database

[simon]

(This rough howto is in reply to an offline question from pattern2)

Usecase: A table has a trigger defined, which updates a timestamp upon insert/update of a row.  The new timestamp values needs to be loaded from the database right after an insert/update from an ActiveGrid application.

Solution: Invoke the dataservice refreshObjects API from a local service right after the dataservice commit() call.

Setup:

This howto uses the Postgres database.  Create table, function and trigger:

(Note that you must have run "CREATE LANGUAGE plpgsql" as the postgres superuser, otherwise you may have trouble with the function definition)

CREATE TABLE update_test(id int, name varchar(20), last_updated time, CONSTRAINT pk_update_test PRIMARY KEY (id));

CREATE FUNCTION stamp_table() RETURNS trigger AS $stamp_table$
    BEGIN
        NEW.last_updated := current_timestamp;
        RETURN NEW;
    END;
$stamp_table$ LANGUAGE 'plpgsql';

CREATE TRIGGER stamp_table BEFORE INSERT OR UPDATE ON update_test
    FOR EACH ROW EXECUTE PROCEDURE stamp_table();

Steps in ActiveGrid:

1. Use the ActiveGrid Application Wizard to create a new application based on this table.  Note that there's a bug in 2.2, requiring you to change the type of the 'lastUpdated' col in the .xsd from 'time' to 'string'.  If you don't do this, updating a record will cause a ConcurrencyException.  

2. In the PageFlow, find the "update_TestEdit" xform, right click on the update_testUpdate action -> "Customize Action".

3. Below the "commit" in the action, add a new local service invokation (python).  This is where we're going to "refresh" the object, right after it has been inserted into the database. 

4. The local service's input message has a single part, of type 'update_test', no output message.

5. In the python code, invoke 'refreshObjects' on the dataservice instance, with the passed in 'update_test' instance:

def refreshObject(obj):
<indentation required here>runtime.getDataService().refreshObjects(obj)

6. In the custom action, don't forget to populate the local service input message with a 'set', something like this: inputVar/obj = message/update_test (my local service input message part is called 'obj').

[dataspill]

Thanks Simon, it works. One item, I thought I'd live on the wild side and not convert the col datatype as you suggested, just to see if it blew up, and in fact I don't see any exception. My details vary slightly from your example, the xml schema type for the col is datetime and the SQL type is timestamp. I'm running 2.2.2.

I'll be needing this kind of functionality for most of my update pages. I can work with this solution, no problem. I suppose it'd be ideal to have a global settting that instructs ActiveGrid to refresh data automatically; or maybe have it as a page level setting; or have a param to the Commit dataservice that adds the extra step.

And...I guess posting this kind of note on a HowTo item should be done here, rather than in the Help forum?

[dataspill]

Will this technique mean that a new local service invocation need to be created for each complex type that needs to be refreshed on update? Doable, but not ideal of course. As far as I can see, the input message type is specific. Would ActiveGrid support the creation of a local service like this that expects a somewhat more generic input message?

[simon]

Hey pattern2,

The bug only happens with the date xsd type I think. 

To make it easier to invoke, we could add "refreshObjects" to the list of "builtin" dataservice operations, so you could directly call it from your bpel action (in the same way the commit is called, no need to drop into code).  Would that work for you?

Simon

[dataspill]

Yes a solution like that would be extremely useful.

I wonder if there is any perceptible overhead with refresh calls? Maybe in the long run it'd be possible to extend the feature you're suggesting to that it'd have the option to refresh just a specific field or set of fields, so the entire record wouldn't have to be pulled.

[dataspill]

Hi Simon

Do you think that the new dataservice RefreshObjects call will be included in 2.3? Else, I need to add it manually for the app I'm building at this time. I can do that, but if it's coming the in 2.3 timeframe, it'll be simpler to implement via a RefreshObjects type call.

Thanks

[Geremy Cohen]

Hi Pattern-

Currently, its slated for either 2.3 or a post 2.3 (eg 2.3.1) bug fix.

Because of its relative priority, I may know more about its fate in a couple weeks whether or not it makes it into 2.3 for sure.

geremy