Bugs with text query parameters?

[desert_safari]

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

[Debayan]

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]

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.