I can't explain the behaviour I am experiencing. It ties to work with strings in Python (running Jython 2.7.2). Consider following line of code
The query is receiving two parameters - table name and value we search for in the column Statistic. You can see that around first parameter (table name) there aren't any apostrophes, around the second there are (because it's a value we look for).
When passing in a table name XYZ and col_name of value 'Assigned', we get a complaint "there's no column Assigned in the table". However, if we copy the script to script editor and try to assemble the string, the result is correct -> Table name is "installed" to the T-SQL as it should be and query ends with Statistics = 'Assigned'. Running this query in query editor yields success.
Running the query as a script, however, gives us this error.
Table names, column name, schema names, et cetera, are structure in a query, not values, and cannot be substituted with prep query question marks or named query value parameters. This is fundamental to database APIs, as structure is needed by databases to construct an execution plan, where values can be inserted into queries after planning.
If you absolutely must do this, you will have to dynamically construct those parts of your queries. (Keep in mind that this is a huge security risk, if there is any chance user input could be included.)
All 100% accurate, however, that line of code is doing string formatting and isn't using Prep Query place holders. I would assume that they are dynamically building the query.
Need to see more code, particularly where they are calling which ever system.db.* function they are using.
Not to mention that the error they are stating doesn't align with the query they are showing, which makes me think there is most likely some type of scoping issue, where a previous query is leaking into this call or something.
sorry for late reply. As Irose wrote, the main problem was really with string formating - i.e. we were replacing %s in the query with variable values to build complete query string before sending it to system.db.runQuery(). As much as this is not understandable for me, it is fixed by me, opening the Designer, shaping the code as it should look like and saving it. Now the code runs flawlessly.
From that time on, it works although on PC of my colleague yesterday, the very same string formatting code did not work. The only explanation I have is quite esoteric but I explain that to myself that in the code could have been some white character wreaking havoc. Thank you all for your effort.
You should consider using system.db.runPrepQuery() and only provide the dynamic table name when building the query, and then use the ? place holder for filter condition. That way at least part of the dynamically provided values can be sanitized and provide a little smaller risk.
