Hi All!
I'm running into some trouble getting SQL query results into a table, when the SQL query is triggered by a JS query.
Background:
I have a search form with several input fields that are all optional criteria. I could try a complex where-clause in the SQL query that will ignore filtering/searching when any of the parameters are NULL/empty strings. (If anyone knows of a stable/reliable/performant query for such a case, please share! )
Current Implementation
However, I thought it could be easier to build the query through JS with a "base query" string and adding the logical phrases to the where-clause for each input field that was not empty. Outputting the final query string and running it in my local DB worked.
Next I tried updating the "query1" (the query object, which is empty when viewing the General tab) with the JS-constructed query string, and triggering it with simple logging.
query1.query = queryBase;
console.log('Showing the query from the query object...');
console.log(query1.query);
query1.trigger({
onFailure: function(data) {
console.log(data);
console.log('MY QUERY FAILED!');
},
onSuccess: function(data) {
console.log(data);
console.log('MY QUERY RAN SUCCESSFULLY!');
}
})
Whenever my JS ran, I could see in the browser's console window that the SQL query object query1 did save the query string, and ran and finished successfully. From what I've gathered so far, the result set is supposed to be passed back into the onSuccess callback function through the "data" argument. But any attempts to save or display that data have failed.
I even tried adding scripts to the Success event handler of the SQL query in the query1 General view. Again, no data from the query result is preserved.
If I read your question correctly.....
If you want to set the table to display the data from the query you can use the Control Component selection in the event handler under Success after that query has executed - select your table component and then choose setData and then enter in Data field the results of that query... for example:
Hi Scott!
Sorry for taking so long to respond. I just tried that but it doesn't work (at least in the current way I have the query setup). So far I've basically tried to update the actual SQL code within the query by checking which input fields actually have information in it. This way I can create a WHERE clause that only filters on the necessary fields. But I feel like even though you can update the Query object's query string property, this doesn't get transferred to whatever the app uses in runtime. It will only run the SQL code that was (or was not) entered into the SQL query window in edit mode.
@Nathan I think the problem you may be running into is that the event handlers don't necessarily fire in the order you have them in the Success section - so you may want to add some milliseconds to each of them 1000, 2000, 3000 and that might give each query to run with the data it needs from the previous one... again. - not seeing any of the queries themselves, I can only surmise that you want to execute the scripts in a certain order....
Interesting suggestion... but lol... I would think the event handlers are only triggered once a query has successfully completed. But here's my form and code (let me know if anything else might be helpful to see):
JS query to collect data from input search fields, build the rest of the query, update the SQL query stub, and trigger the SQL query object to run. I've confirmed locally that whatever query string this JS builds is a usable query.
JS query to display the result set from the SQL query object. I had also tried adding this to the Success Event Handler of the SQL query, but that didn't return anything.
OK so have you tried using additonalScope to pass in the search filters automatically without having set all of the vars in the js? I think using additionalScope may cut down a step and that may help you get that data to be displayed once the query has run for that table....
I actually have not tried that yet. It was a feature that kept coming up in my searches, but I didn't think it was applicable.
In the meantime, I had developed a new query that works way better... locally at least.
SELECT acc.Id AS 'Account ID', acc.BusinessName AS 'Business Name', acc.FirstName AS 'First Name', acc.LastName AS 'Last Name',
loc.Address1 AS 'Address 1', loc.Address2 AS 'Address 2',
zip.City AS 'City', stt.Code AS 'State', zip.ZipCode AS 'Zip Code'
FROM [Location] AS loc
INNER JOIN Account AS acc ON loc.Id = acc.LocationId
INNER JOIN Zipcode AS zip ON loc.ZipCodeId = zip.Id
INNER JOIN [State] AS stt ON zip.StateId = stt.Id
WHERE (ISNULL(acc.BusinessName, '') LIKE (CASE WHEN @BusinessName <> '' THEN @BusinessName ELSE '%' END)) AND
(ISNULL(acc.FirstName, '') LIKE (CASE WHEN @TestFirstName <> '' THEN @TestFirstName ELSE '%' END)) AND
(ISNULL(acc.LastName, '') LIKE (CASE WHEN @TestLastName <> '' THEN @TestLastName ELSE '%' END)) AND
(ISNULL(loc.Address1, '') LIKE (CASE WHEN @TestAddr1 <> '' THEN @TestAddr1 ELSE '%' END)) AND
(ISNULL(loc.Address2, '') LIKE (CASE WHEN @TestAddr2 <> '' THEN @TestAddr2 ELSE '%' END)) AND
(ISNULL(zip.City, '') LIKE (CASE WHEN @TestCity <> '' THEN @TestCity ELSE '%' END)) AND
(ISNULL(stt.Code, '') LIKE (CASE WHEN @TestState <> '' THEN @TestState ELSE '%' END)) AND
(ISNULL(zip.ZipCode, '') LIKE (CASE WHEN @TestZip <> '' THEN @TestZip ELSE '%' END))
Basically there were some test cases where my previous query would return no records when there should have been at least one or few, or otherwise omitted results. But as soon, as I used this query in ReTool, it partially broke for some reason.
Update to that: Somehow I got the input-value injections for StateCode and Zip switched. Fixed it and it works like a dream.
I'll keep AdditionalScope in mind though, I'll probably need it for one of the next pages in this app project.
Thank you so much for the input and bearing with me!
)
