-- enabling execution oc CLR code on SQL server:
EXEC sp_configure 'clr enabled';
EXEC sp_configure 'clr enabled' , '1';
RECONFIGURE;

-- select the database
USE [p2j_test]

-- install the IKVM assemblies (will install all dependencies recursivelly)
CREATE ASSEMBLY ikvm_runtime
FROM 'c:\Program Files\ikvm-7.2.4630.5\bin\IKVM.Runtime.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY ikvm_openjdk_jdbc
FROM 'c:\Program Files\ikvm-7.2.4630.5\bin\IKVM.OpenJDK.Jdbc.dll'
WITH PERMISSION_SET = UNSAFE

-- install the assembly with our functions
CREATE ASSEMBLY [p2j_operators]
AUTHORIZATION [dbo]
FROM 'c:\Work\1stSqlProject\P2JMsSql1\java\p2j_operators.dll'
WITH PERMISSION_SET = SAFE

-- if the original java sources had an entry point, the ikvm will 
-- genetate an exe, that can also be used as normal assembly:
CREATE ASSEMBLY [p2j_operators]
AUTHORIZATION [dbo]
FROM 'c:\Work\1stSqlProject\P2JMsSql1\java\p2j_operators.exe'
WITH PERMISSION_SET = SAFE

-- uninstall the old assembly in order to provide a newer version
DROP ASSEMBLY [p2j_operators]


-- declare the functions:
CREATE FUNCTION [dbo].[P2J_AndScalar]
(@param1 BIT, @param2 BIT)
RETURNS BIT
AS EXTERNAL NAME p2j_operators.Operators.P2J_AndScalar

CREATE FUNCTION [dbo].[P2J_Add32bitScalars]
(@param1 INT, @param2 INT)
RETURNS INT
AS EXTERNAL NAME p2j_operators.Operators.P2J_Add32bitScalars

CREATE FUNCTION [dbo].[P2J_Add64bitScalars]
(@param1 BIGINT, @param2 BIGINT)
RETURNS BIGINT
AS EXTERNAL NAME p2j_operators.Operators.P2J_Add64bitScalars

CREATE FUNCTION [dbo].[P2J_Add64bitObjects]
(@param1 BIGINT, @param2 BIGINT)
RETURNS BIGINT
AS EXTERNAL NAME p2j_operators.Operators.P2J_Add64bitObjects

CREATE FUNCTION [dbo].[P2J_Concat]
(@param1 NVARCHAR(2000), @param2 NVARCHAR(2000))
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME p2j_operators.Operators.P2J_Concat

CREATE FUNCTION [dbo].[P2J_MultiplyDecimalScalar]
(@param1 float, @param2 float)
RETURNS float
AS EXTERNAL NAME p2j_operators.Operators.P2J_MultiplyDecimalScalar

CREATE FUNCTION [dbo].[P2J_interval2]
(@param1 datetime2, @param2 datetime2)
RETURNS bigint
AS EXTERNAL NAME p2j_operators.Operators.P2J_interval2

CREATE FUNCTION [dbo].[P2J_interval1]
(@param1 date, @param2 date)
RETURNS bigint
AS EXTERNAL NAME p2j_operators.Operators.P2J_interval1



-- before uninstalling an assembly all functions implemented in it must be removed
DROP FUNCTION [dbo].[P2J_AndScalar]
DROP FUNCTION [dbo].[P2J_Add32bitScalars]
DROP FUNCTION [dbo].[P2J_Add64bitScalars]
DROP FUNCTION [dbo].[P2J_Concat]
DROP FUNCTION [dbo].[P2J_MultiplyDecimalScalar]




select 
	[dbo].[P2J_AndScalar](1, 1) as 'boolean', 
	[dbo].[P2J_Add32bitScalars](-10, 20) as 'int', 
	[dbo].[P2J_Add64bitScalars](10, 20) as 'long', 
	[dbo].[P2J_MultiplyDecimalScalar](3.1, 2.3) as 'double',
	[dbo].[P2J_Concat]('green ', 'apples') as 'String'

