sql server programming: SQL CLR - Return complex types -- sql server programming

Re: SQL CLR - Return complex types

Robbe Morris - MVP C#
7/12/2007 7:51:33 PM

I really wouldn't use the CLR for this sort of stuff. Ideally,
you'd return the hierarchy in a flatter format with parent
child keys and build that object tree in .NET outside
SQL Server CLR.

You are putting a lot on the database server that
"could" be spread around more if you are in web farm
environment (or ever expand to one). Do this also
requires you to deploy updated assemblies to the
database server when these classes are changed.

That said, these two articles might prove helpful:

Return hierarchies with sql

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

Return tables from clr

http://www.eggheadcafe.com/articles/sql_server_2005_clr_regex.asp

--
Robbe Morris
EggHeadCafe.com
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

[quoted text, click to view]

Re: SQL CLR - Return complex types

kerry
7/13/2007 5:52:02 PM

Thanks for the reply Robbe,

Your analysis would be correct if I had a web app or some other middle tier
to place the .net code. In fact this is a SQL SSIS package that runs
overnight and calls a complex C++ dll using p/invoke calls. The .net code
merely wraps this dll as it is impractical to rewrite it.

The return from the C++ dll's is hierarchical. I could flatten it but then
I'd get data bloat. Recursion is no good as it's within SQL.

I've read in MSDN that you can return multiple result sets from SQL CLR
using the context pipe send() method. They mention it but don't give
practical examples of how the results could be consumed on the SQL T-SQL
side. I assume because there isn't a technique for doing this?

Cheers,

[quoted text, click to view]

Re: SQL CLR - Return complex types

--CELKO--
7/13/2007 6:10:44 PM

[quoted text, click to view]

Many years ago, the INCITS H2 Database Standards Committee(nee ANSI
X3H2 Database Standards Committee) had a meeting in Rapid City, South
Dakota. We had Mount Rushmore and Bjarne Stroustrup as special
attractions. Mr. Stroustrup did his slide show about Bell Labs
inventing C++ and OO programming for us and we got to ask questions.

One of the questions was how we should put OO stuff into SQL. His
answer was that Bells Labs, with all their talent, had tried four
different approaches to this problem and come the conclusion that you
should not do it. OO was great for programming but deadly for data.

I have watched people try to force OO models into SQL and it falls
apart in about a year. Every typo becomes a new attribute or class,
queries that would have been so easy in a relational model are now
multi-table monster outer joins, redundancy grows at an exponential
rates, constraints are virtually impossible to write so you can kiss
data integrity goodbye, etc.

Re: SQL CLR - Return complex types

kerry
7/19/2007 6:16:05 AM

Agreed, and I'm not trying to do OO programming within SQL nor am a proposing
anyone do this.

I am using the .net CLR to perform a very complex mathematical formula for
which SQL is near worthless. This sits within an SSIS package that runs
purely in SQL overnight. I believe this was one of the intended uses of the
CLR when Microsoft added the it to SQL, i.e. use procedural languages where
it is most suitable.

The only issue I have is that the results from the calculation are best
expressed as a simple hierarchical structure. This is not OO in any way, it
is just a structured storage format which I'd ideally like to return and
consume without translation.

[quoted text, click to view]

Re: SQL CLR - Return complex types

--CELKO--
7/19/2007 1:09:59 PM

[quoted text, click to view]

ouch! Can you get to SAS, Mathlab for this? The problem with writing
your own higher math function that the floating point errors, array
manipulations, etc require a PhD in numerical analysis.

Cynic that I am, I thik the main purpose of CLR is to lock you into
Microsoft forever and to let C# abd VB programmers use SQL Server as
if it were a sequential file system without having to learn
RDBMS. ...

Re: SQL CLR - Return complex types

Tony Rogerson
7/19/2007 9:57:45 PM

[quoted text, click to view]

In that case you totally mis understand the point of CLR.

Ever tried to write a CHECK constraint to validate an email address format?
Post code format etc...

While YOU would shoe horn a SQL solution that suffers from nested code bloat
a real programming would recognise they can use CLR and take advantage of
RegularExpressions from the .NET framework.

STOP guessing and speculating on how things have been implemented, do some
research.

It takes at least a year full time to even begin to understand how product
features work and are implemented.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.