SQL 2008 R2 - Need to create a CLR assembly that calls a Java function and implement as a UDF

  • 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.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • You'll need to create a .NET assembly that ultimately calls out to the Java routine. There will be a lot of overhead in this so consider how it will be used - the performance may be unacceptable.

    If it were me, I would implement the functionality in straight .NET.

    The probability of survival is inversely proportional to the angle of arrival.

  • The source code for the function is proprietary, and thus, unavailable. The number of rows it would apply to would be relatively small. As the only non-ETL alternative would be to decrypt a significantly larger quantity of rows, there's really no viable alternative ... especially from a performance perspective, where the alternative is known to be a serious performance problem waiting to happen.

    I also need detailed step by step so that I can learn the process as well as understand it. I can read the VB code needed as I've been writing VBA and VBScript for more years than most folks have been working with computers, but I don't have any exposure to the details behind how a .NET assembly is compiled / created / deployed, or, once those things are done, referenced by a SQL Server UDF.

    sturner (2/26/2014)


    You'll need to create a .NET assembly that ultimately calls out to the Java routine. There will be a lot of overhead in this so consider how it will be used - the performance may be unacceptable.

    If it were me, I would implement the functionality in straight .NET.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Trying to go at it using code that actually reference the Java assembly within .NET is going to force you into making your assembly "unsafe". You may not want to pursue the direct approach and wrap your Java code using a few methods which might make accessing it more palatable.

    Two options spring to mind:

    1. the first option is closest to what you're envisioning. The trick would be to create a .NET assembly that accesses the java assembly using REST. REST is essentially a web service call, which puts the execution context of the java code outside of SQL Server itself, allowing you to preserve the TRUSTWORTHY access (i.e. the SQLCLR procedure can't be used to damage SQL Server, etc...). Obviously you'd have to set up your java assembly to be callable via REST (usually wrapping it within a WAR file and posting it to Apache, etc..)

    A few primers on this:

    http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

    http://sqlsunday.com/2013/03/03/web-requests-using-clr-proc/[/url]

    2. Use SQL broker

    SQL Service Broker can be wired into using JMS (Java messaging). This one's not for the faint of heart, but it can be very performant if you're comfortable with SSB. I've seen it in action, but am not in a position to walk you through all of the steps.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hmmm.... that's disappointing news. We would find it impractical to build a web-service for something with such low volume, and making the assembly UNSAFE isn't going to fly. That pretty much kills this and leaves just the ETL methodology that already exists. Thanks for the detailed explanation, as it will make explaining the problem much easier. Shucks, folks...

    Matt Miller (#4) (2/26/2014)


    Trying to go at it using code that actually reference the Java assembly within .NET is going to force you into making your assembly "unsafe". You may not want to pursue the direct approach and wrap your Java code using a few methods which might make accessing it more palatable.

    Two options spring to mind:

    1. the first option is closest to what you're envisioning. The trick would be to create a .NET assembly that accesses the java assembly using REST. REST is essentially a web service call, which puts the execution context of the java code outside of SQL Server itself, allowing you to preserve the TRUSTWORTHY access (i.e. the SQLCLR procedure can't be used to damage SQL Server, etc...). Obviously you'd have to set up your java assembly to be callable via REST (usually wrapping it within a WAR file and posting it to Apache, etc..)

    A few primers on this:

    http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

    http://sqlsunday.com/2013/03/03/web-requests-using-clr-proc/[/url]

    2. Use SQL broker

    SQL Service Broker can be wired into using JMS (Java messaging). This one's not for the faint of heart, but it can be very performant if you're comfortable with SSB. I've seen it in action, but am not in a position to walk you through all of the steps.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (2/26/2014)


    The source code for the function is proprietary, and thus, unavailable. The number of rows it would apply to would be relatively small. As the only non-ETL alternative would be to decrypt a significantly larger quantity of rows, there's really no viable alternative ... especially from a performance perspective, where the alternative is known to be a serious performance problem waiting to happen.

    Maybe... maybe not. There are a lot of things that people just don't know. Heh..; yeah... right now, I agree...we know nothing of your code but it wouldn't be the first time that someone thought they had code that was unbeatable for performance that got beaten with a bit of T-SQL prestidigitation. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It's already been tested. Against a measly million rows. It's a decryption function that is already CLR. It took far too long to be the least bit practical. We're talking most of a 24 hour day. It's supposed to take a long time to decrypt, so as to help ensure the security of the data. The solution for matching incoming data rows from the ETLs is to have the match take place against a separate HASH value that is static for a given input, as opposed to simply encrypting the incoming value and matching it against the existing encrypted value, because that last CANNOT work... The encryption algorithm will produce a different value based on when it runs, but the decryption routine still has to be able to decrypt everything that gets encrypted, regardless of when. When I tell you that source code is proprietary, it's proprietary for a fiscal reason. It's highly secure, and thus has high dollar value.

    We don't own the source. Anyway, the only viable solution for matching in the long run is to use the hash function on the incoming value as part of the ETL, and stage that data for matching by the rules engine, which can easily match hash value to hash value nearly instaneously, and for all likely incoming volumes and well beyond.

    What we were hoping for was an easy way to take that hashing function and just CLR it so that the ETL would no longer need to do that piece of work, but alas, the requirements for same can't be met in the environment this needs to go into. We don't need to improve hashing performance. We just need to stop having to decrypt a large number of rows just to be able to match incoming data. We already know that the decryption function is just as unavailable to us as the hashing function source (and for the same reason), so that's not a "replaceable" component either. We also know that there's no way to "change the process" to provide a means of further limiting that number of rows to a much smaller subset of the database. Sorry my friend, but it's time for you to grab a beer popsicle and we'll commiserate over the limits the dang "real world" keeps lobbing over our cubicle walls.

    Jeff Moden (2/26/2014)


    sgmunson (2/26/2014)


    The source code for the function is proprietary, and thus, unavailable. The number of rows it would apply to would be relatively small. As the only non-ETL alternative would be to decrypt a significantly larger quantity of rows, there's really no viable alternative ... especially from a performance perspective, where the alternative is known to be a serious performance problem waiting to happen.

    Maybe... maybe not. There are a lot of things that people just don't know. Heh..; yeah... right now, I agree...we know nothing of your code but it wouldn't be the first time that someone thought they had code that was unbeatable for performance that got beaten with a bit of T-SQL prestidigitation. 😉

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I agree. Beer popsicles are in order for this one. Thanks for sharing the nature of it all. Heh... sounds like a lot of "fun". This one is definitely not a job for SQL Server alone.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • That's quite detailed, and I'm sure it could be pursued, but the effort level at this point is somewhat high, and given that I'm already up against a deadline, there's just no time left to go down that road, as the overall level of experience necessary ends up extending into the Java space, to be able to create the wrapper. Lacking the truly quick and dirty solution, we'll leave well enough alone and leave the task in the ETL. Thanks so much for the well-thought out and detailed explanation, as it might otherwise have been one of those rabbit-holes that you check out, only to find yourself either A) right back where you started, less time spent, or B) so far down that hole you can't find your way back. Thanks again!

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (2/27/2014)


    That's quite detailed, and I'm sure it could be pursued, but the effort level at this point is somewhat high, and given that I'm already up against a deadline, there's just no time left to go down that road, as the overall level of experience necessary ends up extending into the Java space, to be able to create the wrapper. Lacking the truly quick and dirty solution, we'll leave well enough alone and leave the task in the ETL. Thanks so much for the well-thought out and detailed explanation, as it might otherwise have been one of those rabbit-holes that you check out, only to find yourself either A) right back where you started, less time spent, or B) so far down that hole you can't find your way back. Thanks again!

    No problem. And certainly understood about time constraints ;-). The COM-accessible wrapper is just the cleanest implementation. I am not sure what level of effort is required to get the Java method exposed as a web-service (what Matt Miller suggested), but if that was somewhat easy, then this might be possible as consuming it via SQL CLR could be done in a matter of minutes (i.e. quick and not even dirty :-)) and then you have your ability to call this method.

    Take care,

    Solomon...

    PS, by "quick and not even dirty" I mean that there are ways to get the SQLCLR side of the web call done correctly and quickly, but maybe not free ;-). I can say, please do NOT use the code shown in either of the links posted by Matt. Nothing against Matt as the REST idea is a good idea, but the SQLCLR code shown in both places is poorly done and should not be on a Production server. I have submitted notes to the SqlSunday.com link.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply