How to add and update data using Run without a user form macro

This article explains how to create the form to add and update data in the database using a Groovy script.

Before we proceed with the instructions, below are the prerequisite:

1. Run Self-Service Reports app.
2. SQL for Confluence (Pro Edition) app.
3. Follow this link to configure the data source profile for SQL for Confluence app.

Instructions

1. Log into your Confluence instance.

2. Navigate to the required page and click .

3. Insert Wiki Markup macro to write the script.

   {wiki}
   {run:titleRun=DATA Display|anchor=result}
   
           {run}
   
             {run:id=update|replace=choice:Add::radio::Add::Update,id:1:Id (for update):integer-select:1:1000,type:dog:Select DB:select::MYSQL_new user:MYSQL_new          user:Mysql_confluence:Mysql_confluence,hand1:default:operational_hand_off_status:text,hand2:default:project_status:text,hand3:default:Resource Type:text|titleRun=Add|autoRun=true|anchor=result|focus=choice}
              {anchor:result}
   
               {hide:showIf=Add:$choice}
   
               {sql:dataSource=Mysql_confluence|contentPropertyPrefix=sql.}
   
               INSERT INTO report (Project_status,Resource_type,operational_hand_off_status)VALUES('$hand2','$hand3','$hand1');
   
               {sql}
   
               *Row Updated*
   
               {hide}
   
               {hide:hideIf=Add:$choice}
               {sql:dataSource=Mysql_confluence|contentPropertyPrefix=sql.}
               UPDATE report set Project_status = '$hand2', Resource_type ='$hand3',operational_hand_off_status = '$hand1' where report_id = $id
               {sql}
               {hide}
           
   {run}
           
     {run-now}
               {hide:showIf=1:%property_sql.update_count%}
               *Row Added*
               {hide}
   
     {run-now}
               ----
               h3. Results
               {sql-query:dataSource=MYSQL_new user}
               select report_id,Project_status,Resource_type,operational_hand_off_status from report
               {sql-query}
          
     
   {wiki}

4. Click on the Save button and the form will be displayed below.
   
   
5. Select the option Add and enter the data. Click on Add button.
   
6. Data will be added to the database and displayed as below.
   
7. Select the option Update and enter the data. Click on Add button.