Workaround for parameterized SQL queries in the cloud

Following the Confluence Data Center-to-Cloud migration, parameterized SQL queries may fail with the following error: “Incorrect syntax near the question mark (?)”. Parameterized queries that worked in Data Center are currently not supported in Cloud. Follow the workaround to replace the parameter placeholders in queries with actual values.

• Prerequisites and setup

• Step-by-step mode

• Direct Input Mode

1. Download the attached script and open it in a browser. The script will display the graphical user interface.

2. Enter the email address associated with your Confluence Cloud account and an API token generated for the same instance. Follow these steps to generate an API token:

   1. Navigate to https://id.atlassian.com/manage-profile/security/api-tokens.

   2. Create a new token:

   3. Copy the generated token and use it in the authentication window opened in step 1.

3. After successful authentication, the following window will appear:

   1. Change the Confluence base URL to the URL of your test instance.

   2. Give access to the CORS Demo API by clicking corsdemo from the Error section and allowing temporary access.

   3. Grant temporary access to the CORS Demo API by selecting Request temporary access to the demo server.

   4. Return to the main script page and refresh it.

4. On refresh, it will fetch the spaces from the specified Confluence instance and show a success message, as below:

Once setup is complete, there are two ways to replace the question marks in SQL queries stored in attachments:

• Step-by-step mode: Update one SQL query at a time by selecting one space, page, and attachment.

• Direct input mode: Update multiple SQL queries from multiple attachments in one operation.

For bulk operations, process the queries in batches of five or ten to reduce the risk of errors.

1. Select the Space, Page, and Attachment containing the SQL query.

2. It will show the SQL query from the attachment as below:

3. Now, to replace the ‘?’, fill the parameter values in the “Replacement values” box on the right side. As mentioned, you can either use comma-separated values or enter each value on a new line, as shown in the window:

Open

Step-by-step flow step 2

Open

Step-by-step flow step 2.1

Click on Save & Update Existing Attachment; it will update the SQL query with actual values in the Confluence space:

Open

Successful implementation of the step-by-step flow method

Once the success message is shown, you can verify the result in the macro configuration in the space.

1. Use the to update the parameter values for multiple queries.

   { "space": "", "page": "", "attachment": "", "replacements": "" }

2. You can use this code block multiple times, for each attachment from a different page.

   Replace the following placeholders in the code block:

   • space → actual space key

   • page → actual page name in the space

   • attachment → actual attachment name

   • replacements → parameter value, or comma-separated values if applicable

3. Once the config.json file is updated, upload it to the page. Refer to the recording below: