SQL query with List parameter

Need help APIs
1

I am using the Bubble SQL API Plugin and have successfully done so for many queries. My current query has a list of numbers as one of the parameters.

Select top 100 tbl1.AppointmentID, tbl1.ClientID, tbl2.AppType, tbl1.Date, tbl3.UserName as Bookedby, tbl4.UserName as Clinician, tbl5.LocName from Appointments tbl1
inner join AppTypes tbl2
on tbl1.Apptypeid = tbl2.AppTypeId
inner join Users tbl3
on tbl1.CreatedByID=tbl3.UserId
Inner join users tbl4
on tbl1.UserId=tbl4.UserID
Inner join locs tbl5
on tbl1.LocID=tbl5.LocID
where (tbl1.AppTypeID = 110 or tbl1.AppTypeID = 272 or tbl1.AppTypeID = 283 or tbl1.AppTypeID = 284 or tbl1.AppTypeID = 292 or tbl1.AppTypeID = 300 or tbl1.AppTypeID = 239 or tbl1.AppTypeID = 250 or tbl1.AppTypeID = 253 or tbl1.AppTypeID = 301 or tbl1.AppTypeID = 109) and tbl1.AppStatusID <=203 and tbl1.Date>DateAdd(m, -6, @DateStart) and tbl1.Date<DateAdd(m, 6, @DateEnd) and ClientID in (@ClientID)

I cannot get Bubble to pass on a list of numbers or text to the query. It can only pass single values in workflows and in the editor, i.e. a list gives and error.

image
image852×484 28.6 KB

When I test it, a list is fine however.
image
image880×455 30.8 KB

Am I missing something?
Here is a video explaining the issue in detail. Screen Recording 2021-04-24...

When I use the type as text from @ClientID I get the following error: image

I have searched far and wide for solutions to convert the text to Int using SQL functions, but I have been unable to get any of the solutions to work using the SQL database connector.

3

I got the SQL “in” statement to work in MySQL. Try this:

  1. Make your ClientID Parameter “text” not “number”.
  2. Remove the space between the numbers in your test list. Hopefully that will work.
  3. If it does, then pass a list of numbers to the parameter when you make the call. It will take the form 21085,26906.
4

Thank you joeyg. I still get the error with SQL being unable to convert to data type int. What I am trying now is creating a txt row with that data in SQL and referencing that instead. So I’ll see how that goes. Still struggling to get it working, but seems to be heading in the right direction.

5

I managed to get it working with your help. I simply had to change the query to:
Select top 100 tbl1.AppointmentID, tbl1.ClientID, tbl2.AppType, tbl1.Date, tbl3.UserName as Bookedby, tbl4.UserName as Clinician, tbl5.LocName, tbl6.PHN from Appointments tbl1
inner join AppTypes tbl2
on tbl1.Apptypeid = tbl2.AppTypeId
inner join Users tbl3
on tbl1.CreatedByID=tbl3.UserId
Inner join users tbl4
on tbl1.UserId=tbl4.UserID
Inner join locs tbl5
on tbl1.LocID=tbl5.LocID
Inner join Clients tbl6
on tbl1.ClientID = tbl6.ClientID
where (tbl1.AppTypeID = 110 or tbl1.AppTypeID = 272 or tbl1.AppTypeID = 283 or tbl1.AppTypeID = 284 or tbl1.AppTypeID = 292 or tbl1.AppTypeID = 300 or tbl1.AppTypeID = 239 or tbl1.AppTypeID = 250 or tbl1.AppTypeID = 253 or tbl1.AppTypeID = 301 or tbl1.AppTypeID = 109) and tbl1.AppStatusID <=203 and tbl1.Date>DateAdd(m, -6, @DateStart) and tbl1.Date<DateAdd(m, 6, @DateEnd) and CAST(tbl1.ClientID as varchar(10)) In (@ExternalID)

The CAST(tbl1.ClientID as varchar(10)) turns the integer into text.

and then make sure there are no spaces after the comma in the parameter: @ExternalID . I simply formatted the numbers as text as you suggested.

Thanks for your help.

6

Great! I’m a big fan of connecting Bubble to SQL DB’s. Haven’t had one challenge like this come up that couldn’t be tackled. Nice job figuring it out.

7

Thanks again for giving me the confidence that it was possible.

8

The previous solution did not work properly. I have finally figured it out.

You would use the following statement, which works fine:

Select top 200 AppType from AppTypes where @AppTypeIDs Like ‘%,’+Cast(AppTypeID as varchar(10))+‘,%’

image
image826×248 15.2 KB

If the field in Bubble is stored as a number then this works:
image

For text the same method works:
image
image

1 Like