Executing Custom API Calls based on Query Response

[SiPurdy]

I have a CustomAPI call which has the following inputs...

• Config: String
• DisablePlugins: boolean
• Target: EntityReference (expando)
  The idea is that the CustomAPI can be invoked on any record.

... and no outputs.

The call can be executed using the following syntax without issue.
exec <customapiname> ....

The question is whether it's possible to run the individually customapi for each record returned from a query?

I have tried Cross Apply to no luck and Sql4Cds doesn't do Cursors.

[MarkMpn]

Cursors are the real solution here, and one I’d like to implement in the future. For now, #424 shows one method of looping through data and performing an action. It’s not as efficient as a cursor would be but should be good enough for small data sets.

[MarkMpn]

Naming parameters in a call to a table-valued function is not supported in TSQL. The order of the parameters is defined by the position attribute of the sdkmessagerequestfield. I don't believe this is exposed by the UI for creating Custom API request parameters, but should be consistent for APIs moved as part of solutions.

[SiPurdy]

but should be consistent for APIs moved as part of solutions

There's no specific positional information in the solution exports having poked around the customapi.xml or customapirequestparameter.xml files which does leave me a little concerned that the order might be a bit more fuzzy, eg alphabetical based on the order that the parameters are first created meaning an updated deployment with a new parameter might have a different order compared to a fresh install of the same version.

I've just been burnt so many times that if something isn't specified within the solution exports you can't rely on it.

I'll see if experience mirrors expectations but there may be situations when diverging from existing supported TSQL syntax might be required to make it possible to create scripts that don't need changing per environment (eg parameter ordering).

[SiPurdy]

Not good news. Took my managed solution with the customapi and imported into a clean environment, sdkmessagerequestfield records are as follows:

parameter	position
DeleteAudit	1
Config	2
DisablePluginWorkflowFlow	3
Target	4

Deleted the managed solution and re-imported the same solution file:

parameter	position
DeleteAudit	1
DisablePluginWorkflowFlow	2
Config	3
Target	4

A third run ended up with a different order.

In short, there is zero guarantee that the position on sdkmessagerequestfield is going to match between environments which means care needs to be taken when running cross-apply scripts across environments.

[MarkMpn]

there may be situations when diverging from existing supported TSQL syntax might be required to make it possible to create scripts that don't need changing per environment (eg parameter ordering).

SQL 4 CDS uses the ScriptDOM library to parse and manipulate queries. This is built specifically for the TSQL syntax. Any deviation from that syntax would require completely replacing that library with a custom parser, probably created as a fork of the main ScriptDOM code. I'd be happy to review a PR that implements such a parser, but it's not something I've got time to work on myself.

[SiPurdy]

Don't blame you on that one. 20-odd years since I played with YACC, and 5 years since I tried some stuff using ANTLR.

Is it viable to ignore the position column on sdkmessagerequestfield and process the parameters alphabetically as that will be deterministic between deployments?