Getting Exception while running query API in OD version 25.2.21

[sukhdev]

Hi Team

I am getting exception while running following query from query API in version 25.2.21:
API : https://api.opendental.com/api/v1/queri ... y?Offset=0
Body :

{
"SqlCommand": "SELECT p.PatNum, REPLACE(REPLACE(p.FName, '\"', '"'), '\\'', ''') AS FName FROM patient AS p LEFT JOIN definition AS bt ON bt.DefNum = p.BillingType AND bt.Category = 4 WHERE p.PatNum IN (1);"
}

Exception:
"Query command must either be read-only or execute on a temporary table."

However the same query API with same SQL command runs just fine on older version.
Are there any changes being done in 25.2.21 which don't allow running this query.

[justine]

Hi Team

I am getting exception while running following query from query API in version 25.2.21:
API : https://api.opendental.com/api/v1/queri ... y?Offset=0
Body :

{
"SqlCommand": "SELECT p.PatNum, REPLACE(REPLACE(p.FName, '\"', '"'), '\\'', ''') AS FName FROM patient AS p LEFT JOIN definition AS bt ON bt.DefNum = p.BillingType AND bt.Category = 4 WHERE p.PatNum IN (1);"
}

Exception:
"Query command must either be read-only or execute on a temporary table."

However the same query API with same SQL command runs just fine on older version.
Are there any changes being done in 25.2.21 which don't allow running this query.

Hello sukhdev,

We are actively looking into this for you and will respond here with our results.

However, this particular query is a bit unusual to me. It performs a LEFT JOIN on definition but doesn’t use any fields from that join, and it double-escapes characters in FName using nested REPLACE calls. If the goal is just to retrieve and display a patient’s first name safely, there are more straightforward (and robust) ways to handle that; ideally in the presentation layer, not SQL. Using REPLACE to convert quotes is fragile, and escaping like '\\'' assumes a very specific data encoding that may not always apply.

If you don't actually need the LEFT JOIN or the escapes, this may be a viable workaround:

Code: Select all

SELECT PatNum, FName FROM patient WHERE PatNum IN (1);

Thanks!