Skip to main content
Skip table of contents

Miscellaneous

You are looking at an older version of the documentation. The latest version is found here.

SYSADMIN.checkSortOrder

This stored procedure queries the specified column, sorts data using a DBMS native ORDER BY, sorts the same data internally in the Data Virtuality Server, and returns differences between the two result sets.

SQL
SYSADMIN.checkSortOrder(IN schemaName string NOT NULL, IN tableName string NOT NULL, IN columnName string NOT NULL, IN maxRows integer)

Example

SQL
CALL SYSADMIN.checkSortOrder('test_tables', 'test_e', 'e', 5)

SYSADMIN.clearResultSetCache

This procedure cleans the result set cache.

SQL
SYSADMIN.clearResultSetCache()

SYSADMIN.clearResultCache is available since v3.7

SYSADMIN.getJwtToken

This procedure calculates the JSON Web Token.

SQL
SYSADMIN.getJwtToken(IN payload clob NOT NULL, IN privateKeyPath blob NOT NULL, IN encryptionAlgorithm string, IN jwtHeader clob, OUT token string RESULT)

It supports different ways of providing a private key:

  • Text in PEM format (in base64);
  •  Path for a file with a private key in PEM or DER format;
  • Byte stream from a file data source.

It supports the following algorithms:

  • RS256 (default)
  • RS384
  • RS512
  • PS256
  • PS384
  • PS512
  • ES256
  • ES384
  • ES512

Examples

SQL
-- JSON Web Token as text
SELECT * FROM (CALL "SYSADMIN.getJwtToken"(
    "payload" => '{
    "exp": 1551273453,
    "iss": "A@AdobeOrg",
    "sub": "A@techacct.adobe.com",
    "https://ims-na1.adobelogin.com/s/ent_analytics_bulk_ingest_sdk": true,
    "aud": "https://ims-na1.adobelogin.com/c/4"
}'/* Mandatory */,
    "privateKeyPath" => to_bytes('-----BEGIN PRIVATE KEY-----
privatkey
-----END PRIVATE KEY-----', 'UTF-8')/* Mandatory */
)) AS s;;

-- JSON Web Token as a file path
SELECT * FROM (CALL "SYSADMIN.getJwtToken"(
    "payload" => '{
    "exp": 1551273453,
    "iss": "A@AdobeOrg",
    "sub": "A@techacct.adobe.com",
    "https://ims-na1.adobelogin.com/s/ent_analytics_bulk_ingest_sdk": true,
    "aud": "https://ims-na1.adobelogin.com/c/A"
}'/* Mandatory */,
    "privateKeyPath" => 'C:\\DataVirtuality\\testdata\\secret.pem'/* Mandatory */
)) AS s;;

 
SELECT * FROM (CALL "SYSADMIN.getJwtToken"(
    "payload" => '{
    "exp": 1551273453,
    "iss": "A@AdobeOrg",
    "sub": "A@techacct.adobe.com",
    "https://ims-na1.adobelogin.com/s/ent_analytics_bulk_ingest_sdk": true,
    "aud": "https://ims-na1.adobelogin.com/c/A"
}'/* Mandatory */,
    "privateKeyPath" => 'C:\\DataVirtuality\\testdata\\secret.key'/* Mandatory */
)) AS s;;

-- JSON Web Token as a stream from a file
BEGIN
DECLARE blob key = SELECT file FROM (CALL testdata.getFiles('secret.key')) AS a;
DECLARE string payload = SELECT '{
    "exp": 1551273453,
    "iss": "A@AdobeOrg",
    "sub": "A@techacct.adobe.com",
    "https://ims-na1.adobelogin.com/s/ent_analytics_bulk_ingest_sdk": true,
    "aud": "https://ims-na1.adobelogin.com/c/A"
}';
SELECT * FROM (CALL "SYSADMIN.getJwtToken"(
    "payload" => payload,
    "privateKeyPath" => key
)) AS s;
END;;

-- JSON Web Token as a stream from a file using ES256 algorithm
BEGIN
	DECLARE blob file = SELECT file FROM testdata.getFiles(pathAndPattern => 'ec_private.pem');
	SELECT * from (CALL SYSADMIN.getJwtToken (
	    payload => JsonObject (
			'69a6de74-ec5f-47e3-e053-5b8c7c11a4d1' as iss,
			TimestampDiff (SQL_TSI_Second, From_Unixtime(0), Now()) as iat,
			TimestampDiff (SQL_TSI_Second, From_Unixtime(0), TimestampAdd (SQL_TSI_MINUTE, 20, Now())) as exp,
			'appstoreconnect-v1' as aud
		),
	    privateKeyPath => file,
	    encryptionAlgorithm => 'ES256'
	)) as s;
END;;

-- JSON Web Token as a stream from a file using ES256 algorithm and optional jwtHeader parameter
BEGIN
	DECLARE blob file = SELECT file FROM testdata.getFiles(pathAndPattern => 'ec_private.pem');
	SELECT * from (CALL SYSADMIN.getJwtToken (
	    payload => JsonObject (
			'69a6de74-ec5f-47e3-e053-5b8c7c11a4d1' as iss,
			TimestampDiff (SQL_TSI_Second, From_Unixtime(0), Now()) as iat,
			TimestampDiff (SQL_TSI_Second, From_Unixtime(0), TimestampAdd (SQL_TSI_MINUTE, 20, Now())) as exp,
			'appstoreconnect-v1' as aud,
			'ES256' as alg
		),
		jwtHeader => JsonObject (
			'ES256' as alg,
			'2X9R4HXF34' as kid,
			'JWT' as typ
		),
	    privateKeyPath => file
	)) as s;
END;;

SYSADMIN.md5

This procedure returns the md5 hash value for a given parameter.

CODE
SYSADMIN.md5(IN param string, OUT hash string)

Example

SQL
SELECT hash FROM (CALL SYSADMIN.md5(1)) AS a WHERE hash=ucase('c4ca4238a0b923820dcc509a6f75849b');

md5 accepts an empty/null string or whitespace-like tab:

  • For null value, there is going to be just an empty string as a result;
  • For non-null values (empty string, string with whitespaces or tabs, etc.), md5 will be calculated and returned as a result.

Examples

SQL
SELECT hash FROM (CALL SYSADMIN.md5('')) AS a;
SELECT hash FROM (CALL md5(null)) AS a;

SYSADMIN.reservedWords

This procedure returns a list of keywords needed for code completion. It includes clauses, data types, functions, arithmetic operation signs, etc.

SQL
SYSADMIN.reservedWords(OUT keyword string NOT NULL, OUT type string NOT NULL)

SYSADMIN.reservedWords procedure is available since v3.2

SYSADMIN.sendOracleADWCWallet

This procedure uploads a wallet file for an Oracle ADWC data source.

SQL
SYSADMIN.sendOracleADWCWallet(IN trustStorePassword string NOT NULL, IN base64Wallet string NOT NULL)

SYSADMIN.setRemark

This procedure sets a remark for a given table, column, procedure, or view.

SQL
SYSADMIN.setRemark(IN name string NOT NULL, IN remark string NOT NULL)

Examples

SQL
CALL "SYSADMIN.setRemark" (
    "name" => 'views.employee'
    ,"remark" => 'here remark was set for the view/proc'
) ;;

CALL "SYSADMIN.setRemark" (
    "name" => 'adv.contacttype'
    ,"remark" => 'here remark was set for the table'
) ;;

CALL "SYSADMIN.setRemark" (
    "name" => 'adv.contact.FirstName'
    ,"remark" => 'here remark was set for the column'
) ;;

The setRemark() procedure can be used with OPTION $NOFAIL. Here is an example of this:

SQL
CALL "SYSADMIN.setRemark" (
    "name" => 'adv.contact.FirstName'
    ,"remark" => 'here remark was set for the column'
) OPTION $NOFAIL;;

The Data Virtuality Exporter will not export remarks set on physical data sources, as well as those set on tables, views or procedures stored in such a data source. Remarks set on virtual schemas and objects will be exported.

Furthermore, the Data Virtuality Exporter adds OPTION $NOFAIL to all setRemark() system procedures.

SYSADMIN.sleep

This procedure pauses the execution of the current process for the specified time in milliseconds.

SQL
SYSADMIN.sleep(IN millis long NOT NULL))

Example

In this example, the sleep time is set to 5 seconds:

SQL
CALL SYSADMIN.sleep(5000)
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.