Changes in VQL Syntax¶

Denodo 6.0 introduces changes to the VQL syntax to avoid ambiguities in the process of parsing VQL statements.

Because of this, you may have to review the queries sent by third-party clients to Virtual DataPort.

Starting with Denodo 6.0, consider the following:

• It is mandatory to surround the values of the right side of a condition with a multi-valued operator. These operators are:

  • in

  • not in

  • textual similarity (~)

    For example:

    SELECT *
    FROM V1
    WHERE field_1 IN (1, 2)
    

    In earlier versions, the parentheses are optional.

• It is mandatory to separate with the clause AND the values of the operator between. E.g. field_1 BETWEEN value_1 AND value_2.

  In earlier versions, you can execute field_1 between value1, value2.

• It is mandatory to surround literals with single quotes as indicated by the SQL standard. In earlier versions, you could use either single or double quotes for this, but not anymore.

  In Denodo 6.0, the double quotes are used to surround identifiers with special characters.

• Surround “Unicode” identifiers with double quotes. In Denodo 5.5, you could only do it with French quotes. In Denodo 6.0 you can use French quotes as well, but they may be deprecated in the future.

  For example, the following two statements are equivalent:

  CREATE VIEW `view with spaces in the name` ...
  

CREATE VIEW "view with spaces in the name" ...

• To provide the value of an interpolation variable, do it with the “=” operator. E.g. the following query is no longer valid.

  SELECT *
  FROM view1
  WHERE input_status > 'value'
  

  In previous versions, you could use any operator and the result was the same as using =. However, it was semantically incorrect.

