SQL Anywhere 17: EXCEPTION for HTTP/1.1 302 Found

[former_SQLA_member326303]

I am faced with the task of logging in and getting cookies from the site for further work.
I used the following constructs (for SQL Ahywhere 12.0.1 and 16.0):

create or replace procedure "dba"."fa_cookies"( 
 "urlSpec" long varchar,
 "certif " long varchar ) 
 url '!urlSpec'
 type 'HTTP:POST:text/plain'
 certificate '!certif'
 set 'REDIR (COUNT = 0)'
 header 'X-Requested-With: X-Requested-With';
...
insert
 into web_results
 select *
 from "dba"."fa_cookies"(@urlSpec,@certif) 
 with ( "attribute" long varchar, "value" long varchar );
...
select "value" into @value
 from web_results
 where lower("attribute")='set-cookie';
...

However, for SQL Ahywhere 17.0.9, I get an exception. For example:
for SQL Anywhere Network Server Version 16.0 (User-Agent: SQLAnywhere/16.0.0.2754):

[connid = 1, 12/26 10:41:24.760, RESPONSE]
HTTP/1.1 302 Found
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-prototype-version,x-requested-with
Access-Control-Allow-Methods: GET,POST,OPTIONS
Access-Control-Allow-Origin: *
Cache-control: no-cache="set-cookie"
Content-Type: text/plain
Date: Wed, 26 Dec 2018 07:41:40 GMT
Location: ...
p3p: CP="CAO PSA OUR"
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=0924DA43AAA4FFDADF636C29D930E840; Path=/; HttpOnly
Set-Cookie: AWSELB=8F27A917087250F091AC8F71E76AC3D9E7E728D4D8E2C0F1262262FE77BD4A99EEADD46879959010D4F134C3DCD0A903A0B8AA6E1F7BD75BB9AE0DEAE59C91BBF6DF5FCCCC;PATH=/;MAX-AGE=1800
Content-Length: 0
Connection: Close
[connid = 1, socket closed by peer]
[connid = 1, socket closed]
  
for SQL Anywhere Network Server Version 17.0.9 (User-Agent: SQLAnywhere/17.0.9.4913):
[connid = 20, [26/12/2018:10:05:14.489 0300], RESPONSE]
HTTP/1.1 302 Found
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-prototype-version,x-requested-with
Access-Control-Allow-Methods: GET,POST,OPTIONS
Access-Control-Allow-Origin: *
Cache-control: no-cache="set-cookie"
Content-Type: text/plain
Date: Wed, 26 Dec 2018 07:05:31 GMT
Location: ...
p3p: CP="CAO PSA OUR"
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=8C030B605E82C4D43C2ED58CFC57483C; Path=/; HttpOnly
Set-Cookie: AWSELB=8F27A917087250F091AC8F71E76AC3D9E7E728D4D8901F6A19976871D7B971EC231237E934420A2E6CB2A4E72C06F49CF0CD75C8F75952868417CDC62AA0D62B5A24055B2F;PATH=/;MAX-AGE=1800
Content-Length: 0
Connection: Close
[connid = 20, socket closed]

and EXCEPTION: 
SQLCODE = -990, SQLSTATE = WW059, ERRORMSG() = The secure connection to the remote host failed: The connection was closed from the other side

Is this behavior of SQL Anywhere 17 a new feature?

Is it possible to avoid this exceptions for SQL Anywhere 17?

[Breck_Carter]

Can you show us the exact SQL statement that raised the exception?

[VolkerBarth]

As the web server returns two Set-Cookie headers, it may (or may not!) have to do with changes introduces with 17.0.4.2000:

See the changes documented for multiple headers with the same name.

That's a very wild guess, another one is the newly introduced EXCEPTIONS option for the CREATE PROCEDURE statement documented on the same page.

In my understanding, the "SELECT value INTO @value" statement should fail when there are more than one rows fitting the WHERE clause...

[former_SQLA_member326303]

I apologize for the delay in replying.
Let:

create or replace procedure "dba"."fa_cookie"( 
 "urlSpec" long varchar,
 "certif" long varchar ) 
 url '!urlSpec'
 type 'HTTP:POST:text/plain'
 certificate '!certif'
 set 'REDIR (COUNT = 0)'
 header 'X-Requested-With: X-Requested-With'
;
call "dba"."fa_cookie"(
'https://d4wnew.1stAvailable.com.au/j_spring_security_check?j_username=...&j_password=...&_spring_security_remember_me=off',
'file=D:\\asa\\DT00022758\\1stA_root_20200625.crt'); 

ASA16:

Attribute,Value
Status,HTTP/1.1 302 Found
Body,
Connection,Close
Access-Control-Allow-Headers,x-prototype-version,x-requested-with
Access-Control-Allow-Methods,GET,POST,OPTIONS
Server,Apache-Coyote/1.1
Location,/login/ajaxSuccess;jsessionid=BE64FBF7976B9CA0209310D9DCBEB3FA
Content-Length,0
Content-Type,text/plain
Cache-control,no-cache="set-cookie"
Set-Cookie,AWSELB=8F27A917087250F091AC8F71E76AC3D9E7E728D4D8901F6A19976871D7B971EC231237E934959010D4F134C3DCD0A903A0B8AA6E1F5952868417CDC62AA0D62B5A24055B2F;PATH=/;MAX-AGE=1800
Access-Control-Allow-Origin,*
Access-Control-Allow-Credentials,true
p3p,CP="CAO PSA OUR"
Date,Wed, 09 Jan 2019 14:55:55 GMT

ASA17:

call "dba"."fa_cookie"(
'https://d4wnew.1stAvailable.com.au/j_spring_security_check?j_username=...&j_password=...&_spring_security_remember_me=off',
'file=D:\\asa\\DT00022758\\1stA_root_20200625.crt')
-- Could not execute statement.
-- The secure connection to the remote host failed: The connection was
-- closed from the other side
-- SQLCODE=-990, ODBC 3 State="HY000"
-- Procedure completed

[former_SQLA_member326303]

Unfortunately,

SET 'HTTP(EXCEPTIONS=OFF), REDIR(COUNT=0)' 

did not help get rid of the error.
Using the third output attribute ("Instance") in RESULT:

RESULT(
"Attribute"  LONG VARCHAR,
"Value"      LONG VARCHAR,
"Instance"   integer)

or in SELECT:

select * from "dba"."fa_cookie"(..., ...)
with("attribute" long varchar,"value" long varchar,"Instance" integer); 

also does not help.
I assume that an empty response body causes this error.
ps I apologize for the delay in replying.

[VolkerBarth]

Well, according to the web log snippets from your question, it seems that both versions receive similar responses from the server, so I'm not sure why the web procedures do not behave identically...:(

Is there any difference in the request they generate (also visible in the web log output)?

[former_SQLA_member326303]

Yes, both versions get the same responds. Except for the end of zoc-file.
ASA16:

[connid = 1, socket closed by peer]
[connid = 1, socket closed]

ASA17:

[connid = 20, socket closed]

[VolkerBarth]

And what about the requests (not the responses)? Do they differ?

[former_SQLA_member326303]

ASA16:

