SQL API Endpoint Error: "No value specified for parameter 1" despite providing test parameters
I am encountering a 500 Internal Server Error when testing a SQL Query endpoint in the API Designer. Even though I have defined the placeholder and provided the test JSON, the system fails to bind the value.
Environment
- Feature: API Designer / SQL Query Endpoint
- Dataiku Version: 14.2
- Backend DB: PostgreSQL
Here is a structured draft you can post to the Dataiku Community. It includes all the technical details necessary for a member or a Dataiku employee to diagnose the issue quickly.
Configuration Details
1. SQL Query:
SQL
SELECT * FROM "Dataset" WHERE "gender" = ?;
2. Test Parameter (JSON):
JSON
{ "gender": "M"}
The Error
When I click "Run Test Queries" in the Test Queries tab, it returns the following error:
Failed: Query failed: No value specified for parameter 1.
Steps Taken
- I used the standard
?placeholder as per the official documentation. - I ensured that the key in the JSON (
gender) matches the intended logic. - I checked the database connection, and a hardcoded query (e.g.,
WHERE "gender" = 'M') works perfectly.
Questions
- Is there a specific mapping step I am missing to link the
?placeholder to the"gender"key in the API Designer's Parameters section? - Does the order of parameters in the "Parameters" list (under the SQL Query box) strictly dictate the mapping for
?placeholders? - Are there any known issues regarding case sensitivity for parameter names in the API Designer?
Dataiku version used: 14.2
Answers
-
1. Is there a specific mapping step missing?
Yes. Beneath your SQL query box, there is a section titled Parameters. You must manually add an entry there:
- Name:
gender(This must match your JSON key exactly).
If that list is empty, the API doesn't know which JSON value to "plug into" the
?, leading to the "No value specified" error.
2. Does the order strictly dictate the mapping?
Yes, absolutely.
- The first
?in your SQL code will always look for the topmost parameter in your configuration list. - The second
?looks for the second parameter, etc. - The name of the parameter in that list is what the API looks for in your Test JSON.
3. Are there known issues with case sensitivity?
Dataiku is strictly case-sensitive regarding parameter names. If your JSON is
{ "gender": "M" }but your parameter list defines it asGender(capital G), the binding will fail because the API won't find the value to pass to the driver. In this scenario, the test often won't throw an error; it will simply return zero results because the query executes with a missing or null value. - Name:
-
The Parameter Names in the API Designer are purely a "bridge" and do not need to match your actual database column names. You can create any parameter name you like (e.g.,
my_input_var), even if it doesn't exist in your dataset. The only requirement is that the name in your Parameters List must exactly match the key used in your Test JSON. As long as that handshake is consistent, Dataiku will take that value and inject it into the?placeholder in your SQL query.
