Dynamic report variables not parsed correctly

[anonymous]

I'm attempting to use dynamic variables in one of my reports however when I submit the report the variable is converted into %s

Here's the SQL query i'm attempting to run on the report:

Select * from openquery([redacted_server], '

SELECT
    [UUT_RESULT]
    ,[ORDER_NUMBER]
    ,[STEP_NAME]
    ,[STEP_TYPE]
    ,[STEP_GROUP]
    ,[STEP_INDEX]
    ,[STATUS]
    ,[REPORT_TEXT]
    ,[ERROR_CODE]
    ,[ERROR_MESSAGE]
    ,[CAUSED_SEQFAIL]
    ,[MODULE_TIME]
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = $UUT_ID')

When attempting to run I'm given the following error:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '+'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Which refers to the following:

WHERE [UUT_RESULT] = '+%s+'')

Even if I make a new report and just put $VALUE, it'll come out as %s regardless of what the URL variables are set to. The default dynamic variable of $USER also gets converted to %s.

I'm not too sure how the internals of MsSqlBackendPlugin works but it looks like it's incorrectly being parsed however any insight as to how I can fix this would be very much appreciated.

[anonymous]

When I typed 'refers to the following' it seems the text wasn't escaped correctly. What it actually is meant to look like is this:

WHERE [UUT_RESULT] = '+%s+' ' )

[Jun Omae]

That is not a issue of this plugin. Your query is wrong. I suggest closing as worksforme.

You should escape value of $UUT_ID like this because your query is passed as a literal to openquery():

WHERE [UUT_RESULT] = ''' + REPLACE($UUT_ID, '''', '''''') + '''')

[anonymous]

Replying to jun66j5:

That is not a issue of this plugin. Your query is wrong. I suggest closing as worksforme.

You should escape value of $UUT_ID like this because your query is passed to literal to openquery():

WHERE [UUT_RESULT] = ''' + REPLACE($UUT_ID, '''', '''''') + '''')

Even when escaping or even just having $EXAMPLE as a variable it is still converted to %s.

ie. the output from the following report:

SELECT * FROM REPORT WHERE TITLE = $EXAMPLE;

Is shown to be the following

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ';'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

SELECT COUNT(*) FROM (
SELECT * FROM REPORT WHERE TITLE = %s;
) AS tab

With the URL being: ​http://server/Trac_sql/report/12?EXAMPLE=test

Here's the output of your example:

SELECT COUNT(*) FROM (
Select * from openquery([<redacted_server>], 'SELECT
    [UUT_RESULT]
    ... etc ...
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')
) AS tab

[Jun Omae]

SELECT * FROM REPORT WHERE TITLE = $EXAMPLE;

Remove semicolon.

[anonymous]

Replying to jun66j5:

SELECT * FROM REPORT WHERE TITLE = $EXAMPLE;

Remove semicolon.

Ah! Apologies, my mistake.

It still seems I need to find a way to escape my variable being parsed as a literal within openquery. The example you provided still seems to output %s.

WHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')

Perhaps I need to escape using the slash character?

[Jun Omae]

Replying to anonymous:

It still seems I need to find a way to escape my variable being parsed as a literal within openquery. The example you provided still seems to output %s.

WHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')

I think this wouldn't lead a problem.

Please post trac.log after enabling TracLogging with debug level and [trac] debug_sql = true and the following query is saved in report and invoking the report.

Select * from openquery([redacted_server], '

SELECT
    [UUT_RESULT]
    ,[ORDER_NUMBER]
    ,[STEP_NAME]
    ,[STEP_TYPE]
    ,[STEP_GROUP]
    ,[STEP_INDEX]
    ,[STATUS]
    ,[REPORT_TEXT]
    ,[ERROR_CODE]
    ,[ERROR_MESSAGE]
    ,[CAUSED_SEQFAIL]
    ,[MODULE_TIME]
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = ''' + REPLACE($UUT_ID, '''', '''''') + '''')

[anonymous]

Here's all the queries I found after navigating to: http://<server>/Trac_sql/report/10?UUT_ID=111

14:26:36 Trac[mssql_backend] DEBUG: (10,)
14:26:36 Trac[report] DEBUG: Report {10} with SQL "Select * from openquery([<redacted_server>], 'SELECT
    [UUT_RESULT]
    ,[ORDER_NUMBER]
    ,[STEP_NAME]
    ,[STEP_TYPE]
    ,[STEP_GROUP]
    ,[STEP_INDEX]
    ,[STATUS]
    ,[REPORT_TEXT]
    ,[ERROR_CODE]
    ,[ERROR_MESSAGE]
    ,[CAUSED_SEQFAIL]
    ,[MODULE_TIME]
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')"
14:26:36 Trac[report] DEBUG: Request args: {u'UUT_ID': u'111', 'id': u'10'}
14:26:36 Trac[report] DEBUG: Report {10} SQL (count): SELECT COUNT(*) FROM (
Select * from openquery([<redacted_server>], 'SELECT
    [UUT_RESULT]
    ,[ORDER_NUMBER]
    ,[STEP_NAME]
    ,[STEP_TYPE]
    ,[STEP_GROUP]
    ,[STEP_INDEX]
    ,[STATUS]
    ,[REPORT_TEXT]
    ,[ERROR_CODE]
    ,[ERROR_MESSAGE]
    ,[CAUSED_SEQFAIL]
    ,[MODULE_TIME]
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')
) AS tab
14:26:36 Trac[mssql_backend] DEBUG: SELECT TOP 1000 COUNT(*) FROM (
Select TOP 1000 * from openquery([<redacted_server>], 'SELECT TOP 1000
    [UUT_RESULT]
    ,[ORDER_NUMBER]
    ,[STEP_NAME]
    ,[STEP_TYPE]
    ,[STEP_GROUP]
    ,[STEP_INDEX]
    ,[STATUS]
    ,[REPORT_TEXT]
    ,[ERROR_CODE]
    ,[ERROR_MESSAGE]
    ,[CAUSED_SEQFAIL]
    ,[MODULE_TIME]
    ,[TOTAL_TIME]
FROM [teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = ''' + REPLACE(?, '''', '''''') + '''')
) AS tab
14:26:36 Trac[mssql_backend] DEBUG: ['']
14:26:36 Trac[report] WARNING: Exception caught while executing Report {10}: u"SELECT COUNT(*) FROM (\nSelect * from openquery([PC1056\\TESTSTAND], 'SELECT\r\n    [UUT_RESULT]\r\n    ,[ORDER_NUMBER]\r\n    ,[STEP_NAME]\r\n    ,[STEP_TYPE]\r\n    ,[STEP_GROUP]\r\n    ,[STEP_INDEX]\r\n    ,[STATUS]\r\n    ,[REPORT_TEXT]\r\n    ,[ERROR_CODE]\r\n    ,[ERROR_MESSAGE]\r\n    ,[CAUSED_SEQFAIL]\r\n    ,[MODULE_TIME]\r\n    ,[TOTAL_TIME]\r\nFROM [teststand].[dbo].[STEP_RESULT]\r\nWHERE [UUT_RESULT] = ''' + REPLACE(%s, '''', '''''') + '''')\n) AS tab", args ['']
Traceback (most recent call last):
  File "C:\Python27\lib\site-packages\trac\ticket\report.py", line 684, in execute_paginated_report
    cursor.execute(count_sql, args)
  File "C:\Python27\lib\site-packages\trac\db\mssql_backend.py", line 209, in execute
    self.cursor.execute(sql, args or [])
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '+'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

[Jun Omae]

Thanks. It seems openquery() cannot accept concatenated literal. REPLACE() cannot solve it.

This query raises the same error.

SELECT * FROM openquery([localhost], 'SELECT ''a' + 'b''')
         -- no error if the query is 'SELECT ''ab''')

Trac always uses ​cursor.execute()'s parameters to pass the report variables (e.g. $USER and user-defined variable). Then, openquery() is unable to use in the report system.

[anonymous]

Replying to jun66j5:

Thanks. It seems openquery() cannot accept concatenated literal. REPLACE() cannot solve it.

This query raises the same error.

SELECT * FROM openquery([localhost], 'SELECT ''a' + 'b''')
         -- no error if the query is 'SELECT ''ab''')

Trac always uses ​cursor.execute()'s parameters to pass the report variables (e.g. $USER and user-defined variable). Then, openquery() is unable to use in the report system.

Seems I'll have to look into alternatives. Thank you for your help!

[Jun Omae]

Did you try this?

SELECT [UUT_RESULT]
     , ...
     , [TOTAL_TIME]
FROM [<redacted_server>].[teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = $UUT_ID

[anonymous]

This may be getting close, I'm able to run that query without the WHERE clause which means there's no longer the issue of the literals.

The following returns me all the rows successfully

SELECT [UUT_RESULT]
    ...
    ,[TOTAL_TIME]
FROM [server].[teststand].[dbo].[STEP_RESULT]

but as soon as I add on the WHERE clause I get the following:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting from a character string to uniqueidentifier. (8169) (SQLExecDirectW)')

With the query ending up as:

SELECT [UUT_RESULT]
    ,[ORDER_NUMBER]
    ...
    ,[TOTAL_TIME]
FROM [server].[teststand].[dbo].[STEP_RESULT]
WHERE [UUT_RESULT] = %s

[anonymous]

Ah, I've just realized that's going to be an issue with my database schema. Consider this solved, it's working now! Thank you very much for the help.

[Jun Omae]

Okay. Closing.

I guess it is needed to use [UUT_RESULT] = CONVERT(uniqueidentifier, $UUT_ID) if [UUT_RESULT] is a uniqueidentifier and pass GUID string to $UUT_ID.

See ​http://stackoverflow.com/a/20940482/709074.