cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Bugs with text query parameters?

desert_safari
New Contributor II

Hi all,

I have query with a handful of text query parameters, I need to use to insert data into a table from a dashboard e.g.

INSERT INTO user_data (first_name, middle_name, last_name, city, country, zip_code) VALUES ('{{first_name}}', '{{middle_name}}', '{{last_name}}', '{{city}}', '{{country}}', '{{zip_code}}')

I am having all sorts of issues with running the query.

Firstly, the 'middle_name' parameter is optional and can be empty. Databricks returns a 'Missing value for middle_name' if this is empty. It just would not run the query. I was expecting the parameter value to come as NULL or as an empty string.

Second issue, Databricks is appending additional quote marks around the value for the last_name parameter. For example, if I enter Smith in that field the query fails because it looks like below. I get an error about 'Smith':

INSERT INTO user_data (first_name, middle_name, last_name, city, country, zip_code) VALUES ('John', 'X', ''Smith'', 'Cyberia', 'Internet', '999999')

I've had to remove the quote around the last_name parameter just to get the query working, so it now looks like this:

INSERT INTO user_data (first_name, middle_name, last_name, city, country, zip_code) VALUES ('{{first_name}}', '{{middle_name}}', {{last_name}}, '{{city}}', '{{country}}', '{{zip_code}}')

Both issues make it tricky to hand dashboards over to users who are keen to use the product. The second issue is more worrying. The backend seems to have picked a field at random to append extra quotation marks to.

Any help or work-around for both issues would be greatly appreciated.

Thanks

2 REPLIES 2

Debayan
Esteemed Contributor III
Esteemed Contributor III

Hi, what if , if you use only first name and last name, eliminating the middle name? Also, please refer to https://docs.databricks.com/sql/language-manual/sql-ref-syntax-dml-insert-into.html.

desert_safari
New Contributor II

Sorry my client wants all 3 fields. The middle name has to be there.

There are a few cases where people have just one name.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!