I have a database with several identical tables. Each table name has the same prefix but a different suffix. I have a string tag that will contain the suffix for the table I want to choose.
I then have a flex repeater using a CheckBox component. I want the chosen table to populate the "text" parameter on the checkbox instances
I think the only way to do this is a binding on the "instances" property of the flex repeater. I can manually add array items and configure the "text" parameter to show names for selection in the checkbox, but I want this to automatically populate from a chosen table in the database. Since the table name will be a string with an appended suffix, the only way I know to do this is a script binding.
I have all of that working, but the problem is I don't know what to return to match the type for "Instances". If I return a list it is the wrong type and doesn't work. A dataset doesn't work either. I don't know how to format or convert it to whatever format "Instances" is.
Instances is a list of dictionaries, or at least that what you need to make in your script. You are also correct, you will need to use a script to iterate through the data you get from your db to create the list of instances.
Dictionary keys are the parameters for each instance. In its most simple form your loop would be:
[
{
"param1": "param1Value",
"param2": "param2Value"
} for row in dbDataset
]
or
instances = []
for row in dbData:
instances.append(
{
"param1": "param1Value",
"param2": "param2Value"
}
)
You can probably do a named query binding on instances with a script transform to build the list of dictionaries. Have the table prefix as a parameter to your named query.
You won't be able to make any bindings on these instances, so if you are modifying the value of the check box, it will need a script to send out a message with the row index and a message Id to be handled by a message handler elsewhere on the page. (I recommend view or root container). This handler would then modify the row in the db based on the row index that sent the message.
If I have a Named Query that is not parameterized and returns a JSON format it works perfectly, but that does not work for the application because the table name needs to be parameterized.
"Have the table prefix as a parameter to your named query."
I would prefer to use a Named Query, but if I parameterize like this ...
The : is not a valid operator in that context because you haven't indicated to the interpreter that you're defining a dictionary. Adding the { } would clear the syntax error.
newList = []
for row in pyData:
newList.append({"text":"gov1row"})
Of course, assuming there is no other logic inside the loop, this could be condensed into a comprehension.
newList = [{"text":"gov1row"} for _ in pyData]
You can not provide schema data via parameters unless you use a QueryString parameter, but this is genrally discoraged as it defeats the protections against SQL Injection.
In this case the recommended (imho correct) approach is to use system.db.runQuery() or system.db.runPrepQuery() if there are other parameters. Then you just build the query dynamically.
tableChoice = 'yourTableName'
query = "SELECT Choice as 'text' FROM Table_{}".format(tableChoice)
dataSet = system.db.runQuery(query,'YourDataBaseName')
NOTE: You should check the tableChoice variable against a "whitelist" of allowed table names, too insure that malicious code can not be entered by an operator (unless the dynamic table name is not generated by operator entry of course).
If you prefer a named query you could do something like this in the named query:
declare @table as varchar(200) = :TableChoice;
declare @sql as varchar(4000) = N'SELECT * from dbo.'
set @sql = @sql + @table
if @table = 'tasks' or @table = 'workorders'
exec (@sql)
else
select ''
If this is Microsoft SQL Server, you could also leverage the "FOR JSON PATH" and alias the column names to build the JSON output instead of looping through the dataset in a script for the template repeater.
That is a SQL script, not a SQL query, and is therefore not officially supported by JDBC. It works only because some brands (including MS) added support to their driver.
Coercing a securely-delivered string parameter to actually be used as an insecure SQL structure component practically screams "attack me with SQL injection, please".
I understand, I would typically do this in a stored procedure. I would pass in the string variable into a stored procedure and dynamically build the query in the procedure. I poorly answered this post.
Here's a a named query without the potential for SQL injection, but it is a SQL script:
declare @table as varchar(200) = :TableChoice;
if @table = 'tasks'
SELECT * from dbo.tasks;
if @table = 'workorders'
SELECT * from dbo.workorders;
if @table != 'workorders' and @table != 'tasks'
SELECT '';
I have now given up on appending the table name in the script, instead writing the complete table name (with the suffix) to a tag. I want to use that tag in a named query with JSON format, which will bind with the instances.
The problem is I am using MySQL and apparently the parameterized query will not work, I am getting a syntax error on the query. Is there a way to make this work?
The problem was never where/how the table name is generated. But that you can not use parameter substitution to generate "Dynamic" SQL. No schema objects can be used in this manner, that means Column Names, Table Names, etc...
The only way to achieve this with a named query is to use a QueryString parameter, because it bypasses the sanitation steps that insure unwanted data is not passed to the Database. Which IMO makes them unsuitable for the task.
You should build the query dynamically in a script, with your own sanitation (especially if you are using operator entered data), and call the query from there. Named Queries are really not the right job for this task.
I abandoned the named query and went back to the script. This works:
def transform(self, value, quality, timestamp):
TableSQL = "SELECT Choice FROM myDB.Table_" + str(value)
Table_DataSet = system.db.runQuery(TableSQL)
# convert the incoming value data
pyData = system.dataset.toPyDataSet(Table_DataSet)
# create a blank list so we can append later
newList = []
# step through the rows
for row in pyData:
newList.append({"text":row["Choice"]})
# return the results
return newList
