Modify

Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#12837 closed defect (worksforme)

Dynamic report variables not parsed correctly

Reported by: anonymous Owned by:
Priority: normal Component: MsSqlBackendPlugin
Severity: normal Keywords: Report SQL Query
Cc: Trac Release: 1.0

Description (last modified by Jun Omae)

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.

Attachments (0)

Change History (14)

comment:1 Changed 8 years ago by 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+' ' )
Last edited 8 years ago by Jun Omae (previous) (diff)

comment:2 Changed 8 years ago by Jun Omae

Description: modified (diff)

comment:3 Changed 8 years ago by 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, '''', '''''') + '''')
Last edited 8 years ago by Jun Omae (previous) (diff)

comment:4 in reply to:  3 ; Changed 8 years ago by 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

comment:5 in reply to:  4 ; Changed 8 years ago by Jun Omae

SELECT * FROM REPORT WHERE TITLE = $EXAMPLE;

Remove semicolon.

comment:6 in reply to:  5 ; Changed 8 years ago by 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?

comment:7 in reply to:  6 Changed 8 years ago by 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, '''', '''''') + '''')

comment:8 Changed 8 years ago by 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)")
Last edited 8 years ago by Jun Omae (previous) (diff)

comment:9 Changed 8 years ago by 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.

comment:10 in reply to:  9 Changed 8 years ago by 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!

comment:11 Changed 8 years ago by Jun Omae

Did you try this?

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

comment:12 in reply to:  11 Changed 8 years ago by 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

comment:13 Changed 8 years ago by 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.

comment:14 Changed 8 years ago by Jun Omae

Resolution: worksforme
Status: newclosed

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.

Last edited 8 years ago by Jun Omae (previous) (diff)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.