Disable Escaping Single Quotes in Document Properties for Information Links

I'm looking for a way to disable Spotfire from escaping single quotes when substituting a document property value into an information link query. I have a SQL statement in an information link that requires a very specific single-quote syntax for the WHERE clause. If I manually edit the SQL and hard-code the filter expression, the query works just fine:

SELECT

   JQL."ISSUEID" AS "ID",

   ISS."SUMMARY" AS "SUMMARY",

   IST."FROMSTATUS" AS "FROMSTATE",

   IST."TOSTATUS" AS "TOSTATE",

   IST."PERFORMEDON" AS "DATETIME"

FROM

   "JDBC4JQL"."PUBLIC"."JQL" JQL JOIN "JDBC4JQL"."PUBLIC"."ISSUES" ISS ON ISS."ID" = JQL."ISSUEID" JOIN "JDBC4JQL"."PUBLIC"."ISSUESTATUSTRANSITIONS" IST ON IST."ISSUEID" = JQL."ISSUEID"

WHERE

   (JQL."QUERY" = ' project = "My Project" ')

See attachment "Screenshot1" for reference. Note the single quotes in the WHERE clause here.

I would like to replace the hard-coded selection with a parameter in the information link and to use a document property in my template to enable user-defined selection and and to load the results on-demand (see attachment "Screenshot2").

[same SQL as above except]

WHERE

   (JQL."QUERY" = ?JQL)

However, when I add a table, and set it up to use a document property to supply the JQL parameter, no results are returned and I get an error message (see attachment "Screenshot3").

I did some digging into the SQL log file (My Drive\tibco\tss\7.8.1\tomcat\logs\sql) and I can see that the problem is that Spotfire has inserted extra single quotes around the ones that I need:

[From log file]

SELECT

   JQL."ISSUEID" AS "ID",

   ISS."SUMMARY" AS "SUMMARY",

   IST."FROMSTATUS" AS "FROMSTATE",

   IST."TOSTATUS" AS "TOSTATE",

   IST."PERFORMEDON" AS "DATETIME"

FROM

   "JDBC4JQL"."PUBLIC"."JQL" JQL JOIN "JDBC4JQL"."PUBLIC"."ISSUES" ISS ON ISS."ID" = JQL."ISSUEID" JOIN "JDBC4JQL"."PUBLIC"."ISSUESTATUSTRANSITIONS" IST ON IST."ISSUEID" = JQL."ISSUEID"

WHERE

   (JQL."QUERY" = ''' project = "My Project" ''')

Note that Spotfire has inserted two single quotes around the inserted document property value - see also "Screenshot4" for reference.

I'm guessing that Spotfire is doing this because of some internal logic to escape bare single quotes so that they always appear as three single quotes (''') in a SQL statement.

How can I disable this behavior so that the exact value of the document property is passed to the information link? If I need to escape the single quotes in my document property to ensure that I only end up with one single quote on either end that would be ok too.

Thanks!

 

Attachments

AttachmentSize
Image icon screenshot1.png498.91 KB
Image icon screenshot2.png458.57 KB
Image icon screenshot3.png495.83 KB
Image icon screenshot4.png803.48 KB

(1) Answer

Login