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

Posted: February 17, 2013 | Author: J du Preez | Filed under: SoftwareDevelopment | Tags: Artifactory, Clojure, JDBC, SQL, SQL Korma, SQL Server, SQL Server Express, SQL Server Express 2008 | Leave a comment

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:

For IP Address 127.0.0.1
- Active: Yes
- Enabled: Yes
- TCP Dynamic Ports: Make sure this entry is empty.
- TCP Port: Make sure this entry is empty.
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
ALTER LOGIN sa ENABLE
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/webapp. Grab 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!

Executing Shell Commands From A Visual Studio Deployment Project

Posted: March 23, 2009 | Author: J du Preez | Filed under: Mono.NET, SoftwareConfiguration | Tags: .vbs, Commandline, Custom Action, Deployment, Shell, SQL Server Express, Visual Basic script, Visual Studio, Visual Studio Deployment Project | 1 Comment

Visual Studio’s Deployment Projects are quite limited. To get them to do more complicated setup tasks require writing custom deployment actions in C#. The problem with writing code is that it places an additional maintenance and testing burden on the developer. Often all that’s needed is to perform a few small operating system tasks. In these scenarios it just feels unjustified to revert to writing a bunch of C# setup logic.

I recently ran into such a scenario. As part of the deployment application, SQL Server 2005 Express had to be automatically unpacked. When the SQL Server Express setup utility is runned with the /x argument, it unpacks its contents, instead of installing SQL Server. The trick is how to execute this shell command with the minimum effort? The answer is to write a little Visual Basic script file (.vbs) that uses the WScript.Shell object. The emphasis is on little. I wouldn’t recommend sticking huge complicated installation logic in a script file. When you have lots of setup logic, then it’s time to write those C# custom actions, together with unit tests, and mocks. But for all those 1 liner shell commands, a script file is just the thing you need.

Visual Basic Scripts (.vbs) Are Your Friend

The VB script:

Set WshShell = CreateObject("WScript.Shell")
WshShell.Exec(Property("CustomActionData") & "\SQLEXPR32.EXE /x:""" & Property("CustomActionData") & "\SQLEXPR2005""")
Set WshShell = Nothing

The Windows Script Host (WScript.Shell) object has two candidate methods to execute commands – Run and Exec. According to Microsoft, if you need access to command-line output, you should use the Exec method instead. In this case I did not specifically need access to shell output, but for whatever reason I was unable to get Run to work.

customactionproperties

Each custom action has access to the CustomActionData argument. This handy little argument is set on the custom action’s properties. It allows you to pass through the directory where the application was installed, using the global variable [TARGETDIR].

WScript.Shell.Exec allows you to execute any standard command line argument in a manner similar to using the Run… dialog box from the Start menu.

visualstudiodeploymentproject What’s nice about this solution is that it allows you to package SQL Server Express with your solution in one file, and then unpack it during the installation process. The other option is to unpack SQL Server beforehand and add the individual files to your deployment solution. If you’re using Visual SourceSafe, you will run into problems with some of SQL Server’s files that have very long names, and are not DOS compatible.