Sunday, May 28, 2006

C# 2.0/3.0 Language and LINQ Chat on May 30

The C# team will be chatting about C# 2.0 and 3.0, as well as LINQ (and probably DLinq and XLinq), on Tuesday, May 30, 2006 at 12:45 - 1:45 p.m. PDT (GMT - 7:00). Here's their pitch:

Want to know more about anonymous delegates in C# 2.0, or our design rationale for them? Have some questions about our current thinking on C# 3.0 and LINQ? Perhaps you'd like to share your ideas with other C# users or the C# language team. Join the C# team for all that and more - it's all up to you!

Click here to go directly to the Chat Room.

Technorati Tags: , , ,

Sunday, May 21, 2006

DLinq, Visual Basic 9.0, and ASP.NET 2.0 Issues

Most LINQ sample applications that I've seen to date are C# 3.0 console projects that write results to the command window. In my opinion, neither C# code nor console applications are optimum choices for demonstrating LINQ features and capabilities. The advantages of Visual Basic 9.0 as a LINQ programming language are well known—static typing where possible and dynamic typing where necessary. But today's perception of LINQ and its DLinq/XLinq components is primarily as advanced extensions—anonymous types, query comprehensions, lambda expressions, expression trees, and extension methods—to the C# language. According to the LINQ May 2006 CTP's "Getting Stated with Visual Basic 9.0" white paper, the goal of LINQ is "increasing productivity in data-intensive programming." VB is regarded as a highly productive language because of its ease of use and enhanced readability compared to C, C++, C# and Java. Thus all the articles, columns, and blog posts I write use VB rather than C#. Note: Paul Vick expands on the preceding topic in his "BASIC Principals" post, which links to John Montgomery's "Express 'Orcas' Principles" post. The latter article debates whether 'Orcas Express' should include the LINQ extensions. Console projects minimize the effort required to demonstrate basic LINQ, DLinq, and XLinq features and their coding techniques. But console projects lack interactivity and aren't suited to displaying substantial amounts of data, such as result sets from DLinq queries against large tables. So I use Windows forms with databound controls for most DLinq examples or text boxes for manipulating XML documents with XLinq. Here's a typical example of a Windows form with DataGridViews bound to a DLinq Data Source from a recent "LINQ Takes Shape in May CTP" article that I wrote for FTP Online (click image for larger version).

Web Forms, GridViews, and ObjectDataSources

Scott Guthrie's "Using LINQ with ASP.NET (Part 1)" post demonstrates that Web forms with GridView controls bound to IEnumerable(Of T) instances greatly enhance the learning experience compared to console projects. I would write more Web site solutions or Web application projects (WAP) if ASP.NET 2.0 offered a better infrastructure for LINQ demo projects. Unfortunately, ASP.NET 2.0 doesn't let you take advantange of new DLinq features, such as the DLinq Data Source designer, nor do GridView controls support bidirectional databinding or paging directly, which would require a LINQDataSource to replace the ObjectDataSource control.

Note: Scott Guthrie says "DLinq designer support for web projects will be coming in the future" in his comment to this post. You can, however, create a LINQ Class Library with a DLinq designer, and add the class library as a reference to a file-system Web site (as Scott suggested).

The following code in the Page_Load event handler populates a GridView control in a WAP with rows from a join of the Northwind Suppliers and Products entities:

Dim objProds = _
From s In dcNwind.Suppliers, _
  p In dcNwind.Products _
Where s.Country = "USA" AndAlso s.SupplierID = _
  CType(p.SupplierId, Integer) _
Select s.SupplierID, s.CompanyName, p.ProductID, _
  p.ProductName, p.QuantityPerUnit, p.UnitPrice _
Order By It.CompanyName, It.ProductName
gvProducts.DataSource = objProds

The preceding code in the Default.aspx page of a WAP generates the following simple report:

Note: The "LINQ + WAP == Coolness" post on Harry Pierson's DevHawk Weblog shows you how to edit a C# WebApplication.csproj file to specify the C# 3.0 compiler for a WAP. ("Change the Target Import element to import '$(ProgramFiles)\LINQ Preview\Misc\Linq.targets' instead of '$(MSBuildBinPath)\Microsoft.CSharp.targets').

Hector Cruz provides step-by-step instructions to set up WAP and specify the Visual Basic 9.0 compiler in a March 13, 2006 message from the LINQ Project General newsgroup's "Using VB LINQ in Web Applications" thread. Hector adds instructions for creating a user template for LINQ Web Applications in the next (March 14) message.

An issue with using the LINQ ASP.NET Web Site template for the file-system (Web site) model is setting the compiler version for dynamic Web page compilation. In this case, the Web site model uses the C# 3.0 compiler for pages in the project folder but apparently not for classes added to the ...\App_Code folder. Workarounds are pasting the DataContext class code into the Default.aspx page or adding a reference to a LINQ Class Library created from code generated by SQLMetal.exe or the DLinq Data Source Designer.

Any of these three workarounds causes this exception when invoking the gvProducts.DataBind() method of the preceding LINQ query code: "Member access 'System.Nullable`1[System.Int32] SupplierID' of 'DLinqDesignerLibrary.Product' not legal on type 'System.Data.DLinq.Table`1[DLinqDesignerLibrary.Product]." This exception doesn't occur in the WAP version of the project, as the preceding figure proves.

An alternative execution path executes the following query to verify that the DataContext object isn't the source of the problem:

Dim objCusts = From c In dcNwind.Customers _
  Where c.Country = "USA" _
  Select c.CustomerID, c.CompanyName, _
  c.ContactName, c.City, c.Region, _
  c.PostalCode, c.Phone
gvCusts.DataSource = objCusts

Here's the GridView populated by the simple alternative query:

The difference in behavior of identical DataContexts in WAPs and file-system Web Sites is a major issue; the source of the problem remains a mystery.

Problems with VB External (XmlMappingSource) XML Mapping Files

The May 2006 CTP modifies SQLMetal.exe to enable creating a class file without mapping attribute decorations and an XML document that supplies the mapping attributes. Here's the command-line syntax to generate an XML mapping file (NwindMap.xml) and a corresponding DataContext class (XmlMap.Northwind):

"\program files\linq preview\bin\sqlmetal"
/server:.\SQLEXPRESS /database:Northwind
/map:NwindMap.xml /namespace:XmlMap
/language:vb /code:Northwind.vb /pluralize
The C# version of the SampleQueries.sln project has an \101+ DLinq Queries\External Mapping node with a Load and Use an External Mapping sample, which executes as expected. The VB version doesn't have an External Mapping example. Here's the code that should instantiate the XmlMap.Northwind DataContext:
Private dcNwind As XmlMap.Northwind
Private Sub XMLMapping_Load(ByVal sender _
  As Object, ByVal e As System.EventArgs) _
  Handles Me.Load
  Dim strMapSource As String = _
    Application.StartupPath + "\NwindMap.xml"
  Dim xmsNwind As XmlMappingSource = _
    XmlMappingSource.FromXml(File. _
  dcNwind = New XmlMap.Northwind(My.Settings. _
    NorthwindConnection, xmsNwind)
End Sub

Executing the last instruction throws "An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.DLinq.dll. Additional information: The type 'DLinqXMLMapping.XmlMap.Customer' is not an entity."

Could this be the reason that VB version of Sample Queries doesn't have an External Mapping example?

P.S.: Mike Champion, Microsoft's XLinq program manager, is seeking "feedback from potential LINQ customers about what they like, what they don't like, and what more they need in a product before the design is frozen."

Technorati Tags: , , , , , , ,

Tuesday, May 16, 2006

Workarounds for May 2006 CTP DLinq Problems

While I was writing the sample code that illustrates how to bind DataGridView controls to DLinq Data Sources for my FTPOnline "LINQ Takes Shape in May CTP" article, I encountered these three significant issues: • A True value for the IsID property (id:=true DLinq.Column attribute) is required for each DLinkProperty element of a DLinq Class that's mapped from a primary key field of a relational table having a composite primary key. An instruction in the DLinq Designer.doc file for adding the DLinq Class for the Order Details table (step 7 on page 9) states "Set the IsID property to True (for the OrderID column only)." If you don't set the ProductID field's IsID property value to True also, as shown here, the bound OrderDetailsDataGridView displays erroneous (repeated) values in all rows, as shown here.

Note: Microsoft's Antoine Cote posted the following message on May 18, 2006 in the "Associations in DLinq Designer" thread: "[F]or the CTP, the designer only support[s] single column keys. We're working on supporting composite keys for the next release."

• Deleting rows from bound DataGridView controls manually doesn't persist the deletions to the source tables when you apply the DataContext.SubmitChanges() method. • Code to work around the failure to persist deleted rows throws untrapped NullReferenceExceptions when it encounters the the auto-generated DLinq Designer code's instruction that's emphasized below in the Public Property Customer() As Customer procedure:

If (Not (Me._Customer.Entity) Is Nothing) Then
   'Fix for deletions
   'Me._Customer.Entity = Nothing (moved below)
   Me._Customer.Entity = Nothing
End If
and in the Public Property Order_() As Order_ procedure:
If (Not (Me._Order_.Entity) Is Nothing) Then
   'Fix for deletions
   'Me._Order_.Entity = Nothing (moved below)
   Me._Order_.Entity = Nothing
End If
The preceding procedures are auto-generated by the DLinq Designer tool, so it's likely that you'll need to make the changes multiple times. Note: Terry Adams (tadam)—the development lead on the DLinq team—confirms some of these issues in a comment to this post. The article and its sample code, which you download from the "Get the code" link in the Article Tools box on page 1, includes these fixes in the DLinqDataBinding.sln project. This project also demonstrates how to take advantage of the new Optimistic Concurrency Confict Resolution (OCCR) features of the May 2006 CTP of the DLinq bits. Technorati Tags: , , , , ,

Erik Meijer Promotes LINQ at Expo-C 2006

The peripetetic Erik Meijer is on the LINQ lecture circuit again—this time it's at Expo-C 2006 Software Architecture Conference in Karlskrona, Sweden. Here are links to Erik's two presentations: • May 17, 2006: Seminar: VB IsNot C# • May 19, 2006: Tutorial: Haskell for Mere Mortals + C# 3 Innovations Here's Erik's description of the VB IsNot C# seminar:

About a year ago I had a true "Road To Damascus" experience when I discovered that Visual Basic is unique amongst all programming languages in that it has static typing where possible, but allows dynamic typing where possible [necessary]. This makes Visual Basic better than dynamically typed-only scripting languages on the one hand, and also better than statically typed systems programming languages on the other hand. In fact, I am betting my career and reputation on the presumption that Visual Basic is the programming language that will democratize dynamic, data-intensive distributed programming in the Cloud, just like Visual Basic 1 democratized programming against Windows. In this talk I will explain why Visual Basic is not C# with a different syntax, what Visual Basic's late binding is all about, what we are doing right now to make Visual Basic 9 the best programming language for normal people that leverages the LINQ framework to query any form of data with features such as deep XML support and powerful query comprehensions, and how we envision to grow Visual Basic in the future as the language for the Cloud.

<WARNING> Do not attend this talk if you don't want to be converted into a Visual Basic believer yourself </WARNING>

There's more about the two presentations in Erik's Lambda the Ultimate post, "Functional Programming Has Reached The Masses; It's Called Visual Basic," which also contains a link to his recent "Confessions of a Used Programming Language Salesman: Getting the Masses Hooked on Haskell" Microsoft Research paper. Erik says the paper "starts with my attempt to use Haskell as the language to write three-tier distributed data intensive applications, then continues with my brief flirtation with the Internet scripting language Mondrian, the Cω language, the LINQ framework and C# 3.0 and Visual Basic 9, and ultimately comes to a happy end with my devotion to Visual Basic." Erik submitted the paper to the 11th ACM SIGPLAN International Conference on Functional Programming (ICFP 2006) scheduled for September 18-20, 2006 and close to home in Portland, Oregon. Karlskrona is a beautiful maritime city, especially in the summer. Having spent a substantial amount of time in southern Sweden, primarily Helsingborg and Trelleborg, I envy the Expo-C 2006 participants. A list of OakLeaf blog posts about Erik Meijer, which include links to his other recent LINQ presentatrions, is here. May 17, 2006 Update: Erik is the program chair for Lang .NET 2006 (The .NET Programming Languages And Compilers Symposium) to be held at the Microsoft Redmond campus on August 1 t0 3, immediately following OSCON (the O'Reilly Open Source Convention) on July 24 to 28 in Portland, OR. Of course LINQ is one of the "areas of interest." Technorati Tags: , , , , , , ,

Monday, May 15, 2006

ADO.NET 3.0 Entity Framework Ephemera

Erwyn van der Meer posted "ADO.NET 3.0 - Next-Generation Data Access: Making the Conceptual Level Real" on Thursday, May 11, 2006. I was in the midst of finishing up the text and code samples for a FTPOnline .NET Development article, "LINQ Takes Shape in May CTP," so I deferred reading the resources from these links in Erwyn's post:

My mistake. All the preceding links were 404 by the weekend, exccept for "The first entry on the new Microsoft Data blog," which is empty except for default items and the word "Data." The only vestiges of the vanished content are links on MSDN Data pages from the Google cache, such as these:

However, a French reader named Sami had the presence of mind to preserve a copy of some of the missing content in the comments on Microsoft Developer Division Corporate VP Soma Somasegar's blog post about the LINQ May 2006 CTP.

Here's Pablo Castro's opening salvo for the ADO.NET 3.0 (a.k.a. ADO.NET vNext) team's "vision on data programmability."

Once you are done with shipping large products such as SQL Server 2005 and Visual Studio 2005 you'd expect to have a quiet time for a while, slow down a bit, that kind of stuff ... Turns out that it wasn't the case this time. Right after SQL Server 2005 and Visual Studio 2005 were ready to ship (and actually even before they were completely done) we started to work hard on the next version of the data programming technologies; we worked on a broad vision that spans various releases and various technologies, and also specifically on how ADO.NET plays in that vision. In the context of the vision for data programmability and ADO.NET, we decided that the next big step for ADO.NET was to move to a higher-level of abstraction. Connections, commands and readers are great for low-level stuff, but it's not exactly what you want to be dealing with continuously when writing business logic. There are other aspects that are similarly "low level" and applications have to deal with, like the actual database schemas (e.g. did you even wonder why you have to do a 3-way join just to navigate a relationship between entities instead of just saying "traverse the relationship"?). Today we're making public our vision on data programmability, it's a great read, I highly recommend it. We're also making public the specific plans for the next version of ADO.NET, a bit more technical, less formal, but with all the details of how we're moving the technology forward.

Also check out Channel 9, we've recorded a few videos that discuss the next version of ADO.NET from various angles. The first one of the series has been posted there already; it features Mike, Tim and myself talking about what we've been working on for the next version of ADO.NET, including some high-level descriptions, details on the various services that are being introduced and some live demos. Sam, the product unit manager for the data programmability team in Microsoft, has posted a blog entry that introduces the vision and the first round of concrete innovations around it. Feedback on all of this stuff is welcome. I highly encourage folks to check out all of the content we're putting out there and write us with your thoughts. Pablo Castro ADO.NET Technical Lead Microsoft Corporation

Sami also provided a copy of the text that introduced the O-R mapping tool demo screencast:
So, Now that we are talking about the upcoming ADO.NET Entity Framework, I thought it would be interesting to do a screencast on our mapping infrastructure. If you have seen our vision on data programmability you will see that one of our investments is in using view maintenance technology to replace the common case-by-case mapping scenarios that ORM solutions typically employ. Feel free to take a look at the screencast to see examples of us modeling and mapping simple inheritance, complex types and entity splitting. In future screencasts I will go into more detail about our metadata infrastructure, modeling and more on mapping. We look forward to hearing your feedback! Tim M Program Manager, Data Programmability

It's unfortunate that the whitepapers, video, blog posts and screencast received so much publicity while extant. More than 2,150 folks had watched the "What's coming in ADO.NET?" video before it disappeared from Channel 9. The LINQ, DLinq, and XLinq keywords are missing in both quoted posts. Jimmy Nilsson says, "I'm not sure if we will see both DLinq and EDM because DLinq isn't mentioned at all in the [ADO.NET Tech Preview: Overview]." There's no mention of an "ADO.NET 3.0 Entity Framework" in the LINQ May 2006 CTP documentation.

The obvious question is: Are the ADO.NET Entity Framework, LINQ, DLinq, and XLinq teams reading from the same playbook? Let's hope so. At the moment, however, it appears that only time will tell. May 17, 2006 Update (thanks to Fabrice Marguerie): The current outlook for DLinq in the "ADO 3.0 Entity Framework" isn't too bright. Here's most of Jim Wooley's post in the LINQ Project General newsgroup's "DLinq vs. ADO.NET vNext" thread:

From what I recall, the ADO appeared to abandon all of the strong typing mechanisms that are present in LINQ and take a step back into creating dynamic SQL that mapped to dynamic object entities. We would then need to map the intermediary entities into DLinq entities if we wanted to take advantage of the functionality in LINQ.

I understand the challenges in working with n-level object trees and mapping them to relational data stores, taking into account polymorphism. From my initial read of the ADO.Next documentation, I didn't get the feeling that they were headed in the right direction. Hopefully the next document will be more encouraging.

Here's another question: How much time will be required to rationalize these two—apparently very dissimilar—approaches? Technorati Tags: , , , , , , , ,

Scott Guthrie on Orcas RTM Dates and LINQ with ASP.NET

Scott Guthrie runs the Microsoft development teams that build IIS, ASP.NET, and Visual Web Developer 2005, and writes the popular ScottGu MSDN blog that covers these topics in depth. Scott posted Using LINQ with ASP.NET (Part 1), a tutorial for evaluating the use of LINQ with ASP.NET 2.0 on May 14, 2006. The sample project uses the LINQ May 2006 CTP version of the C# 3.0 compiler. Scott's LINQ with ASP.NET post provoked a large number of comments and replies, but the most interesting reply included this gem:

We are looking to ship the second half of next year. We will start having full Orcas CTP drops (of all technologies) starting later this summer, and will also have a go-live license of Orcas before the final RTM date. So not too far off now when you can use the above techniques in production.
This is the first Orcas RTM estimate and go-live committment directly from a Microsoft employee that I've encountered. Technorati Tags: , , , , , ,

Saturday, May 13, 2006

Jon Udell Interviews Anders Hejlsberg about the LINQ May 2006 CTP

InfoWorld analyst Jon Udell's Friday Podcast for May 13, 2006 is a 22-minute interview with Anders Hejlsberg about the LINQ May 2006 CTP release. Anders is a Microsoft Distinguished Engineer, Technical Fellow, and lead architect of the C# programming language. The interview's first 8 minutes is the background of the LINQ project and its capabilities. New features in the May 2006 CTP are discussed at these approximate starting times: 07:55 The three implementations of IQueryable—DLinq, LINQ Over DataSets, and Objects—deliver polymorphic representation of a query. 10:45 The IQueryable implementation for objects which ordinarily implement IEnumerable builds an application that's independent of the data source. 12:20 Customers have written IQueryable wappers around WMI and Active Directory. IQueryable is a formalization of a process that accommodates puggable query processors. 13:00 C# 3.0 Join and GroupJoin sytax with decision trees deliver much greater capability. Copy and paste XML works in the May 2006 CTP. 15:40 VB 9.0 query syntax sequence changes from Select ... From to From ... Select, which enables IntelliSense statement completion. Operations now come in the order of execution, unlike SQL. 17:15 VB 9.0 gets the Group By clause for DLinq and XML Literal outlining for XLinq. "VB programs are starting to look mighty much like Xquery programs." 17:40 DLinq provides a graphical designer and supports inheritance, which lets you map relational tables to an object hierarchy with discriminator columns. 18:50 Multi-tier objects support a Web scenario to minimize refetching data. 19:50 O-R mapping now lets you attach an entity to an existing DataContext. 20:50 There's no "deep connection" between XLinq and the SQL Server native XML data type. Technorati Tags: , , , , , , ,

DLinq Chat Scheduled for May 18 at 12:45 pm PDT

The DLinq team has scheduled an MSDN Online Chat about the May 2006 CTP version of DLinq on Thursday, May 18, 2006 from 12:45 to 1:45 pm PDT. The DLinq Designer.doc file uses a form with three bound DataGridView controls to demonstrate binding to a hierarchical entity set, similar to the form described in my earlier "Anomalies and Issues with VB 9 DLinq Code" post. Surprisingly, most DLinq-related problems with the January 2006 CTP that I discussed in the post remain in the May 2006 CTP version. I also found some suprising Visual Basic documentation and code issues in this release. Here's a link to my May 16, 2006 FTPOnline article, "LINQ Takes Shape in May CTP," which provides sample code with workarounds for the problems I encountered. See ya at the chat. (Links updated on May 18, 2006.) Technorati Tags: , , , , ,

Wednesday, May 10, 2006

LINQ Preview (May 2006 CTP) Available for Download

The May 2006 Community Technical Preview (CTP) of the Language-Integrated Query (LINQ) features for the next version of Visual Studio (codenamed "Orcas") is available for download from MSDN. This update optionally brings C# 3.0 into DLinq and XLinq parity with VB 9.0.

The LINQ Preview (May 2006).msi installer uninstalls the LINQ Preview (Jan 2006) and LINQ Technical Preview (PDC 2005) bits before installing the May 2006 CTP. New VB9 DLinq and XLinq Features According to the Readme.htm page, following are the new LINQ May 2006 CTP features for the forthcoming Visual Basic 9.0 language in Orcas: • Enhanced DLinq Support: This CTP adds support for Inheritance, Stored Procedures, User-Defined Functions, and Optimistic Concurrency Conflict Resolution (OCCR). The new DLinq Designer provides a visual design surface for creating DLinq entity classes from database tables. • LINQ over DataSet: The full power of LINQ can now be applied to the DataSet, allowing you to use the Standard Query Operators and some DataSet-specific extensions to query against DataRows. Group By Query Comprehensions: The compiler now supports Group By as a valid clause in LINQ Queries. • Outlining support for XML Literals in the Editor: In this release, we added outlining support for XML literals. You can now expand or collapse any XML element literal that spans across more than one line. Value extension property for XML axis properties: We added a Value extension property to the collections that are returned from the XML axis properties (i.e. IEnumerable(Of XElement) and IEnumerable(Of XAttribute)). This extension property does two things, it first picks up the first object in that IEnumerable, and if this object exists, it calls the Value property on this object (either XElement or XAttribute). Changes to the LINQ January 2006 CTP Select/From syntax: The previous tech preview supported using Select before From. In an effort to provide better IntelliSense™, we’re switching to the From/Select format for this CTP. See yesterday's blog entry, "DLinq: VB 9 Throws in the Towel on Select/From". • Joins don’t require It: You don’t need to use the iterator variable It anymore when performing a join operation, though it’s still required for grouping operations. • XML axis properties syntax: The late bound XML feature has a new name and a new distinct syntax where we wrap the element name with angle brackets. This new syntax makes the XML axis properties visually distinct and solves problems that the previous CTP syntax had. See the "VB 9.0 Xml features - latest update" entry of the Microsoft XML Team's Weblog for more information. New C# 3.0 Features

The C# LINQ update isn't installed by default. [To be updated after verification of new C# features.]

Previous OakLeaf Posts on LINQ, DLinq and XLinq

Here's a list of my 34 posts to date on LINQ, DLinq and XLinq. (One member of the list is a duplicate with a title typo). Technorati Tags: , , , , , , ,

Monday, May 08, 2006

DLinq: VB 9 Throws in the Towel on Select/From

Paul Vick's May 5, 2006 post, "Select/From vs. From/Select revisited..." post reveals that the Visual Studio Orcas release's Visual Basic 9 syntax for DLinq queries will adopt "Yoda style" From ... Select sequence similar to that of C# 3.0:

Dim WACusts = _
   From c In Customers _
   Where c.State = "WA" _
   Select c
rather than the conventional SQL Select ... From order:
Dim WACusts = _
   Select c _
   From c In Customers _
   Where c.State = "WA"
The obvious reason for the change is the need to support IntelliSense for statement completion. Reader response in comments to the post is favorable so far.

Use of the SELECT ... FROM ... WHERE sequence in SQL has always seemed strange to me, because the query processor must identify the source table(s) before validating the query's field list. However, I'm not sanguine about the proposed From ... Where ... Select order. It seems to me that From ... Select ... Where is more in tune with VB's traditional reputation for readability.

Paul mentioned a forthcoming VB LINQ CTP but didn't provide an estimated drop date.

P.S.: The Irish National Developer's Conference included a session on May 4, 2006 entitled "LINQ the future of handling database queries and XML in .NET" by Mark Lawrence.

Technorati Tags: , , , , , ,

Tuesday, May 02, 2006

FTPOnline Posts SQL Server 2005 Special Report

"Special Report: SQL Server" is live today on Fawcette Technical Publications' FTPOnline site with this description:

Microsoft SQL Server has come a long way since the release of Ashton-Tate/Microsoft SQL Server 1.0 in 1989, now providing a developer-oriented, enterprise-grade relational database management system. This Special Report delves into SQL Server 2005's new offerings, including business intelligence features, management tools, advanced data and text mining, more granular database permissions, Visual Studio integration, and more.
My three contributions to the Special Report are: "Microsoft SQL Server Turns 17" covers the history of SQL Server since 1989 and provides current product and market information on Microsoft's flagship relational database management system. "Install SP-1 for SQL Server 2005 and Express" provides detailed installation and related information about the release version of SP-1. "SQL Server 2005 Mobile Edition Goes Everywhere" describes what you can expect from SQL Server Everywhere when it releases by the end of 2006 and proves that SSE will be an effective alternative to XML files for persisting typed DataSets. Enjoy... Technorati:

"Free" Databases: Express vs. Open-Source RDBMSs

Open-source relational database management systems (RDBMSs) are gaining IT mindshare at a rapid pace. As an example, BusinessWeek's February 6, 2006 "Taking On the Database Giants" article asks "Can open-source upstarts compete with Oracle, IBM, and Microsoft?" and then provides the answer: "It's an uphill battle, but customers are starting to look at the alternatives." There's no shortage of open-source alternatives to look at. The BusinessWeek article concentrates on MySQL, which BW says "is trying to be the Ikea of the database world: cheap, needs some assembly, but has a sleek, modern design and does the job." The article also discusses Postgre[SQL] and Ingres, as well as EnterpriseDB, an Oracle clone created from PostgreSQL code*. Sun includes PostgreSQL with Solaris 10 and, as of April 6, 2006, with Solaris Express.** *Frank Batten, Jr., the investor who originally funded Red Hat, invested a reported $16 million into Great Bridge with the hope of making a business out of providing paid support to PostgreSQL users. Great Bridge stayed in business only 18 months, having missed an opportunity to sell the business to Red Hat and finding that selling $50,000-per-year support packages for an open-source database wasn't easy. As Batten concluded, "We could not get customers to pay us big dollars for support contracts." Perhaps EnterpriseDB will be more successful with a choice of $5,000, $3,000, or $1,000 annual support subscriptions. **Interestingly, Oracle announced in November 2005 that Solaris 10 is "its preferred development and deployment platform for most x64 architectures, including x64 (x86, 64-bit) AMD Opteron and Intel Xeon processor-based systems and Sun's UltraSPARC(R)-based systems." There's a surfeit of reviews of current MySQL, PostgreSQL and—to a lesser extent—Ingres implementations. These three open-source RDBMSs come with their own or third-party management tools. These systems compete against free versions of commercial (proprietary) databases: SQL Server 2005 Express Edition (and its MSDE 2000 and 1.0 predecessors), Oracle Database 10g Express Edition, IBM DB2 Express-C, and Sybase ASE Express Edition for Linux where database size and processor count limitations aren't important. Click here for a summary of recent InfoWorld reviews of the full versions of these four databases plus MySQL, which should be valid for Express editions also. The FTPOnline Special Report article, "Microsoft SQL Server Turns 17," that contains the preceding table is here (requires registration.) May 8, 2006 update: Computerworld published "Users Buying Into Free 'Express' Databases" on March 6, 2006. The article notes that Sybase ASE Express Edition for Linux has had 45,000 downloads since September 2004, Oracle 10g Database XE has had "hundreds of thousands" of downloads, and IBM DB2 Express-C had 50,000 downloads in its first two weeks of availability. The article states that download data was "Not available" for SQL Server 2005 Express Edition. However, Microsoft's subsequent (April 6, 2006) "SQL Server 2005 Update from Paul Flessner" letter reports that "there have now also been over 2 million copies of SQL Server 2005 Express Edition downloaded". Australia's Builder AU site published "Road test: Four databases tested" on December 23, 2005. The review purported to test "free or 'light' versions" of major RDBMSs, but compared Oracle 10g Standard Edition (very far from free) with SQL Server 2005 Express (free), IBM DB2 Express (far from free) and MySQL v4.1.14 (free "[i]f you are developing and distributing open source applications under the GPL License", otherwise requires paid support). Readers point out significant errors and omissions in many negative comments on the review. (Scroll below the Related Stories section to read the comments). SQL Server 2005 Express Edition SP-1 Advanced Features

SQL Server 2005 Express Edition with Advanced Features enhances SQL Server 2005 Express Edition (SQL Express or SSX) dramatically, so it deserves special treatment here. SQL Express gains full text indexing and now supports SQL Server Reporting Services (SSRS) on the local SSX instance. The SP-1 with Advanced Features setup package, which Microsoft released on April 18, 2006, installs the release version of SQL Server Management Studio Express (SSMSE) and the full version of Business Intelligence Development Studio (BIDS) for designing and editing SSRS reports. My "Install SP-1 for SQL Server 2005 and Express" article for FTPOnline's SQL Server Special Report provides detailed, illustrated installation instructions for and related information about the release version of SP-1. SP-1 makes SSX the most capable of all currently available Express editions of commercial RDBMSs for Windows.

OpenLink Software's Virtuoso Open-Source Edition OpenLink Software announced an open-source version of it's Virtuoso Universal Server commercial DBMS on April 11, 2006. On the initial date of this post, May 2, 2006, Virtuoso Open-Source Edition (VOS) was virtually under the radar as an open-source product. According to this press release, the new edition includes:

  • SPARQL compliant RDF Triple Store
  • SQL-200n Object-Relational Database Engine (SQL, XML, and Free Text)
  • Integrated BPEL Server and Enterprise Service Bus
  • WebDAV and Native File Server
  • Web Application Server that supports PHP, Perl, Python, ASP.NET, JSP, etc.
  • Runtime Hosting for Microsoft .NET, Mono, and Java
VOS only lacks the virtual server and replication features that the commercial edition offers. According to Kingsley Idehen's Weblog, "The Virtuoso build scripts have been successfully tested on Mac OS X (Universal Binary Target), Linux, FreeBSD, and Solaris (AIX, HP-UX, and True64 UNIX will follow soon). A Windows Visual Studio project file is also in the works (ETA some time this week)." VOS includes a Web-based administration tool called "Viruoso Conductor." InfoWorld's Jon Udell has tracked Virtuoso's progress since 2002, with an additional article in 2003 and a May 3, 2006 column, "Accessing the web of databases," which points to a one-hour podcast with Kingsley Idehen of April 26, 2006. A major talking point for Virtuoso is its support for Atom 0.3 syndication and publication, Atom 1.0 syndication and (forthcoming) publication, and future support for Google's GData protocol, as mentioned in this Idehen post. Yahoo!'s Jeremy Zawodny points out that the "fingerprints" of Adam Bosworth, Google's VP of Engineering and the primary force behind the development of Microsoft Access, "are all over GData." Click here to display a list of all OakLeaf posts that mention Adam Bosworth. One application for the GData protocol is querying and updating the Google Base database independently of the Google Web client, as mentioned by Jeremy: "It's not about building an easier onramp to Google Base. ... Well, it is. But, again, that's the small stuff." Click here for a list of posts about my experiences with Google Base. Watch for a future OakLeaf post on the subject as the GData APIs gain ground. Open-Source and Free Embedded Database Contenders Open-source and free embedded SQL databases are gaining importance as the number and types of mobile devices and OSs proliferate. Embedded databases usually consist of Java classes or Windows DLLs that are designed to minimize file size and memory consumption. Embedded databases avoid the installation hassles, heavy resource usage and maintenance cost associated with client/server RDBMSs that run as an operating system service. Andrew Hudson's December 2005 "Open Source databases rounded up and rodeoed" review for The Enquirer provides brief descriptions of one commercial and eight open source database purveyors/products: Sleepycat, MySQL, PostgreSQL, Ingres, InnoBase, Firebird, IBM Cloudscape (a.k.a, Derby), Genezzo, and Oracle. Oracle Sleepycat* isn't an SQL Database, Oracle InnoDB* is an OEM database engine that's used by MySQL, and Genezzo is a multi-user, multi-server distributed database engine written in Perl. These special-purpose databases are beyond the scope of this post. * Oracle purchased Sleepycat Software, Inc. in February 2006 and purchased Innobase OY in October 2005. The press release states: "Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software." Derby is an open-source release by the Apache Software Foundation of the Cloudscape Java-based database that IBM acquired when it bought Informix in 2001. IBM offers a commercial release of Derby as IBM Cloudscape 10.1. Derby is a Java class library that has a relatively light footprint (2 MB), which make it suitable for client/server synchronization with the IBM DB2 Everyplace Sync Server in mobile applications. The IBM DB2 Everyplace Express Edition isn't open source or free*, so it doesn't qualify for this post. The same is true for the corresponding Sybase SQL Anywhere components.**

* IBM DB2 Everyplace Express Edition with synchronization costs $379 per server (up to two processors) and $79 per user. DB2 Everyplace Database Edition (without DB2 synchronization) is $49 per user. (Prices are based on those when IBM announced version 8 in November 2003.)

** Sybase's iAnywhere subsidiary calls SQL Anywhere "the industry's leading mobile database." A Sybase SQL Anywhere Personal DB seat license with synchronization to SQL Anywhere Server is $119; the cost without synchronization wasn't available from the Sybase Web site. Sybase SQL Anywhere and IBM DB2 Everyplace perform similar replication functions.

Java DB from Sun Microsystems, another commercial version of Derby, comes with the Solaris Enterprise Edition, which bundles Solaris 10, the Java Enterprise System, developer tools, desktop infrastructure and N1 management software. A recent Between the Lines blog entry by ZDNet's David Berlind waxes enthusiastic over the use of Java DB embedded in a browser to provide offline persistence. RedMonk analyst James Governor and eWeek's Lisa Vaas wrote about the use of Java DB as a local data store when Tim Bray announced Sun's Derby derivative and Francois Orsini demonstrated Java DB embedded in the Firefox browser at the ApacheCon 2005 conference. Firebird is derived from Borland's InterBase 6.0 code, the first commercial relational database management system (RDBMS) to be released as open source. Firebird has excellent support for SQL-92 and comes in three versions: Classic, SuperServer and Embedded for Windows, Linux, Solaris, HP-UX, FreeBSD and MacOS X. The embedded version has a 1.4-MB footprint. Release Candidate 1 for Firebird 2.0 became available on March 30, 2006 and is a major improvement over earlier versions. Borland continues to promote InterBase, now at version 7.5, as a small-footprint, embedded database with commercial Server and Client licenses. SQLite is a featherweight C library for an embedded database that implements most SQL-92 entry- and transitional-level requirements (some through the JDBC driver) and supports transactions within a tiny 250-KB code footprint. Wrappers support a multitude of languages and operating systems, including Windows CE, SmartPhone, Windows Mobile, and Win32. SQLite's primary SQL-92 limitations are lack of nested transactions, inability to alter a table design once committed (other than with RENAME TABLE and ADD COLUMN operations), and foreign-key constraints. SQLite provides read-only views, triggers, and 256-bit encryption of database files. A downside is the the entire database file is locked when while a transaction is in progress. SQLite uses file access permissions in lieu of GRANT and REVOKE commands. Using SQLite involves no license; its code is entirely in the public domain.

The Mozilla Foundation's Unified Storage wiki says this about SQLite: "SQLite will be the back end for the unified store [for Firefox]. Because it implements a SQL engine, we get querying 'for free', without having to invent our own query language or query execution system. Its code-size footprint is moderate (250k), but it will hopefully simplify much existing code so that the net code-size change should be smaller. It has exceptional performance, and supports concurrent access to the database. Finally, it is released into the public domain, meaning that we will have no licensing issues."

eSQL 2.11 from Vieka Technology, Inc. is a port of SQLite to Windows Mobile (Pocket PC and Smartphone) and Win32, and includes development tools for Windows devices and PCs, as well as a .NET native data provider. A conventional ODBC driver also is available. eSQL for Windows (Win32) is free for personal and commercial use; eSQL for Windows Mobile requires a license for commercial (for-profit or business) use.

HSQLDB isn't on most reviewers' radar, which is surprising because it's the default database for (OOo) 2.0's Base suite member. HSQLDB is an open-source (BSD license) Java dembedded database engine based on Thomas Mueller's original Hypersonic SQL Project. Using OOo's Base feature requires installing the Java 2.0 Runtime Engine (which is not open-source) or the presence of an alternative open-source engine, such as Kaffe. My prior posts about OOo Base and HSQLDB are here, here and here.

The HSQLDB 1.8.0 documentation on SourceForge states the following regarding SQL-92 and later conformance:

HSQLDB 1.8.0 supports the dialect of SQL defined by SQL standards 92, 99 and 2003. This means where a feature of the standard is supported, e.g. left outer join, the syntax is that specified by the standard text. Many features of SQL92 and 99 up to Advanced Level are supported and here is support for most of SQL 2003 Foundation and several optional features of this standard. However, certain features of the Standards are not supported so no claim is made for full support of any level of the standards.

Other less well-known embedded databases designed for or suited to mobile deployment are Mimer SQL Mobile and VistaDB 2.1. Neither product is open-source and both require paid licensing; VistaDB requires a small up-front payment by developers but offers royalty-free distribution. Java DB, Firebird embedded, SQLite and eSQL 2.11 are contenders for lightweight PC and mobile device database projects that aren't Windows-only. SQL Server 2005 Everywhere If you're a Windows developer, SQL Server Mobile is the logical embedded database choice for mobile applications that run on Pocket PCs and Smartphones. Microsoft's April 19, 2006 press release delivered the news that SQL Server 2005 Mobile Editon (SQL Mobile or SSM) would gain a big brother—SQL Server 2005 Everywhere Edition. Currently, the SSM client is licensed (at no charge) to run in production on devices with Windows CE 5.0, Windows Mobile 2003 for Pocket PC or Windows Mobile 5.0, or on PCs with Windows XP Tablet Edition only. SSM also is licensed for development purposes on PCs running Visual Studio 2005. Smart Device replication with SQL Server 2000 SP3 and later databases has been the most common application so far for SSM. By the end of 2006, Microsoft will license SSE for use on all PCs running any Win32 version or the preceding device OSs. A version of SQL Server Management Studio Express (SSMSE)—updated to support SSE—is expected to release by the end of the year. These features will qualify SSE as the universal embedded database for Windows client and smart-device applications. For more details on SSE, read John Galloway's April 11, 2006 blog post and my "SQL Server 2005 Mobile Goes Everywhere" article for the FTPOnline Special Report on SQL Server. Technorati: