Workshop Oktoberfest

Check_European_Tax

-- Rainer Ross DE154580259, Toolmaker DE128668519, Giesswein ATU31774906

values QSYS2.HTTP_POST(
      'http://ec.europa.eu/taxation_customs/vies/services/checkVatService',
      '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
        <soapenv:Header/>
         <soapenv:Body>
          <urn:checkVat>
           <urn:countryCode>DE</urn:countryCode>
           <urn:vatNumber>154580259</urn:vatNumber>
          </urn:checkVat>
         </soapenv:Body>
        </soapenv:Envelope>',
      null
    );

-- Ergebnis mit XML Parsing

Select *
  from
  XMLTABLE('*:Envelope/*:Body/*:checkVatResponse'
   passing xmlparse(document 
    QSYS2.HTTP_POST(
      'http://ec.europa.eu/taxation_customs/vies/services/checkVatService',
      '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:ec.europa.eu:taxud:vies:services:checkVat:types">
        <soapenv:Header/>
         <soapenv:Body>
          <urn:checkVat>
           <urn:countryCode>AT</urn:countryCode>
           <urn:vatNumber>U31774906</urn:vatNumber><!-- Giesswein -->
          </urn:checkVat>
         </soapenv:Body>
        </soapenv:Envelope>',
      null
    )
   )
    COLUMNS
     CountryCode varchar(02)             Path '*:countryCode',
     VatNumber   varchar(10)             Path '*:vatNumber',
     RequestDate varchar(20)             Path '*:requestDate',
     Valid       varchar(10)             Path '*:valid',
     Name        varchar(60)  ccsid 1208 Path '*:name',
     Address     varchar(90)  ccsid 1208 Path '*:address'
 ); 

GoogleMaps_Distance

-- JSON Daten

Select * 
  from JSON_TABLE(
    QSYS2.HTTP_GET(
     'https://maps.googleapis.com/maps/api/distancematrix/json?origins=' concat
     QSYS2.URL_ENCODE('Bürgermeister-Hollweck-Straße 6, 85599 Vaterstetten DE') concat '&destinations=' concat
     QSYS2.URL_ENCODE('VRMW+7J 38066 Riva del Garda It') concat
    '&key=yourKey',
    '{
        "header":"Content-Type,application/json; charset=utf-8",
        "header":"Accept,*/*"
     }'    
    ),
     '$'
     columns (
       Origin      varchar(128) path '$.origin_addresses'      default '' on empty,
       Destination varchar(128) path '$.destination_addresses' default '' on empty,
       nested '$.rows[0].elements[*]' columns (         
         Distance  varchar(15) path '$.distance.text'          default '' on empty,
         Duration  varchar(15) path '$.duration.text'          default '' on empty
       ) 
     )
);

-- XML Daten

 
 SELECT *
   FROM XMLTABLE('DistanceMatrixResponse' PASSING
	XMLPARSE(DOCUMENT QSYS2.HTTP_GET(
	'https://maps.googleapis.com/maps/api/distancematrix/xml?origins=' concat
     QSYS2.URL_ENCODE('Bürgermeister-Hollweck-Straße 6, 85599 Vaterstetten DE') concat '&destinations=' concat
     QSYS2.URL_ENCODE('VRMW+7J 38066 Riva del Garda It') concat
    '&key=AIzaSyDYTuz0BaK_PYp2iSKKNVJow7Q3-MsmSvo',
    '{
        "header":"Content-Type,application/json; charset=utf-8",
        "header":"Accept,*/*"
     }' 
	))
	COLUMNS
	 Origin        varchar(128) path 'origin_address',
	 Destination   varchar(128) path 'destination_address',	 
	 Distance      varchar(15)  path 'row/element/distance/text',
	 Duration      varchar(15)  path 'row/element/duration/text'
 );

GoogleMaps_Geolocation

Select * 
  from JSON_TABLE(
    QSYS2.HTTP_GET(
      'https://maps.googleapis.com/maps/api/geocode/json?address=' concat
       QSYS2.URL_ENCODE('Bürgermeister.-Hollweck Str. 6, 85599 Parsdorf') concat
      '&key=yourKey:DE',
      '{
        "header":"Content-Type,application/json; charset=utf-8",
        "header":"Accept,*/*"
      }'
    ), '$'
     columns (
       nested '$.results[*]' columns (         
         FormattedAddress varchar(100) path '$.formatted_address'     default '' on empty,
         Latitude         dec(11, 8)   path '$.geometry.location.lat' default 0  on empty,
         Longitude        dec(11, 8)   path '$.geometry.location.lng' default 0  on empty,
         nested '$.address_components[*]' columns (
           LongName       varchar(30)  path '$.long_name'             default '' on empty,
           ShortName      varchar(30)  path '$.short_name'            default '' on empty,
           nested '$.types[0]' columns (
            Types1        varchar(100) path '$'                       default '' on empty
           )
         )
       ) 
     )
);

http.conf

# Configuration originally created by Create HTTP Server wizard on Tue Sep 17 14:21:08 CEST 2024

Listen *:8030
DocumentRoot /www/rrserver01/htdocs
TraceEnable Off
Options -FollowSymLinks

#=========================================================================
# CCSID's
#=========================================================================

DefaultNetCCSID 1208
DefaultFsCCSID 1141

#=========================================================================
# Logs
#=========================================================================

LogFormat "%h %T %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%{Cookie}n \"%r\" %t" cookie
LogFormat "%{User-agent}i" agent
LogFormat "%{Referer}i -> %U" referer
LogFormat "%h %l %u %t \"%r\" %>s %b" common
CustomLog logs/access_log combined
LogMaint logs/access_log 30 0
LogMaint logs/error_log 30 0

#=========================================================================
# Subsystems
#=========================================================================

HTTPSubsystemDesc QHTTPSVR/QHTTPSVR
HTTPStartJobDesc QHTTPSVR/QZHBHTTP
HTTPStartJobQueue *JOBD
HTTPRoutingData *JOBD

#=========================================================================
# GZIP Options
#=========================================================================

LoadModule deflate_module /QSYS.LIB/QHTTPSVR.LIB/QZSRCORE.SRVPGM

# Insert Filter for Content Types except Images
AddOutputFilterByType DEFLATE text/html text/plain text/xml text/css text/javascript application/javascript application/json application/xml
AccessFileName .htaccess

# Compression Level Highest 9 - Lowest 1
DeflateCompressionLevel 1

#=========================================================================
# Directories
#=========================================================================

DirectoryIndex index.html
<Directory />
   Require all denied
</Directory>

<Directory /www/rrserver01/htdocs>
   Require all granted
</Directory>

<Directory /home/webprojects>
   Require all granted
</Directory>

#=========================================================================
# Aliases
#=========================================================================

Alias /codebase/                 /home/webprojects/codebase/

#=========================================================================
# MyApp
#=========================================================================

Alias /myapp/                    /home/webprojects/myapp/

ScriptAliasMatch /rrlib/(.*)     /qsys.lib/rrlib.lib/$1

<Directory /qsys.lib/rrlib.lib>
   SetEnv QIBM_CGI_LIBRARY_LIST "RRLIB;WEBSRVUTL"
   Require all granted
</Directory>

OpenExchangeRates

  
Select *
  from JSON_TABLE(
     QSYS2.HTTP_GET('http://openexchangerates.org/api/latest.json?app_id=yourid'), 
        '$'
     Columns(
        Date  int         path '$.timestamp' default 0  on empty,
        Base  char(3)     path '$.base'      default '' on empty,
        BTC   dec(21, 12) path '$.rates.BTC' default 0  on empty,
        CHF   dec(15, 6)  path '$.rates.EUR' default 0  on empty,
        EUR   dec(15, 6)  path '$.rates.EUR' default 0  on empty,
        GBP   dec(15, 6)  path '$.rates.GBP' default '' on empty
     )
  );
  
 
Select Date(TIMESTAMP('1970-01-01') + Date seconds) as Date, Base, BTC, CHF, EUR, GBP
  from JSON_TABLE(
     QSYS2.HTTP_GET('http://openexchangerates.org/api/latest.json?app_id=yourId'), 
        '$'
     Columns(
        Date  int         path '$.timestamp' default 0  on empty,
        Base  char(3)     path '$.base'      default '' on empty,
        BTC   dec(21, 12) path '$.rates.BTC' default 0  on empty,
        CHF   dec(15, 6)  path '$.rates.EUR' default 0  on empty,
        EUR   dec(15, 6)  path '$.rates.EUR' default 0  on empty,
        GBP   dec(15, 6)  path '$.rates.GBP' default '' on empty
     )
  );
    
  

Translate_with_Deepl

Select *
  from JSON_TABLE(
   QSYS2.HTTP_POST(
   'https://api-free.deepl.com/v2/translate',
   'text=Hallo Welt!&source_lang=DE&target_lang=EN',
   '{
     "header":"Content-Type,application/x-www-form-urlencoded; charset=utf-8",
     "header":"Accept,*/*",
     "header":"Authorization,DeepL-Auth-Key yourKey"
    }'
   ), '$'
   Columns(
     nested '$.translations[*]' Columns(  
      SourceLang  char(02)      path '$.detected_source_language'  default '' on empty,
      Text        varchar(100)  path '$.text'                      default '' on empty
     )
    ) 
  );

Webservice Customer

Select *
  from JSON_TABLE(
   QSYS2.HTTP_GET(
   'http://bhadev.rzkh.de:8030/rrlib/customer01.pgm',
   '{"header":"Content-Type,application/json; charset=utf-8"}'
   ), '$'
   Columns(
     nested '$.data[*]' Columns(  
      Id           int          path '$.id'         default 0  on empty,
      Name         varchar(50)  path '$.name'       default '' on empty,
      Country      char(02)     path '$.country'    default '' on empty,
      ZIP          varchar(05)  path '$.zip'        default '' on empty,
      City         varchar(25)  path '$.city'       default '' on empty,
      Street       varchar(30)  path '$.street'     default '' on empty,
      Sales        dec(15, 2)   path '$.sales'      default 0  on empty,
      Credit       dec(15, 2)   path '$.credit'     default 0  on empty,
      Balance      dec(15, 2)   path '$.balance'    default 0  on empty,
      Date         char(10)     path '$.date'       default '' on empty
     )
    ) 
  );