This is mostly a bug fix release, but with one new SQL feature implemented which could be very useful for testing and ALM usage scenarios.

WAITFOR statement

This SQL statement pauses your script for a period of time. This can be useful if you need to wait for some async operation to happen in the background after you run an INSERT/UPDATE/DELETE command. You could also leave a script running in a loop to see how your data changes over time. For example, while you’re running a data migration:

DECLARE @count AS INT;

WHILE 1 = 1
    BEGIN
        SET @count = (SELECT count(*)
                      FROM   account);
        PRINT CONVERT (VARCHAR, CURRENT_TIMESTAMP, 120) + ' - ' + CONVERT (VARCHAR, @count);
        WAITFOR DELAY '00:10';
    END

This produces a log in the Messages pane that updates every 10 minutes with the number of accounts in your system so you can monitor the progress of your data import process. Just hit the Stop button in the toolbar when you’re done.

With the WAITFOR statement you can either specify a DELAY or a TIME. Both should be in the format 'hh:mm[:ss]'. WAITFOR DELAY '12:00' will pause your script for 12 hours, while WAITFOR TIME '12:00' will wait until 12pm.

Improved null handling

This release fixes a few bugs with how the null literal is handled in different situations, particularly with INSERT and UPDATE statements.

One other useful enhancement is with clearing out polymorphic lookup fields. Normally if you want to set the value of one of these lookups that can refer to more than one other table (like contact.parentcustomerid or <anything>.ownerid) you also have to set the corresponding ___type field. For example, if you write:

UPDATE contact
SET    parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761'
WHERE  firstname = 'Mark'

it won’t know if that ID relates to a contact or an account. Instead you have to write:

UPDATE contact
SET    parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761',
       parentcustomeridtype = 'account'
WHERE  firstname = 'Mark'

However, null is a special case, so you can now remove any existing values from a polymorphic lookup field without having to specify the type:

UPDATE contact
SET    parentcustomerid = null
WHERE  firstname = 'Mark'

Metadata filters

If you query the metadata using a SELECT statement like:

SELECT displayname
FROM   metadata.entity
WHERE  logicalname = 'Account'

this would previously not produce any results due to the incorrect case of the logical name (should be all lower case). These filters are now translated to the correct case so you get the expected results.

Because filters have to be supplied in the correct case when SQL 4 CDS issues the metadata query to the server, filters on other columns where SQL 4 CDS doesn’t know what case the value will be in can’t be sent to the server. In these cases SQL 4 CDS will instead retrieve all the possible values and perform the filtering itself. This means you’ll get the correct results but the queries may take a little longer to run.

10 thoughts on “SQL 4 CDS 6.2 Released”

  1. Hi Mark, seems this new version the Bypass custom plugins is not longer working.

    Can I add a code on my UPDATES while this bug gets fixed?

    Thanks!

    Reply

    1. Thanks for letting me know. There isn’t a workaround for this but I should be able to get an update out shortly. If you need something quicker you can download an earlier release from GitHub.

      Reply

      1. Hello Mark,

        There seem to be an issue with the OPTION (USE HINT ‘BYPASS_CUSTOM_PLUGIN_EXECUTION’)) when the “Use TDS Endpoint where possible” option is activated, as we get the following error :

        “‘BYPASS_CUSTOM_PLUGIN_EXECUTION’ is not a valid hint.
        RequestId: ****************
        Time: 2022-06-02T13:16:53.5440131Z
        See the Execution Plan tab for details of where this error occurred”

        And in the Execution plan tab we can see that the connector sent the instruction through TDS altought there is a “non SQL” hint.

        Thank your for this great tool.

        Regards,

        S.

        Reply

  2. Hi Mark

    First let me say that I think that ‘SQL 4 CDS’ is a great tool and coming from a SQL-Server background it’s enabled me to get up and running with the dataverse really quickly.

    After using the tool for some time for selects I’ve just attempted the first Update and am having an issue.

    I have a attribute lets call it flag which current has values of null,0,1 and I want to set all null values to 0.

    Update table set flag-0 where flag is null

    but is gives me the error:-

    Error updating table – The given key was not present in the dictionary.

    I’m assuming that because the value of the attribute is null, the attribute does not exist in the object and therefore can’t be updated.

    Is there any way round this in ‘SQL 4 CDS’? I did try searching through the blog to see if it had come up before, but no luck.

    Oh and it would be great if the query results could be saved as a csv or/and Excel.

    Thanks for any help you can give and again congrats on such a useful tool!

    Gordon

    Reply

      1. Hi Mark

        Think we’ve traced the issue to a custom plug-in. I’ll let you know if fixing the custom plug-in resolves it.

        thanks

        Gordon

        Reply

  3. Just discovered SQL 4 CDS… and it’s great.
    So far just one question: if I save a new query I get the ‘Save as’ dialog box. If I re-save a saved query, it just saves so it writes over the original file. Is there a way of re-accessing ‘Save As’ so I can save as a different name / check or change where I’m saving?

    Reply

    1. Good question – no, that’s currently not possible. The workaround for now would be to copy & paste the query to a new query tab and save it again from there. I’ve logged this as an issue on the GitHub site now and hopefully I’ll include something for this in a future update.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.