Skip to Content

Dear Colleagues,

In this post I want to participate currency exchange rate updating challenge for SAP Business One. There are already 2 different approaches described on the forum how to reach this goal using DI API and B1IF.

DI API: https://blogs.sap.com/2015/05/29/updating-exchange-rates-automatically-in-sap-business-one/

B1IF: https://blogs.sap.com/2015/06/23/how-to-import-exchange-rates-using-b1if/

In this post we will do the same but using HANA XS as the development and administrating tool and B1 Service Layer as the data interface. This approach is applicable to SAP Business One version for HANA since SQL Server version doesn’t support neither XS nor ServiceLayer. After you have performed all steps you can schedule currency rates updating using standard HANA tools to update your rates regularly. This application updates rates for all currencies in your company using ECB (European Central Bank) web-service. Rates are updated according to company rate direction (Direct/Indirect rate) and using Euro cross-rate (in case the local currency is not Euro). Company details are passed in job settings.

If you don’t want to perform all development steps manually you can just download a delivery unit from here and import it into your environment. 

PREREQUSITES

To test it out you will need an SAP Business One version for HANA instance with HANA XS up and running. To make sure that your HANA XS works well you can go to the following addresses: https://<YourHanaAddress>:43<InstanceNumber> or http://<YourHanaAddress>:80<InstanceNumber>

For example: https://192.168.0.1:4300 (for instance number 00). In case you see the following picture, you can go ahead otherwise you need to make it work first

CREATING THE HANA XS PACKAGE

First of all, we need to create a new package that performs our business-logic. Let’s open HANA Studio and choose “SAP Hana Development” perspective:

Go to “Repositories” tab and create a new working space (skip this if you want to use the default or if you already have one)

I have created a workspace “DONE” for our partner namespace (DatatechOne).

We can create a package now. Right click on your workspace and choose New -> Repository Package… in the context menu:

Specify name “LoadCurrencyRates” for your package and press “Finish”. If you decided to change the name of the package you will need to change the reference in the XSJS file as well since it has dependencies by name when it loads the destination files.

Now we need to create an XS Project in our repository. Right click at our LoadCurrencyRates package and choose New -> XS Project:

Specify same project name “LoadCurrencyRates” and press “Next”. At the second step choose your repository workspace (in my case it’s DONE) and press “Next” again. Make sure that you have “Access objects” ticked at this step:

Then press “Finish” to add all default XS Project files to your package. Now your package must look like that:

In this package we will need to create the following files:

  1. Performer.xsjslib – the main file with business-logic implemented in JavaScript
  2. RunJob.xsjs – the entry point for the XS JOB Scheduler
  3. RunTest.xsjs – the entry point to test this functionality using web-browser
  4. EcbService.xshttpdest – destination file with ECB web-service connection details
  5. ServiceLayer.xshttpdest – destination file with B1 Service Layer connection details
  6. Scheduler.xsjob – file with the schedule settings to run our package regularly.

Please note that it’s better not to rename any files since there are references in the code.

Let’s start with the xsjslib file. Right click on your package and choose: New -> Other -> XS JavaScript Library File -> Next -> File name:  Performer

Paste the following code to this file and save it:

// ========================== SERVICE LAYER METHODS ======================= //

// Logs in ServiceLayer and returns session cookies
function getSlCookies(companyDb, userName, password)
{
	// Create client
	var client = new $.net.http.Client();

	// Where and what to send
	var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
	var request = new $.net.http.Request($.net.http.POST, "Login"); 
	request.setBody(JSON.stringify(
			{
				"CompanyDB": companyDb,
				"UserName": userName,
				"Password": password
			}));
	
	// Send the request and synchronously get the response
	client.request(request, dest);
	var response = client.getResponse();

	// Get all the cookies from the response
	var cookies = [];
	for(var c in response.cookies) 
	{
		cookies.push(response.cookies[c]);
	}

	// Close the connection
	client.close();   
	
	// Throw an error message if B1SESSION cookie isn't got
	if (cookies === undefined 
		|| cookies.length === 0 
		|| cookies.findIndex((e) => { return e.name === 'B1SESSION'; }) === -1)
	{		
		throw new Error(response.body.asString());
	}
	
	// Return cookies if everything is ok
	return cookies;
}

