In a comment to my previous post, I was pointed to a Yukon presentation that was given in Netherland´s DevDays that has some interesting details.
I am quite dissappointed with what I saw. It seems that it will be very similar to what we can do in other databases with Java (or COBOL, C, etc). You will need to add an assembly to the database:
CREATE ASSEMBLY events FROM ‘events.dll’
And then define a stored procedure that will be bound to a static method in a class:
CREATE PROCEDURE check_inventory
EXTERNAL NAME ‘events:CInventory.check_level’
Where Cinventory is a class in the 'event' assembly that has a check_level static method.
In Oracle you write:
CREATE PROCEDURE swap (x IN OUT NUMBER, y IN OUT NUMBER)
AS LANGUAGE JAVA NAME 'Swapper.swap(int[], int[])';
Being able to write stored procedures in a .NET language is better than writing them in T-SQL, but it seems that Microsoft is just playing catch-up…
I'll be under NDA for Yukon (aka the next SQL Server version) in a couple of weeks, so this is the moment to post my thoughts about it.
Yukon will probably have a lot of cool features, but the one I'm most interested in is its CLR hosting support. We will be able to run .NET code inside SQLServer.
Oracle included support for writing stored procedures in Java a long time ago, and the same did other databases like DB2, but it is not very popular. One reason could be that Java developers usually do not use stored procedures as discussed in a previous post. Other reason could be that if you wanted to have pre-compilation in the stored procedures you needed to use SQLJ, and that is not very popular also.
So, Java move into the stored procedures world was not very successful, and also, I do not like stored procedures, so why I’m so excited about using the CLR inside SQL Server?
The reason is because I hope Microsoft does what the other guys did not do. I’d probably be disappointed once I know what they did, because I’m quite ambitious, but let’s dream.
To call a Java stored procedure you have to do a stored procedure call as any other. The client application does not know that is written in Java. It has to know the stored procedure name, the parameters, the types, do the call and if something went wrong you get a java.sql.SqlException.
I hope this is not the way we will be able to invoke .NET stored procedures.
I want to keep the same CLR semantics, send complex types as parameters (a DataSet), throw typed exceptions from inside SQL Server and catch them in the middle tier, etc. So, I want something like .NET Remoting to invoke stored procedures. I'm not sure if it can be implemented with .NET Remoting, it would probably involve writing something like a TDS channel and extending the TDS, but it would be great.
On the other side, I want SQL Server to throw typed exceptions for runtime errors, I do not want a SqlException for all the errors. I want a ReferentialIntegrityConstraintViolatedException, and have a property in the Exception class that tells me which constraint was violated.
If they make something like this, it will be a breakthrough in database programmability, and I’ll probably start liking SQL Server stored procedures.