SQL query returning results from last query

Squiz Matrix Support
1

I've got a SQL query, which takes the values from a submitted form as part of the query, and retuns results. The query is working but its one behind, eg:


search 1 for 'option1' - no results

search 2 for 'option2' - returns results for option1

search 3 for 'option3' - returns results for option2



The values from the form get set as session variables, and then DB Data Source has dynamic variable mapping to the session variables. If I write out the session variables in the results they do seem to change correctly each time. So it seems like the cache is at the SQL query level?



The original query started with '!nocache:select' but if I put that into Matrix it errors. This is the full querey:

    
    select [Ambition],[LeadOrg],[Partners],[Status],[Ref],[ID],[Progress],[Desc],[Date] FROM [ExportData] where [Ambition] like %%ambition%% and [LeadOrg] like %%leadorg%% and [Partners] like %%partners%% and [Status] like %%status%%


What can I include in my SQL query to stop it caching?

Thanks!
Charlie.

I've also just noticed that I get the results (on the thankyou page of my form), with the last searches results on, and then if I do a hard refresh (Ctrl + F5) my browser asks if I want to resend the data, do that and I get the results I want.

So where is it caching? In SQL, or in Matrix?
2

DB data source asset will cache the query results, but the cache key is based on the query string with all dynamic variable populated.
Therefore Matrix caching shouldn't be the problem here in theory.



I'm not sure the DB you are using, nor cache options of the DB, so I can not give comments on that.



I would suggest you to lodge support ticket, so developers can help you out.



Otherwise, you can do a bit debugging in code to figure it out.

function &getResultSet() in data_source_db.inc will be a good place to start with.

3

Fixed it!
Removed the submission actions to set Session variables, and instead in the dynamic inputs for the SQL query, use the POST variables from the form.

Now returning the right results at the right time - hurrah!