July 19, 2011 at 2:44 am
Not that I'm calling YOUR code "one big nested splat of code". I would never do that, Phil. (Can I call you Phil?)
Sure you can Call me Phil and call my code "one big nested splat of code". My back is broad! The code may look pretty impenetrable, but it has already been ported to CLR/ C# and it works very well. I have to admit that I never tackled the date issue as it wasn't in the JSON spec I worked from. I'll add it if I can only understand how the big nested splat of code works.
I tried to do YAML too but gave up. Now, if you have any spare energy why not tackle that, because doing YAML to the full spec in TSQL is a huge world of pain. The only way ahead is CLR, hooked up to the standard implementation.
Best wishes,
Phil Factor
July 19, 2011 at 2:54 am
the future of .Net is uncertain, with rumours abounding Microsoft is considering its replacement by HTML5/JavaScript.
[p]If this happens, I'll eat my trousers. No. It is difficult for the tech people at Microsoft to talk about this issue because of the eagerness of the marketing people to 'manage the expectations' of the public.[/p]
[p]What seems to be happening is that Microsoft have realized that the huge pool of developers creating apps for the tablets in HTML5/Javascript just aren't going to adopt .NET/Silverlight and they need to be accommodated if there is to be any chance of Windows hosting all these good things. (of course they have Adobe Air, but they wouldn't want to rely on Adobe's good-will:- see how quickly AIR dropped support for Linux!). So, there is going to be one platform based on HTML5 for the 'ponytails' and another based on .NET and Silverlight for the serious Windows developers.[/p]
[p]Remember: you read it here first![/p]
Best wishes,
Phil Factor
July 19, 2011 at 4:56 am
I agree alternative forms for serialization are useful for integration problems. One issue I have with JSON is with the typing system. JSON only supports strings and numbers.
If requirements need additional types, like date and time then I think you should look to JSON successor YAML
July 19, 2011 at 10:11 am
Phil Factor wrote:
'... there is going to be one platform based on HTML5 for the 'ponytails' and another based on .NET and Silverlight for the serious Windows developers.'
Phil (can I call you Phil, too?)
There will be two platforms for some time, but mobile computing is where the growth is, and MS definitely knows that.
I would dare say the new HTML5/JavaScript framework will increasingly ease out the .Net platform, at least where web development is concerned. It just doesn't make sense for MS to compete in the mobile market with any framework other than a 'write once, run everywhere' one - hopefully one which is W3C standards-compliant.
It's the right strategy for Microsoft - now let's see how well they execute it.
July 23, 2011 at 1:21 pm
First let me say that this is a great article. It shows a wonderful use of SQLCLR for an area that T-SQL is just not well suited to. In fact, I had looked into adding a JSON library to my SQL# project just over a year ago but it required UNSAFE permissions so I moved on to other features. The only thing missing from the article is a description of how the metrics were gathered (unless somehow it's there and I missed it).
Now, onto this:
Craig-315134 (7/18/2011)
I'm inclined not to use this approach, for these reasons:1. As the author indicated, using the CLR within SQL Server opens up a number of potential performance and security problems. The author's code is quite admirable, but, I feel, ought to be executed outside the SQL Server environment.
False and False. Performance is just as easy to screw up in regular T-SQL, especially given how prone people are to using row-by-row approaches and how likely people are to not understand the concept of SARGability. Security in CLR Procs and Functions use the same security context as regular T-SQL Procs and Functions.
Whether or not the functionality should exist outside of the DB is an entirely different discussion. The fact is that a lot of people have a lot of various needs so to have this ability will definitely help some/many people. In fact, SQL Server is kinda late to the game in terms of allowing people to use other languages to extend functionality. DB2 has had the ability for over 10 years and PostgreSQL allows for maybe 5 other languages at least.
2. DBAs generally are not facile at writing .Net code. Such would typically be a task for .Net developers, not DBAs. The author mentioned reviewing a T-SQL-based approach to JSON serialisation/deserialisation, but rejected it because of its length/complexity. Still, I wonder if revisiting a T-SQL solution might be worthwhile, in part for the reason of making it more accessible to DBAs?
DBA's not being familiar with .NET code is a moot point. Most system admins aren't familiar with much of the 3rd party software, such as RDBMS's that are installed on their networks but they still support them. And we all use software that we would never be able to write. And DBA's are not necessarily good programmers anyway and can do quite a bit of damage with the built-in T-SQL that they feel so comfortable with.
3. There are many shops which use SQL Server as their standard RDBMS, but do not use .Net for application development.
Again, an entirely moot point. If the CLR Assembly can be installed and works as advertised, then that has no negative impact to a JAVA shop. It is only better and easier for a shop that is .NET savvy. Besides, it is common for shops to have a mix of platforms and languages.
Also, the future of .Net is uncertain, with rumours abounding Microsoft is considering its replacement by HTML5/JavaScript.
-- this is from another post by Craig, but on the same topic:
There will be two platforms for some time, but mobile computing is where the growth is, and MS definitely knows that.
I would dare say the new HTML5/JavaScript framework will increasingly ease out the .Net platform, at least where web development is concerned. It just doesn't make sense for MS to compete in the mobile market with any framework other than a 'write once, run everywhere' one - hopefully one which is W3C standards-compliant.
These statements betray a very deep misunderstanding of what .NET is. .NET is a framework that supports several languages and has many things built on top of it. Silverlight, which most closely resembles Flash, is one of those things that uses .NET. And yes, there has been speculation that Silverlight will go away due to so many of its advantages being delivered in HTML5, but it is still used for Windows Phone 7+ so it is doubtful that it will die entirely. HTML5 and JavaScript are also client-side functionality whereas the .NET platform is server-side. This is where Silverlight fit in: it was the client-side environment for .NET code. But rest assured, .NET is not going away anytime soon.
PS. 'write once, run everywhere' is mostly a marketing term. Different physical devices and even OS's all have their nuances.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 25, 2011 at 9:46 am
Well, Sol (can I call you Sol?),
I'll try to keep this brief. I'd first thought to rebut your post, point by point, and then realised there was something else about it that concerned me more - not what was said, but the way it was said.
I've been working with .Net for ten years, and about as long with SQL Server (my IT experience totals a little over three decades). I don't think my post betrays a 'deep lack of understanding' about either product. Certainly my co-workers and supervisors have never felt I lacked understanding or competence in the tools I use daily.
Our exchanges shouldn't be characterised in terms of 'right vs wrong'. What you and I have is difference of opinion, between two experienced professionals - nothing more, nothing less. I'm not going to question your understanding or competence, just because I might disagree with your views.
In the future, please give me (and others) the same professional courtesy. Thanks.
July 25, 2011 at 10:07 am
Hi Craig.
I prefer Solomon but am not offended or upset when people use Sol as it is a common shortening.
I sincerely apologize if my tone was unprofessional and/or harsh, especially with regards to the topics which are clearly opinion-based such as whether or not DBA's should use code that they are not familiar with.
But I am not convinced that the statements of "performance and security problems" as well as ".NET is being replaced by HTML5 & JavaScript" are of opinion rather than something a bit more verifiable. Again, I apologize for my previous tone, but my main concern for these two topics is that many people make the claims of "performance and security problems" as reasons to not use or enable CLR in SQL Server but I have yet to see any evidence of them. And the HTML5/JavaScript statement caught me as it is the second time I have heard it but yet .NET is not analogous to those client-side technologies so it doesn't make much sense, unless being used over-generally to mean Silverlight, which would make more sense.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 25, 2011 at 10:49 am
Greetings, Solomon,
Thanks for your kind reply.
Regarding the use of CLR integration within SQL Server: a highly competent database developer such as yourself (I've visited your website - impressive stuff!), who understands both tool sets extremely well, would hardly ever make errors which might adversely affect SQL Server performance and security.
However, I've seen less-skilled individuals compromise both. (I've observed this tends to be an issue more in smaller shops, where the talent pool may not run as deep, than in larger ones. 'Small shop DBAs' are not necessarily experienced database developers.)
These two problems seem to be the most contributory:
1. The DBAs I've observed are simply much more familiar with T-SQL than with .Net. That doesn't mean they shouldn't learn .Net, nor that they should refrain from using CLR integration; only that there is currently a much higher level of competence with T-SQL.
2. 'Best practices' are not always followed. I've seen .Net code employed when it would have been much more appropriate to use T-SQL constructs for the particular job at hand. Microsoft discusses part of this issue - please see particularly the section 'CLR and Its Alternatives'.
These problems can be remedied through more training and experience; but in the meantime, they make the deployment and maintenance of .Net code in SQL Server somewhat problematic.
Re .Net, Silverlight, HTML5 and JS: don't shoot the messenger! I'm just relaying some of the scuttlebutt that's out there right now. Hopefully we should all know more, once Microsoft completely explains its plans for its software development frameworks, supposedly come September. I agree with you that Silverlight particularly will still have a future. Our GIS group uses it extensively with ESRI-based applications, and so long as ESRI supports Silverlight, we'll continue using it.
Cheers.
October 24, 2011 at 10:59 am
I'm having some trouble creating my CLR stored procedures and functions. I'm using the solution files you provided and trying to mimic what you did in my solution, but it's not working. I downloaded the JSON.net solution files from http://json.codeplex.com/, then added their project to my SQL Server project solution. When I try to add a reference from my project to theirs, it throws an error saying SQL server projects can only reference other SQL server projects. It appears that when I added their project, it came in as a class library, but in your example it looks like it came in as another SQL server project. Any ideas what I need to do?
Thanks in advance.
Ben
May 18, 2013 at 10:33 am
Thanks for the nod Phil! Yes, I just resumed work on the CLR-based project originally inspired by your own T-SQL solution last weekend. I did initially migrate your T-SQL solution to the CLR closely maintaining your logic. Eventually, I completely re-wrote the solution from scratch and it is now available on GIT here:[/url] https://github.com/jgcoding/JSQL/[/url]
Before posting it to GIT last weekend I had to remove some customized CLR functions I had written to serve my last employer. I did notice a casualty to a drive crash I suffered in the interim between the last revision I made to the library and the last back-up I made of the project. The last revision I made to the project included a CLR aggregate replacement for your ToJSON T-SQL solution. The CLR aggregate was incredibly simple and concise. The last version I was able to recover either is missing the last piece that set it free or I have forgotten how I implemented it to achieve full serialization of previously parsed data.
I plan to resume reconstructing examples how I got the current ToJson CLR aggregate function to work apparently lost. Or, figuring out if the version recovered had been modified to work correctly. In the meantime, I believe your ToJson T-SQL solution while reserialize what my CLR rxJsonParser generates.
As the function name may reveal, I use some regular expressions within a recursive function to parse any kind of json structure and any of the valid JavaScript types. I will provide more documentation to the posting as I have opportunity.
Regards John Galt
May 18, 2013 at 10:48 am
If I am not mistaken, uploading JSON.net into the SQL-CLR requires the permission level be set to 'UNSAFE'. This is not an acceptable option. JSON.Net is a brilliant project and I use it to this day. Quite a bit of work would need to go into striping out features and dependencies to compile to a 'SAFE' permission level. Your solution and mine safely compile under a 'SAFE' permission level.
June 11, 2013 at 8:17 pm
I attempted to deploy the JSON.NET assembly into the CLR a few years ago. Unfortunately, at the time anyway, I had to set the assembly permission to UNSAFE before before SQL Server accepted it. This was in no way acceptable and so I abandoned the effort. Did you find this was required as well?
Having failed to deploy JSON.NET with SAGE permissions, I resumed developing my own library I named J-SQL. After reading Phil Factors T-SQL implementation I was able to finally get past parsing more than a generation deep in a nested hierarchy. So, for those who care, anyone is free to use my solution available at https://github.com/jgcoding/J-SQL.git.
Be sure the JSON string is compressed as I am still working out addressing tab, line-feeds, carriage returns, and spaces not residing within a properly quoted string value as one may see when the JSON string is formatted for display.
I should have this worked out soon.
Enjoy
John Galt Coding
June 11, 2013 at 8:18 pm
My library is deployed with SAFE permissions. https://github.com/jgcoding/J-SQL.git
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply