Issue with using variable in SQL query

(Jamie Smith) #1

I have a number of DB Data Source assets that contain SQL queries with variable names (set in the Dynamic Inputs screen) drawing from GET variables.

 

However, Matrix is throwing an error message on save, citing a syntax error at the variable name's position, e.g.:

...

WHERE (groups.idgroups = %%foo%%)

It seems that Matrix is getting stuck at the foo variable:

 

SQL Execution Error:Error: SQLSTATE[42000]: Syntax error or access violation: ... WHERE (`groups`.`idgroups` = E'')

 

If I replace %%foo%% with a hard coded value, the query is accepted and the results appear as expected on the relevant web page.

 

Any thoughts on why this might be happening?

(Bart Banda) #2

Hey, what version of Matrix are you using? And can you give some other screenshots of your setup in admin as well? 

(Jamie Smith) #3

Hey, what version of Matrix are you using? And can you give some other screenshots of your setup in admin as well? 

 

Hi Bart. Just saw your response – thank you. We're on Matrix v4.14.1. This is a screenshot of the Query Builder screen for the problematic asset:

 

matrix_query_builder_issue.png
matrix_query_builder_issue.png658x746

(Ashish Karelia) #4

Hi, this seems to be an issue what that particular version of Matrix.

 

It's been fixed on bug #6244 Using MYSQL database on the DB Data Source Asset may break after upgrade to 4.14.0. The patch ws release with Matrix v 4.14.2. If you are a Squiz client then please raise a support ticket to fix the issue up.

 

Alternatively, you could ask for an upgrade which would provide you with additional bug fixes ad features.

 

Hope this helps.

 

Ash

(Jamie Smith) #5

Many thanks indeed, Ash.