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 «««12345

What's in your CLR? Expand / Collapse
Author
Message
Posted Sunday, July 28, 2013 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
KermitTheRock (7/28/2013)
Question Requirements!
Yes, to make sure they are correct. Once that is established, they are first priority.

Well, not always. In some circumstances, requirements can be changed for the convenience of the company.

But not in healthcare, communications, transportation, defense, finance, generating/managing electricity, ....

So, pardon my tunnel vision--I've been full-time in at least one of those since 1974. (But only doing software since 1985).

Except for twenty months finding out that teaching, much as I enjoyed it, doesn't pay the bills. And now that I no longer have the bills, I'm too old to switch. But that's off-topic.
Post #1478386
Posted Sunday, July 28, 2013 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
LerxtDBA (12/14/2009)
You can buy X12 exporters but they are VERY expensive and still require a lot of work to set up.
That (and import as well) is where I would rather not do SQL, though I have actually done both (which is how I learned they're not the best use of my time).

There are free X12 tools, but every one I have ever looked at (free or not) handles only the syntax level--the easy part. They expect the user to define the semantics and business logic in XML (or worse) that is more complicated than the entire program would be in VB or C#
Post #1478387
Posted Sunday, July 28, 2013 9:00 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
Wesley.Groleau (7/28/2013)
Jeff Moden (7/27/2013)
Wesley.Groleau (7/27/2013)
(snip) Sure, a stored procedure CAN do it, but a script component or other CLR is easier. (snip)
I guess my question would be, why do you think that couldn't be done in a set-based or column-based fashion?
If I thought that, would I have said what I didn't snip?

If you instead ask "why do you think that shouldn't," my answer would be "because I've done both"

If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.

If you are able to avoid either by using nested subqueries (yes, I've done that too, four levels deep), then there's a high probability the next person who has to work on it won't understand it, and will just start over. Or break it.

I know others disagree, but for me, the priorities are
1. Meets the requirements
2. Easy to understand
3. Robust
4. Amenable to re-use
5. Easy to write
6. Efficient

(1) is obviously the first, but (2) makes 1/3/4/5 more likely. And an irrational commitment to (6) often screws up all the others, including (1).


Me too. I mean that I've worked on many complex problems (admittedly, not specifically EDI X12, though) where people have done things like ...

If you haved a screen full of declares and ifs and loops, and are storing values as you go along to be used in later iterations, you are doing procedural programming and might as well use a language originally intended for that.



What I have found is that many people resort to such RBAR in SQL Server because they don't know of a set-based manner to tackle the problem. The presence of such things doesn't necessarily mean that a set-based solution isn't possible.


--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 #1478432
Posted Tuesday, July 30, 2013 5:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 12,895, Visits: 32,090
i have a big proof of concept CLR project that is filled with items i either create myself, or find here on SSC.
I just keep adding to it as I encounter ideas.
some of the things I've done in it:

Regular Expressions.
Read Write Files and Folders, including binary/blobs.
Send Email.
Read Email via POP3.
Read Email via IMAP.
PGP Encryption/Decryption.
AES Encryption/Decryption
Zip/Unzip Files.
Send/Get files via FTP and SFTP.
Convert RTF to text/text to RTF/HTML to RTF.
Export a table or query to multiple formats via a web service.(excel/word/pdf/mht/html/more)
A different write to PDF function.
Read Excel without Office DLL's.
HTML Encode/Decode functions, UrlEncode/Decode Functions.


The usual caveat here applies, just because it can be done in TSQL, or CLR, does not mean it's the right thing to do the right place to do it.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1478925
Posted Tuesday, July 30, 2013 7:39 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
Lowell (7/30/2013)
i have a big proof of concept CLR project that is filled with items i either create myself, or find here on SSC.
I just keep adding to it as I encounter ideas.
some of the things I've done in it:

Regular Expressions.
Read Write Files and Folders, including binary/blobs.
Send Email.
Read Email via POP3.
Read Email via IMAP.
PGP Encryption/Decryption.
AES Encryption/Decryption
Zip/Unzip Files.
Send/Get files via FTP and SFTP.
Convert RTF to text/text to RTF/HTML to RTF.
Export a table or query to multiple formats via a web service.(excel/word/pdf/mht/html/more)
A different write to PDF function.
Read Excel without Office DLL's.
HTML Encode/Decode functions, UrlEncode/Decode Functions.



Those are some pretty good ideas for CLR. On the one for reading/writing files, did you check to make sure that any parameters passed to them were "DOS Injection" proof?

The usual caveat here applies, just because it can be done in TSQL, or CLR, does not mean it's the right thing to do the right place to do it.



Fully agreed but you also have to remember something else. Different companies and organizations (the Department of Defense, for example) have different rules/restrictions/availabiliuty on what can be used and you can't always use the "right place" to do it but the job must still be done.


--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 #1479003
Posted Tuesday, July 30, 2013 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 10:52 AM
Points: 7, Visits: 13
I don't know whether the DoD "Ada Mandate" still exists, as I got out of defense work in 2003. But from the 1980s till then, there was always the option to use "the best language for the job" instead of Ada, provided you wrote a sufficiently convincing argument that it was actually the best language.

In the early days, it was rather difficult to do so, because the people you had to convince know absolutely nothing about programming.

By the time I got out of that field, those people had been replaced by youngsters who still knew little about programming, but had played with the most error-prone language I have ever seen (C) on their home computers. Consequently, it was too easy for other clueless youngsters to sell them on C or Java.

There was a group in the early days who believed that LISP was the best language for their job. So instead of seeking a waiver, they wrote a preprocessor that translated their LISP into Ada. Impressive accomplishment, but it was illegal. Writing LISP and auto-translating it into Ada is NOT developing in Ada. Of course, the clueless bureaucrats I mentioned earlier didn't understand that.

Not very different from writing SQL that looks like Pascal .....
Post #1479012
Posted Tuesday, July 30, 2013 8:55 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:55 AM
Points: 21, Visits: 96
I use a set of CLR based functions to detect injection. Helps that its done right at the database level, with no possible client or network hacking possibilities.

Handwritten, they can't be perfect, but I considered very many tricks to get code to run. It seems their are a awful lot of ways to encode plain text. Once assured its simple plain text with no encoded characters, then just make sure none of the SQL keywords or abbr. are in the text.

I use a similar function to "clean" a string that I use for free text (keyword search) fields. And another that uses REGEX to validate text as a valid email address (and not injection).

I suppose there isn't a need to have used CLR over SQLs string functions, but I have several other CLRs that cannot be done in SQL alone and there was simply no reason not to add a few extra functions using a preferred language.
Post #1479051
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse