New post
Question
· Jul 7, 2018

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. 

Discussion (30)0
Log in or sign up to continue
Robert Cemper · Jul 8, 2018

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

0 0
Robert Cemper · Jul 8, 2018

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

0 0
Robert Cemper · Jul 8, 2018

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.

0 0
Robert Cemper · Jul 8, 2018

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 ? 

0 0
Robert Cemper · Jul 8, 2018

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'
0 0
Robert Cemper · Jul 8, 2018

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

0 0
Robert Cemper · Jul 8, 2018

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.

0 0
Robert Cemper · Jul 8, 2018

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

0 0
Robert Cemper · Jul 7, 2018
  • 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
 
0 0
Pradip Patoliya · Jul 8, 2018

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. 

0 0