// Sets currency rates for the specified currencies using specified SL session 
function postRates(slCookies, currencyRates, date)
{	
	try
	{
		// Create client and destination
		var client = new $.net.http.Client();
		var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
		
		// Create request
		var request = new $.net.http.Request($.net.http.POST, "SBOBobService_SetCurrencyRate");
		for (var cookiesCounter = 0; cookiesCounter < slCookies.length; cookiesCounter++)
		{
			var cookie = slCookies[cookiesCounter];
			request.cookies.set(cookie.name, cookie.value);
		}
		
		// Send a request for each currency.
		for (var counter = 0; counter < currencyRates.length; counter++)
		{
			
			var currencyDetails = currencyRates[counter];
			if (!currencyDetails.rate) continue; // Continue if rate isn't specified
	
			// Set body to the request
			request.setBody(JSON.stringify(
					{
						"Currency": currencyDetails.currencyCode,
						"Rate": currencyDetails.rate,
						"RateDate": date
					}));
			
			// Send
			client.request(request, dest);	
			client.getResponse();
		}
		
		// Close the connection
		client.close();
	}
	catch (exc) {
		var test = exc;
	}
}

// Logs out ServiceLayer
function closeSlSession(slCookies)
{
	try
	{
		// Create client
		var client = new $.net.http.Client();

		// Where and what to send
		var dest = $.net.http.readDestination("LoadCurrencyRates", "ServiceLayer");
		var request = new $.net.http.Request($.net.http.POST, "Logout");
		for (var counter = 0; counter < slCookies.length; counter++)
		{
			var cookie = slCookies[counter];
			request.cookies.set(cookie.name, cookie.value);
		}
				
		// Send the request. This request returns nothing.
		client.request(request, dest);	
		client.getResponse();
		// Close the connection
		client.close();
	}
	catch (exc) {
		var test = exc;
	}
}

// ========================== GET CURRENCIES INFO METHODS ========================= //

// Gets info about company currencies settings
function getCompanyCurrenciesInfo(schemaName)
{
	// Result object with all necessary data
	var result = {
			localCurrency: '',
			isDirectRate: false,
			isLocalEuro: false,		
			currencies: []
		};
	
	// Query to get currencies info
	var qryText = `
			SELECT "OCRN"."CurrCode" AS "CurrencyCode" 
				, "OADM"."MainCurncy" AS "LocalCurrency" 
				, "OADM"."DirectRate" AS "DirectRate" 
			FROM "<SchemaName>"."OCRN" 
			CROSS JOIN "<SchemaName>"."OADM" 
			WHERE "<SchemaName>"."OCRN"."CurrCode" <> "<SchemaName>"."OADM"."MainCurncy"
			`;
	
	// Open connection to HANA
	var con = $.hdb.getConnection();
	try
	{
		// Execute for the schema provided
		qryText = qryText.replace(/<SchemaName>/g, schemaName);
		var rs = con.executeQuery(qryText);
		
		// Fill result object with the data from the first row
		result.localCurrency = rs[0].LocalCurrency.trim();
		result.isLocalEuro = result.localCurrency === 'EUR';
		result.isDirectRate = rs[0].DirectRate === 'Y';
		
		// Fill currencies array
		var iterator = rs.getIterator();
		while (iterator.next())
		{
			
			result.currencies.push({
				currencyCode: iterator.value().CurrencyCode, 
				rate: 0
			});
		}
	}
	finally
	{
		// Close connection
		con.close();
		return result;
	}
}

function getCurrencyRateFromXml(currencyCode, xmlString)
{
	// Response is in XML so we need to parse it as string 
	// since there are no XML built-in functions in HANA 1.0
	try
	{
		var pattern = "[Ss]*(?:s|<CurrencyCode>' rate=')([0-9.]*)['][Ss]*";
		pattern = pattern.replace('<CurrencyCode>', currencyCode.trim());
		
		var regex = new RegExp(pattern);
		var match = regex.exec(xmlString);
		
		if (match !== null)
		{
			// Value is presented
			return parseFloat(match[1]);
		}
		else
		{
			// Value is not presented
			return undefined;
		}
	}
	catch (exc)
	{
		return undefined;
	}
}

function calculateCurrencyRate(rateAgainstEuro, localAgainstEuro, isDirectRate)
{
	var rate = isDirectRate ? localAgainstEuro / rateAgainstEuro : rateAgainstEuro / localAgainstEuro;
	return Math.round(rate * 10000) / 10000;
}