[connid = 1, 01/11 15:39:12.264]
[connid = 1, 01/11 15:39:12.264, REQUEST]
POST /j_spring_security_check?j_username=...&j_password=...&_spring_security_remember_me=off HTTP/1.0
Content-Type: text/plain; charset=windows-1251
Accept-Charset: windows-1251, UTF-8, *
Connection: close
ASA-Id: 17a23c58d3d94e20a81b4b133718fbd9
X-Requested-With: X-Requested-With
Host: d4wnew.1stAvailable.com.au
Content-Length: 0
User-Agent: SQLAnywhere/16.0.0.2754
Date: Fri, 11 Jan 2019 12:39:12 GMT
[connid = 1, 01/11 15:39:13.494, RESPONSE]
HTTP/1.1 302 Found
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-prototype-version,x-requested-with
Access-Control-Allow-Methods: GET,POST,OPTIONS
Access-Control-Allow-Origin: *
Cache-control: no-cache="set-cookie"
Content-Type: text/plain
Date: Fri, 11 Jan 2019 12:39:37 GMT
Location: /login/ajaxSuccess;jsessionid=B71010AE156064F0FCA386D21FA979B6
p3p: CP="CAO PSA OUR"
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=B71010AE156064F0FCA386D21FA979B6; Path=/; HttpOnly
Set-Cookie: AWSELB=8F27A917087250F091AC8F71E76AC3D9E7E728D4D8E2C0F1262262FE77BD4A99EEADD46879420A2E6CB2A4E72C06F49CF0CD75C8F739596CB10F01749990E747F12C9295DB;PATH=/;MAX-AGE=1800
Content-Length: 0
Connection: Close
[connid = 1, socket closed by peer]
[connid = 1, socket closed]
ASA17:
[connid = 1, [11/01/2019:15:40:44.447 0300]]
[connid = 1, [11/01/2019:15:40:44.457 0300], REQUEST]
POST /j_spring_security_check?j_username=...&j_password=...&_spring_security_remember_me=off HTTP/1.0
Connection: close
X-Requested-With: X-Requested-With
Content-Type: text/plain; charset=windows-1251
Content-Length: 0
Date: Fri, 11 Jan 2019 12:40:44 GMT
Accept-Charset: windows-1251, UTF-8, *
ASA-Id: 6ef2a05b286f40768a672242f1af07a8
Host: d4wnew.1stAvailable.com.au
User-Agent: SQLAnywhere/17.0.9.4913
[connid = 1, [11/01/2019:15:40:45.587 0300], RESPONSE]
HTTP/1.1 302 Found
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: x-prototype-version,x-requested-with
Access-Control-Allow-Methods: GET,POST,OPTIONS
Access-Control-Allow-Origin: *
Cache-control: no-cache="set-cookie"
Content-Type: text/plain
Date: Fri, 11 Jan 2019 12:41:09 GMT
Location: /login/ajaxSuccess;jsessionid=A942AE7AF4F3CBCB35F971C87F9C5EAA
p3p: CP="CAO PSA OUR"
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=A942AE7AF4F3CBCB35F971C87F9C5EAA; Path=/; HttpOnly
Set-Cookie: AWSELB=8F27A917087250F091AC8F71E76AC3D9E7E728D4D8E2C0F1262262FE77BD4A99EEADD46879959010D4F134C3DCD0A903A0B8AA6E1F39596CB10F01749990E747F12C9295DB;PATH=/;MAX-AGE=1800
Content-Length: 0
Connection: Close
[connid = 1, socket closed]
and 
Could not execute statement.
The secure connection to the remote host failed: The connection was
closed from the other side
SQLCODE=-990, ODBC 3 State="HY000"
Line 10, column 1
call "dba"."fa_cookie"(
'https://d4wnew.1stAvailable.com.au/j_spring_security_check?j_username=...&j_password=...&_spring_security_remember_me=off',
'file=D:\\asa\\DT00022758\\1stA_root_20200625.crt') 

[VolkerBarth]

Well, I hope Graeme or other SQL Anywhere engineers have a clue, I don't have one...

[former_SQLA_member326303]

Thanks (((

[graeme_perrow]

I'm afraid I'm unable to reproduce this behaviour here. Would it be possible for me to test against your HTTPS server? If that's OK, can you email me (myfirstname.mylastname @ sap.com) the root certificate (i.e. 1stA_root_20200625.crt)?

[graeme_perrow · Accepted solution]

Sorry for the long delay but this problem is fixed. The fix will be included in the upcoming 17.0.10 release.

[former_SQLA_member326303 · Accepted solution]

For SQL Anywhere 17.0 SP0 PL41 Build 5745 https-request works fine.

[ximen]

I'm also bothered by this problem with cors , Hi you can send me an email and I would like to communicate with you to facilitate communication at the technical level. My email is myname@gmail.com