@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

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)

Since you used the migration wizard, I assume that the problem is due to the primary key ID and the indexes on it.

Can you use the wizard to create a test table with only two fields, ID and row_status, and post the source code of the generated class here?
And more: how many records are there in the table?

Enable auditing for %System/%SQL/DynamicStatementQuery, %System/%SQL/EmbeddedStatementQuery, %System/%SQL/XDBCStatementQuery system events.

You can view last actual values of the passed parameters of SQL query in SMP: System > Processes > Process Details > Variables

Rather, the discussion was about out-of-the-box equivalents of some SQL functions, so that programmers wouldn't have to reinvent the wheel. Furthermore, the routine of ^%qarfunc is undocumented.

For example, making an Object Script analog of VECTOR_COSINE for integer vectors yourself is a non-trivial task, given that $VECTOROP doesn't support the integer type.

Sometimes it's easier to use embedded SQL.

I'm curious why LPAD, RPAD and similar string functions are not exposed as $SYSTEM.SQL.Functions class members

This is a good question that I would also like to get an answer to.

See Signing XML Documents (and especially pay attention to section "Requirements of the XML-Enabled Class")

This should be checked on your environment, depending on the versions of Caché, Windows, and Microsoft SQL Server used. I have already provided the code for this above.

I would advise you to first make sure that you are using the latest version of Caché (and the ODBC/JDBC driver), which has fixed many bugs, including various memory leaks:
Caché® and Ensemble® Change Notes (2018.1.11)

In addition, you may find the New Method $SYSTEM.Util.CleanDeadJobs() useful.

Once upon a time, when the grass was greener and the sky was bluer, there was even an add-in application Caché RoseLink^PDF.

Here is what is in the documentation for Caché:

Disable Query Timeout — Optional. If selected, causes the ODBC client driver to ignore the value of the ODBC query timeout setting.

The ODBC query timeout setting specifies how long a client should wait for a specific operation to finish. If an operation does not finish within the specified time, it is automatically cancelled. The ODBC API provides functions to set this timeout value programmatically. Some ODBC applications, however, hard-code this value. If you are using an ODBC application that does not allow you to set the timeout value and the timeout value is too small, you can use the Disable Query Timeout option to disable timeouts.

java.sql Exceptions
The following exceptions are listed here for completeness, but are not required and are never used:
...
SQLTimeoutException
...

You can empirically verify all your considerations by calling a custom stored procedure from Microsoft SQL Server, for example:

Class dc.a [ Abstract ]
{

Query DbgSqlProc(
  qMaxRows = 10,
  qTimeWait As %SmallInt = 3) As %Query(ROWSPEC = "nrow:%Integer,name:%String") [ SqlProc ]
{
}

ClassMethod DbgSqlProcExecute(
  ByRef qHandle As %Binary,
  qMaxRows = 10,
  qTimeWait As %SmallInt = 3) As %Status
{
  #define Log(%s) Set ^dbgSqlProc(qHandle("id"),%s)=$ZDateTime($NOW(),1,1,6)
  
  Set qHandle("id")=$Increment(^dbgSqlProc)
  Set qHandle("qMaxRows")=qMaxRows
  Set qHandle("qTimeWait")=qTimeWait

  $$$Log("1_Execute")

  For i=1:1:qMaxRows Set qHandle(i)=$ListBuild(i,"name"_i)
  Set qHandle=0
  
  ; Hang qHandle("qTimeWait")*3
  
  Quit $$$OK
}

ClassMethod DbgSqlProcFetch(
  ByRef qHandle As %Binary,
  ByRef Row As %List,
  ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = DbgSqlProcExecute ]
{
  $$$Log("2_Fetch")
  
  If qHandle>qHandle("qMaxRows") {
    Set Row=""
    Set AtEnd=1
  }else{
    Set Row=qHandle($Increment(qHandle))
    Hang qHandle("qTimeWait")
  }
  Quit $$$OK
}

ClassMethod DbgSqlProcClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = DbgSqlProcExecute ]
{
  $$$Log("3_Close")
  
  Quit $$$OK
}

}

Example of a call: select * from dc.a_DbgSqlProc(11,5)

See Customized Class Queries

Well, of course it works.
See loads data from 'C:\data\sales.csv' above.

Not quite so (see RowID Hidden?).

ALLOWIDENTITYINSERT is needed for other purposes:

• IDENTITY and Counter Values
• Copy Data into a Duplicate Table

Set Up Delegated Authentication

Have you enabled delegated authentication for services and applications, in particular for %Service_WebGateway?

Interestingly, if I try your examples, I can recreate the ambiguity error, but then get an error when attempting to specify the full param specification

What is your version of IRIS and .NET?
I have IRIS 2025.3CE and .NET 8.0/Framework 4.6.2 - the examples above work flawlessly and give respectively 1, 0, 1 as indicated in the comments.

This is also possible:

USER>s gw = $System.external.getDotNetGateway()
USER>w gw.invoke("System.Convert","ToBoolean(System.UInt64)",123)
1
USER>

Since you manage the ID yourself, in the general case it may not necessarily be a simple counter, but, for example, a Fibonacci sequence.
If the source code of the class or table is available, it is better to see how the ID is generated in it. If there is no source code, and you are sure that this is a simple counter, then in my opinion it would be easier to make a standard ID and let IRIS manage it itself.