Disable Escaping Single Quotes in Document Properties for Information Links

[Michael Schmidt 4]

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 aninformation 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 Drivetibcotss7.8.1tomcatlogssql) 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 quotesaroundthe 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 asthree 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!

[Michael Schmidt 4]

I figured this out and am posting here in case anyone has the same problem. It looks like Spotfire always inserts single quotes around the document property value when inserting into the SQL statement in the information link. For example, when I set the document property to simply "x" (without the quotes), I see this in the sql log:

[same SQL as above except]

WHERE

(JQL."QUERY" = 'x')

So this accounts for one of the extra single quotes that was inserted. The other must come from Spotfire escaping the single quotes that I had in my document property, giving a total of three single quotes as seen above.

In my case the solution is very simple - just remove the single quotes from my document property and that fixes the problem:

Document Property: project = "My Project"

[same SQL as above]

WHERE

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