Friday, July 06, 2018

Flexible Custom Reports for SQL Server

Flexible Custom Reports for SQL Server

This article is written by developer for other developers, sharing my experience with a technique that I found in Kimberly Tripp's SQL Server blog article titled " Building High Performance Stored Procedures". In her article, she demonstrated a dynamic SQL technique that allows for "Query By Example" screens that allow most fields to be optional. I would strongly encourage you to read her article along with the associated cautionary notes. I would further add that this technique should not be used for any automated reporting tasks. If you have a known report requirement, make a dedicated stored procedure for it!

I am writing this article to share a few refinements as well as a nice way to catch refactoring dependencies when this stored procedure is part of a database project in Visual Studio.  As a point of reference, I used MS SQL 2014 and Visual Studio 2017, but the technique should work with older versions of SQL.

1) Include a reference copy of the QUERY in the stored procedure.  This will create a junk query plan, but it will allow you to find table and field references in Visual Studio.  If this causes performance problems, I would love to hear about it.  If we only had conditional compilation, we could include the reference query in a DEBUG build, but remove it from production builds.  I am aware that I could probably do this with some marker comments and a tricky Perl regular expression, but I try to avoid complexity where I can.

 ** Stored Procedure Comments and History go here **
create procedure Foo_Report_AllInOne
   @Key1Id int = null,
   @Key2Id int = null,
   @Name1 varchar(50) = null,
   @Start datetime = null,
   @End datetime = null
set nocount on;

-- check parameters
if ( @Key1Id is null
    and @Key2Id is null
    and @Name1 is null
    and @StartDate is null
    and @EndDate is null )
    raiserror ('You must supply at least one parameter.', 16, -1);

declare @ExecStr nvarchar (4000),
        @Recompile  bit = 0;
if (1=0)
   -- include a non-executing version of the query with ALL conditional fields referenced, so we
   -- can find this code when refactoring.
     Key1Id, Field1, Key2Id, Field2, Name1, EventDate
     from myschema.FOO
     where 1=1
     and Key1Id = @Key1Id
     and Key2Id = @Key2Id
     and Name1 like @Name1
     and EventDate >= @Start
     and EventDate < @End

-- continue with code from the Kimberly Tripp article.

Everything inside the "if (1=0)" code block MUST be a full copy of the dynamic query with all possible options included, otherwise it has no value for field reference and refactoring purposes. Since this query will never execute, it doesn't have to be a logically sound query.  This also allows you to quickly copy and paste new query code (down to the "where 1=1") into your dynamic query after Visual Studio has checked the syntax.

2) Include a print statement at the bottom of the procedure with all of the parameters and the dynamic query statement to be executed.  Ideally you would print a declare statement, set statements and the dynamic code so it could be manually executed for testing and debugging.  In fact, the best way to debug dynamic code is just to comment out the sp_executesql call (uncomment the print statements if needed) and view the generated code.

3) If your query is longer than 4000 characters, use care to avoid truncating and remember to always append to the larger type - @ExecStr in our case.

Labels: , ,

Sunday, April 15, 2018

Android OS Redesigned

Imagine the following scenario - you are looking for an application (app) on the Google Store, but the application that you found does 3 or 4 other things that you are not really interested in.   Perhaps it is a photo editor that also syncs with Dropbox, has an online gallery, etc.  All you want is local photo editing.  Today, there is nothing you can do unless the app uses Android 6.0 run-time permissions, but if you had more fine-grained permission control, you could deny or just limit access to those extraneous permissions like web access.

I previously wrote about a solution to many of the problems associated with the Android operating system.  For the rest of this article, I will pretend that the Android community has adopted these design ideas.

A trustworthy OS would give the user full control over each app's ability to run in the background, upload and download data over various connections (mobile, public network, private "home" network), etc.  Before installation, the user would be prompted (as usual) but for each permission there would be a drop-down list of choices - allow full access, prompt every time, deny access, limited access, custom access, or simulated access.

Let us examine the permission "access your contacts".  If the app only needs to confirm your identity, give it "limited access - just me", or a custom list of your work and home contact entries.  If this app had a social media aspect, you could still use it with confidence knowing that they would not be stealing your entire contact list and sending it to their servers.

If this was well implemented, it would not eliminate rogue apps, but would allow the user to de-fang the app by both preventing access, and preventing communication, and even if they tunneled some information out, it could be simulated data.

What does this mean to users? No more ring-tone apps that steal every file, call history, and contacts.  
The next obvious step would be for some trusted authority to provide a list of recommended permissions to grant an app.

This would require Ad-ware apps to redesign themselves to use an official "Advertisement" channel so they could still remain profitable.  Google would be responsible for shutting down abuses like using the advertisement channel to steal data.

Wednesday, May 20, 2015

How Android OS should REALLY deal with privacy

I'll give you two design options that would improve OS privacy.  I am thinking of the Android Operating System, but these are design principles, so they would be just as applicable for a Windows 10 Universal Application (App) or an iOS App.  I am aware that this would be a huge undertaking, but it would be awesome!

1.  Handle-based access

This is not a new concept, but when you combine a system with pick-list controls and standard display controls, it is possible to allow an application to choose one or more contacts, display them and send a message without knowing the names or phone numbers.  The system would only expose a single handle to represent each contact.  Only isolated components would have the special privileges allowing access to the private details.  This would change the way applications create custom-drawn components, but would prevent even a HACKED system from leaking information as long as the process isolation subsystem was not compromised.  They could install "isolated" custom components that would be available only to a list of apps signed with a particular certificate.  This custom component could then access private information, but could not share memory back to the parent app, talk to the internet, write to the file system, etc. without gaining this permission just like an App.

This design would have a HUGE positive impact for persons with sight, hearing or other limitations, as they could select different components to serve their purpose (for example, the audio system could flash and buzz instead of playing sound, the display could talk over Bluetooth to an interactive Braille supplemental tablet or wristband).

2.  Information control

Allow the user to select the level of access for each application, with the option to select a default access level:
  • Dummy API, for example a list of dummy contacts with none, just you, or a small list of dummy phone numbers.
  • Limited API, only a tagged list of contacts is available.
  • Full API, same as today.

Another API example would be the network subsystem - we could have:
  • No connection
  • limited network
  • access over a specific WiFi only (only on the trusted HOME network)
  • access over Wifi only
  • access over 4G only
  • access over any network with certain white/blacklist active
  • unlimited access over any network

A combination of these two designs would make for an amazingly secure system, but it might start resembling the HURD kernel.  What does this design NOT prevent?  It would not prevent in-app advertising (unless you gave an App the no-connection, or the all-sites-blocked network API).  Applications that needed to serve advertisings might start requesting a working internet connection before they will allow you to play your game, so it wouldn't particularly break the ecosystem as it is today, but would give users amazing control over their system.

Along with a tightly controlled App Store, this might even allow Android to be used by some government agencies, or by users with privacy concerns.


I think it would actually be pretty easy to implement a different limited contact list, and perhaps a dummy phone.  Tablets have to present a dummy phone after all.

Labels: ,

Friday, April 17, 2009

Behind the times with NHibernate 2

I am so behind the times ... NHibernate 2.0.1 GA was released last fall and 2.1.0 Alphas are coming out. In the mean time, has been subsumed by Redhat's JBoss. The url doesn't take you anywhere useful, and following the javascript menu to projects / services / hibernate still doesn't get you anywhere - pure garbage. It is clear that JBoss wants to sell you their enterprise build of Hibernate instead (that "Products" link works).

I have been so busy with my day job that I haven't been keeping up. I work at a hospital, supporting Cerner Millenium - writing CCL, Perl, Python, Microsoft SQL (occasionally), and a bit of C#.

Cerner CCL is a proprietary language that feels like a mash-up of Fortran IV and and an arcane dialect of SQL from 1976. The language used to run on VMS servers, but we have migrated to HP-UX, and I am OVERJOYED to have a choice of using a posix shell, ksh or csh. Also, I recently discovered that CCL allows me to map C library functions into the language. So far we have used this to allow file open, close, read, write and seek.

Labels: , ,

Wednesday, March 18, 2009

ADDPATH NT/DOS command line script

(Yes, We say script now instead of batch ... times are changing).

Here is a stupid little batch file that I have written several times. It adds a single argument to the path in your current command-line session (so it is not permanent).

@echo off
if "%1" == "" goto end_script
set path=%path%;%1

Labels: ,

Monday, March 02, 2009

Iron Languages on DLR

The "Iron" languages that run on the .NET DLR (Dynamic Language Runtime) are making progress. I just noticed that IronRuby has a release on RubyForge.

To be honest though, I have moved to Python as my script language of choice, with Perl as the ever-present fallback. Iron Python seems like it is coming along nicely. Iron Python 2 can even host itself. I can't wait for MonoDevelop or SharpDevelop to support IronPython on Windows.

Labels: ,

Thursday, February 19, 2009

The Castle Project will facilitate native SQL.

I just spotted this on FishEye ... an example native SQL query. You'll need source from SVN or a recent download from the build server - more recent than patch 5551 by mzywitza on 17 February 2009.

This is very nice, allowing parameterized SQL without the hacks that I used (you can search here for some of my old blogs on the subject).