'Execute database query' trigger action doesn't appear to be working

Squiz Matrix Support
1

Hi,


Further to this topic (http://forums.squizsuite.net/index.php?showtopic=8445), I’ve decided to try the ‘Execute Database Query’ action to achieve the desired result.



Currently my test trigger is set up to fire when a PDF, Word, PowerPoint or Excel file is created beneath my test standard page. In practice the root node would be our entire Intranet as people can upload files just about anywhere on the site.



The only action on this trigger is ‘Execute Database Query’.



The query is:


    UPDATE sq_ast_lnk SET "value"='files' WHERE minorid LIKE '%asset_assetid%';


I should point out that I can successfully run this query in pgAdmin III.

Initially I used an existing DB Connector, but it turned out to be a read-only user so I got a permission denied error, as well as two trigger failure notices.

I replaced the DB connector with Database details, using my own username and password (as used in pgAdmin). I stopped getting the permission denied error but continued to get the two notices.

They are:

    
    MySource Notice
    File: [SYSTEM_ROOT]/core/include/locale_manager.inc
    LINE: 547
    Message: Trigger Action: trigger_action_execute_database_query Failed [CORE0135]


AND

    
    MySource Warning
    File: [SYSTEM_ROOT]/core/include/locale_manager.inc
    Message: FAILURE: Trigger "Add link value to file assets for display purposes" (Trigger# 105), Event "trigger_event_asset_created" broadcast by "2010-Dec-08.pdf" (Id# 45746) [CORE0127]


I've also tried different events - Link created (same sort of notices); After Asset Built (didn't appear to do anything); and Asset Accessed (didn't appear to do anything).

I'm not sure if this is a bug, or if it's a problem with the trigger or the connection to the database. I'd really appreciate some advice on this one!

Thanks,

Margaret
2

[quote]
Hi,



Further to this topic (http://forums.squizsuite.net/index.php?showtopic=8445), I’ve decided to try the ‘Execute Database Query’ action to achieve the desired result.



Currently my test trigger is set up to fire when a PDF, Word, PowerPoint or Excel file is created beneath my test standard page. In practice the root node would be our entire Intranet as people can upload files just about anywhere on the site.



The only action on this trigger is ‘Execute Database Query’.



The query is:


    UPDATE sq_ast_lnk SET "value"='files' WHERE minorid LIKE '%asset_assetid%';


I should point out that I can successfully run this query in pgAdmin III.

Initially I used an existing DB Connector, but it turned out to be a read-only user so I got a permission denied error, as well as two trigger failure notices.

I replaced the DB connector with Database details, using my own username and password (as used in pgAdmin). I stopped getting the permission denied error but continued to get the two notices.

They are:

    
    MySource Notice
    File: [SYSTEM_ROOT]/core/include/locale_manager.inc
    LINE: 547
    Message: Trigger Action: trigger_action_execute_database_query Failed [CORE0135]


AND

    
    MySource Warning
    File: [SYSTEM_ROOT]/core/include/locale_manager.inc
    Message: FAILURE: Trigger "Add link value to file assets for display purposes" (Trigger# 105), Event "trigger_event_asset_created" broadcast by "2010-Dec-08.pdf" (Id# 45746) [CORE0127]


I've also tried different events - Link created (same sort of notices); After Asset Built (didn't appear to do anything); and Asset Accessed (didn't appear to do anything).

I'm not sure if this is a bug, or if it's a problem with the trigger or the connection to the database. I'd really appreciate some advice on this one!

Thanks,

Margaret
[/quote]

Hi Margaret,

Try to use 
    UPDATE sq_ast_lnk SET "value"='files' WHERE minorid LIKE %asset_assetid%;
as the query. The quotes around the keyword aren't need as matrix will use var binding before evaluating them.

Also on the side note "After Asset built" will not work in this case unless you are using Asset Builder Page to create new assets.

Hope this helps.

Ash
3

[quote]
Hi Margaret,



Try to use 

    UPDATE sq_ast_lnk SET “value”=‘files’ WHERE minorid LIKE %asset_assetid%;
as the query. The quotes around the keyword aren’t need as matrix will use var binding before evaluating them.



Also on the side note “After Asset built” will not work in this case unless you are using Asset Builder Page to create new assets.



Hope this helps.



Ash

[/quote]



Hi Ash,



Still no luck unfortunately. I’ve tried it the way you suggest, without the quotes around the %asset_assetid%, but I still get the same notice and warning.



Oh ok, thanks for explaining ‘After Asset built’… I actually want it to fire on Asset Created but I thought I’d try some different events just to rule out that being the problem.



Thanks,



Margaret
4

[quote]
Hi Ash,



Still no luck unfortunately. I've tried it the way you suggest, without the quotes around the %asset_assetid%, but I still get the same notice and warning.



Oh ok, thanks for explaining 'After Asset built'… I actually want it to fire on Asset Created but I thought I'd try some different events just to rule out that being the problem.



Thanks,



Margaret

[/quote]

Hi Margaret,

It is very strange as I was trying the query you mentioned before on my system and it was indeed coming up with all those errors. But after removing the quotes it was executing it smoothly. Are you still trying it on Asset Created Trigger action?



Are the error still the same or throwing different errors now?



Ash

5

[quote]
Hi Margaret,

It is very strange as I was trying the query you mentioned before on my system and it was indeed coming up with all those errors. But after removing the quotes it was executing it smoothly. Are you still trying it on Asset Created Trigger action?



Are the error still the same or throwing different errors now?



Ash

[/quote]



Hi Ash,



Yes, I've just checked and it's definitely on Asset Created. The errors are identical to before.



Margaret



EDIT:



I should add that we're on version 4.2.4, so I don't know if that helps…

6

Hi,


Does anyone else have any idea why this might not be working for us?



It doesn't appear to be a permission thing as I don't get a permission denied error, and I can run the same query in pgAdmin.



Thanks!



Margaret