SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server


JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server

Author
Message
GoofyGuy
GoofyGuy
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 971
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.
Solomon Rutzky
Solomon Rutzky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2177 Visits: 2965
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# - http://www.SQLsharp.com/
GoofyGuy
GoofyGuy
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 971
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.
benm 37301
benm 37301
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 114
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
johngaltx87
johngaltx87
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
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: https://github.com/jgcoding/JSQL/

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
johngaltx87
johngaltx87
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
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.
johngaltx87
johngaltx87
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
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
johngaltx87
johngaltx87
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
My library is deployed with SAFE permissions. https://github.com/jgcoding/J-SQL.git
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search