CODEWITHSUNDEEP
sql-serverdelphistored-proceduresdelphi-xe2
UnDiUdin
UnDiUdin

Reputation: 15384

Write a CLR stored procedure

I would like to use existing Delphi code to write SQL Server stored procedures.

In the past I used extended stored procedures, somehow a dll compiled by Delphi wrapped by a SQL Server extended stored procedure.

Extended stored procedures are now deprecated, so somehow I wonder if there is a solution, even in the "trick domain", like some sample CLR code that wraps a normal dll or something like that.

Just to give you an example:

I insert in the db some documents by encrypting them and I would like to create a kind of API based on SQL Server functions / procedures for inserting or reading documents, so other people accessing sql server can call those functions.

Of course an alternative solution is to use webservices but I would like to try the SQL Server way first.

Note: I don't want to use Delphi Prism, my code is for XE2.

Upvotes: 1

Views: 924

Answers (2)

Michael
Michael

Reputation: 597

Not to mention the fact that CLR in SQL Server is a guaranteed deep performance hit. Keep to the standard CRUD operators and you should be fine. The other way to do it is to use the file system as your encryption mechanism. If you are only trying to prevent casual access to docs this is a fine way to go. Otherwise it might be time to rethink your access protocol.

CLR in SQL Server is a convenient bad idea. Use it sparingly if at all.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294277

Unsafe SQLCLR assemblies can p-invoke native dlls. A better way would be to expose the native DLL services as a COM interface and use COM interop from SQLCLR, or even call the COM APIs directly from SQL via OLE Automation Procedures. An even better way would be to rewrite the Delphi code as CLR code and invoke it directly as SQLCLR procedure. And the best way would be to use SQL Server native encryption features.

Upvotes: 5

Related Questions