Sending SQL to BB CRM and getting data back

Mikhail Zolikoff

I've chatted with support and poked around in the docs, but it doesn't seem like there's a way to send SQL to a hosted BB CRM instance and get data back.

Comparatively, Salesforce allows you to do the following, where you can send a SQL query after the q:

sandbox.my.salesforce.com/services/data/v59.0/queryAll?q=

In short, I'm looking for a simpler way to extract and send data to BB CRM without having to use a variety of SOAP endpoints etc, and the SKY APIs are limited in terms of their functionality.

=====

EDIT support did respond with the following: “You could write something custom which expects SQL as a string, then executes that through CLR. It's a bit of a hack but should work.”

Joseph Styons

@Mikhail Zolikoff
There is no API that supports arbitrary SQL requests. However, the suggestion that support gave is doable. You can write a customization that takes SQL, runs it, then returns the result. I have placed an example of this here:

In this Global Change, you provide SQL, run the GC, and the results are written to a log. Then you can view the log via a custom query view. I have not included the query view or the log, but the core of the functionality is here, and it should get you started, or give you some ideas.

Mikhail Zolikoff

@Joseph Styons This is GREAT; thank you!

1. Did you call it Global Change for any particular reason (EDIT: ah, here)
2. Being new to this, where does the VB and XML get placed / how does it get installed? For both hosted and on-prem instances? (EDIT: here? And if so, what global change definition should be used for this?)
3. When it's installed, how would one hit it via the API? Do we hit the ExternalResource Url that's in the XML file?

Matthew Handelman

This is certainly a workable hack, but be aware of how open this could be to SQL injection attacks.