Miscellaneous

SYSADMIN.checkSortOrder

This stored procedure queries the specified column, sorts data using a DBMS native ORDER BY, sorts the same data internally in the CData 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.cleanOrphanRemark

This procedure cleans obsolete data source object remarks for an object specified by the name parameter. It is possible to clean a specific remark by name or a set of remarks using the asPattern parameter.

SQL

CALL SYSADMIN.cleanOrphanRemark(IN name string NOT NULL, IN asPattern boolean)

Example

SQL

-- Cleans only obsolete remark for the table pg.test1 itself
CALL SYSADMIN.cleanOrphanRemark('pg.test1', false) ;;

-- Cleans obsolete table and table_column remarks for objects matching the SQL LIKE pattern. 
-- Not obsolete remarks are not cleaned and warning is thrown "SYSADMIN.cleanOrphanRemark: some of the remarks which match the pattern pg.test1% could not be removed as they are not orphaned"
CALL SYSADMIN.cleanOrphanRemark('pg.test1%', true) ;;

SYSADMIN.cleanOrphanRemarks

This procedure cleans obsolete remarks for the selected schema or for the whole system.

SQL

CALL SYSADMIN.cleanOrphanRemarks(IN schemaName)

Example

SQL

CALL SYSADMIN.cleanOrphanRemarks('pg')

SYSADMIN.clearResultSetCache

This procedure cleans the result set cache.

SQL

CALL SYSADMIN.clearResultSetCache()

SYSADMIN.getDriverLogos

This procedure returns logos for embedded CData drivers in SVG format.

SQL

SYSADMIN.getDriverLogos() RETURNS (driverName string NOT NULL, logo string)

getDriverLogos procedure available since v4.6

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)

Examples

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.

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.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.refreshSSOUserCache

This procedure loads and refreshes SSO users and roles. It depends on CData Virtuality OAuth 2 Options.

Loaded SSO users and roles have the @SSO postfix. For example, if there is a user with the name 'test_user_1' on the CData Virtuality OAuth 2 Server, this user will have the name 'test_user_1@SSO' in the CData Virtuality Server. The same postfix is added to loaded SSO roles.

Example

SQL

-- Set CData Virtuality OAuth 2 Options
CALL "SYSADMIN.setDefaultOptionValue"("opt" => 'DV_AUTH_REALM', "val" => 'test_realm1');;
CALL "SYSADMIN.setDefaultOptionValue"("opt" => 'DV_AUTH_CLIENT_ID', "val" => 'odata4-oauth');;
CALL "SYSADMIN.setDefaultOptionValue"("opt" => 'DV_AUTH_CLIENT_SECRET', "val" => 'client_secret');;
CALL "SYSADMIN.setDefaultOptionValue"("opt" => 'DV_AUTH_LOAD_USERS', "val" => true);;
CALL "SYSADMIN.setDefaultOptionValue"("opt" => 'DV_AUTH_LOAD_ROLES', "val" => true);;

-- Run refreshSSOUserCache to load SSO users and roles
SYSADMIN.refreshSSOUserCache();;

SYSADMIN.refreshSSOUserCache is available since v4.2

SYSADMIN.setRemark

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

Parameter resType is optional and mark the type of remark, available values:

NONE (default)
TABLE
VIEW
PROCEDURE
TABLE_COLUMN
VIEW_COLUMN
PROCEDURE_ARGUMENT
PROCEDURE_COLUMN

SQL

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

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'
	,"resType" => '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 CData 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 CData 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)

[data-colorid=bb9tolq88i]{color:#000c34} html[data-color-mode=dark] [data-colorid=bb9tolq88i]{color:#cbd7ff}[data-colorid=k8jg645xhl]{color:#333333} html[data-color-mode=dark] [data-colorid=k8jg645xhl]{color:#cccccc}SYSADMIN.checkSortOrder

Example

SYSADMIN.cleanOrphanRemark

Example

SYSADMIN.cleanOrphanRemarks

Example

SYSADMIN.clearResultSetCache

SYSADMIN.getDriverLogos

SYSADMIN.getJwtToken

Examples

SYSADMIN.md5

Examples

SYSADMIN.reservedWords

SYSADMIN.sendOracleADWCWallet

SYSADMIN.refreshSSOUserCache

Example

SYSADMIN.setRemark

Examples

SYSADMIN.sleep

Example

SYSADMIN.checkSortOrder