Get Up and Running With SQL Server Express, Clojure, SQL Korma and Local Jars

Just a sweet and short little post to help others get up to speed accessing SQL Server Express 2008 with Clojure’s SQL Korma library.

Set Up SQL Server Express

I’m using SQL Server 2008 Express. To configure your DB server go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. Under SQL Server Network Configuration select Protocols for SQLEXPRESS. On the panel on the right size of the screen, make sure TCP/IP is Enabled, then right click it and select Properties. Select the IP Addresses tab, and make sure you have the following settings:

  1. For IP Address
    • Active: Yes
    • Enabled: Yes
    • TCP Dynamic Ports: Make sure this entry is empty.
    • TCP Port: Make sure this entry is empty.
  2. For IPAll:
    1. TCP Dynamic Ports: Make sure this entry is empty.
    2. TCP Port: 1433

Enable SQL Server mixed mode authentication (SQL Korma doesn’t do integrated/Windows authentication). Run regedit.exe and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer. Change the LoginMode to 2, and restart the SQL Server Express service.

Okay, so we can now create SQL accounts on SQL Server Express, so let’s enable the SA account with SQLCMD:

  • sqlcmd .\SQLEXPRESS -E
  • GO
  • ALTER LOGIN sa with password=’aPassword’
  • GO
  • exit

Maybe you don’t want to enable the SA account, but rather create a new non-sysadmin account, as it’s a security risk to use SA for your apps. I just used it here, as it was the shortest way to get a SQL account ;-)

Load The SQL Server JDBC Driver Into a Local Artifactory Repository

Download JFrog’s Artifactory. Go to Artifactory’s bin folder and run InstallService.bat. Then launch Artifactory with  artifactory.bat, and browse to http://localhost:8081/artifactory/webappGrab Microsoft SQL Server JDBC Driver 3.0 and load it into a local Artifactory repository with the following settings:

  • GroupId: sqljdbc4
  • ArtifactId: sqljdbc4
  • Version: 3.0

The reason we’re using Artifactory, is that Leiningen demands that all your dependencies come from a repository somewhere. Since Microsoft’s JDBC driver isn’t available on a public repository like Clojars, is make it available from a local repository. You’re other option is to directly load it into a Maven repository, but trust me, this is time consuming and something you want to avoid.

Now add your local repository to Lein’s defproject, :repositories {“ext-release-local” “http://localhost:8081/artifactory/ext-release-local/”}. You can see I chose to load sqljdbc4.jar into the ext-release-local repository. Also add the SQL Server JDBC driver as a dependency: [sqljdbc4/sqljdbc4 “3.0”]

Make a Nice Clojure SQL Korma

And finally drop SQL Korma as a dependency into your Leiningen project, and do the rest of its configuration as specified on Git Hub:  [korma “0.3.0-RC4”]. If you’re new to SQL Korma, like I am, you might think that (defentity …) will also create your database objects for you. Not so! SQL Korma doesn’t have this feature currently (version 0.3.0) – hopefully they’ll add it in future. So make sure you’re DB objects exist and that the defentity statements correctly map to them.

Sounds like a few simple steps, but I took me unnecessarily long to piece all the above together, and get to the point of accessing SQL Server Express 2008 with SQL Korma. Hopefully the above will help others to reach SQL Server and Korma Nirvana, one time!



Sexy Transactions In Spring.NET

Spring.NET really has tons of handy features you can put to work in your application. One of them is its transaction management, that provides an implementation agnostic abstraction for your application. The first question you’re probably asking yourself is, why do you need to abstract away your transaction technology? The short answer is there are various ways of using transactions and different transaction technologies: ADO.NET, Enterprise Services, System.Transactions, and the different transaction implementations for other database technologies like ORMs (NHibernate). I won’t go into the detail of these, suffice to say that choosing, using and changing a transaction technology can become a complicated affair, and that Spring.NET protects your application from these ugly details. I encourage you to take a deep dive into the official Spring.NET documentation, should you want to know more about the details behind this.

What I did was, write a helper class that makes it a little easier and cleaner to use Spring.NET’s transaction management:

/// <summary>
 /// Manages database transactions by wrapping executed
 /// methods in a transaction.
 /// </summary>
 public static class Transaction
 /// <summary>
 /// Executes the specified method in a transaction.
 /// </summary>
 public static void Execute(Action method, bool commitChanges = true)
 // Create a new transaction
 Instantiate.New<ITransactionOperations>().Execute(delegate(ITransactionStatus transaction)
 // If changes should not be committed, make
 // sure we roll back once done
 if (!commitChanges) transaction.SetRollbackOnly();
 return null;

/// <summary>
 /// Instantiates objects based on their name.
 /// </summary>
 public static class Instantiate
 #region Fields

 private static IApplicationContext context;


 #region Get objects from ApplicationContext

 /// <summary>
 /// Retrieves the object with the specified name.
 /// </summary>
 /// <typeparam name="T">The type of object to return.
 /// <param name="name">The name of the object.</param>
 /// <returns>A newly created object with the type specified by the name, or a singleton object if so configured.</returns>
 public static T New<T>(string name)
 return (T)Context.GetObject(name);

 /// <summary>
 /// Retrieves the object of the specified type.
 /// </summary>
 public static T New<T>()
 return (from obj in Context.GetObjectsOfType(typeof(T)).Values.Cast().ToList()
 select obj).FirstOrDefault();

 /// <summary>
 /// Retrieves the object with the specified name, and constructor arguments.
 /// </summary>
 public static T New<T>(string name, object[] arguments)
 return (T)Context.GetObject(name, arguments);


 #region Private methods

 /// <summary>
 /// Gets the context.
 /// </summary>
 /// <value>The context.</value>
 private static IApplicationContext Context
 if (context == null)
 context = ContextRegistry.GetContext();

 return context;


Class Instantiate

The Instantiate class is a wrapper for Spring.NET’s IApplicationContext, with a few helper methods. It provides three overloads of the method New<T>, that instantiates new objects from the ApplicationContext configuration. The most important method is New<T> without any arguments. This method simply grabs the first instance of the requested Type it finds in the ApplicationContext. The other two variations allow you to instantiate a new object with a specific id.

With the help of the Instantiate class the Transaction class will obtain the specified transaction management from Spring’s ApplicationContext.

Class Transaction

This is where all the action really happens as far as database transactions go. The Transaction class has a single Execute method, that transparently wraps any block of code in a transaction. There are a small number of things you have to do to use Spring’s transaction management:

1. First get an instance of your chosen transaction implementation.

2. Call Execute with a delegate with an ITransactionStatus argument.

3. When you’re done, you have to return something, even if it’s null.

My goal is to remove the above 3 requirements, so I can just pass a block of code through to get it wrapped in a transaction. This is exactly what Execute achieves:

Transaction.Execute(() =>
 // Get a FileTypeRepository
 var fileTypeRepository = Instantiate.New();

 // Add an object

 // Save the changes to the database


Spring.NET provides 4 transaction implementations:

  • Spring.Data.Core.AdoPlatformTransactionManager – local ADO.NET based transactions
  • Spring.Data.Core.ServiceDomainPlatformTransactionManager – distributed transaction manager from Enterprise Services
  • Spring.Data.Core.TxScopeTransactionManager – local/distributed transaction manager from System.Transactions.
  • Spring.Data.NHibernate.HibernateTransactionManager – local transaction manager for use with NHibernate or mixed ADO.NET/NHibernate data access operations.

To configure your transaction manager for the ApplicationContext you need:

<objects xmlns="">
 <object id="transactionManager"
 type="Spring.Data.Core.TxScopeTransactionManager, Spring.Data" />
 <object id="transactionTemplate"
 type="Spring.Transaction.Support.TransactionTemplate, Spring.Data"
 autowire="constructor" />

Spring.NET’ provides a TransactionTemplate that handles all the necessary transaction logic and resources like commits, rollbacks, and errors. It frees you from getting involved with low level transaction management details, and provides all transaction management out of the box. If you desire you can directly access a transaction manager, through an IPlatformTransactionManager interface, to manage the details of your transaction process.

TransactionTemplate implements the ITransactionOperations interface. It has a constructor that requires a IPlatformTransactionManager instance. All transaction managers implement the IPlatformTransactionManager interface. This means TransactionTemplate contains the specified transaction manager. The required transaction manager is provided to the TransactionTemplate through autowiring of the constructor’s parameters. The autowiring searches through the objects and returns the first configured object that implements the IPlatformTransactionManager interface, which is transactionManager in  our case.

Object transactionManager is an instance of TxScopeTransactionManager, that is in turn based on System.Transactions.

Transaction.Execute has a second optional parameter, commitChanges. The commitChanges argument defaults to true, so the transaction manager will always attempt to commit changes. I use it when I test my application, and I want all test data to be rolled back.

Based on the fact that it commitChanges is an optional parameter, you’ve probably figured out by now that I recompiled Spring.NET to run on .NET 4.

The example IFileTypeRepository is actually based on Entity Framework 4 with Plain Old CLR/C# Objects (POCO). Just in case you were wondering if Spring.NET’s transaction management works with Entity Framework.

The Joy, Blood, Sweat And Tears Of InfoPath 2007

InfoPathI recently completed a project based on InfoPath 2007 (Office client version) and Microsoft Office SharePoint Services 2007 (MOSS 2007). Looking back I can say that InfoPath has its uses, but before you build a solution around it you have to be very sure about its limitations. InfoPath has a number of limitations, especially with regards to submitting data, that aren’t that apparent at first sight. If you don’t watch out, you can quickly get caught up in what feels like a never ending maize of dead ends.

InfoPath is often pitched as a solution that doesn’t require writing custom code. This project was no different, and its time lines were made accordingly. In the end we had to write a fair amount of custom code, which was fun, but took more time.

The Many Limitations Of InfoPath DataBase Data Source

InfoPath generally works well viewing standard enterprise data sources such as database tables or SharePoint lists. The limitations become apparent when you attempt to submit to a database using an InfoPath SQL connection, or perform advanced queries. There are a number of limitations when you work with an InfoPath SQL database data source/connection:

  1. Only submit to a single table. This excludes database data sources such as views, and queries with joins. You cannot submit to views or SQL DataSources with joins.
  2. To submit to a database you can only use the main data connection. In other words you can’t have a database-view as the main data source, and setup another simple single table select to submit to.
  3. Range queries are not possible. You can only use a field once in a query’s WHERE clause with an equality operator.
  4. SQL data source dependent on table schema. If a SQL data source’s underlying table is modified, even just adding a column (in other words InfoPath’s SELECT statement doesn’t actually change), the data source will break.
Data Connection Wizard

Data Connection Wizard

With SharePoint lists you cannot query the data source with queryfields like relational data sources.

The above limitations, especially regarding relational data sources, mean one thing: Web services are mandatory for working with your relational data. Using web services allows you to overcome all the limitations of the standard InfoPath SQL data source, and work with a consistent schema.

Another thing to watch out for is that InfoPath’s performance deteriorates quickly when you have more than 50 rows in your result set. Sometimes this figure is much lower. In the project I worked on the data was coming lightning fast from the data base through the web service. But when the data hits the form, and InfoPath starts parsing the XML document, it completely froze for quite a while. I have decided not to torture myself trying to page my form data, so I haven’t looked into this yet (and I believe InfoPath is not meant to be used in this manner). The quickest and most effective solution I could come up with is to allow users to load data into their form incrementally. How this works is that you’ll do a normal retrieve of your data from the data source, but instead of clearing the form, you’ll just add the new result set to the rest of the form’s data. The big drawback of this is that you need to write custom code to modify the XML document directly using XmlWriter: Not a too pleasant exercise.

public void Load_Clicked(object sender, ClickedEventArgs e)
  // Call the web service of the secondary DataSource, which will populate it         
  var clients = DataSources["ClientWS2"].CreateNavigator().Select("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult/tns:Client", NamespaceManager);

  // The 1st time rows are added GetClientsResult might not exist, only GetClientsResponse
  var main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult", NamespaceManager);
  if (main == null) main = MainDataSource.CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetClientsResponse", NamespaceManager);

  using (XmlWriter writer = main.AppendChild())
    // Make sure we are adding Client elements to /dfs:myFields/dfs:dataFields/tns:GetClientsResponse/tns:GetClientsResult and not, /dfs:myFields/dfs:dataFields/tns:GetClientsResponse
    if (main.LocalName == "GetClientsResponse")
      // So if it doesn't exist, create it first
      writer.WriteStartElement("GetClientsResult", "");

    while (clients.MoveNext())
      writer.WriteStartElement("Client", "");

      // Select all the client element's child elements
      var fields = proposals.Current.Select("*", NamespaceManager);
      while (fields.MoveNext())
        // Write each element and value to the Main DataSource
        writer.WriteStartElement(fields.Current.Name, "");


    if (main.LocalName == "GetClientsResponse)

The above event fires when a user clicks the Load button. The trick to load data incrementally is that you need a second DataSource exactly the same as the Main DataSource (they should point to the same data store). Whenever you call DataSource.QueryConnection.Execute() InfoPath will wipe any previous data from that DataSource, and reload it with the new data. That’s why you need a separate second DataSource that you call Execute on, and then copy that data to the Main DataSource. The end result is the Main DataSource doesn’t lose its data, but data gets added to it on each query.

Just another side note on InfoPath: Pivot tables are not possible, because you have to know exactly which columns your binding to at design time, and cannot create columns dynamically at runtime. This shouldn’t be a show stopper to most projects, but I’m just mentioning it. All the InfoPath forms we had to do came from Excel spreadsheets, and the one spreadsheet was a monster pivot table.

Hacking The DataConnection

It’s possible to query a data connection directly from InfoPath, change the SQL command dynamically, or extract the connection string. The biggest drawback of this hack (apart from being a hack, i.e. not recommended) is that it requires FullTrust and Sql Code Access Security (CAS) permissions. That means you have to certify your InfoPath form, or create an installer so users have to install it locally onto their machines. This doesn’t really work well when the form is made available to users through a SharePoint document library.

Anyways, here is a very unrefined sample to achieve this:

private const string CONNECTION_STRING = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;";

private string GetConnectionString(AdoQueryConnection queryConnection)
  var password = GetConnectionStringParameter(queryConnection, "Password");
  var user = GetConnectionStringParameter(queryConnection, "User ID");
  var server = GetConnectionStringParameter(queryConnection, "Data Source");
  var db = GetConnectionStringParameter(queryConnection, "Initial Catalog");
  return string.Format(CONNECTION_STRING, server, db, user, password);

// Hmmm, if your using my wonderful hack, then you might want to
// consider rewriting this method to use regular expressions instead 😉
private string GetConnectionStringParameter(AdoQueryConnection queryConnection, string name)
  var paramIndex = queryConnection.Connection.IndexOf(name + "=");
  var parameter = queryConnection.Connection.Substring(paramIndex, queryConnection.Connection.IndexOf(";", paramIndex) - paramIndex);
  return parameter.Substring(parameter.IndexOf("=") + 1);

private IDataReader SelectWorksheetItems(SqlConnection connection, int pocketID)
  using (var dbCommand = new SqlCommand("WorksheetItemGetByPocket", connection))
    dbCommand.Parameters.Add("@pocketID", SqlDbType.Int).Value = pocketID;
    dbCommand.CommandType = CommandType.StoredProcedure;
    return dbCommand.ExecuteReader();

worksheetItemCurrentDS = DataSources["WorksheetItemCurrent"];
worksheetItemCurrentCmd = ((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection).Command + " where \"PocketID\"={0}";

using (var connection = new SqlConnection(GetConnectionString((AdoQueryConnection)worksheetItemCurrentDS.QueryConnection)))
  using (var reader = SelectWorksheetItems(connection, 24))
    // Do some stuff with the DataReader here...

InfoPath’s different data sources each use a specific data connection that inherits from the abstract class Microsoft.Office.InfoPath.DataConnection. The main point of the above example is that you can cast your InfoPath’s DataConnections to its specific implementation. For SQL database data sources InfoPath uses AdoQueryConnection. With AdoQueryConnection you have the ability to extract or manipulate the data source’s command and connection string.

Using SQL Server To Store InfoPath Documents

You cannot call store procedures directly from InfoPath, but if you develop on SQL Server 2005 or later, you can use SQL Web Services to call stored procedures as a web service. The big catch here is that SQL authentication and SQL Web Services don’t really go well together. Therefore when using SQL authentication for your InfoPath DataConnections you will either have to support integrated authentication for calls coming through the SQL Web Service (and SQL authetication for direct calls from the InfoPath form), or you will have to throw open access to your stored procedure to all users. If your using SQL authentication, there’s usually a good reason your doing so, so additionally supporting integrated authentication might not be an option. Giving access to anyone is an even worse idea.

CREATE ENDPOINT ClientInsertEndpoint
  SITE = 'ServerName',
  PATH = '/WebServiceName',
  WEBMETHOD 'ClientInsert'
    NAME = 'DataBase.dbo.ClientInsert',
  DATABASE = 'DataBase'

If is possible to support SQL authentication for SQL Server Web Services, but this requires a SSL server certificate. Microsoft also plans to remove this feature from SQL Server in future releases.

“This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Definitely read Microsoft’s best practices for SQL Server Native XML Web Services.

Finally, you can use SQL Server 2005’s XML data type to save an InfoPath Form or query it in a relational format. Here’s a sample stored procedure that takes in the root node of the InfoPath’s XML document, and inserts the items into a table:

ALTER PROCEDURE [dbo].[ClientUpdate]
  @clientsXml XML
  INSERT INTO  Client (FirstName,
SELECT         Clients.Client.query('data(@FirstName)').value('.', 'VARCHAR(25)') as FirstName,
               Clients.Client.query('data(@LastName)').value('.', 'VARCHAR(25)') as LastName,
               Clients.Client.query('data(@CellNo)').value('.', 'VARCHAR(25)') as CellNo,
               Clients.Client.query('data(@TelNo)').value('.', 'VARCHAR(25)') as TelNo,
               Clients.Client.query('data(@WorkNo)').value('.', 'VARCHAR(25)') as WorkNo
FROM           @clientsXml.nodes('declare namespace d=" namespace dfs=""; //d:Client') Clients(Client)

The new SQL XML syntax is a bit tricky, but once you get it right it works wonderfully well.

XPath Expressions Are Your Friend

Conditional Format

Conditional Format

Conditional formatting and XPath expressions are very handy to display unique values in a RepeatingTable. For instance say you’ve got a Client object, with multiple Addresses – street, postal, and work. Say you only wanted to show a client’s name once, and list each of his addresses without repeating his name. When you’re using a SQL DataSource, you will do a left join with the address table from the client table. This means you’ll repeat the same client name for each address.

To solve the aforementioned you need to make sure you order by client name, and then hide the textbox with a XPath expression:

tns:ClientName = preceding-sibling::tns:Client/tns:ClientName

What this expression is saying is that if the current Client’s ClientName is the same as the previous Client’s, then do something. That something is the action you’ll check on the Conditional Formatting window, that will be “hide” in our case.

This approach can be extended to multiple fields. All you have to do is make sure your order sequence is correct. So just by ordering your resultset corrrectly and using the right XPath expression, you’ll achieve quite a bit without having to write code.

InfoPath And Visual SourceSafe Does Not Play Well Together

If you’re creating InfoPath forms with Visual Studio Tools for Office and using Visual SourceSafe for source control, you will quickly get a whole range of different and meaningless error messages. Here are the general things to do to resolve them:

  1. Make sure all files in your Visual Studio InfoPath project’s “InfoPath Form Template” directory are checked out, before doing any work on manifest.xsf (the InfoPath form).
  2. If you’re having trouble checking files out of SourceSafe, from within Visual Studio:a. Close Visual Studio.b. Open the Visual SourceSafe application, and check out the files for the project from there. Once you’ve done this you can close Visual SourceSafe.c. Make the directory “InfoPath Form Template” and all its content writeable, by unchecking the Read-only option from the folder’s Options.d. Reopen Visual Studio, and continue working as usual.

Most Annoying InfoPath Deployment

Another aspect of InfoPath you need to consider is how you’re deploying your forms. To deploy a form you need to manually, that’s right manually, update each DataConnection to point to your production environment. Ouch! If you have say 5 forms, with 5 DataConnections each, then your looking at 25 DataConnections to manually update. Nasty! And if you mess one connection up, you’ve got a problem.

SQL DataConnections are the worst to update. When you want to change to a new DataBase, InfoPath completely clears your original select statement and forgets which table you were using, and you have to reselect the columns/table. Should your DataSource’s schema change (i.e. your select statement is not exactly the same as previously), InfoPath will do you the favor of removing your controls’ databindings. Most of the time you’ll probably use all the table’s columns, but you still have to go and re-select that table.

Web Service DataConnections are the easiest to reconfigure (but still pretty painful). You can just take the URL of the new web service, and copy it into the web service address box, and quickly click Next through the DataConnection wizard. InfoPath doesn’t forget which web method your DataConnection uses, like it does with SQL table DataConnections.

The aforementioned makes it extremely time consuming and error prone to deploy InfoPath forms between development, QA and production environments.


  1. Use web services to retrieve and save form data, and plan accordingly. I cannot state this enough. Yes, maybe for the simplest of simple forms you can get away with using InfoPath’s SQL DataConnection (and I mean really simple), but for everything else a web service is an absolute must.
  2. Try to avoid large editable, repeating data grids (or referred to as a RepeatingTable in InfoPath lingo). Be extra cautious when you’re planning on editing large result sets, with lots of drop down lists and lookup data. Forms that work best are ones that displays and edits a single entity, and apposed to forms that edit multiple instances of an entity on the same form.
  3. Don’t think you’re going to deploy those 5 forms in a few minutes. Give yourself enough time for the deployment, and to test each form afterwards to check that you didn’t mess a DataConnection up.
  4. Do a quick prototype of your forms to check whether InfoPath can really handle it. In my case the person who recommended InfoPath for the solution should have checked that it can accommodate pivot tables. This is general good software dev practice, but I think because of all InfoPath’s restrictions, I think one needs to be particularly careful.We’ve gotten so used to having control over every element of the user interface with ASP .NET and Windows Forms that we expect the same of other technologies we use. Remember InfoPath’s controls and their behaviour dictate how your information is displayed. You do not have access to the underlying API that these controls are based on. In other words know what InfoPath’s UI controls can do, because you won’t be able to write your own.

SQL Server Error: OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.]


You receive the following error trying to execute a query against a linked server:

Server: Msg 7399, Level 16, State 1, Line [Line Number]
OLE DB provider ‘SQLOLEDB’ reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.]


Turn off the SQLOLEDB provider options

  • LevelZeroOnly
  • IndexAsAccessPath (not always required)
  1. In SQL Server 2000 Enterprise Manager, expand the server node, then the security node. Right click on the “Linked Servers” node. Select the “New Linked Server… ” option to add a new linked server.
  2. Complete the fields as follows:
    • Linked server: Type the logical name for the SQL Server that will be used in SQL queries.
    • Other data source, Provider name: Select Microsoft OLE DB Provider for SQL Server.
    • Product name: SQLOLEDB.
    • Data source: Network name of the SQL Server.
    • Provider string: Can be left empty.
    • Catalog: Database that will be connected to.
  3. Edit the Provider Options by clicking the “Provider Options…” button.

    • Uncheck “Level zero only”.
    • Depending on your specific scenario, optionally uncheck “Index as access path”.

    After completing the above tasks you can continue setting the properties for the new linked server as you would under normal circumstances.