I'm trying:
ALTER FUNCTION "DBA"."GetWebData"()
RETURNS long varchar
URL 'https://wis.no/filer/fjernhjelp.html'
--TYPE 'HTTP:POST'
--set 'HTTP(VERSION=1.1)'
CERTIFICATE 'certificate=-----BEGIN CERTIFICATE-----
MIIFaDCCBFCgAwIBAgISBNIdUcx83nq4AHn4apqeKFbsMA0GCSqGSIb3DQEBCwUA
MEoxCzAJBgNVBAYTAlVTMRYwFAYDVQQKEw1MZXQncyBFbmNyeXB0MSMwIQYDVQQD
ExpMZXQncyBFbmNyeXB0IEF1dGhvcml0eSBYMzAeFw0yMDExMjMyMzExMjBaFw0y
MTAyMjEyMzExMjBaMBQxEjAQBgNVBAMTCXdpc3dlYi5ubzCCASIwDQYJKoZIhvcN
AQEBBQADggEPADCCAQoCggEBALiyLtibtiDjFn7jyPt1umOQ6uSUiqg3bw/1A7xh
tiZQUI79QmySLHWHqBGPrN4+WQ9Pw2djhok75evlr+4O5ZxiOeTatc3BOt2EPTV/
TgeZtQJLs50TcFkkPpH25jrSF0VGfYJS+7OYDJyg8sRk1+ghZ7vAxTxPE9dXo1he
sfjZs3KGNfl4YMwupNmGs+mEitgHLmTlpw5qVajnKKcprKGCeNl94jVsijfZjW6F
JCtxoHINKog8X7dkwaVbytIRFjAe5fu8fJhsShrNOydUhpAAPwItyN01boBecNM5
SxwlprWCZBJh7W2XwHiB8PYh8yPXv5FCQwOBT1NDuByLIJECAwEAAaOCAnwwggJ4
MA4GA1UdDwEB/wQEAwIFoDAdBgNVHSUEFjAUBggrBgEFBQcDAQYIKwYBBQUHAwIw
DAYDVR0TAQH/BAIwADAdBgNVHQ4EFgQUrOFKjaaTVOF8xhmBmRKHfWwUDVQwHwYD
VR0jBBgwFoAUqEpqYwR93brm0Tm3pkVl7/Oo7KEwbwYIKwYBBQUHAQEEYzBhMC4G
CCsGAQUFBzABhiJodHRwOi8vb2NzcC5pbnQteDMubGV0c2VuY3J5cHQub3JnMC8G
CCsGAQUFBzAChiNodHRwOi8vY2VydC5pbnQteDMubGV0c2VuY3J5cHQub3JnLzAz
BgNVHREELDAqgggqLndpcy5ub4ILKi53aXN3ZWIubm+CBndpcy5ub4IJd2lzd2Vi
Lm5vMEwGA1UdIARFMEMwCAYGZ4EMAQIBMDcGCysGAQQBgt8TAQEBMCgwJgYIKwYB
BQUHAgEWGmh0dHA6Ly9jcHMubGV0c2VuY3J5cHQub3JnMIIBAwYKKwYBBAHWeQIE
AgSB9ASB8QDvAHYA9lyUL9F3MCIUVBgIMJRWjuNNExkzv98MLyALzE7xZOMAAAF1
95crFgAABAMARzBFAiBOw7nBgGCqpBxcsf3U30LzG2STW81GmQz3iCN4AGO83QIh
AMFRSNB1ylvDKrgPVA/T76qkE0T8UCqcfovB3Nnzgy96AHUAb1N2rDHwMRnYmQCk
URX/dxUcEdkCwQApBo2yCJo32RMAAAF195csKwAABAMARjBEAiBUTdjsJ0gZGb7t
m2zmkF8LI51qHlmiO9hdnzO7Yak+7QIgDY4XTGYR/Y64R9JxdsiI/vBj0dJXw8Bn
MadElwzxggIwDQYJKoZIhvcNAQELBQADggEBABkSLwrm2wPXGQQKoTRyY8qNcY/O
+G7rbjd4WCrBeV9fsveLP+xySKLZc5mn/DFyvXZ0ceNP2t8MEZpmRGqKmfyv1Ah5
KL0cBOMD9GSpRcuJ4egm8GAg8+WEYeaAfwq75nWF/msh2vla9IJtSdGgbpz2K6+2
sqI6KuuRJrcTm13cgHzLymrf5VdDH1Uy3B1cYFyir2oVIT38ArgRtQ04aWA+vivT
ekuR02o+vu5x0puHlkXuXT+RHtGrbhl9Wj4VjLZYBbjnNt1gQhEnTtWEJbkcEMB4
PcKrEaxduQaMPg81JQb9h6TB8ZBgARUKwufSpYiymjI35nV51x7q4VA+Zr4=
-----END CERTIFICATE-----';

But that only gives:
ERROR: SQL Anywhere Error -983: HTTP request failed. Status code
'<none>'
Query = Select GetWebData()

asked 08 Dec '20, 02:34

Ove%20Halseth2's gravatar image

Ove Halseth2
113
accept rate: 0%

What version of SQL Anywhere is this being encountered? I do not get an error for this code sample.

(08 Dec '20, 07:12) Chris Keating
Replies hidden
Comment Text Removed

The Select GetWebData() throws this error in V17...

Select GetWebData()
-- There was an error reading the results of the SQL statement.
-- The displayed results may be incorrect or incomplete.
-- The secure connection to the remote host failed: The TLS handshake
-- failed, error code 0x14090086
-- SQLCODE=-990, ODBC 3 State="HY000"

Similar error in V16...

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.

The secure connection to the remote host failed: The TLS handshake
failed, error code 20
SQLCODE=-990, ODBC 3 State="HY000"
Line 1, column 1
(Continuing after error)
(08 Dec '20, 09:05) Breck Carter
Replies hidden

IMHO, the switch within v16 from Certicom (?) to OpenSSL and within v17 from OpenSSL to SAP's own crypt lib makes it difficult to state that a HTTPS web client call does work with v16/v17 generally... Been there, done that... :)

(08 Dec '20, 09:39) Volker Barth

This is not the same error but do also have a look at that FAQ because of the HTTP version option.

(08 Dec '20, 09:42) Volker Barth

Well this is odd, no error is reported in v17 if the function name used is different. I tested this as foo, foobar, and myproc and get a return value. Out of habit, I tested by altering an existing proc foo that I have in my test db. With GetWebData, I see the following:

There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. The specified certificate is not valid SQLCODE=-1588, ODBC 3 State="HY000"

(08 Dec '20, 09:44) Chris Keating

(08 Dec '20, 10:38) Breck Carter
Comment Text Removed

It seems to be working fine for me. Select GetWebData() returns what looks to be the source of the page in German.

Reg

PS: Attempts at pasting HTML into my response have been removed.

(08 Dec '20, 16:33) Reg Domaratzki

It is version: dbsrv12 GA 12 0 1 3967 linux 2013/09/04 15:54:03 posix 64 production

(09 Dec '20, 00:28) Ove Halseth2

Great! Or... I was hoping that it was something wrong with my statement, not something missing in my installation... What version are you testing on?

Oh, and it is just a dummy static page that i could easily find trace of it in our webserver log. And as long as I have been trying, nothing in our webserver log. But I think I found trace of your tries: nn.71.69.247 - - [08/Dec/2020:13:09:43 +0100] "POST /filer/fjernhjelp.html HTTP/1.0" 200 4311 "-" "SQLAnywhere"

And German is close, it's Norwegian :)

(09 Dec '20, 00:46) Ove Halseth2

If you turn on webclient logging, what do you see in your log?
CALL sa_server_option( 'WebClientLogFile', '/home/sybase/db/wisweb/webclient.log' );
CALL sa_server_option( 'WebClientLogging', 'ON' );

(09 Dec '20, 01:32) Ove Halseth2

FWIW, with 17.0.10.6175 the request from the original question does work for me, i.e. with the name "GetWebData()".

(09 Dec '20, 04:29) Volker Barth

I was using version 17.0.10.6230 when I was able to successfully connect, and Volker was able to connect with 17.0.10.6175. Build 6230 will be the next Windows EBF posted shortly.

(09 Dec '20, 09:10) Reg Domaratzki
More comments hidden
showing 4 of 12 show all flat view

Trying against a test http page works fine: ALTER FUNCTION "DBA"."GetNSRData"() returns long varchar url 'http://help.websiteos.com/websiteos/example_of_a_simple_html_page.htm'

And trying my https page without certificate gives: ERROR: SQL Anywhere Error -990: The secure connection to the remote host failed: A certificate file must be specified

Trying with wrong certificate(changed 3 first letters) gives: ERROR: SQL Anywhere Error -990: The secure connection to the remote host failed: Error parsing certificate file, error code 545

So it looks like it gets past the certificate, but the error: ERROR: SQL Anywhere Error -983: HTTP request failed. Status code '<NONE>'

Does not give me much to go on. And it fails without talking to our webserver...

permanent link

answered 09 Dec '20, 00:57

Ove%20Halseth2's gravatar image

Ove Halseth2
113
accept rate: 0%

Ah, just found:
CALL sa_server_option( 'WebClientLogFile', '/home/sybase/db/wisweb/webclient.log' );
CALL sa_server_option( 'WebClientLogging', 'ON' );

And it gives a little bit more:
[connid = 19, 12/09 07:26:16.813]
[connid = 19, 12/09 07:26:16.813, REQUEST]
GET /filer/fjernhjelp.html HTTP/1.1
Connection: close
ASA-Id: 34f5221c39f811eb8000bcfab9ef8940
Accept-Charset: UTF-8, *
Date: Wed, 09 Dec 2020 06:26:16 GMT
Host: wis.no
User-Agent: SQLAnywhere/12.0.1.4378

[connid = 19, Error: socket closed by peer]
[connid = 19, socket closed]

(This one with the options TYPE 'HTTP:GET' and SET 'HTTP(VERSION=1.1)')

What's up with the "Connection: close"?
And all the time, no traces of this in our webservers logfiles :(

(09 Dec '20, 01:30) Ove Halseth2
Replies hidden
1

User-Agent: SQLAnywhere/12.0.1.4378

You are using v12.0.1, which reached end of mainstream maintenance 4 years ago. The build you are using is almost five years old and contains an ancient build (by security standards) of OpenSSL.

Have you considered testing on a supported version of SQL Anywhere? All the certificate issues you have been encountering appear to be resolved in v17.0.10.

Reg

(09 Dec '20, 09:25) Reg Domaratzki

What's up with the "Connection: close"?

This is the default behaviour unless you have specified a keep-alive timeout value using the KTIMEOUT (short form KTO) protocol-option-string.

https://help.sap.com/viewer/93079d4ba8e44920ae63ffb4def91f5b/17.0/en-US/3be475ec6c5f10149a70de3d57f416de.html?q=KTimeout

(09 Dec '20, 09:31) Reg Domaratzki

I found the solution in this thread:
https://sqlanywhere-forum.sap.com/questions/26350/how-to-download-a-file-from-internet-using-sql-anywhere-11-that-requires-a-certificate

I had to use the root certificate. So for my version of SQL Anywhere, this function works:
ALTER FUNCTION "DBA"."GetWebData"()
returns long varchar
url 'https://wis.no/filer/fjernhjelp.html'
certificate 'certificate=-----BEGIN CERTIFICATE-----
MIIDSjCCAjKgAwIBAgIQRK+wgNajJ7qJMDmGLvhAazANBgkqhkiG9w0BAQUFADA/
MSQwIgYDVQQKExtEaWdpdGFsIFNpZ25hdHVyZSBUcnVzdCBDby4xFzAVBgNVBAMT
DkRTVCBSb290IENBIFgzMB4XDTAwMDkzMDIxMTIxOVoXDTIxMDkzMDE0MDExNVow
PzEkMCIGA1UEChMbRGlnaXRhbCBTaWduYXR1cmUgVHJ1c3QgQ28uMRcwFQYDVQQD
Ew5EU1QgUm9vdCBDQSBYMzCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEB
AN+v6ZdQCINXtMxiZfaQguzH0yxrMMpb7NnDfcdAwRgUi+DoM3ZJKuM/IUmTrE4O
rz5Iy2Xu/NMhD2XSKtkyj4zl93ewEnu1lcCJo6m67XMuegwGMoOifooUMM0RoOEq
OLl5CjH9UL2AZd+3UWODyOKIYepLYYHsUmu5ouJLGiifSKOeDNoJjj4XLh7dIN9b
xiqKqy69cK3FCxolkHRyxXtqqzTWMIn/5WgTe1QLyNau7Fqckh49ZLOMxt+/yUFw
7BZy1SbsOFU5Q9D8/RhcQPGX69Wam40dutolucbY38EVAjqr2m7xPi71XAicPNaD
aeQQmxkqtilX4+U9m5/wAl0CAwEAAaNCMEAwDwYDVR0TAQH/BAUwAwEB/zAOBgNV
HQ8BAf8EBAMCAQYwHQYDVR0OBBYEFMSnsaR7LHH62+FLkHX/xBVghYkQMA0GCSqG
SIb3DQEBBQUAA4IBAQCjGiybFwBcqR7uKGY3Or+Dxz9LwwmglSBd49lZRNI+DT69
ikugdB/OEIKcdBodfpga3csTS7MgROSR6cz8faXbauX+5v3gTt23ADq1cEmv8uXr
AvHRAosZy5Q6XkjEGB5YGV8eAlrwDPGxrancWYaLbumR9YbK+rlmM6pZW87ipxZz
R8srzJmwN0jP41ZL9c8PDHIyh8bwRLtTcm1D9SZImlJnt1ir/md2cXjbDaJWFBM5
JDGFoqgCWjBH4d1QB7wCCZAA62RjYJsWvIjJEubSfZGL+T0yjWW06XyxV3bqxbYo
Ob8VZRzI9neWagqNdwvYkQsEjgfbKbYK7p2CNTUQ
-----END CERTIFICATE-----'

permanent link

answered 09 Dec '20, 01:46

Ove%20Halseth2's gravatar image

Ove Halseth2
113
accept rate: 0%

edited 09 Dec '20, 01:48

Bah, but then it failed again when trying with the url I wanted to get data from:
ALTER FUNCTION "DBA"."GetWebData"()
returns long varchar
URL 'https://data-nsr.udir.no/enhet/1004669?api_key=%2Fenhet%2F1009346'
certificate 'certificate=-----BEGIN CERTIFICATE-----
MIIDtzCCAp+gAwIBAgIQDOfg5RfYRv6P5WD8G/AwOTANBgkqhkiG9w0BAQUFADBl
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3
d3cuZGlnaWNlcnQuY29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJv
b3QgQ0EwHhcNMDYxMTEwMDAwMDAwWhcNMzExMTEwMDAwMDAwWjBlMQswCQYDVQQG
EwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNl
cnQuY29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJvb3QgQ0EwggEi
MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCtDhXO5EOAXLGH87dg+XESpa7c
JpSIqvTO9SA5KFhgDPiA2qkVlTJhPLWxKISKityfCgyDF3qPkKyK53lTXDGEKvYP
mDI2dsze3Tyoou9q+yHyUmHfnyDXH+Kx2f4YZNISW1/5WBg1vEfNoTb5a3/UsDg+
wRvDjDPZ2C8Y/igPs6eD1sNuRMBhNZYW/lmci3Zt1/GiSw0r/wty2p5g0I6QNcZ4
VYcgoc/lbQrISXwxmDNsIumH0DJaoroTghHtORedmTpyoeb6pNnVFzF1roV9Iq4/
AUaG9ih5yLHa5FcXxH4cDrC0kqZWs72yl+2qp/C3xag/lRbQ/6GW6whfGHdPAgMB
AAGjYzBhMA4GA1UdDwEB/wQEAwIBhjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdDgQW
BBRF66Kv9JLLgjEtUYunpyGd823IDzAfBgNVHSMEGDAWgBRF66Kv9JLLgjEtUYun
pyGd823IDzANBgkqhkiG9w0BAQUFAAOCAQEAog683+Lt8ONyc3pklL/3cmbYMuRC
dWKuh+vy1dneVrOfzM4UKLkNl2BcEkxY5NM9g0lFWJc1aRqoR+pWxnmrEthngYTf
fwk8lOa4JiwgvT2zKIn3X/8i4peEH+ll74fg38FnSbNd67IJKusm7Xi+fT8r87cm
NW1fiQG2SVufAQWbqz0lwcy2f8Lxb4bG+mRo64EtlOtCt/qMHt1i8b5QZ7dsvfPx
H2sMNgcWfzd8qVttevESRmCD1ycEvkvOl77DZypoEd+A5wwzZr8TDRRu838fYxAe
+o0bJW1sj6W3YQGx0qMmoRBxna3iw/nDmVG3KwcIzi7mULKn+gpFL6Lw8g==
-----END CERTIFICATE-----'

Where the certificate is the root certificate from data-nsr.udir.no exported as base64 the same way that worked for the test against our own webserver...

(09 Dec '20, 02:01) Ove Halseth2
Replies hidden

With v17.0.10.6175, I do get here

HTTP request failed. Status code '405 Method Not Allowed'
SQLCODE=-983, ODBC 3 State="HY000"

so this does not seem to be a certificate problem but a wrong URL or wrong HTTP method.

Adding "TYPE 'HTTP:GET'" to the function's definition makes the call work for me and returns a JSON object (with data of a Norwegian school, methinks).

(09 Dec '20, 04:34) Volker Barth

Yes, I'm after data on Norwegian schools :)
Adding Type 'HTTP:GET' does not help for me:(
It gives the error:
ERROR: SQL Anywhere Error -988: Invalid response from the HTTP server
And nothing helpful in the logfile:(

BUT I would like it in xml(as an input to OpenXML...), so I try to add:
Type 'HTTP:GET:application/xml'
But that gives:
ERROR: SQL Anywhere Error -987: Invalid procedure subtype
'GET:application/xml' for type 'HTTP'

So back to:
Type 'HTTP:GET'
Header 'accept: application/xml'
Then I'm back to:
ERROR: SQL Anywhere Error -988: Invalid response from the HTTP server

(09 Dec '20, 09:12) Ove Halseth2
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×435

question asked: 08 Dec '20, 02:34

question was seen: 191 times

last updated: 09 Dec '20, 09:33