Editable reports - update command denied

(7 posts) (2 voices)

Tags:

No tags yet.

  1. Philip, Member

    Hi, I get this when I try edit an editable report:

    "Could not execute the report. There was an error in the report.
    UPDATE command denied to user 'mydbr'@'10.100.30.91' for table 'mydbr_edit_test'"

    What should I do?
    Thanks,
    Philip

  2. myDBR Team, Key Master

    myDBR is missing permissions to update the table. Use MySQL's GRANT-command to add the permission.

    --
    myDBR Team

  3. Philip, Member

    Can you elaborate please. Does this belong to the procedure that executes the update, or the procedure for the report in which the user inputs their update? What is the syntax? Why do the examples in the manual not mention GRANT? Could you provide an example please.

    Many thanks for your help.

  4. myDBR Team, Key Master

    This is a setup issue. By default, myDBR is installed in a way that myDBR has all permissions to myDBR's own database and SELECT-access to the databases containing the actual data. If you want to update a table in another database (not in myDBR's own), you need to grant update permission to myDBR connection.

    In your case, your myDBR operates under user spcification 'mydbr'@'10.100.30.91' and tries to update table mydbr_edit_test, but lacks the update permission. If the mydbr_edit_test-table is for example in a database 'mydata', you would need to issue a command:

    mysql> GRANT UPDATE ON mydata.mydbr_edit_test TO 'mydbr'@'10.100.30.91'

    You would execute this command when logged in as root (or any other user which has GRANT permission. See MySQL documentation for more info.

    --
    myDBR Team

  5. Philip, Member

    Thanks that worked.

    Unfortunately now I can't get the command 'dbr.editable' to work...

    If I run this report:
    SELECT 'dbr.editable', 1, 'sp_DBR_editable_report_test_executor','inValue=Col1', 'inRank=Rank'; SELECT Col1,Rank FROM scratchpad.mydbr_edit_test

    I get an table with values I can edit, but it does not seem to pass them through to sp_DBR_editable_report_test_executor.

    sp_DBR_editable_report_test_executor is defined as:
    CREATE PROCEDURE sp_DBR_editable_report_test_executor(inValue varchar(40), inRank int) BEGIN UPDATE scratchpad.mydbr_edit_test SET Col1 = inValue WHERE Rank = inRank

    If I run the report that sp_DBR_editable_report_test_executor belongs to, I can input parameter values for inValue and inRank, and the table scratchpad.mydbr_edit_test updates successfully.

    What else am I missing?

    Thanks.

  6. myDBR Team, Key Master

    Hi,
    you only need to define the key for the update in 'dbr.editable'. The updated value will be added by default as a last parameter.

    So when you update the Col1 and have the Rank as key, you do:

    SELECT 'dbr.editable', 'Col1', 'sp_DBR_editable_report_test_executor', 'inRank=Rank';

    and define the edit report as:

    CREATE PROCEDURE sp_DBR_editable_report_test_executor(inRank int, inValue varchar(40))

    --
    myDBR Team

  7. Philip, Member

    That worked thankyou!

    FYI I had actually tried something very similar previously, following the example in the manual, but the difference was I put

    CREATE PROCEDURE sp_DBR_editable_report_test_executor(inValue varchar(40), inRank int)

    - note the only difference is I put inValue first and inRank second... and then I got this error when I opened the first report:
    Edit report 'sp_DBR_editable_report_test_executor' requires parameter 'inValue', which was not supplied. Column 1 will not be editable.

    You might want to updated the manual for other users.

    Thanks again.


Reply

You must log in to post.