Question
· 17 hr ago

What is the way to see the latest executed SQL query?

#ObjectScript #SQL #InterSystems IRIS

Hi folks!

Is there any way to know the text of the SQL queries executed against the IRIS server?

My case: I have an SQL class query with a few parameters and and some combination of values causes unexpected results.

It'd be great if I could just copy the query from some log of queries done vs my IRIS the server. Cannot find it easily, though. Any advice?

Product version: IRIS 2025.3
Discussion (11)2
Log in or sign up to continue
Robert Cemper · 16 hr ago

If you use a custom class query %Library.Query type you may write your parameters to some
^mtemp.Evgeny($i(^mtermp.Egeny)) = ..... direct from the Execute method
or ^mtemp.Evgeny($h) = ....

For basic class query %SQL.Query () you may take the usual SQL approach

  • Create a SQL method that always returns 1 (TRUE)
  • You pass all your parameters into that method 
    • which does the ^mtemp trick and a QUIT 1
  • add to the WHERE clause  . . .  AND MYTRACE(par1,par2,---)=1

I refer to this a STATIC clause since it is only executed once by query
because of no reference to any column values 

It was my approach to SQL debugging
 

0 0
David Hockenbroch · 15 hr ago

The only places I know of are, if it caused an xDBC error, that in the system management portal under System Operation, System Logs, xDBC Error Logs, and if it was a query run through the management portal itself, you'd be looking in the global %sqlcq and for your search mask, use %sqlcq("NAMESPACE","SMPQueryHistory","USERNAME" to see the query history for a specific user. That's where the query history you see in the SQL section of the management portal is stored.

I don't know of anything overall, though.

0 0
Vitaliy Serdtsev · 3 hr ago

If auditing is enabled, you can see the query without the actual parameter values.
To copy the query to the SQL query tool, the query will also have to be cleaned of garbage.

For example, there is the following query

select * from del.a where id<3

From SMP:

Event: DynamicStatementQuery
Event Data: SELECT * FROM del . a WHERE id < ?

From DbVisualizer:

Event: XDBCStatementQuery
Event Data: SELECT * INTO :i%%col1, :i%%col2, :i%%col3 FROM del . a WHERE id < :%qpar(1)
0 0
Vitaliy Serdtsev · 2 hr ago

@Evgeny Shvarov

Unfortunately, I have not yet found a documented and [Not Internal] way out of the box to see not only the text of the request, but also the values of its input parameters.

PS: take a look at the methods of the %SYSTEM.SQL.xDBC class.
Here is an example of the log for the query above (from DbVisualizer):

        SQL text (raw from client): 1 lines
        line 1: SELECT * FROM del . a WHERE id < :%qpar(1)
        Var Types: Parameter 1: Constant of type INTEGER
        INPUT params:
          %qpar(1) = 3
1 0