Forum Academy Marketplace Showcase Pricing Features

SQL Database Connector

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

Hello Community… Please assist me with this.

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

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

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)

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?

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.

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.

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