Error: CSP application closed the connection before sending a responce

SQL, Caché

I am trying to fetch the data from cache database. But i got the error like "CSP application closed the connection before sending a responce".

Below is the query.

SELECT
CallbackComment
FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'

I have investigated and found that "CallbackComment" contains the special character single quotes " ' "  for one result and due to this i got this error.

In this field data is enter by customer. so we cannot restrict them like Do Not use single quotes.

Please provide some solution as soon as possible.

Thanks in advance. 

  • 0
  • 0
  • 502
  • 28
  • 2

Answers

Looks like Timeout issue.

1. Do you have index on PhoneDateODBC?

2. Can you execute the same query using some JDBC tool?

1. Yes, We have index on PhoneDateODBC

2. As i have mentioned that i am using the Intersystem ODBC driver to connect the cache database. And it is failed to read special character single quotes. so please gives some details about that. 

As i have mentioned that i am using the Intersystem ODBC

Where? I thought you were using SMP.

Try to change SELECT field CallbackComment to one of:

  • %EXTERNAL(CallbackComment)
  • %INTERNAL(CallbackComment)
  • %ODBCOUT(CallbackComment)
  • referring to CSP.... you  let me assume you are working from Mgmt Portal
  • next you say:  I am using the Intersystem ODBC driver

This is a contradiction  as CSP doesn't use ODBC driver
So what are you really assuming to do ??

Anyhow in both cases a SQL String delimiter (') entered in a %String property aka VARCHAR is always presented as it was entered.
I verified it over an external ODBC viewer +  Intersystem ODBC driver as well as over JDBC:  no issue.

For ODBC on Windows just use 64bit Version on 64bit platforms.

in addition, you may verify your query also from the terminal prompt without any eventual timeout:

USER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>  << entering multiline statement mode >>
        1>>SELECT
        2>>CallbackComment
        3>>FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'
        4>>go

 

in addition, it would be worth to see what your SQ.CBPhoneResult_View does.

By the ending VIEW I assume it is a View and not a Table:
So the complexity of the executed query is hidden and could be the real performance problem. 

Comments

Sorry Community, I was trying to hide some information because of our company policies.

Below is the brief  details.

We have application which has one script. That script is using SQL query which i have already mentioned. so that SQL query is giving us the error like "ODBC read failed" in that application.  So i have verified the Inter System ODBC connection and that is working fine. So for more investigation, I have connected to cache database using cache management portal. There i have set the ODBC mode and I have executed that same SQL query,  And i got the error "CSP application closed the connection before sending the response". So manually i have verified the data and found out that whenever single quotes is come in data, it is giving this error. SO this is the scenario.

Please give your suggestion and let me know if you have any question.

Thanks in Advance. 

OK. now the picture gets clear.

 the key problem looks as if a single ' is interpreted as a String delimiter.
so instead of naked CallbackComment 
You may try

$TRANSLATE(CallbackComment  ,'''','?')

(it's really 4single quotes, no typo)
So you replace the single quote by a non-conflicting character BEFORE the string is passed to ODBC   
This would prove that the single quote is the cause of trouble.

Thanks for the Answer. But I'm new to this cache domain, Could you please provide me the modified query that i can run using management portal.

SELECT
$TRANSLATE(CallbackComment  ,'''','?')
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 

Also try

  • %EXTERNAL(CallbackComment)
  • %INTERNAL(CallbackComment)
  • %ODBCOUT(CallbackComment)

OK.
You use a Caché version before 2015.1 that doesn't know $TRANSLATE

It's then 

SELECT
 REPLACE(CallbackComment  ,'''','?')
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 

I expect you will run into a timeout again.
So please click to "Show Query Plan" and let us see what's going on.

Additionally, on left border click to Views;
select  SQ.CBPhoneResult_View and get an Image similar to this

important part:  VIEW TEXT


 

OK,now your timeout is clear.
with a Relative Cost of  ~2 millions, your query requires some support to speed up.
You just see the first 6 empty results.

First reason: you run over ALL records in  ...PhoneMaster
with an inner loop in  ...Testmaster on IndexCall,OrdeCode, TestCode,...Result_Index

So your timeout is not surprising.

I'd suggest creating an Index on PhoneDateODBC to speed up your query. >>  lower  Relative Cost
with 2 million I'm quite sure that you even timeout over ODBC.

So I'm back to my earlier suggestion:
Let your query run from terminal prompt,
have a coffee or two and maybe it is completed then.

This is not your fault.
Blame the designer of that ugly VIEW


 

You may also reformulate your SELECT to read directly from the tables used
starting with the one holding  PhoneDateODBC and forget all the other burden.

When I was selecting other field, there was no error. The problem is only with "CallbackComment" filed.

As you see the query plan is significantly shorter.
Your original refers to 3 other tables.
To find out why and how to improve would require all table/class  & view definitions

  • SQ.CBPhoneResult_View
  • SQ.CBPH_Phone
  • SQ. CBPH_Result      your 2nd query ends here  
  • SQ. CB_Test      access to  CallbackComment starts here
  • SQ. CB_Order
  • SQ. CB_Contact

Without the related class definition it is hard to say how these tables link to each other

But you really should run it from the terminal prompt to see your result at least once:

Do you have developer access to your Caché  at all? 
Can you see the class definitions in Studio ? 

I have run same query for 10th April. Below is the details. 

OK. You found 801 entries  with ~200 k global  access.
and the query works as expected

So 

SELECT COUNT(*) 
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-10'

for Tuesday Apr.10 it should return 801  

What is the count for Monday  2018-04-09 ?  I 'd expect more

SELECT
 COUNT(*)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 

If you limit the result set and increase the number, does it show any rows or other impact?

SELECT TOP 50
REPLACE(CallbackComment  ,'''','?')
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 

Up to 256 no issue because there is no special character single quotes in callbackcomment field. but 257 row has callbackcomment field with single quotes and it is given the error. verify below screenshot.

As it stops also with the REPLACE let'S do 2 other checks:

SELECT 
CallbackComment
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NULL

this verifies that the day plays no role

SELECT 
LENGTH(CallbackComment)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND NOT CallbackComment [ ''''

Now we exclude all single quotes
If this one fails means that we see a single quote but it might be some other character

I also changed to LENGTH as  it should not interfere with single quotes

[ is the contains operator

Very interesting result.

You fall in timeout even with CallbackComment IS NULL   !!!!!

so single quotes can't be part of the game. 
So this should also work

SELECT 
LENGTH(CallbackComment)
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NOT NULL

and this too

SELECT 
LENGTH(CallbackComment), CallbackComment
FROM SQ.CBPhoneResult_View
WHERE PhoneDateODBC = '2018-04-09' 
AND CallbackComment IS NOT NULL

This will indicate that some empty (NULL)  element is causing the troubles.

It might make sense to initialize all NULL CallbackComments with something.

You can do an additional check directly in the table that holds CallbacKComment

From the Query Plan I  assume it is 

  • SQ. CB_Contact

 something like 

SELECT ID,
LENGTH(CallbackComment), CallbackComment 
FROM SQ.CB_Contact
WHERE CallbackComment IS NOT NULL

this should lead you to the critical point and someone with enough 
privileges could take a look at the stored data if it is really a single quote or something else

I have already verified the data with LAB person and we have confirmed that the problem with special character single quotes '. 

if there is a problem with the single quote it should be in the query above as well. 
Your LAB person may look different to it. No by SQL.

So you also have no chance to add indices, check for implicit joins and other useful stuff.
And of course no terminal access.