SQL 4 CDS 2.3.0 Released!

I’m pleased to release the latest update for SQL 4 CDS today! For this update I’ve focused on common problems I’ve seen people encounter with their queries from my telemetry. These updates should either mean that more queries can be executed immediately, or the error message is more descriptive to help solve common errors.

SQL handling improvements

This release fixes a bug which caused an error when referring to an aliased column in an ORDER BY clause, so you can now correctly run:

SELECT   name,
         firstname,
         count(*) as count
FROM     account
         INNER JOIN
         contact
         ON parentcustomerid = account.accountid
GROUP BY name,
         firstname
ORDER BY count

For queries that need to be filtered in memory by SQL 4 CDS as the WHERE clause could not be converted to FetchXML, this release also fixes bugs:

• comparing two lookup values
• queries that include additional parentheses
• filter date/time values

SELECT name
FROM   account
WHERE  telephone1 = '+44' + telephone2
       AND (createdby = ownerid)
       AND createdon >= '2020-01-01'

When limiting the number of rows to return using the TOP clause, I’d often see errors when people enclose the number in brackets. This is now fixed so you can correctly run:

SELECT TOP (10) name
FROM   account

To get the number of records, I’d normally write COUNT(*) but I’d often see people using COUNT(1) instead. This would work, but would trigger SQL 4 CDS to calculate the count in memory instead of converting it to an aggregate FetchXML query. Now COUNT(*), COUNT(1) and even SUM(1) are all treated identically and will take advantage of more efficient execution whenever possible.

Date filters often include calculations involving GETDATE() to get the current date & time, so I’ve now added support for this and other similar functions. You can now filter records using queries such as:

SELECT name
FROM   account
WHERE  createdon < DATEADD(month, -1, GETDATE())

This can’t be converted directly to FetchXML (see my earlier post for more information on the dynamic date filtering options offered by FetchXML), so where possible SQL 4 CDS will calculate the filter value and pass this into the FetchXML as:

<fetch>
  <entity name="account">
    <attribute name="name" />
    <filter>
      <condition attribute="createdon" operator="lt" value="2020-07-28 21:46:23" />
    </filter>
  </entity>
</fetch>

Although most queries people run are SELECT, I personally use it almost as much for UPDATE, INSERT and DELETE as well. To make it simpler for some bulk update scenarios I’ve added support for the REPLACE function, so you can write:

UPDATE new_blogpost
SET    new_content = REPLACE(new_content, 'Dataflex Pro', 'CDS')

T-SQL endpoint improvements

I’ve been doing quite a bit of work recently investigating the preview TDS endpoint for executing SQL queries directly against CDS, and one common issue I keep hitting is a valid query like this will generate errors:

SELECT account.name,
       contact.fullname
FROM   account
       INNER JOIN
       contact
       ON account.accountid = contact.parentcustomerid

If you use SQL 4 CDS to execute the query against the T-SQL endpoint (you can turn this option on in the Settings dialog), the query will be automatically rewritten behind the scenes as:

SELECT account.name,
       contact.fullname
FROM   account AS account
       INNER JOIN
       contact AS contact
       ON account.accountid = contact.parentcustomerid

This release also improves the user experience when enabling or disabling the TDS endpoint from within the SQL 4 CDS Object Explorer pane.

Error handling improvements

When you update a polymorphic lookup field, e.g. the parentcustomerid field on a contact, or regardingobjectid on an activity, you need to specify the entity type of the related record as well as the ID. This is a common error I see when someone writes a query such as:

UPDATE contact
SET    parentcustomerid = 'FAA30FF7-EDB2-4053-9A3C-BDBCEF645FAA'
WHERE  contactid = 'AA4E2264-9375-4CFF-B160-1CEF86BB94C7'

This needs to be written instead as:

UPDATE contact
SET    parentcustomerid = CREATELOOKUP('account', 'FAA30FF7-EDB2-4053-9A3C-BDBCEF645FAA')
WHERE  contactid = 'AA4E2264-9375-4CFF-B160-1CEF86BB94C7'

and the error message will now direct you to this solution.

I also commonly see errors due to people using double quotes to enclose string literals instead of single quotes. By default, SQL treats double quotes to enclose identifiers such as table or column names. There is an option in the Settings dialog to change this (untick the “Quoted Identifiers” checkbox), but if you use this incorrectly the error message will now suggest that you use single quotes instead.

FetchXML to SQL Conversion

Inspired by Michael Ochs’ virtual entity provider for connecting to SQL databases, I’ve now updated the conversion from FetchXML to SQL and made it available as a separate package for you to reuse in your own tools!

You could already do this using the full SQL 4 CDS engine, but this stripped down version makes it easier to include this functionality in plugins.