Making file paths OS-independent in Invantive UniversalSQL and Invantive Script

Sometimes it’s necessary to use Invantive UniversalSQL and Invantive Script statements that must be able to run unchanged on different OS platforms. In that case, it’s important to use the correct notation when exchanging data with the operating system. In this topic, you’ll learn how to make the separators in path names OS-independent.

For use in Invantive UniversalSQL, you can use the context USERENV or a query on SystemProperties@DataDictionary.

Query on SystemProperties

With a query on SystemProperties@DataDictionary, this works as follows:

select *
from   SYSTEMPROPERTIES@DataDictionary
where  name like 'environment-%-separator%'

with the following result:

SYS_CONTEXT function on USERENV

Using the ‘USERENV’ context, the separators can be retrieved with:

select 'Path Separator'
,      sys_context('USERENV', 'PATH_SEPARATOR') val
union all
select 'Directory Separator'
,      sys_context('USERENV', 'DIRECTORY_SEPARATOR')
union all
select 'Directory Separator (Alternative)'
,      sys_context('USERENV', 'DIRECTORY_SEPARATOR_ALT')
union all
select 'Volume Separator'
,      sys_context('USERENV', 'VOLUME_SEPARATOR')

with the following result:

Invantive Script

Invantive Script is widely used in Invantive Data Hub and Invantive Query Tool to exchange data, so there are also options available directly within Invantive Script:

select 'Path Separator: ${system:pathseparator}'
union all
select 'Directory Separator: ${system:directoryseparator}'
union all
select 'Directory Separator (Alternative): ${system:directoryseparatoralt}'
union all
select 'Volume Separator: ${system:volumeseparator}'

gives:

A path name can, for example, be constructed as follows:

local define OUT_FOLDER "${system:userdocumentsdirectory}${system:directoryseparator}myfolder"

Availability

The features described are available from version 20.1.467 BETA onwards.