Changing a table's SQL query without code

Archived Topics
1

I am designing a user interface with a number of radio buttons and a table. When the user clicks on a radio button, I want to change the table’s SQL query and update the table to show the new data.

Is it possible to do this solely with binding i.e. without resorting to Jython? I can display the first lot of data by binding the table’s Data property to a SQL query, but I can’t find out how to change this to a new SQL query using binding as it is expecting a DataSet.

2

Yes, it’s easy. Use the insert property value (chain link) icon of an “SQL Query” type property binding. This value can be a part of, or your entire query. Bind it to a string property that dynamically generates your query - it will probably be generated based on expressions.

Whenever the string (query) changes, the query will be re-evaluated. In your case, user selects a different radio button, bound expression changes the query, table automatically runs the new query and updates.

3

Nathan,

Thanks for your reply - that was exactly what I was looking for! Rewiring my brain to use binding is taking some time…

One small gotcha: with Jython I had to make sure any quotes in the SQL query were single quotes - with the Set Property method, I had to replace all single quotes with double quotes!

4

Please explain...

5

The SQL query SELECT CONCAT(Surname,", ",Forename," ",IFNULL(Middle_Names,"")) as Name, Year FROM Personal LEFT JOIN Company ON Personal.UPRN = Company.UPRN ORDER BY Nameworks in the Set Property dialog, but needs to be changed to SELECT CONCAT(Surname,', ',Forename,' ',IFNULL(Middle_Names,'')) as Name, Year FROM Personal LEFT JOIN Company ON Personal.UPRN = Company.UPRN ORDER BY Name to work in Jython (unless you escape the " in the string I guess).

6

Thats what I thought you were saying.

FYI - you always should quote strings with single quotes in SQL. Double-quotes for string literals is technically incorrect in the SQL language. You must be using (the very lenient) MySQL. If you get in the habit of using single quotes you’ll save yourself a major headache if you ever have to switch databases on a project.

7

So how can I get the second version of the query (with single quotes) working in the Set Property dialog? If I enter it as written above, the following mangled value (which causes an error) gets set:('SELECT CONCAT(Surname,', ',Forename,,IFNULL(Middle_Names,)) as Name, Year FROM Personal LEFT JOIN Company ON Personal.UPRN = Company.UPRN ORDER BY Name')

8

:scratch: What “Set Property” dialog are you talking about?

9

Oh, the Jython builder. I thought your whole purpose was to avoid Jython and use only bindings?

The answer, by the way, would be to change the enclosing quotes around the string in the script that the Set Property builder creates from single to double quotes.