• In literals, you can no longer use the backslash (\) to escape the single quote ('). To escape a single quote, you have prefix it with another single quote.

  For example, in Denodo 5.5, the following query returns he\’llo and in Denodo 6.0, returns he\.

  SELECT 'he\'llo' AS f1
  

Changes in VQL Statements¶

Dependencies to Develop Custom Elements (Functions, Wrappers…)¶

Some of the jars required to develop custom elements (custom functions, custom wrappers, stored procedures and custom input filters) now have different name.

However, the names of the Java packages and classes have not changed. Therefore, the custom elements developed for previous version will work correctly in Denodo 6.0.

The new names are documented in the Virtual DataPort Developer Guide.

Importing Roles from an LDAP Server¶

In Denodo 6.0, in the “Import roles from LDAP” dialog of “Roles Management”, the value you have to enter in “Role base” is different from the previous versions.

To import the roles, Virtual DataPort queries the LDAP server and this LDAP query is performed under a specific level.

In Denodo 5.5 and earlier versions, this level is the concatenation of two values:

1. The base search of the URI of the LDAP data source.

2. And, the value of the “Role base” field of the settings of the database.

In Denodo 6.0, this level is just the value of the “Role base” field.

For example, if the URL of the LDAP data source used by the database is “ldap://acme.denodo.com:389/DC=Denodo” and “Role base” on the database is DC=contoso,DC=com:

• In Denodo 5.5 and earlier, the search is performed under the level DC=denodo,DC=contoso,DC=com.

• In Denodo 6.0 and 7.0, the search is performed under the level DC=contoso,DC=com.

JDBC Data Sources¶

JDBC Driver¶

JMX¶

Starting with Denodo 6.0, the operation GetActiveRequestList() of the JMX MBean VDBServerManagementInfo returns the complete list of requests sent to the server. In earlier versions, this operation only returns information about SELECT, CALL, INSERT, UPDATE and DELETE requests.

With this change, you also can monitor Data Definition Language and DESC statements.

Memory Usage Options¶

Starting with Denodo 6.0, by default, Virtual DataPort limits the total amount of memory used by a query.

This feature existed in earlier versions of Denodo, but it was disabled by default.

The section Limit the Maximum Amount of Memory of a Query describes of the Virtual DataPort Administration Guide this option.

ODBC Driver¶

REST Web Services No Longer Return the HTTP Code 204 by Default¶

Starting with Denodo 6.0, the REST Web services published by Denodo no longer return the HTTP code 204 (No content) by default.

In previous versions, the RESTful web service and the published REST web services return the HTTP code 204 in the following scenarios:

1. The request has the parameter $filter and does not return any rows.

2. Or the request queries a view that does not return any rows.

This applies only to the JSON and XML representations; not to the HTML one.

If you want the REST Web services to go back to the behavior of previous versions (returning 204), execute the following command from the VQL Shell of the Administration Tool:

SET 'com.denodo.wsgenerator.restws.noResultsReturnHTTPCode' = '204';

After executing this statement, all the REST Web services that you deploy or redeploy will:

• Return the HTTP code 204 instead of 200 when the result does not return any rows.

• They will return an empty response, instead of an empty XML or JSON document (depending on the representation requested).

You do not need to restart Virtual DataPort to apply this change, but you have to redeploy the REST web services.

REST services continue returning the HTTP code 204 when updating (HTTP PUT request) or deleting rows (HTTP DELETE request).

The behavior of the RESTful Web service (http://localhost:9090/denodo-restfulws) has changed in the same way. To configure it to return the HTTP code 204 instead of 200 in the scenarios listed above, follow these steps:

1. Edit the file other_settings.xml of the directory <DENODO_HOME>/resources/apache-tomcat/webapps/denodo-restfulws/WEB-INF/

2. Set the value of the property noResultsReturnHTTPCode to 200.

3. Restart the Virtual DataPort server to apply the changes.

Stored Procedures¶

The return parameter old type of the SOURCE_CHANGES built-in stored procedure has been renamed to old_type.

User Privileges¶

Starting with Denodo 6.0, users can query views that belong to databases other than the one they are connected. Therefore, even if a user is not allowed to connect to a database, she can query the views of that database over which she has read access, when she is connected to another database. However, as it cannot connect to that database, it will not be able to create, edit or drop its views.

In previous versions, the users can only query views that belong to databases over which they have the privilege “Connect”.

Version Control System Integration (VCS)¶

The process of importing checking out a database from a Version Control System (VCS) has changed in this version.

In Denodo 5.5, when a statement obtained from the VCS fails, the process is stopped and nothing is imported.

In Denodo 6.0, when a statement obtained from the VCS fails, the Server continues executing the next one. This is necessary so is possible to partially update a database that has elements that depend on elements from another database that does not exist yet.

Windows Services¶

Starting with Denodo 6.0, when you change the user that starts the Denodo Windows services, you do not have to modify the value of the parameter “jna_tmpdir”. In Denodo 6.0, this property points to a folder in the Denodo installation.

In previous versions, this parameter points to the TEMP directory of the user that installed the Denodo Platform. Therefore, when you change the user that runs the Denodo Windows services, in previous versions you have to change the value of the “jna_tmpdir”. But you do not have to do this in Denodo 6.0.

Features Deprecated in Denodo 6.0¶

This section lists the features that are marked as deprecated in Denodo 6.0. Deprecated features are not actively maintained and may be removed in future versions of the Denodo Platform. We recommend not using them anymore.

CREATE TABLE internet_inc I18N us_est (
    iinc_id:long,
    summary:text,
    ...
    ...
);

ALTER TABLE internet_inc
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD internet_inc(
        I18N us_est
        CONSTRAINTS (
             ADD iinc_id (any) OPT ANY
             ADD summary (any) OPT ANY
             ...
             ...
        )
        OUTPUTLIST (iinc_id, specific_field1, specific_field2, summary, taxid, ttime
        )
        WRAPPER (jdbc internet_inc)
    );

CREATE TABLE internet_inc I18N us_est (
    iinc_id:long,
    summary:text,
    ...
    ...
    )
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD internet_inc(
        I18N us_est
        CONSTRAINTS (
             ADD iinc_id (any) OPT ANY
             ADD summary (any) OPT ANY
             ...
             ...
        )
        OUTPUTLIST (iinc_id, specific_field1, specific_field2, summary, taxid, ttime
        )
        WRAPPER (jdbc internet_inc)
    );

Features Removed in Denodo 6.0¶

This section lists the features that are no longer available in Denodo 6.0.