View actual SQL for ServiceAPI calls from .Net code ???

I am calling the Service API from .Net running RM8.3.

I am seeing unexpected results if a user defined field (Additional field) is part of the search string and the additional field does not exit in RM8.  I understand that we should use Additional fields that exist in RM8, but we ran across this when the sending applicaiton used a different spelling than what was in RM8.  The Desktop client throws an error say that the additional field does not exist.  In the Service API the results are different if you use a record type filter or not.

Below I have capture queries with the total records found:

The first test was with Client ID and date range without a record type filter, the correct results was returned.
recordsRequest.q: LetterCreatedDate:20170303 to 20170324 AND ClientID:xxxxxxx
recordsRequest.Filter:
response.TotalResults: 2

The 2nd test was with Client ID and date range with a record type filter, the correct results was returned.
recordsRequest.q: LetterCreatedDate:20170303 to 20170324 AND ClientID:xxxxxxx
recordsRequest.Filter: type:541
response.TotalResults: 2

The 3rd test was with Invalid Client ID search name and date range with a record type filter.  All of the records of that record type was returned.
recordsRequest.q: LetterCreatedDate:20170303 to 20170324 AND ClientID2:xxxxxxx

recordsRequest.Filter: type:541
response.TotalResults: 6751

The 4th test was with Invalid Client ID search name and date range without a record type filter.  No records were returned.
recordsRequest.q: LetterCreatedDate:20170303 to 20170324 AND ClientID2:xxxxxxx
recordsRequest.Filter:
response.TotalResults: 0

The 5th test was with date range with a record type filter.  Correct Results returned.
recordsRequest.q: LetterCreatedDate:20170303 to 20170324

recordsRequest.Filter: type:541
response.TotalResults: 12

The 6th test was all with record type filter.  Correct Results returned.
recordsRequest.q: all

recordsRequest.Filter: type:541
response.TotalResults: 6751

Test 3 and Test 6 returned the same results.   I would expect test 3 to return no records since an invalid search field was passed into the API.  I would like to be able to capture the SQL that is sent to the database to determine why we are seeing the same results between Test 3 and Test 6.   Is there a way to capture the native SQL? 

Thanks,
MikeB

Parents
  • The easiest way to find the SQL is to enable workgroup server logging and then inspect the workgroup server logs. There are a heap of posts here talking about how to enable and view such logs.

    Alternatively you could enable ServiceAPI logging, but I don't believe the ServiceAPI itself generates any SQL.
  • After a little research, I was able to find a TRIMWorkgroup2017_3_27.log file.    It appears that if one or more record types are added as filters, then an SQL statement is logged and executed .   I can see that in the query, but it apprears that all of the search string is omitted from the query if a bad search field is used.   If there is no record type passed as a filter, then no query is logged and thus, I assume that no values are returned do to an internal error.  

    Shouldn't the API error off if an invalid search field is passed in whether a record type filter is used or not?  I would think an error would be much better than returning every document for the record type or record types if multiple filters are used.  

    Do I need to create a support ticket to have this issue researched further?

    Thanks,
    MikeB

Reply
  • After a little research, I was able to find a TRIMWorkgroup2017_3_27.log file.    It appears that if one or more record types are added as filters, then an SQL statement is logged and executed .   I can see that in the query, but it apprears that all of the search string is omitted from the query if a bad search field is used.   If there is no record type passed as a filter, then no query is logged and thus, I assume that no values are returned do to an internal error.  

    Shouldn't the API error off if an invalid search field is passed in whether a record type filter is used or not?  I would think an error would be much better than returning every document for the record type or record types if multiple filters are used.  

    Do I need to create a support ticket to have this issue researched further?

    Thanks,
    MikeB

Children