Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 R2 - Need to create a CLR assembly that calls a Java function and implement as a UDF Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 12:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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)

Internet ATM Machine
Post #1545539
Posted Wednesday, February 26, 2014 2:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
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.
Post #1545595
Posted Wednesday, February 26, 2014 3:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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)

Internet ATM Machine
Post #1545618
Posted Wednesday, February 26, 2014 4:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:22 PM
Points: 7,074, Visits: 15,318
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/

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?
Post #1545641
Posted Wednesday, February 26, 2014 5:53 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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/

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)

Internet ATM Machine
Post #1545650
Posted Wednesday, February 26, 2014 7:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 35,262, Visits: 31,748
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545662
Posted Wednesday, February 26, 2014 8:00 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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)

Internet ATM Machine
Post #1545669
Posted Wednesday, February 26, 2014 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 35,262, Visits: 31,748
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545699
Posted Wednesday, February 26, 2014 11:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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# - http://www.SQLsharp.com/
Post #1545717
Posted Thursday, February 27, 2014 5:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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)

Internet ATM Machine
Post #1545797
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse