We have an SQLCLR assembly (DLL-file) built in C# that contains about 100 functions that we call from store procedures in SQL Server databases. Is is possible to call these functions in MySQL store procedures as well ? How ??
2 Answers
Nope.
A C# CLR assembly is specific to SQL Server which is intentionally designed to be able to load such an assembly. (Pretty easily conducive since Microsoft owns both C# and SQL Server.)
MySQL (which is not owned / maintained by Microsoft) is not designed in the same way. So you won't be able to directly load the assembly into your MySQL instance and consume it similarly as SQL Server. Maybe if you're lucky, there's some roundabout way to consume the assembly and access its functionality, but I would imagine that would involve a ton of work.
-
1Thanks for the reply, J.D. I will try to find an easier way...user291050– user2910502024-06-04 15:55:15 +00:00Commented Jun 4, 2024 at 15:55
No, it appears that MySQL does not support SQLCLR assemblies. MySQL does seem to allow for adding custom functions, called Loadable Functions, but the only supported language is (at least currently) C++:
https://dev.mysql.com/doc/extending-mysql/8.4/en/adding-loadable-function.html
For the loadable function mechanism to work, functions must be written in C++ and your operating system must support dynamic loading.
Now, if there was some way to call the .NET CLR code from C++ (and I'm guessing there isn't, BUT if there is...), then perhaps you could create C++ wrapper functions to be "loadable functions" and at least make use of the same CLR code-base. However, even if possible, this might be more work than it's worth, especially if those 100 or so functions are largely simple string manipulation, math, etc functions. You might just need to create a C++ project that parallels the same functionality (also assuming that MySQL's Loadable Function API is similar enough to Microsft's SQLCLR API, at least for what you have implemented).
P.S. On a somewhat related side-note: it does appear that IBM's DB2 support CLR assemblies, but not in the exact same manner (or API) as SQL Server, so you can't simply load a SQL Server SQLCLR Assembly into DB2, but you can at least create .NET-based objects (C# or VB.NET):
https://www.ibm.com/docs/en/db2/11.5?topic=functions-developing-db2-clr-procedures
I mention this as an (optimistic) indication that it's possible for another RDBMS to implement SQLCLR via the same API that Microsoft is using for SQL Server, though realistically I'm not expecting this to ever happen, especially seeing as how IBM got close but then, for whatever reason(s), didn't create a compatible API. 😿
-
1Thanks for the reply, Salomon. I feared that there were no easy solution.user291050– user2910502024-06-04 15:53:52 +00:00Commented Jun 4, 2024 at 15:53
-
1In general there are ways to call .net from C++. MS has had several different versions of "C++.net" compatibility layers. The older ones probably only work with MSVC, so I'm not sure how well or even if they'd play with MySQL. Assuming the DB doesn't heavily lock down what you can do with C++ plugins though, falling back on generic IPC methods should work though. OTOH you'd lose any wrappers around database constructs that way.Dan Is Fiddling By Firelight– Dan Is Fiddling By Firelight2024-06-05 03:09:16 +00:00Commented Jun 5, 2024 at 3:09
-
I suspect you'd have better odds with scalar functions than anything that operates at the level of entire tables. stackoverflow.com/questions/778590/…Dan Is Fiddling By Firelight– Dan Is Fiddling By Firelight2024-06-05 03:09:20 +00:00Commented Jun 5, 2024 at 3:09
-
@DanIsFiddlingByFirelight Also on the topic of calling unsupported code from inside supported code, my workaround for that is typically to put the unsupported code behind an API and have the supported code just call and consume that API. In C# / SQLCLR that's rather easy to do. I'd assume in C++ it wouldn't be much harder to call an API.J.D.– J.D.2024-06-05 12:38:43 +00:00Commented Jun 5, 2024 at 12:38