Start a new topic

Change or disable parameter markers?

In Snowflake, the following SQL command is valid SQL and works very well in the Snowflake Worksheet editor.

  

CREATE OR REPLACE TEMPORARY TABLE tmp_receipt_data (
  receiptid    INTEGER
 ,ReceiptJson  VARIANT
) AS
SELECT $1 AS receiptid
      ,PARSE_JSON($2) AS ReceiptJson
  FROM VALUES
         (1, $${"products":[{"productDescription": "Descr1a", "Quantity": 10},{"productDescription": "Descr1b", "Quantity": 25}]}$$)
        ,(2, $${"products":[{"productDescription": "Descr2a", "Quantity": 15},{"productDescription": "Descr2b", "Quantity": 50}]}$$)
        ,(3, $${"products":[{"productDescription": "Descr3a", "Quantity":  5},{"productDescription": "Descr3b", "Quantity": 10}]}$$);

select * from tmp_receipt_data

-- Row RECEIPTID   RECEIPTJSON
-- 1           1   { "products": [ { "Quantity": 10, "productDescription": "Descr1a" }, { "Quantity": 25, "productDescription": "Descr1b" } ] }
-- 2           2   { "products": [ { "Quantity": 15, "productDescription": "Descr2a" }, { "Quantity": 50, "productDescription": "Descr2b" } ] }
-- 3           3   { "products": [ { "Quantity": 5, "productDescription": "Descr3a" }, { "Quantity": 10, "productDescription": "Descr3b" } ] }

 I'd like to run this same SQL in DbVis but it's interpreting the $$ as a variable/parameter marker.


In Tools -> Tool Properties -> General ->  Variables, I can change the Variable Identifier Prefix (from "${" to "%{") and suffix (from "}$" to "}%}


image


In Tools -> Tool Properties -> Database -> SQL Commander, in the Preprocess Script pane, I disabled the Parameterized SQL checkbox (and tried again disabling the "Preprocess Script" checkbox with the same result).


Attempting to execute this SQL in DbVis brings up the Enter Data for Parameter Markers screen.


image


Is there a way to completely disable the parsing of the '$$' parameter markers to allow the Snowflake SQL to be executed within DbVis?


I'm using DbVis 11.0.6


Thanks


-- Tom



Drats... I really wish there was an "edit" capability for entries in this support forum.


To set the Preprocess Script options, I did this:


In Tools -> Tool Properties -> Database -> Snowflake -> SQL Commander, in the Preprocess Script pane, I disabled the Parameterized SQL checkbox (and tried again disabling the "Preprocess Script" checkbox with the same result).  I also disconnected and reconnected from the database to assure the latest changes were applied.


image


image


Regardless of the changes, the Enter Data for Parameter Markers screen is still presented when attempting to execute this SQL in DbVis





Hi Tom,


This looks like defect that we need to look into. I will convert your post to a support ticket and bring it up for discussion.


Thanks for reporting this!


Regards / Peer

Quick followup.   


I heard from support.  Just noting the information obtained here in case anyone else has similar issues.


The changes made in "Preprocess Script" changes are applied to new SQL Commander tabs only (if you have an existing SQL Commander tab and make these changes, the changes won't be applied to the existing tabs, even if you disconnect/reconnect to the database).

Login or Signup to post a comment