sgmunson (2/26/2014)
Hi all,I have an existing Pervasive ETL process that uses a Java method, and I would like to create a user-defined function in SQL Server 2008 R2 that references that Java method. The only info I have is the exact path and dll filename that contains the method, and that it's a Java method. Does anyone know all the detailed steps that would be necessary to get this method to be available to SQL Server 2008 R2 as a UDF? I can presume some of them, but don't have the knowledge or experience to know for sure. As it's a Java method, it might rely on other Java stuff, so it may mean having to get Java installed on the SQL Server, or at the absolute minimum, the specific dll copied over to it and registered. Beyond that, I have no idea other than that a CLR assembly would have to be created that somehow references that method and is also SQL Server UDF compliant, so to speak. Any/all guidance would be appreciated.
Hey there. The Java code can't simply be called by any other random code; it needs to be accessed within the context of the JVM, correct? Unless this is J# in which case it would be .Net accessible. Basically, if this is Java and not J#, then any CLR code, whether within SQL Server or native Windows, would still need to contact some process that understands how to interpret this Java DLL. A web-service call like someone else mentioned is one way but regardless, something needs to interpret the p-code and understand the links / references made by that DLL to other code, not to mention also translating the types between the languages.
So, the first step is figuring out how to interact with this Java code (and this method specifically) from the .Net realm since you cannot make a simple reference to the DLL. Not wanting to do a web-service means needing to install Java on the SQL Server machine and potentially loading the entire Pervasive ETL program depending on what the DLLs dependencies are.
Next, do you know the full signature, including specific types, of this method? If so, perhaps a small Java method can be written that is just a wrapper to this and can be called (i.e. an .EXE or something). Maybe that could be called by xp_cmdshell? Of course, this is essentially the same road as the web-service route. One benefit of the web-service route is that it can be done with just EXTERNAL_ACCESS for the SQLCLR assembly and doesn't require UNSAFE permissions. If the Java side of that is easy to create then maybe it is plausible as the .Net is also fairly easy.
Of course, if it were possible to write a wrapper in Java that could access that proprietary method AND be COM-accessible, THAT would be usable in a more direct fashion. I just found this on Google which is pretty much the idea, but just going the wrong direction (from .Net to Java instead of accessing Java from .Net), but it is worth checking out, I think:
http://groovy.codehaus.org/Bridging+the+Gap+Between+Java+and+.NET+with+Groovy+and+Scriptom
Outside of making a COM-accessible Java wrapper, I don't see any way to handle this as an internal method call.
I know this is not a specific answer, but hopefully some of the ideas spark some useful thoughts :-).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR