SQL db connection made, won't return values. Please help :)

Hey everyone,

Looking for some help to trouble-shoot a MS SQL DB connection I made between my bubble app and a SQL db. I got the sql db connection to work, but can’t get it to return the data I want into my bubble db.

There might be some things I’m missing or that I could check that I’m not aware of, so I’m looking for help on how to trouble-shoot this.

Here’s the current situation

I created a MS SQL database on AWS and created some sample records.

Here’s a screen shot of the DB fields and records from RazorSQL.

Within bubble, I figured out how to make the connection string work

Editor link to the plugins: callbossbriggs | Bubble Editor

Here’s the configuration image.

You’ll notice from the parameters that I’m giving the sql db a phone number and it’s returning all of the records.

The connection worked. Here’s bubble asked me to define the data types of the returning values.

Here’s the issue.

I created a little test button to see if I could pass the sql db a phone number and it would return variables around address and existing customer status.

Here’s the live URL: http://briggsmechanical.getcallboss.com/startcall
U: test@gmail.com
P: test
Editor link to workflows: callbossbriggs | Bubble Editor

Here’s the workflow configuration

I knew that number I was passing it “+12073701354” was in the mssql db already.

The problem is that two records I asked it to update “aws_existing” and “everyone_api_address” don’t get updated in my bubble db.

Any advice on what I should check here? Please keep in mind I’m super new to this.

Here’s a few thoughts on what could be wrong, these are just guesses.

  1. Wrong query is being sent to SQL database.
  2. Data types are messed up somehow between what bubble is sending. The data type of “phone” within both bubble and MS SQL is text. Not sure if this would create an issue.
  3. Perhaps I’m setting up the return program in bubble incorrectly.

Ok, I’ve figured out part of the issue. When the workflow is sent to the db, here is what is sent.

exec sp_executesql @statement=N’select top 200 * from crm where cast(phone as varchar(1000)) = ‘’$1’’;’,@params=N’@phone text’,@phone=’, +12073701354, +12073701354, +16173866557, +16173318291, +15162536232, +16173318291, 207-370-1354, 5162536232, , 5162536232, 5162536232, 5162536232, 5162536232, 5162536232, 5167778888, 5167778888, 6179998888, 6179785555, , , 5167778888, , 5165556666, 5162536232, 5165556666, , 5162536232, 5162536232, 5162536232, 5162536232, 5162536232, 5162536232, 5162536232, 2075552536, 5162536232, 2076321904, , 2076323509, , , , ’

This looks like all of the phone numbers are being sent. It should operate so only a single number is sent.

This looks like the query was written incorrectly in the SQL database connector.

Anyone have any experience with this?

So the database works properly.

This script will return the value I want

SELECT TOP 1 *
FROM tempdb.dbo.crm
WHERE CAST(phone AS VARCHAR(100)) = ‘+12073701354’;

The issue is that I need to get this script into bubble so the phone number can be dynamically inserted.

It looks like the issue is with bubble’s SQL script generator.

This SQL works with the AWS database.

SELECT TOP 1 *
FROM tempdb.dbo.crm
WHERE CAST(phone AS VARCHAR(100)) = ‘+12073701354’;

It will return the values for the record associated wit the phone number +12073701354.

When I try to put it into bubble, in the SQL Database Connector

However, this is the SQL script bubble creates

exec sp_executesql @statement=N’SELECT TOP 1 *
FROM tempdb.dbo.crm
WHERE CAST(phone AS VARCHAR(100)) = ‘’$1’';

‘,@params=N’@Phone text’,@Phone=‘+12073701354’

This script doesn’t return anything.

Thus, it looks like the problem is not with the SQL database, that is working fine, but with bubble’s script generator. (or at least how I’m using it!)

@emmanuel does this look like a bug to you or am I writing something incorrectly in the bubble SQL connector?

For anyone following this breaking news (<-- crappy joke) or might just be working on a similar project/problem. Here’s what I’m doing to try now, as a wait for a response from bubble.

Connect the AWS SQL DB to dreamfactory: https://bitnami.com/stack/dreamfactory/cloud/aws

Dreamfactory will connect with the db and then allow me to interact with it from normal REST APIs. So theoretically, I can then use the bubble API connector to access any SQL db through Dreamfactory!

Can you try making the query on one line only?

Ok, I changed the query to read one a single line in the editor.

Still no luck returning the values from the sql db into the bubble db.

However, I can’t see what SQL script is being generated/sent to AWS now, my friend was helping me with this yesterday.

Is there any way I can see the SQL query generated by bubble?

Any idea of what else could be going/setup wrong?

Here’s the editor to the page I’m working on: callbossbriggs | Bubble Editor

i can see some values

You can see values being returned?

Or you can values in terms of the SQL query that is being sent from bubble?

I’ve tested the query I wanted to send the sql db and confirmed it works outside of bubble.

I meant when I try to initialize the query.

Yes, the query will initialize. The issue (I believe, but could be wrong), is that the incorrect SQL query is being generated and thus nothing is returned. Is there any way of investigating this hypothesis further on your end?

Did you set up a test page? Other users cannot test right now as we don’t know where to look at…

Here’s a test page: http://briggsmechanical.getcallboss.com/sql

The button will initialize the call with the number below. It’s looking to return “existing_customer” and “customer_since” from sql.

Editor for that button workflow is here: https://bubble.io/page?version=test&type=page&name=sql&id=callbossbriggs&tab=tabs-2

Where do you fetch data from that page? I just see some search for…

I’m pulling the phone number from the"phone" column in the “SQL Test” database. However, I just manually insert it so it’s “+12073701354”

Can you share a screenshot with the debugger on where the data is being fetched with the DB connector?

Is this what you need? Or are you looking for a different page?

Sorry my bad your query returns nothing, so you should debug it. We cannot say more on Bubble’s end, what we get back is an empty list.

Correct, it does return nothing from the bubble DB connector.

This is the query that bubble generates. If I put this query to the database, it will not return anthing.

However, this query works

“SELECT TOP 1 * FROM tempdb.dbo.crm WHERE CAST(phone AS VARCHAR(100)) = ‘+12073701354’;”

Thus, how can I get bubble to send the second query?

Unfortunately we cannot do such a change like this, this impact other users