SQL Database Connector

Need help
1

Hi,

Using the SQL Database Connector, I can use variables n a query e.g. 'Select * from table where organisation = ? and period = ?. By creating 2 variables I can then pass them on to the query. This method however uses positional referencing of variable name. Is there not a way to reference the actual variable names and not simply using “?” to reference them.

Please assist.

Thanks and regards,

Phuthi

2

Hello Community… Please assist me with this.

3

Positional parameters using ? in the query is pretty much standard for the programming industry.

image.png756×279 12.2 KB

On the plus side, the named parameters you create are used to prompt for values when you use the query:

image.png694×182 14.1 KB

4

The query I provided was just a sample because I wanted to communicate context. I have a very complex query that has subqueries which may repeat a parameter. I would prefer to use e.g. select * from employees where first_name = $1. I think in postgreSQL one case use e.g. $1 to reference the variable. However in MySQL it looks like I can only use “?” positional parameter. This is not working out too well for me. The full query I need to run is below:

select distinct issue_classification from Reporting
where organisation_name = ?
and review_period_type = ?
and report_type = ?
and date_time_reported between ? and ?
and issue_classification in (select issue_classification from Reporting
where organisation_name = ?
and review_period_type = ?
and report_type = ?
and date_time_reported
between
(case
when review_period_type = ‘Weekly’ then date_add(?, interval -1 week)
when review_period_type = ‘Bi-Weekly’ then date_add(?, interval -2 week)
when review_period_type = ‘Monthly’ then date_add(?, interval -1 month)
when review_period_type = ‘Bi-Monthly’ then date_add(?, interval -2 month)
when review_period_type = ‘Quarterly’ then date_add(?, interval -1 quarter)
when review_period_type = ‘Bi-Annually’ then date_add(?, interval -2 quarter)
when review_period_type = ‘Annually’ then date_add(?, interval -1 year)
end)

	             and

		        (case 
                    when review_period_type = 'Weekly' then date_add(?, interval -1 week) 
      				when review_period_type = 'Bi-Weekly' then date_add(?, interval -2 week)
	  		    	when review_period_type = 'Monthly' then last_day(date_add(?, interval -1 month))
	  		    	when review_period_type = 'Bi-Monthly' then last_day(date_add(?, interval -2 month))
	  		    	when review_period_type = 'Quarterly' then date_add(?, interval -1 quarter)
	  		    	when review_period_type = 'Bi-Annually' then date_add(?, interval -2 quarter)
	  		    	when review_period_type = 'Annually' then date_add(?, interval -1 year)
		       	 end) limit 200)
5

I agree it would get messy to use it as is.

How about arrange the query differently so you only need to pass each parameter once?

6

I cracked my head over that for quite a while and I couldn’t make it simpler. Main problem is the case statement which is required. I am sure there must be a way. Maybe I should make it a request for bubble to allow this. I mean why would postgresSQL allow for this, i.e. to use $1, $2, etc but not something sent to mySQL. Am sure this is possible to do. Hope we get a response from Emmanuel and Josh on whether this is possible… Thanks for the assist.

7

How about put the params in a one-row derived table, and cross join it to Reporting?

Example:

select thingy 
from Reporting,  (? orgname, ? periodtype, ? reporttype, ? datestart) myparams
where organisation_name = orgname
and blah blah
and datereported between
(case  blah blah date_add(datestart, interval blah

If things get too complex, can put them into a stored procedure instead.

8

Thankyou. Will try this and let you know. Thanks for the help.