Note: Kipper is now basically redundant not that the FOR JSON keyword has been added to SQL Server 2016. But that's OK, because I did it for fun, and it was fun even if I can now manually export whatever dataset I want to as JSON.
Kipper is a JSON serializer for Microsoft SQL Server. It is similar to Python's pickle module, which takes Python objects and turns them into a text format that can then be saved and loaded later (serialization). That's where it got its name from. Let's not talk about the similarities between Kipper and Pickle. Instead, let's talk about the differences, because they are a lot better at explaining what Kipper does.
Instead of generating a custom text format, Kipper generates JSON, an interchange format that is naturally easy to understand because it is almost identical to how it would be printed from Python. Naturally.
Where Pickle serializes an actual object, in an object-oriented language, Kipper serializes a database object. Except that database objects (aside from CLR objects, which are not what I am talking about here) do not actually exist. They are more of a concept.
The second of those bullet points kind of opened a can of worms, so I need to pick up this here shovel and dig myself a little deeper. In a database, you have one or more tables, such as "orders" and "customers," and each one of those can exist on its own. A row in the order table is an order, and a row in the customer table is a customer. If this were an object-oriented system, the customer might be an attribute of the order, or a customer might contain a list of orders, but in a database, you can't even get that far, because objects do not exist. But you do have references between tables, and if you do a little work, you can map your tables out as if they were objects. Then you can serialize them if you want. If your order contains a reference to a customer, then you can follow that reference one way, making the customer appear as an attribute of the order, or you can follow it the other way, making orders children of the customer.
This is what Kipper does. You give Kipper one or more database rows and say, "serialize this," and Kipper follows all the relationships, builds an object, and when it has followed every relationship (if you have an infinite recursive loop, it will blow up eventually), it generates the JSON string and returns it to you. Look at the link to "Kipper JSON Sample.txt" at the very bottom of this page to see an example of a decent amount of output, or read this page for smaller samples.
I wrote Kipper because it was fun. I haven't used it in production. I will never use it in production. It is completely unlike anything I would ever do during the cource of my job. In the cases where we would need to deliver some JSON output from the database to a client, this would be done with a web service, and the service would only pick up the specific data that was needed. I don't know what actual applications there would be for a JSON object relational serializer, but I wrote it because it was interesting. I tested it out enough to see that it worked, and to see what I needed to do to capture a complete picture of "something" in the database, its references and its children, but that is all. So be warned that I'm just killing time, yo.
Kipper comes in two main versions, a Python script that can be imported and used as a module and a C# CLR User Defined Function that can be used in SQL Server. Both have the same functionality, though the interface is slightly different in parts. The fact that nothing can be dynamic in SQL Server and no complex data types can exist there really do limit the SQLCLR version a bit, but I think I found some useful methods to make it work, which shouldn't come across as too clunky and hard to use.
In addition, I have put together Python 2 (2.7) and Python 3 (3.3) versions of the Python Kipper, and Dot.Net Framework 3.5 and 4.0 versions of the C# Kipper. There are very few actual differences. I haven't made use of any advanced features. In the Dot.Net 4.0 version, about the only thing I did was label the method calls with keywords, so they can be somewhat understandable, and include the 4.0 version of JsonFx.
Outside of the standard library, the python version depends on pyodbc (the best Python database library, no question). If you have simplejson installed, it attempts to use it, because simplejson is able to serialize datetime and decimal/numeric datatypes. The json library in the standard library fails to handle both of them, so I had to add some code of my own to produce reasonable output if simplejson is not installed. The date datatype cannot be handled by either version, so I return the normal Python representation of a date, YYYY-MM-DD.
Installing pyodbc and simplejson is trivial. If you are on a Linux machine, I know from experience that installing a SQL Server ODBC driver can be a bit of a PITA, but it varies from distribution to distribution so I can't give any advice that is better than what you'd find by Googling for FreeTDS and UnixODBC on your distribution of choice.
It is not necessary to install Kipper. Just make sure the directory containing Kipper.py is in your sys.path and import it.
To build the JSON text, I use JsonFx. This wasn't my first choice, because the documentation is very minimal. I wanted to use Json.NET, which is more documented, but it depends on System.Runtime.Serialization.dll, and Microsoft SQL Server rejects that very forcibly. Basically tells you "How dare you try to use this function (which we wrote) on our beautiful server." So anyway, JsonFx works fine. You just might have to search the source for how to do something. I included it with my database solutions to make it easy for you to install, which was legal because JsonFx is available under an MIT-style License.
If you want to use a different version of JsonFx.dll, download the right version from https://github.com/jsonfx/jsonfx/downloads and use it. You cannot simply replace the DLL, because Visual Studio will choke. Remove the reference to the old DLL, replace the file, and then add a reference to the replaced DLL.
The database will need to be in UNSAFE mode to work (you will also probably need to set it as TRUSTWORTHY). Reflection is heavily restricted in the Microsoft SQL Server world. If CLR integration is not enabled, you must enable it.
I provided SSDT database solutions. I find that the are the easiest way to build and deploy CLR assemblies in SQL Server. Open the solution, build it, and then open Kipper Deploy.scmp and do a schema comparison with your database of choice. It will ask you to drop any functions and assemblies you might have on your database: DO NOT DROP ANYTHING THAT YOU WANT TO KEEP. That's common sense, but it's better to be safe. SSDT provides no setting to turn off the dropping of nonexistent objects. It's annoying, but otherwise the deployment is easy.
Right here: Kipper program files
Kipper runs queries that you provide it. If you ask it to delete your Users table, it will do just what you ask it (unless you don't have permission). Do not allow uncleansed input from users. The parameter arguments are used in normal, clean, parameterised queries as they are supposed to be, but table names, schema names, and entire SELECT queries in QueryToJson are included either using string replacement or by simply running what you give it. To ensure that this query wasn't doing anything dangerous would require an AI to determine what "dangerous" meant.
Python
>>> from kipper import Kipper
>>> k = Kipper('Driver={SQL Server Native Client 11.0};Server=localhost; Database=AdventureWorks2012; Trusted_Connection=yes;')
When creating an instance of the kipper object, you must supply it with an ODBC connection string that will allow a connection to the database.
C#
There is nothing special you need to do.
Python
>>> k.Kipper_ObjectToJson('SpecialOffer', 'Sales', {'SpecialOfferID': 10})
In this sample, SpecialOffer is the table name and Sales is the schema name (the AdventureWorks database makes heavy use of non-dbo schemas). The third parameter is a dictionary of filter criteria used to narrow down the object or objects to be kippered. This sample is looking for the records in Sales.SpecialOffer WHERE SpecialOfferID = 10. If more items are given in the parameters dict, then the query will AND them together when filtering.
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"Category": "Customer",
"StartDate": "2007-06-15 00:00:00",
"EndDate": "2007-08-30 00:00:00",
"Description": "Mountain Tire Sale",
"SpecialOfferID": 10,
"DiscountPct": 0.5000,
"MaxQty": null,
"ModifiedDate": "2007-05-16 00:00:00",
"MinQty": 0,
"SpecialOfferProduct": [
{
"Product": {
"Style": null,
"ProductNumber": "TI-M267",
"WeightUnitMeasureCode": null,
"StandardCost": 9.3463,
"SizeUnitMeasureCode": null,
"ListPrice": 24.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 85,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "L ",
"SellEndDate": null,
"Name": "LL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "76060A93-949C-48EA-9B31-A593D6C14983",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "E3CDC5DD-27C3-4891-9D5E-0D46D1B8457F",
"ProductModelID": 85,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "LL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 928,
"UnitMeasure": null,
"Size": null
},
"rowguid": "DC836DA3-B45C-4CC8-9C17-45B84E9333DE",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 928
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M602",
"WeightUnitMeasureCode": null,
"StandardCost": 11.2163,
"SizeUnitMeasureCode": null,
"ListPrice": 29.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 86,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "M ",
"SellEndDate": null,
"Name": "ML Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DAFF9E11-6254-432D-8C4F-F06E52687184",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "0434F63A-A361-4D0B-A9FC-8AC2A866CE85",
"ProductModelID": 86,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "ML Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 929,
"UnitMeasure": null,
"Size": null
},
"rowguid": "400EE299-745C-4003-B59A-4142FC8CE0B1",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 929
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M823",
"WeightUnitMeasureCode": null,
"StandardCost": 13.0900,
"SizeUnitMeasureCode": null,
"ListPrice": 35.0000,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 87,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "H ",
"SellEndDate": null,
"Name": "HL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DDAD25F5-0445-4B5C-8466-6446930AD8B8",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "CE1B1064-6679-4212-8F56-2B2617EC56A5",
"ProductModelID": 87,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "HL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 930,
"UnitMeasure": null,
"Size": null
},
"rowguid": "33713B2B-4275-4004-82E0-BAA66E63B095",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 930
}
],
"rowguid": "220444AD-2EF3-4E4C-87E9-3AA6EE39A877",
"Type": "Excess Inventory"
}
]
C#
>>> SELECT dbo.Kipper_ObjectToJson('Sales.SpecialOffer', 'SpecialOfferID, 10')
Kipper_ObjectToJson is a scalar UDF that returns a string. In this sample, Sales.SpecialOffer is the table/schema (the AdventureWorks database makes heavy use of non-dbo schemas). Do not wrap your table/schema or columns with brackets ([]), because that is already being done inside the function.
The second parameter is a comma-separated list of filter criteria used to narrow down the object or objects to be kippered, in the format "Column1, Value1, Column2, Value2, ..., ColumnN, ValueN." A string is the simplest way to provide this information from T-SQL, which does not have a dictionary object, or even a list or tuple.
The parameter list obeys the parsing rules of my SQL List object (see SQL Server Lists Preview 1), except with no [] on the ends. This means that if you need a comma in a single item, you can enclose the value in double-quotes, single-quotes (not recommended in T-SQL), triple-double-quotes, or triple-single-quotes (really not recommended in T-SQL). All variables are strings, because there's no easy way to specify data types in a 2xN CSV list.
This sample is looking for the records in Sales.SpecialOffer WHERE SpecialOfferID = 10. "SpecialOfferID", "10" would also have been acceptable. If more items are given in the parameters, then the query will AND them together when filtering, as in this example:
SpecialOfferID, 10, TotallyMadeUpColumnName, "Einstein, Albert"
If you don't want any parameters, set the second input to NULL. You can't leave parameters out when calling functions.
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"SpecialOfferID" : 10,
"Description" : "Mountain Tire Sale",
"DiscountPct" : 0.5000,
"Type" : "Excess Inventory",
"Category" : "Customer",
"StartDate" : "2007-06-15T00:00:00.000",
"EndDate" : "2007-08-30T00:00:00.000",
"MinQty" : 0,
"MaxQty" : null,
"rowguid" : "220444ad-2ef3-4e4c-87e9-3aa6ee39a877",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"SpecialOfferProduct" : [
{
"SpecialOfferID" : 10,
"ProductID" : 928,
"rowguid" : "dc836da3-b45c-4cc8-9c17-45b84e9333de",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 928,
"Name" : "LL Mountain Tire",
"ProductNumber" : "TI-M267",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 9.3463,
"ListPrice" : 24.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "L ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 85,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "76060a93-949c-48ea-9b31-a593d6c14983",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 85,
"Name" : "LL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "e3cdc5dd-27c3-4891-9d5e-0d46d1b8457f",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 929,
"rowguid" : "400ee299-745c-4003-b59a-4142fc8ce0b1",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 929,
"Name" : "ML Mountain Tire",
"ProductNumber" : "TI-M602",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 11.2163,
"ListPrice" : 29.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "M ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 86,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "daff9e11-6254-432d-8c4f-f06e52687184",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 86,
"Name" : "ML Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "0434f63a-a361-4d0b-a9fc-8ac2a866ce85",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 930,
"rowguid" : "33713b2b-4275-4004-82e0-baa66e63b095",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 930,
"Name" : "HL Mountain Tire",
"ProductNumber" : "TI-M823",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 13.0900,
"ListPrice" : 35.0000,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "H ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 87,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "ddad25f5-0445-4b5c-8466-6446930ad8b8",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 87,
"Name" : "HL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "ce1b1064-6679-4212-8f56-2b2617ec56a5",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
}
]
}
]
The basic object syntax is fine if your query can be built by SELECT * FROM TABLE WHERE COL1 = @value1 AND COL2 = @value2. But that is not always possible. What if you need to build your query by joining some tables together with an INNER JOIN? What if you want to query some tables and include multiple related objects in the same data set (for example, join 3 tables and kipper 2 of them in the same object)? What if you just want to convert the query results to JSON without any object seeking at all? The first thing I think of and it risks breaking the whole project.
To provide the answer, execute a QueryToJson command.
Doing this requires some special formatting. How do you inject an extra command, to seek out related objects, into the middle of a query? In the proper place so that the request picks up the relationships? In a dynamic fashion? Well, if T-SQL functions were allowed to be dynamic, you could simply include a scalar user-defined function (like the C# version) in your return dataset, except for one really critical flaw: The data would be returned as a string, not an object, which means the JSON conversion would be subtly different. It would be enclosed in an extra set of quotation marks, and any quotation marks inside the string would be escaped. It would also take a lot of extra work to build out a query that returned data, and ideally the function in question would allow a variable number of inputs, which is illegal.
But we have a way out. The query is a string that is being handled by the Kipper program. We could parse it out and inject our new results ... this would be very hard, so iya da. Instead, we will let SQL Server itself do the first part of the parsing and even organize it for us, and then we will iterate through the nicely pre-processed query results and insert the requested additional data.
The way this is done is by including one or more columns in the rowset whose names begin with |<, two characters that will probably never justifiably start any real SQL Server column name but together almost look like K, and then contain -> to divide it into pieces that can be treated as arguments for a dynamic function. The data you use for the column name will be parsed out to identify the table to be kippered and the column or columns to be used for filter criteria. The VALUE of the column is used as the value of the query.
Here are some example special columns:
[|<NewObject->ColumnName] = Alias.Column1
[|<NewObject->TableName->ColumName] = Alias.Column1
[|<NewObject->SchemaName->TableName->ColumnName] = Alias.Column1
If you need to filter by multiple criteria, then just include them all. They should all point to the same table, or you will end up getting nothing or a syntax error. Only the table/schema on the first special column name is used.
[|<NewObject->SchemaName->TableName->ColumnName1] = Alias.Column1
[|<NewObject->SchemaName->TableName->ColumnName2] = Alias.Column2
As you should be able to see now, SQL Server is ensuring that you don't have a horribly broken query (it will raise an error if you do). SQL Server is matching up the column values with the column names. All the heavy lifting, which would be very difficult to do from text, has been done by the server for us.
Python
>>> k.Kipper_QueryToJson("""
... SELECT *
... ,[|<Special->Sales->SpecialOffer->SpecialOfferID] = SpecialOfferID
... FROM Sales.SpecialOfferProduct p
... INNER JOIN Production.Product prod ON prod.ProductID = p.ProductID
... WHERE prod.ProductNumber = @Product
... AND p.SpecialOfferID <> 1
... """, {'@Product': 'TI-M602'}
... )
In this sample, you can see the special kipper column being provided, under the name |<Special->Sales->SpecialOffer->SpecialOfferID. It needs to be surrounded by [] because |, <, >, and - are not valid characters that can appear in identifiers such as column names. The kippered object, which will come from the Sales.SpecialOffer table where SpecialOfferID = Sales.SpecialOfferProduct.SpecialOfferID, will be in the dictionary key named "Special."
The second parameter, which is optional, is a dictionary including variables and their values. This allows parameterised queries, using a @variable format similar to the way that ADO.Net allows, instead the ODBC way, which allows only the question mark as a positional placeholder. Doing this took some extra work on my part and it has a drawback. I am using a regular expression to extract variables, looking for an @ sign followed by one or more letters, numbers, or underscores. My parsing rules don't get any more advanced than that, so if you need to include an @ sign in your query, but NOT as a variable (such as in an email address inside a string), you must escape the @ by doubling it up (@@). If you want to use one of the builtin double-at variables (@@IDENTITY), you need to double them up too (@@@@IDENTITY). This is annoying, but it's the only way I can come up with to skip them aside from actually identifying if a given character is within a string or not or if it is a builtin system variable. I don't want to make this a six month project.
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"Style": null,
"ProductNumber": "TI-M602",
"WeightUnitMeasureCode": null,
"SellEndDate": null,
"SizeUnitMeasureCode": null,
"ListPrice": 29.9900,
"ProductLine": "M ",
"ProductModelID": 86,
"SpecialOfferID": 10,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "M ",
"StandardCost": 11.2163,
"Name": "ML Mountain Tire",
"DaysToManufacture": 0,
"MakeFlag": false,
"DiscontinuedDate": null,
"rowguid": "DAFF9E11-6254-432D-8C4F-F06E52687184",
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 929,
"Special": [
{
"Category": "Customer",
"StartDate": "2007-06-15 00:00:00",
"EndDate": "2007-08-30 00:00:00",
"Description": "Mountain Tire Sale",
"SpecialOfferID": 10,
"DiscountPct": 0.5000,
"MaxQty": null,
"ModifiedDate": "2007-05-16 00:00:00",
"MinQty": 0,
"SpecialOfferProduct": [
{
"Product": {
"Style": null,
"ProductNumber": "TI-M267",
"WeightUnitMeasureCode": null,
"StandardCost": 9.3463,
"SizeUnitMeasureCode": null,
"ListPrice": 24.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 85,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "L ",
"SellEndDate": null,
"Name": "LL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "76060A93-949C-48EA-9B31-A593D6C14983",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "E3CDC5DD-27C3-4891-9D5E-0D46D1B8457F",
"ProductModelID": 85,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "LL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 928,
"UnitMeasure": null,
"Size": null
},
"rowguid": "DC836DA3-B45C-4CC8-9C17-45B84E9333DE",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 928
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M602",
"WeightUnitMeasureCode": null,
"StandardCost": 11.2163,
"SizeUnitMeasureCode": null,
"ListPrice": 29.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 86,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "M ",
"SellEndDate": null,
"Name": "ML Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DAFF9E11-6254-432D-8C4F-F06E52687184",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "0434F63A-A361-4D0B-A9FC-8AC2A866CE85",
"ProductModelID": 86,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "ML Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 929,
"UnitMeasure": null,
"Size": null
},
"rowguid": "400EE299-745C-4003-B59A-4142FC8CE0B1",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 929
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M823",
"WeightUnitMeasureCode": null,
"StandardCost": 13.0900,
"SizeUnitMeasureCode": null,
"ListPrice": 35.0000,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 87,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "H ",
"SellEndDate": null,
"Name": "HL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DDAD25F5-0445-4B5C-8466-6446930AD8B8",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "CE1B1064-6679-4212-8F56-2B2617EC56A5",
"ProductModelID": 87,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "HL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 930,
"UnitMeasure": null,
"Size": null
},
"rowguid": "33713B2B-4275-4004-82E0-BAA66E63B095",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 930
}
],
"rowguid": "220444AD-2EF3-4E4C-87E9-3AA6EE39A877",
"Type": "Excess Inventory"
}
],
"Size": null
}
]
C#
>>> SELECT dbo.Kipper_QueryToJson('SELECT *
... ,[|<Special->Sales->SpecialOffer->SpecialOfferID] = SpecialOfferID
... FROM Sales.SpecialOfferProduct p
... INNER JOIN Production.Product prod ON prod.ProductID = p.ProductID
... WHERE prod.ProductNumber = @Product
... AND p.SpecialOfferID <> 1'
... , '@Product, TI-M602')
Kipper_QueryToJson is a scalar user-defined function that returns a string.
In this sample, you can see the special kipper column being provided, under the name |<Special->Sales->SpecialOffer->SpecialOfferID. It needs to be surrounded by [] because |, <, >, and - are not valid characters that can appear in identifiers such as column names. The kippered object, which will come from the Sales.SpecialOffer table where SpecialOfferID = Sales.SpecialOfferProduct.SpecialOfferID, will be in the dictionary key named "Special."
The second parameter is a comma-separated list of variables and their values which will be replaced in the query, "@Variable1, Value1, @Variable2, Value2, ..., @VariableN, ValueN." A string is the simplest way to provide this information from T-SQL, which does not have a dictionary object, or even a list or tuple.
The variable list obeys the parsing rules of my SQL List object (see SQL Server Lists Preview 1), except with no [] on the ends. This means that if you need a comma in a single item, you can enclose the value in double-quotes, single-quotes (not recommended in T-SQL), triple-double-quotes, or triple-single-quotes (really not recommended in T-SQL). All variables are strings, because there's no easy way to specify data types in a 2xN CSV list.
This sample is looking for the records in Sales.SpecialOffer WHERE SpecialOfferID = 10. "SpecialOfferID", "10" would also have been acceptable. If more items are given in the parameters dict, then the query will AND them together when filtering, as in this example:
SpecialOfferID, 10, TotallyMadeUpColumnName, "Einstein, Albert"
If you don't want any parameters, set the second input to NULL. You can't leave function parameters out in SQL Server.
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"SpecialOfferID" : 10,
"ProductID" : 929,
"rowguid" : "400ee299-745c-4003-b59a-4142fc8ce0b1",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"ProductID1" : 929,
"Name" : "ML Mountain Tire",
"ProductNumber" : "TI-M602",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 11.2163,
"ListPrice" : 29.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "M ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 86,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid1" : "daff9e11-6254-432d-8c4f-f06e52687184",
"ModifiedDate1" : "2008-03-11T10:01:36.827",
"Special" : [
{
"SpecialOfferID" : 10,
"Description" : "Mountain Tire Sale",
"DiscountPct" : 0.5000,
"Type" : "Excess Inventory",
"Category" : "Customer",
"StartDate" : "2007-06-15T00:00:00.000",
"EndDate" : "2007-08-30T00:00:00.000",
"MinQty" : 0,
"MaxQty" : null,
"rowguid" : "220444ad-2ef3-4e4c-87e9-3aa6ee39a877",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"SpecialOfferProduct" : [
{
"SpecialOfferID" : 10,
"ProductID" : 928,
"rowguid" : "dc836da3-b45c-4cc8-9c17-45b84e9333de",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 928,
"Name" : "LL Mountain Tire",
"ProductNumber" : "TI-M267",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 9.3463,
"ListPrice" : 24.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "L ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 85,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "76060a93-949c-48ea-9b31-a593d6c14983",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 85,
"Name" : "LL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "e3cdc5dd-27c3-4891-9d5e-0d46d1b8457f",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 929,
"rowguid" : "400ee299-745c-4003-b59a-4142fc8ce0b1",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 929,
"Name" : "ML Mountain Tire",
"ProductNumber" : "TI-M602",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 11.2163,
"ListPrice" : 29.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "M ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 86,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "daff9e11-6254-432d-8c4f-f06e52687184",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 86,
"Name" : "ML Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "0434f63a-a361-4d0b-a9fc-8ac2a866ce85",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 930,
"rowguid" : "33713b2b-4275-4004-82e0-baa66e63b095",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 930,
"Name" : "HL Mountain Tire",
"ProductNumber" : "TI-M823",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 13.0900,
"ListPrice" : 35.0000,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "H ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 87,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "ddad25f5-0445-4b5c-8466-6446930ad8b8",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 87,
"Name" : "HL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "ce1b1064-6679-4212-8f56-2b2617ec56a5",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
}
]
}
]
}
]
In the samples we just looked at, the kippered data set was in the dictionary key named "Special." But what if we didn't want it to be in its own object. What if we wanted to use all the complex joining and filtering capability we can use in a straight SQL query, but we don't want any of the columns in that query to appear in the result. We only want to use that query to identify the objects to be kippered. Well, in the previous examples, we'd get an object that looked like [ {"Special": data}, {"Special", data}, ... ]. We want [data, data, ... ]. How do we do that?
We do that by including the |<Override column in the query. A column that states |<Override=AnObjectName will execute and kipper the query, and then take the object named AnObjectName and replace the main array of the fields in the main query with an array of fhe rows in AnObjectName. The value of the column named |<Override doesn't matter. You can make it a column from the query, 1, NULL, or "Clipper."
Python
>>> k.Kipper_QueryToJson("""
... SELECT *
... ,[|<Special->Sales->SpecialOffer->SpecialOfferID] = SpecialOfferID
... ,[|<Override=Special] = NULL
... FROM Sales.SpecialOfferProduct p
... INNER JOIN Production.Product prod ON prod.ProductID = p.ProductID
... WHERE prod.ProductNumber = @Product
... AND p.SpecialOfferID <> 1
... """, {'@Product': 'TI-M602'}
... )
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"Category": "Customer",
"StartDate": "2007-06-15 00:00:00",
"EndDate": "2007-08-30 00:00:00",
"Description": "Mountain Tire Sale",
"SpecialOfferID": 10,
"DiscountPct": 0.5000,
"MaxQty": null,
"ModifiedDate": "2007-05-16 00:00:00",
"MinQty": 0,
"SpecialOfferProduct": [
{
"Product": {
"Style": null,
"ProductNumber": "TI-M267",
"WeightUnitMeasureCode": null,
"StandardCost": 9.3463,
"SizeUnitMeasureCode": null,
"ListPrice": 24.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 85,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "L ",
"SellEndDate": null,
"Name": "LL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "76060A93-949C-48EA-9B31-A593D6C14983",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "E3CDC5DD-27C3-4891-9D5E-0D46D1B8457F",
"ProductModelID": 85,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "LL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 928,
"UnitMeasure": null,
"Size": null
},
"rowguid": "DC836DA3-B45C-4CC8-9C17-45B84E9333DE",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 928
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M602",
"WeightUnitMeasureCode": null,
"StandardCost": 11.2163,
"SizeUnitMeasureCode": null,
"ListPrice": 29.9900,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 86,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "M ",
"SellEndDate": null,
"Name": "ML Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DAFF9E11-6254-432D-8C4F-F06E52687184",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "0434F63A-A361-4D0B-A9FC-8AC2A866CE85",
"ProductModelID": 86,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "ML Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 929,
"UnitMeasure": null,
"Size": null
},
"rowguid": "400EE299-745C-4003-B59A-4142FC8CE0B1",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 929
},
{
"Product": {
"Style": null,
"ProductNumber": "TI-M823",
"WeightUnitMeasureCode": null,
"StandardCost": 13.0900,
"SizeUnitMeasureCode": null,
"ListPrice": 35.0000,
"ProductSubcategory": {
"rowguid": "3C17C9AE-E906-48B4-BDD3-60E28D47DCDF",
"Name": "Tires and Tubes",
"ProductCategoryID": 4,
"ProductCategory": {
"ProductCategoryID": 4,
"rowguid": "2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6",
"Name": "Accessories",
"ModifiedDate": "2002-06-01 00:00:00"
},
"ModifiedDate": "2002-06-01 00:00:00",
"ProductSubcategoryID": 37
},
"ProductLine": "M ",
"ProductModelID": 87,
"UnitMeasure_WeightUnitMeasureCode": null,
"SellStartDate": "2007-07-01 00:00:00",
"FinishedGoodsFlag": true,
"ProductSubcategoryID": 37,
"Class": "H ",
"SellEndDate": null,
"Name": "HL Mountain Tire",
"DaysToManufacture": 0,
"rowguid": "DDAD25F5-0445-4B5C-8466-6446930AD8B8",
"DiscontinuedDate": null,
"MakeFlag": false,
"Weight": null,
"Color": null,
"ReorderPoint": 375,
"ProductModel": {
"rowguid": "CE1B1064-6679-4212-8F56-2B2617EC56A5",
"ProductModelID": 87,
"CatalogDescription": null,
"ModifiedDate": "2007-06-01 00:00:00",
"Name": "HL Mountain Tire",
"Instructions": null
},
"SafetyStockLevel": 500,
"ModifiedDate": "2008-03-11 10:01:36.827000",
"ProductID": 930,
"UnitMeasure": null,
"Size": null
},
"rowguid": "33713B2B-4275-4004-82E0-BAA66E63B095",
"SpecialOfferID": 10,
"ModifiedDate": "2007-05-16 00:00:00",
"SalesOrderDetail": [],
"ProductID": 930
}
],
"rowguid": "220444AD-2EF3-4E4C-87E9-3AA6EE39A877",
"Type": "Excess Inventory"
}
]
C#
>>> SELECT dbo.Kipper_QueryToJson('SELECT *
... ,[|<Special->Sales->SpecialOffer->SpecialOfferID] = SpecialOfferID
... ,[|<Override=Special] = NULL
... FROM Sales.SpecialOfferProduct p
... INNER JOIN Production.Product prod ON prod.ProductID = p.ProductID
... WHERE prod.ProductNumber = @Product
... AND p.SpecialOfferID <> 1'
... , '@Product, TI-M602')
(Google stripped out every HTML tag that would have made this not spit a giant block of text into the middle of the page. Free hosting isn't actually free.)
[
{
"SpecialOfferID" : 10,
"Description" : "Mountain Tire Sale",
"DiscountPct" : 0.5000,
"Type" : "Excess Inventory",
"Category" : "Customer",
"StartDate" : "2007-06-15T00:00:00.000",
"EndDate" : "2007-08-30T00:00:00.000",
"MinQty" : 0,
"MaxQty" : null,
"rowguid" : "220444ad-2ef3-4e4c-87e9-3aa6ee39a877",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"SpecialOfferProduct" : [
{
"SpecialOfferID" : 10,
"ProductID" : 928,
"rowguid" : "dc836da3-b45c-4cc8-9c17-45b84e9333de",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 928,
"Name" : "LL Mountain Tire",
"ProductNumber" : "TI-M267",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 9.3463,
"ListPrice" : 24.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "L ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 85,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "76060a93-949c-48ea-9b31-a593d6c14983",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 85,
"Name" : "LL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "e3cdc5dd-27c3-4891-9d5e-0d46d1b8457f",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 929,
"rowguid" : "400ee299-745c-4003-b59a-4142fc8ce0b1",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 929,
"Name" : "ML Mountain Tire",
"ProductNumber" : "TI-M602",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 11.2163,
"ListPrice" : 29.9900,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "M ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 86,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "daff9e11-6254-432d-8c4f-f06e52687184",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 86,
"Name" : "ML Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "0434f63a-a361-4d0b-a9fc-8ac2a866ce85",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
},
{
"SpecialOfferID" : 10,
"ProductID" : 930,
"rowguid" : "33713b2b-4275-4004-82e0-baa66e63b095",
"ModifiedDate" : "2007-05-16T00:00:00.000",
"Product" : {
"ProductID" : 930,
"Name" : "HL Mountain Tire",
"ProductNumber" : "TI-M823",
"MakeFlag" : false,
"FinishedGoodsFlag" : true,
"Color" : null,
"SafetyStockLevel" : 500,
"ReorderPoint" : 375,
"StandardCost" : 13.0900,
"ListPrice" : 35.0000,
"Size" : null,
"SizeUnitMeasureCode" : null,
"WeightUnitMeasureCode" : null,
"Weight" : null,
"DaysToManufacture" : 0,
"ProductLine" : "M ",
"Class" : "H ",
"Style" : null,
"ProductSubcategoryID" : 37,
"ProductModelID" : 87,
"SellStartDate" : "2007-07-01T00:00:00.000",
"SellEndDate" : null,
"DiscontinuedDate" : null,
"rowguid" : "ddad25f5-0445-4b5c-8466-6446930ad8b8",
"ModifiedDate" : "2008-03-11T10:01:36.827",
"ProductModel" : {
"ProductModelID" : 87,
"Name" : "HL Mountain Tire",
"CatalogDescription" : null,
"Instructions" : null,
"rowguid" : "ce1b1064-6679-4212-8f56-2b2617ec56a5",
"ModifiedDate" : "2007-06-01T00:00:00.000"
},
"ProductSubcategory" : {
"ProductSubcategoryID" : 37,
"ProductCategoryID" : 4,
"Name" : "Tires and Tubes",
"rowguid" : "3c17c9ae-e906-48b4-bdd3-60e28d47dcdf",
"ModifiedDate" : "2002-06-01T00:00:00.000",
"ProductCategory" : {
"ProductCategoryID" : 4,
"Name" : "Accessories",
"rowguid" : "2be3be36-d9a2-4eee-b593-ed895d97c2a6",
"ModifiedDate" : "2002-06-01T00:00:00.000"
}
},
"UnitMeasure" : null,
"UnitMeasure_UnitMeasureWeightUnitMeasureCode" : null
},
"SalesOrderDetail" : []
}
]
}
]
Kipper, by necessity, fetches each row from the data one by one. It would be nice to do this in a setwise manner. but imagine how difficult it would be to do a setwise recursive query following all reference links that might exist. So far in SQL Server, all recursive queries have been in a tightly controlled context, the recursive CTE, and not "Go fetch anything you need to from the database." This can take a lot of time. And if you are going back to the database over and over for the same value (perhaps a reference status that is being used by every record in your dataset), that is very expensive in terms of time. Rather than making a trip to the database for every piece of data, it would be much faster to simply look up the data you already got from the database, which are currently sitting in memory.
This is done using a process called memoization and it dramatically speeds up some queries. Every time data is fetched, it is stored in a data structure so that it can be used later if necessary. And any time later in the process, if that data is requested again, it comes out of memory, not the database, saving a great deal of time. When testing, I hit some cases where I just couldn't wait around for the data. When a query takes 10-15 minutes, you start to wonder if you have an infinite loop somewhere. Turning on memoization changed these queries to ones that were nice and snappy.
As expected, memory use skyrockets. I saw out of memory exceptions on very large objects. But that's the tradeoff.
Python
To use memoization in Python, when creating the kipper object, set the second parameter, "memoize," to True.
>>> kipper = Kipper('Driver={SQL Server Native Client 11.0}; Server=localhost;Database=AdventureWorks2012; Trusted_Connection=yes;', True)
C#
To use memoization in C#, include a column with the name [|<Memoize] somewhere in the query. You could say [|<Memoize] = 1, or [|<Memoize] = 'True', whatever you would like. Why is in the query, not an input variable? Because functions in SQL Server aren't allowed to have optional inputs like stored procedures do. I didn't want to pollute the function list with two more UDFs that were identical, except memoized.
Kipper is Copyright 2014 Jeremy Lakatos, released under the GNU Public License version 3. It contains modules released under the MIT License or MIT-style licenses (Pyodbc, JsonFx, Simplejson) and the Python Software Foundation License. JsonFx, which is included, is Copyright 2006-2010 Stephen M. McKamey.
If you find any issues or mistakes, please email me and I'll try to help. Although TBH, if I find that anyone has used this, I will be very surprised.