HTTP_GET and HTTP_GET_BLOB
The HTTP_GET or HTTP_GET_BLOB scalar function retrieves a text-based resource from the specified URL through an HTTP GET request.
- url
- An expression that returns a built-in character string or graphic string data type that specifies the URL of the resource being accessed.
- options
- An expression that returns a built-in character string or graphic string data type. This string must be formatted as a JSON object containing the options, including headers, for the request. See HTTP options for the list of options.
The result of the HTTP_GET function is CLOB(2G) CCSID 1208 containing the response message. The result of the HTTP_GET_BLOB function is BLOB(2G) containing the response message.
Notes
For more information about using the HTTP functions see HTTP functions overview.
To return the header information from the HTTP request, use the HTTP_GET_VERBOSE or HTTP_GET_BLOB_VERBOSE table function: HTTP_GET_VERBOSE and HTTP_GET_BLOB_VERBOSE
Example
- Retrieve service information from the IBM® PSP website
using a certificate store created from a Java™ certificate
store. See SSL considerations for more information.
VALUES QSYS2.HTTP_GET( 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml', '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}');
HTTP options
{"option1":"option-setting1","option2":"option-setting2"}
{"headers":{"User-Agent":"IBM i HTTP function"}}
will cause
"User-Agent": "IBM i HTTP function"
to be included in the HTTP header sent to
the server. The following options can be set in the JSON object:
Option | Possible settings | Default setting | Description |
---|---|---|---|
basicAuth | userid,password | Sets the userid and password that will be used for HTTP basic
authentication. To prevent exposure of the userid and password, this option is only allowed when the URL begins with https:. |
|
connectTimeout | 0-2000000 | Dependent on TCP/IP system settings | Sets the connect timeout value. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a connection to complete. |
header | headername,headervalue | Sets an HTTP header in the HTTP request with the specified
headername and headervalue. This option can be specified multiple times to set multiple headers. It is recommended that the headers option be used instead of the header option. |
|
headers | {"header1":"value1", "header2":"value2", "header3":"value3" } | Sets one or many HTTP headers in the HTTP request. The setting
consists of a JSON object with key value pairs representing a header name and corresponding header
value. A common header that may be required is the Accept header. This header can be set in the following way: {"headers":{"Accept":"*"}} By default, only the Host and User-Agent HTTP header are set for all requests. The Host is set using the host information in the URL. The User-Agent header defaults to curl/XX.YY where XX.YY are some version numbers. This value was chosen to provide anonymity when accessing web services. If the content-type HTTP header is not set for a POST or PUT operation, the default content type that will be used is: Content-Type: text/xml; charset=UTF-8 To correctly use these headers, consult the appropriate Web server and RFC documentation on the use of HTTP request headers. Examples of RFCs are RFC2616 and RFC7231. |
|
ioTimeout | 0-2000000 | Dependent on TCP/IP system settings | Sets how long to wait for a read request to complete. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a read request to complete. |
proxy | host,port | Sets the HTTP proxy information. | |
proxyAuth | userid,password | Sets the HTTP proxy authentication information. To prevent exposure of the userid and password, this option is only allowed when the proxySsl option is true. |
|
proxySsl |
true
false |
true | Sets whether a secure (SSL) connection should be used when connecting to the proxy server. A value of true indicates that the transport will connect to the proxy server using a secure channel. A value of false indicates that the transport will connect to the proxy server using an unsecure channel. |
redirect | 0-2000000 | 0 | Specifies the number of HTTP redirects that will be followed. If the number is greater than zero, HTTP redirects will be followed up to the number specified. If the value is less than one, HTTP redirects will not be followed. |
signalErrors |
true
false |
false | Indicates whether an HTTP error should be signaled as an SQLException. This will be seen as SQLSTATE 38501, SQLCODE -443. |
sslCertificateStoreFile | file |
/QIBM/USERDATA/ICSS/
CERT/SERVER/DEFAULT.KDB |
Specifies the name of the certificate store file to be used for the
secure session or SSL environment. This parameter is ignored if the sslApplicationID option is set
to a value. If SSL communication is to be done by using a path to a keystore file, the user profile the application is running under must have authority to the file.
This option is only used when the URL begins with https:. |
sslCertificateStorePassword | password | Specifies the password for the certificate store file to be used
for the secure session or SSL environment. If the option is not passed or is set to the null string,
the internal stash file associated with the user profile that is being used to run the application
is used as long as the user has authority to the certificate store file and the password has been
used one time on the system. This option is ignored if the sslApplicationID option is set to a
value. This option is only used when the URL begins with https:. |
|
sslCertificateLabel | label | Specifies the label associated with the certificate in the
certificate store to be used for the secure session or SSL environment. If the option is not passed
or is set to the null string, the default certificate label in the specified certificate store file
is used for the SSL environment. This option is ignored if the sslApplicationID option is set to a
value. This option is only used when the URL begins with https:. |
|
sslTLS11 |
ENABLE
NONE |
NONE | Enable or disable the TLS Version 1.1 ciphers. A value of NONE will
disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:. |
sslTLS12 |
ENABLE
NONE |
ENABLE | Enable or disable the TLS Version 1.2 ciphers. A value of NONE will
disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:. |
sslTLS13 |
ENABLE
NONE |
ENABLE | Enable or disable the TLS Version 1.3 ciphers. A value of NONE will
disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:. |
sslTolerate |
true
false |
false | Tolerate soft validation errors (expired certificate or certificate
not in certificate store). Specify a value of true to tolerate soft validation errors, or false to
not tolerate soft validation errors. Warning: Enabling this option allows man-in-the-middle attacks to occur and is not recommended. This option is only used when the URL begins with https:. |
sslApplicationId | id | Application ID to use for the SSL environment. This option is only used when the URL begins with https:. |
|
sslDomainName | name | Set using hostname present in https: URL | Fully qualified domain name that will be used as Server Name
Indication (SNI) as defined by RFC 6066. This option is only used when the URL begins with https:. |
sslSniCritical |
true
false |
false | A value of true indicates that the SNI request is critical and thus
the server must support the SNI extension. Otherwise, the secure connection will fail if server does
not send the extension. A value of false indicates that the server does not need to support the SNI
extension. This option is only used when the URL begins with https:. |
verboseResponseHeaderFormat |
json
jsonCookiesArray jsonCookiesObject jsonString raw |
json | Specifies the formatting used by the RESPONSE_HTTP_HEADER column of
the HTTP_xxx_VERBOSE functions.
|
- Use a global variable and concatenate the global values when the SQL Statement is executed.
CREATE VARIABLE MYLIB.HTTP_PW VARCHAR(30); SET MYLIB.HTTP_PW = 'http_pwd'; VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', '{"basicAuth":"userid,' CONCAT MYLIB.HTTP_PW CONCAT '"}' );
- For interfaces that support a parameter marker, set the password using a parameter marker. Here
is an QSHELL example using the JDBC client provided in
jt400.jar.
java -jar /qibm/proddata/os400/jt400/lib/java6/jt400.jar jdbc:db2:localhost !PREPARE select QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', '{"basicAuth":"userid,' CONCAT ? CONCAT '"}' ) from sysibm.sysdummy1 !setParm 1,http_pwd !executeQuery
- Store passwords in a table, protected with a column
mask.
CREATE MYLIB.TABLE PASSWORDS (TYPE VARCHAR(20), PWD_VALUE VARCHAR(100) CCSID 37); CREATE MASK MYLIB.PASSWORD_MASK ON MYLIB.PASSWORDS FOR COLUMN PWD_VALUE RETURN CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'<my user id>') = 1) THEN PWD_VALUE ELSE 'Sorry' END ENABLE; ALTER TABLE MYLIB.PASSWORDS ACTIVATE COLUMN ACCESS CONTROL; INSERT INTO MYLIB.PASSWORDS VALUES ('HTTP', 'My secure password'); VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', '{"basicAuth":"userid,' CONCAT (SELECT PWD_VALUE FROM MYLIB.PASSWORDS WHERE TYPE = 'HTTP') CONCAT '"}' );