Koha How-To
Koha Question of the Week: Why am I getting an error when I try to run a custom SQL report?
by Sara Porter on Nov 22, 2024
Each Friday, we will bring you a new Koha Question of the Week. We will select real questions that we receive and share the answers with you!
Question: Why am I getting an error when I try to run a custom SQL report?
There are many reasons a report might return an error and sometimes it can be difficult to understand the problem. Below are some of the most common errors and what they mean.
Ambiguous columns
You’ll see this error if your report references a field that exists in multiple tables without specifying the table name. For example, this report would display an error because the biblionumber field exists in both the items and biblio tables:
However, this version would work because it explicitly specifies which table to use for the biblionumber field:
Bad Words
Koha prevents the following words from appearing in a report: UPDATE, DELETE, DROP, INSERT, SHOW, or CREATE. If you need to use one of these words in your report, you can safely work around this security measure using a wildcard. For example, instead of this:
SELECT *
FROM action_logs
WHERE action = "DELETE"
You could use this:
SELECT *
FROM action_logs
WHERE action LIKE "DELET%"
Sensitive Information
Koha doesn’t allow the display of fields that contain sensitive information such as password, secret, or overdrive_auth_token from the borrowers/deletedborrowers tables. That includes selecting * from a table containing these fields. As the error message suggests, the way to fix this is to edit your report so that no sensitive fields are selected.
Unknown Column
This error appears when a report references a field that doesn’t exist – or that the system thinks doesn’t exist. To troubleshoot this error, you can start by double-checking your spelling and looking at the database schema to make sure the field exists in the table being referenced.
If you’re sure the field is correct and there are no typos, you can also check that you’re using the correct table name/alias, because once a table is given an alias, the full table name can no longer be used in the report. For example, this report would not work because the items table is given the alias “i” and the SELECT statement refers to the full table name:
SELECT items.itemnumber
FROM items i
Syntax error
This error means there is some sort of technical problem with the code. This can appear in many scenarios but some good first troubleshooting steps include: making sure the report isn’t missing any commas, checking that all parentheses are closed, and ensuring all clauses are written in the correct order.
Additional Resources
We hosted a koha Community GiftED Session earlier this year on SQL Reports featuring Eric Phetteplace from California College of the Arts!
Read more by Sara Porter