// Fills rates from ECB web-service
function fillRates(currencyInfo)
{
	// Create client
	var client = new $.net.http.Client();

	// Where and what to send
	var dest = $.net.http.readDestination("LoadCurrencyRates", "EcbService");
	var request = new $.net.http.Request($.net.http.GET, ""); 
	
	// Send the request and synchronously get the response
	client.request(request, dest);
	var response = client.getResponse();

	// Get the body
	var bodyXml = response.body.asString();

	// Close the connection
	client.close();        

	// All rates are presented against euro so we need to get the cross-rate 
	// in case if local currency is not Euro
	var localAgainstEuro = 1.0;
	if (!currencyInfo.isLocalEuro)
	{
		localAgainstEuro = getCurrencyRateFromXml(currencyInfo.localCurrency, bodyXml);
		if (localAgainstEuro === undefined) throw new Error('Cannot find the local currency with code ' + currencyInfo.localCurrency + ' in the ECB web-service'); // Stop processing if local currency isn't presented
	}
	
	var currencyAgainstEuroRate = 0.0;
	for (var counter = 0; counter < currencyInfo.currencies.length; counter ++)
	{
		var currency = currencyInfo.currencies[counter];
		
		if (!currencyInfo.isLocalEuro && currency.currencyCode === 'EUR')
		{
			// Calculate Euro rate according to company rate direction
			currency.rate = calculateCurrencyRate(1, localAgainstEuro, currencyInfo.isDirectRate);
		}
		else // Calculate other currencies using Euro cross-rate 
		{
			// Get currency exchange rate against euro
			currencyAgainstEuroRate = getCurrencyRateFromXml(currency.currencyCode, bodyXml);
			
			// Handle next if this currency isn't presented in the XML
			if (currencyAgainstEuroRate === undefined) continue;
			
			// Calculate rate with cross-rate and company rate direction
			currency.rate = calculateCurrencyRate(currencyAgainstEuroRate, localAgainstEuro, currencyInfo.isDirectRate);
		}		
	}
}

function GetCurrentDateAsString()
{
	// Prepare todays date in necessary format
	var todayDate = new Date();
	var stringYear = String(todayDate.getFullYear());
	var stringMonth = todayDate.getMonth() + 1 < 10 // Zero based
				? '0' + String(todayDate.getMonth() + 1)
				: String(todayDate.getMonth() + 1);
	var stringDate = todayDate.getDate() < 10
				? '0' + String(todayDate.getDate())
				: String(todayDate.getDate());
	return stringYear + stringMonth + stringDate;
}

// =============================== ENTRY POINT ============================ //

// Main function that performs the business logic 
function loadRates(schemaName, userName, password)
{
	var cookies; // SL session cookies
	try // Handle exceptions in test mode only
	{
		// Try to login to Service Layer
		cookies = getSlCookies(schemaName, userName, password);
		
		// Get company currencies to work with
		var curInfo = getCompanyCurrenciesInfo(schemaName);
		
		// Check if currencies are got successfully and there are currencies except local currency
		if (curInfo === undefined 
				|| curInfo.localCurrency === undefined 
				|| curInfo.localCurrency === ''
				|| curInfo.currencies.length === 0
			) throw new Error('Cannot get currency details!');
		
		// Get currency rates for all necessary currencies
		fillRates(curInfo);
		
		// Set currency rates for the company
		postRates(cookies, curInfo.currencies, GetCurrentDateAsString());
	}
	finally
	{
		// Logout if connected
		if (cookies !== undefined) closeSlSession(cookies);
	}	
}

The business logic is created and now we need to create 2 XSJS files. One to test our functionality manually and one to schedule it using the XS JOB Scheduler.

Same with XSJSLIB file (the file type is not the same), right click on your package and choose: New -> Other -> XS JavaScript File -> Next -> File name:  RunTest

Insert the following code:

// Gets query string parameters
// Parameters are the following: schemaName, userName, password
function getParams()
{
	var params = {};
	
	// Extract parameters from the query string
	for (var counter = 0; counter < $.request.parameters.length; counter++)
	{
		var param = $.request.parameters[counter];		
		if (param.name.toUpperCase() === 'SCHEMANAME') params.schemaName = param.value;
		else if (param.name.toUpperCase() === 'USERNAME') params.userName = param.value;
		else if (param.name.toUpperCase() === 'PASSWORD') params.password = param.value;
	}
	
	// Validate parameters
	var mandatoryParams = [];
	if (!params.hasOwnProperty('schemaName')) mandatoryParams.push('SchemaName');
	if (!params.hasOwnProperty('userName')) mandatoryParams.push('UserName');
	if (!params.hasOwnProperty('password')) mandatoryParams.push('Password');

	// Throw an error in case not all parameters are got
	if (mandatoryParams.length > 0) throw new Error('The following mandatory parameters are not provided: ' + mandatoryParams.join(', '));
	
	return params;
}

function execute()
{
	var result = {
			isSuccess: true,
			errMessage: ''
	};

	try
	{
		// Get parameters from the query string
		var requestParams = getParams();
		
		// Execute function
		var rateLoader = $.import('LoadCurrencyRates', 'Performer');
		rateLoader.loadRates(requestParams.schemaName, requestParams.userName, requestParams.password);
	}
	catch (exc)
	{
		result.isSuccess = false;
		result.errMessage = !exc.message ? exc : exc.message;
	}
	
	//Build the response for test purposes
	$.response.contentType = "application/json";			
    $.response.status = $.net.http.OK;
    $.response.setBody(JSON.stringify(result));
}

execute();

One more time for the RunJob file, right click on your package and choose: New -> Other -> XS JavaScript File -> Next -> File name:  RunJob

Paste the following:

// The main entry point for an XS Job
function execute(inputParameter)
{
	try
	{
		var rateLoader = $.import('LoadCurrencyRates', 'Performer');
		rateLoader.loadRates(inputParameter.schemaName, inputParameter.userName, inputParameter.password);
	}
	catch (exc) {
		throw !exc.message ? exc : exc.message;
	}
}

XSJS files are created, now we can create the destination files.

Destination for ECB service: right click on your package and choose: New -> Other -> XS HTTP Destination Configuration -> Next -> File name:  EcbService

Place the following in your destination file (change the proxy part in case you use proxy):

host = "www.ecb.europa.eu";
port = 443;  
description = "Daily currency exchange rates posted by ECB";
useSSL = true;
pathPrefix = "/stats/eurofxref/eurofxref-daily.xml";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Destination for ServiceLayer: right click on your package and choose: New -> Other -> XS HTTP Destination Configuration -> Next -> File name:  ServiceLayer

Here we have 2 options: we can either use HTTP calling one of load balancing ports (such as 50001) or HTTPS. This will be internal traffic so you can decide if it’s important to use the secure connection. In case you have decided to use the untrusted connection, you can skip trust store configuring (for ServiceLayer, you will need to set it up for ECB anyway since there is no untrust connection available anymore).

Place the following in your destination file for HTTP (change the proxy part in case you use proxy):

host = "<HanaAddress>";
port = 50001;  
description = "ServiceLayer UNTRUSTED!!!!";
useSSL = false;
pathPrefix = "/b1s/v1/";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Or place the following in your destination file for HTTPS (change the proxy part in case you use proxy):

host = "<HanaAddress>";
port = 50000;  
description = "ServiceLayer trusted";
useSSL = true;
pathPrefix = "/b1s/v1/";
authType = none;
useProxy = false;
proxyHost = "";
proxyPort = 0;
timeout = 0;

Don’t forget to change <HanaAddress> to your IP or domain name. Destination files are done now we need to create the xsjob file to configure our schedule.

Right click on your package and choose: New -> Other -> XS Job Scheduler File -> Next -> File name:  Scheduler.

In this file input the following code and save it:

{
    "description": "Loads currency exchange rates from ECB web-service using ServiceLayer",
    "action": "LoadCurrencyRates:RunJob.xsjs::execute",
    "schedules": [
       {
          "description": "The default schedule",
          "xscron": "* * * * 23 00 0",
          "parameter": {"schemaName": "SBODEMOGB",
					"userName": "manager",
					"password": "manager"
				}
       }
    ]
}

That’s it. The package is ready and we can activate it. Right click at your package and choose Activate in the context menu.

If you see no errors we can move on and setup our package.

CREATING AN ADMIN USER

To administrate (setup schedule and trustore) our package we will need to create a user with necessary privileges. Let’s create a user called “XS_ADMIN” and grant necessary roles to him. Execute the following script in HANA Studion:

CREATE USER XS_ADMIN PASSWORD "Password1!";
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::TrustStoreAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::JobAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::JobSchedulerAdministrator','XS_ADMIN');
CALL _SYS_REPO.GRANT_ACTIVATED_ROLE ('sap.hana.xs.admin.roles::HTTPDestAdministrator','XS_ADMIN');

Now we have the user “XS_ADMIN” with the necessary privileges and the password “Password1!”. You will be asked to change the password when you log into XS Admin Tool.

TRUST STORE SETTING UP

Here we need to create trust stores and assign them to our package for ECB and B1 ServiceLayer certificates in case you decided to call ServiceLayer via HTTPS. Let’s start from ECB service. As the first step you need to download the certificate. You can do that if you open this web-site in a browser and press the lock in the address bar. You can find more information of how to export a certificate via browser in this video.

Once we have our certificate downloaded we can setup the trustore for it and assign it to our application.

Go to HANA XS admin tool by path https://<YourHanaAddress>:43<InstanceNumber>/sap/hana/xs/admin and input our XS_ADMIN user credentials. Open the popup menu (press the burger button on the left-hand side) and choose the Trust Manager option.

Press “Add” on the bottom tool bar (on the left-hand side) and input “ECB” to the Trust Store name field. Press OK. Trust store is created. Now we need to import our certificate we have downloaded in the previous step. Choose “ECB” trust store, go to the “Certificate list” tab, press “Import Certificate” button in the bottom tool-bar, choose the saved certificate (*.crt/*.cer or another certificate file depending on the way you saved that) file and press “Import Certificate”. Trust store is ready to be used.

After trust store is created we need to assign this trust store to our package. Go to “XS Artifact Administration” and press the arrow (it might be changed in the future XS Admin Tools versions UI) next to our package.

      

Choose our EcbService.xshttpdest file to assign the trust store to. Go to “Authentication details” tab and press the Edit button from the bottom tool-bar. Tick “SSL Enabled” checkbox, change “SSL Authentication type” to “Client certificate” and choose our “ECB” trust store and hit the save button. The result must look like on the picture below:

The trust store is assigned and now we need to do the same in case we decided to use trusted connection with ServiceLayer. Firsly we need to download the certificate. You can do that from this address: https://<YourHanaServer>:50000 same with ECB certificate or you can download it from your SLES system by path: <ServiceLayer installation folder>/conf/server.crt.

By default it’s /usr/sap/SAPBusinessOne/ServiceLayer/conf/server.crt

When you have your certificate downloaded you need to create the trust store for it (let’s call it B1 SL) and assign it to the package for the ServiceLayer.xshttpdest file. The result must be the following:

The trust stores are assigned and our package is ready to be tested.

TESTING THE PACKAGE

To test our package we just need to call it via HTTP (simply input https://<YourHanaAddress>:43<InstanceNumber>/LoadCurrencyRates/RunTest.xsjs?SchemaName=SBODEMOGB&UserName=manager&Password=manager in the address bar of any browser). You can specify your DB credentials in the query string. In case everything went smooth you will see the following result:

{"isSuccess":true,"errMessage":""}

Otherwise you will see the error message, for example:

{"isSuccess":false,"errMessage":"{\n   \"error\" : {\n      \"code\" : -304,\n      \"message\" : {\n         \"lang\" : \"en-us\",\n         \"value\" : \"Fail to get DB Credentials from SLD\"\n      }\n   }\n}\n"}

NOTE: To test this application you must login with a user with privileges to select data from your schema (for example SYSTEM). Same user must be assign to the JOB. Otherwise you will have the authorisation error, such as: Cannot get currency details!

SCHEDULING RATES LOADING

In the meantime, all steps are completed and now we just need to setup the schedule. Go back to the HANA XS Admin Tool (same where we managed our trust store) and choose the “XS Job Dashboard” menu point. Enable the scheduler by a tumbler “Scheduler enabled” on the top, right-hand side corner. Drill down into our package by clicking on the row corresponding to our package.

Here we need to activate our job. Go to the “Configuration” tab and tick the “Active” checkbox. Input credentials for this job. Press the “Save” button. Job is activated.

Now we need to setup the schedule. We already have one (the one we have created in our Schedule.xsjob file) but since ECB updates their rates at 4 pm ETC we want to run it twice a day: at 1 am to setup it for the business day and at 4:10 pm to keep it updated. Press “Add schedule” button and input settings like on the picture below:

NOTE: Cron settings are in UTC, so I need to setup it 2 hours before since I have CEST timezone.

Press Ok. The schedules are setup and now we can check if the loader is planned. If you press “View Logs” button over the grid you must see the following:

That’s it. Functionality is done and scheduled using native HANA tools. Hope you enjoyed this post. Feel free to share your feedback.

Cheers,

Sergei Travkin,

Datatech One

To report this post you need to login first.

57 Comments

You must be Logged on to comment or reply to a post.

  1. Jérémie SIONNEAU

    Hello Sergei

    first thanks for you documentation. I have an issue after making everything you wrote.

     

    I have this error when i call : https://…:4300/LoadCurrencyRates/Perform.xsjs

     

    404 – Not found

    We could not find the resource you’re trying to access.
    It might be misspelled or currently unavailable.

    Do you have an idea ?
    (0) 
    1. Sergei Travkin Post author

      Hi Jeremie,

      Have you performed every step before trying to call this script? I assume the problem can be one of the following:

      1. You renamed either the package or the XSJS file. In this case you need to change the URL with your details.
      2. You haven’t activated your package. In this case your application exists only on the client and cannot be called.

      Do you use the trusted connection? Do you see your package in XS Artifacts?

      (0) 
      1. Jérémie SIONNEAU

        Hello Sergei

        1. I named every file as you mentionned in your documentation
        2. My package is activated (Right click on the package -> Activate in HANA Studio)
          1. Yes i use a trusted connection :
            : None [Altered]
            : SSL Disabled [Altered]
            : Not Applicable
            : Not Applicable
            : B1 SL
          2. And yes I can see my package in XS Artifacts

        Thank you for your help

        (0) 
    1. Sergei Travkin Post author

      Please note that you must have input the HANA user (when you ran this test) that has privileges for the schema you load rates to. I test it with SYSTEM user. Our XS_ADMIN won’t fit as it has no schemas assigned.

      (0) 
      1. Jérémie SIONNEAU

        Sergei this is what i do. Means i use credentials i used to connect to my schema in HANA Studio. So it should work.

        Any idea of why it doesn’t copy anything in the database.

        For information in my project the local currency is PLN.

        (0) 
    2. Sergei Travkin Post author

      Jeremie,

      Please let me know if it works or not when you are done. Also I would appreciate any feedback about complexity of the guide. Maybe you can suggest something to change in the places that were confusing.

      (0) 
  2. Jérémie SIONNEAU

    Sergei this is what i do. Means i use credentials i used to connect to my schema in HANA Studio. So it should work.

    Any idea of why it doesn’t copy anything in the database.

    For information in my project the local currency is PLN.

    (0) 
    1. Sergei Travkin Post author

      Jeremie,

      Have you changed this portion with your B1 credentials?

      // Test entry point 
      loadRates({
      		schemaName: 'SBODEMOGB',
      		userName: 'manager',
      		password: 'manager'
      	});
      (0) 
        1. Sergei Travkin Post author

          Well, I have updated the Perform.XSJS code to show the output. Would you please update your code, reactivate the package and try to call it again? It must clarify the reason.

          (0) 
            1. Sergei Travkin Post author

              Ok, now it’s much more clear. Please go to XS Artifact administration again (where you specified your trust store) and specify as on the picture below:

              So when you save it it must look like:

              (0) 
                1. Sergei Travkin Post author

                  Sorry for using you as a guinea pig 🙁 I updated the XSJS script to show the original SL error, maybe it might be easier to understand the reason. Would you please update the code, reactivate and try again.

                  (0) 
                  1. Jérémie SIONNEAU

                    Oups sorry …

                     

                    I have this :

                    {"isSuccess":false,"errMessage":"{\n   \"error\" : {\n      \"code\" : -304,\n      \"message\" : {\n         \"lang\" : \"en-us\",\n         \"value\" : \"Fail to get DB Credentials from SLD\"\n      }\n   }\n}\n"}
                    (0) 
                    1. Sergei Travkin Post author

                      I get this error when setup incorrect user name and password in the credentials portion. Can you please try to login to SL using postman or another REST client if you are used with SL? Also you can check this thread: https://answers.sap.com/questions/188349/fail-to-get-db-credentials-from-sld.html

                      (0) 
                        1. Sergei Travkin Post author

                          POST to https://YourHanaServer:50000/b1s/v1/Login

                          with body (set the same parameters as at the end of the script)

                          {
                          “CompanyDB”: companyDb,
                          “UserName”: userName,
                          “Password”: password
                          }

                          (0) 
  3. Jérémie SIONNEAU

    I have this now :

    {"isSuccess":false,"errMessage":"HttpClient.getResponse: Can't get the response from the server: IPCon error: \"Connection to bbhdph1.sboga.local lost while reading response.\""}
    
    For information I change in the file ServiceLayer the port. I put 50000 instead of 50001
    (0) 
      1. Jérémie SIONNEAU

        I’m sorry Sergei I don’t understand your question.

         

        host = “……”;
        port = 50000;
        description = “ServiceLayer TRUSTED!!!!”;
        useSSL = false;
        pathPrefix = “/b1s/v1/”;
        authType = none;
        useProxy = false;
        proxyHost = “”;
        proxyPort = 0;
        timeout = 0;

        (0) 
        1. Sergei Travkin Post author

          Ok, maybe I wasn’t clear in the post. You can call ServiceLayer by HTTP or HTTPS. You specify it in the destination file.

          For HTTP you must setup:

          port = 50001; // Or another load balancing member port

          useSSL = false;

          For HTTPS:

          port = 50000;

          useSSL = true;

           

          Now you are trying to call HTTPS without SSL – that is not possible. If you just changed from 50001 to 50000 that means that you tried to call using HTTP (untrusted). Can you change useSSL to true in the destination file and call it again?

          (0) 
          1. Jérémie SIONNEAU

            Here is my Authentification details :

            None [Altered]
            SSL Enabled [Altered]
            Client Certificate
            Enabled
            B1 SL
            And general information :
            Not Applicable
            ServiceLayer trusted [Altered]
            bbhdph1.sboga.local [Altered]
            50000 [Altered]
            /b1s/v1/ [Altered]
            0 [Altered]
            (0) 
            1. Sergei Travkin Post author

              Please change your ServiceLayer.xshttpdest to the following:

              host = “bbhdph1.sboga.local”;

              port = 50000;

              description = “ServiceLayer trusted”;

              useSSL = true;

              pathPrefix = “/b1s/v1/”;

              authType = none;

              useProxy = false;

              proxyHost = “”;

              proxyPort = 0;

              timeout = 0;

              Reactivate the package after the change.

              BTW, have you tried to call SL via Postman?

              (0) 
              1. Jérémie SIONNEAU

                It doesn’t work again. Still have the same message :

                {"isSuccess":false,"errMessage":"{\n   \"error\" : {\n      \"code\" : -304,\n      \"message\" : {\n         \"lang\" : \"en-us\",\n         \"value\" : \"Fail to get DB Credentials from SLD\"\n      }\n   }\n}\n"}
                (0) 
                1. Jérémie SIONNEAU

                  Here is the response of my REST Client :

                  {
                  “error” : {
                  “code” : 101,
                  “message” : {
                  “lang” : “en-us”,
                  “value” : “Bad POST content.”
                  }
                  }
                  }

                  (0) 
                    1. Sergei Travkin Post author

                      BTW, you need to surround your values with double quotes, for example: {“CompanyDB”:”SBODEMOGB”,”UserName”:”manager”,”Password”:”manager”}

                      (0) 
                2. Sergei Travkin Post author

                  Yup, but at least that error means that SL was reached but couldn’t connect to the DB. Otherwise the error was on the connection stage. Are you sure you set the right credentials? Cause this error means that access was denied. If the error via postman will be the same and you are absolutely sure that credentials are correct you should try the solution from HERE

                  (0) 
                    1. Sergei Travkin Post author

                      Hey, now we are in! 🙂 SL error has gone, but now your XS user (the one you input when you tried to test this application) has no rights to execute query for this schema. Go to HANA XS ADMIN tool and press logout. Then try to call it again, you will be asked to input credentials. Input your user you are going to use (for example SYSTEM) for the job. Or you can grant privileges for the XS_ADMIN user using this script:

                      GRANT SELECT ON SCHEMA SBODEMOGB TO XS_ADMIN;

                      Don’t forget to change the schema name.

                      (0) 
                      1. Jérémie SIONNEAU

                        It works. I have this :

                        {"isSuccess":true,"errMessage":""}
                        
                        But for our case it doesn't work because our local currency is PLN so it doesn't fill the rate for EUR. 
                        
                        Maybe this is due to the XML source ..
                        (0) 
                        1. Sergei Travkin Post author

                          Must work, I tested it for SBODEMOGB where local currency is GBP and SBODEMOIT where local currency is EUR. How many currencies do you have? This application takes currency ALPHA-3 codes from the code field. Do you have accurate currency codes?

                          (0) 
                        2. Sergei Travkin Post author

                          If you have currencies like that this application must fill rates for all currencies that are presented at ECB service if your local currency is Euro or another presented at ECB. But only if the code is valid

                          (0) 
                            1. Sergei Travkin Post author

                              Actually this is ok. Please update the XSJS file, I have added the check for the currency code. If your local currency doesn’t fit you will be informed.

                              (0) 
                                1. Sergei Travkin Post author

                                  I’m glad it helped. Can you please recap the steps confused you or that you did but they weren’t described well enough? I will update the blog according to your experience. Thanks for the feedback.

                                  (0) 
                                  1. Jérémie SIONNEAU

                                    Yes Sergei I will do that. I just have one more question wich is :

                                    Do you have an idea of why the scheduler doens’t work ?

                                    When i click on the button “View logs” nothing appears. We should see next events … You confirm ?

                                    (0) 
                                    1. Sergei Travkin Post author

                                      Hi Jeremie,

                                      Yes if you go to the view log tab you need to see the following:

                                      Have you enabled jobs on the Job Dashboard page?

                                      BTW, please update the XSJS file. It’s not critical but with the previous version Jobs will show an error even if everything went smoothly.

                                      (0) 
                                          1. Jérémie SIONNEAU

                                            Hello Sergei

                                            i have a last issue (I hope) about the scheduler. I added 2 schedules :

                                             

                                             

                                            My issue is that even if I put different parameters between the 2 schedules it always take the one in the function runTest() of the file perform.xsjs

                                            So i decided to comment this block and now i have no result. Means nothing is insert in the database.

                                            This is strange it seems like the function need to have the block “runTest” to work. But if it is the case I canno’t use this process for my multiple company(DB).

                                            Thanks

                                            (0) 
                                            1. Sergei Travkin Post author

                                              Hi Jeremie,

                                              Looks like I screwed up here a bit. Give me a couple of days, I will investigate the reason this weekend and get back with the solution.

                                              (0) 
                                            2. Sergei Travkin Post author

                                              Hi Jeremie!

                                              I have changed the post in several points after your feedback. The company credentials were transfered from code to the query string and the XSJS file was split for 2 XSJS and 1 XSJS Lib files for easier testing and scheduling. Please remove the old Perform.xsjs file and create new ones. Also during the tests I realised that old unsecured ECB service is not availablle anymore, so I had to change the destination file and update the guide in a trustore section. Also I added a link to the delivery unit, so if you don’t want to make this changes manually you can just import it using this DU.

                                              I have tested the updated functionality with your use-case (scheduled for different DBs) and it worked fine.

                                              Please let me know if you still have problems or questions unanswered.

                                              (0) 
                                              1. Jérémie SIONNEAU

                                                Hello Sergei sorry for the delay of my answer. Anyway it works for my company in Poland if we use the XML for the european central bank but the legal rule in Poland is to use the one from the poland central bank. So I have to work on the perform.xsjs file for reading this new XML. Thanks

                                                 

                                                (0) 
                                                1. Sergei Travkin Post author

                                                  Hi Jeremie, yes, then in your case you need to change the destination file to retrieve rates from another service and customise the fillRates function to get rates from the service response.

                                                  (0) 
  4. Jérémie SIONNEAU

    Hello Sergei

    how can I contact you in private. I would like to know if it is possible to extract massive datas of items via Service layer with a result in XML File ?

    (0) 
    1. Sergei Travkin Post author

      Hi Jeremie,

      You can find the reference to my linkedin at my accounts page. You can reach me out there. But I’d rather post a question on the forum, if you have any questions regarding SAP. Then the chance to get it answered will be much better + maybe someone else finds it useful.

      PS if your question is about getting response from service layer in xml then I think the answer is no. The response is in JSON. 

      (1) 

Leave a Reply