sqlServer Management Studio not remembering users & passwords

Since I had a new Windows build for my PC I have had the very annoying bug that SQLServer Management Studio would not remember the username & passwords for hosts.

The Workarounds tab here provides the solution, but is essence it is this:

Rename:
SQL Server Management Studio 2008 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SQL Server Management Studio 2005 delete the file – same as above answer but the Vista path. C:\Users\%username%\AppData\Roaming \Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

Editing Excel XLSX files manually to change cell label names

We have an application that manipulates XLSX Excel files using the excellent Java Apache POI package

We have the need to deploy this application for lots of different customers & the labels used in the spreadsheets need to be customer specific. In our case the cell names are prefixed with the customer name say 'foo' & we need to alter them all to 'foo'

Now you could do this longhand in Excel but this is time comsuming & a major issue for us is that you are not actually renaming the cells in Excel, you are actually adding  an extra name to a cell.

So what to do.

1) Handily XLSX files are acxtually zip compressed files so firstly extract all the files using your favourite tool, for me I use 7zip 
2) run a command to search for all the files that contain the name you want to change

•     find . | xargs grep -l foo 2> /dev/null

This will probably list 4 files:

• ./docProps/app.xml
• ./xl/charts/chart1.xml
• ./xl/charts/chart2.xml
• ./xl/workbook.xml 

3)Edit these files using your favourite editor. For the changes we want to make something like 'vi' or 'vim' is perfect.
4) If you are just changing names then one command will do it

•     :1,$s/foo/bar/g

5)We also had a case where cells were duplicated for two customers 'foo' & 'bar' and we wanted to remove all the 'foo' tags. This was in the files 'app.xml' & 'workbook.xml' . Here care is needed. Excel creates these files as one long line with loads of XML tags.

6) So firstly you have to seperate the lines, For app.xml this is:

• :1,$s/<\/vt:lpstr><vt:lpstr>/<\/vt:lpstr>\r<vt:lpstr>/g
  

    For workbook.xml, this two commands are needed:

• :1,$s/<\/definedName><definedName name/<\/definedName>\r<definedName name/g
  

• :1,$s/<definedNames><definedName name/<definedNames>\r<definedName name/g
  

7) You then delete the lines containing the string 'foo', but firstly count how many lines contain the string 'foo', so

• :%s/foo//gn

Then delete the lines

• :g/foo/d

8) In the case of app.xml two extra edits are required. See the XML below. This is a snippet of app.xml. In this example note the two numbers '87' & '93'. These both need decreasing by the numbers of lines you have deleted.Note I have formatted the XML below to make for easier reading

9) You now need to repackage this up using something like 7zip, but note the archive format must be 'zip' and the compression method must be 'DEFLATE'

Now go & enjoy.

Enhancing Apache DBUtils when column names do not match getter & setter names

In a recent post on running stored procedures I made use of Apache's DBUtils package as a basic ORM.

I had a problem where certain fields in my POJO were not getting populated.
The reason being that DBUtils does not make use of the '@Column' annotation and therefore some extra code is needed.

In our case it occurred for every column that had an underscore in the name but not in the POJO getters & setters.

A snipped from my POJO could look like this.


Using the same Dao in the post I refferred to above the ResultSetHandler will now be created as follows:

Hibernate - accessing database data using views

I have recently been working on a database for which I had no control
of the structure and it certainly was not laid out as I would have desired.

Interestingly the application that we were replacing had created convenience Views to access the data.
These Views were often complex typically including JOINS across several tables.

So it may be obvious but I decided to map some of my POJOs to these views.

A few provisos:

• The view needs to be designed such that there is still a column(s) that can be mapped to an ID.
• The views are clearly critical to the application so just as in the creation of tables the creation of these views MUST be under version control

Stored Procedures or functions that return POJO objects using hibernate

I have an application that needs to run some stored procedures that want to return List but I struggled with how to get this working.

To quote from the hibernate docs this is an area that they need to work on. It says:

27.5. Database functions

Warning

This is an area in Hibernate in need of improvement. In terms of portability concerns, this function handling currently works pretty well from HQL; however, it is quite lacking in all other aspects.

This is my eventual solution.

First off a Dao class

Here is the interface that the Dao will implement

Then the generic Dao MetOfficeDao
Note the following:

1. The use of ResultSetHandler from Apache is a basic ORM
2. JDBC parameters start from '1'
3. getSession().connection(); is deprecated

Just for completeness - here is an example stored procedure for SQLServer
Note the line:
SET NOCOUNT ON
This is needed so that the procedure just returns the data and not the number of rows returned

eclipse extension bundle timeout errors

If you find that eclipse is failing to install bundles or extensions with timeout errors then try adding this line to your INI file.

    -Dsun.net.client.defaultReadTimeout=30000

I use the spring sourse STS variant of eclipse so my file is called STS.ini but yours might be ECLIPSE.ini

what architecture is my windows PC

run this command

wmic os get osarchitecture

Version control of a database schema & it's procedures

I am lost for words having had an on-going battle at my current work.

This blog sums up my thoughts perfectly.

http://www.codinghorror.com/blog/2008/02/get-your-database-under-version-control.html

Viewing very large text files on windows

Just found this little app.

It's awesome - opening a 2GB file instantly.

Large Test File Viewer

android - rooting & upgrading

Not quite sure why I wrote this on my sister blog, but I did so here is the link

MSN, skype & pidgin

If you have not heard, Microsoft are merging MSN & Skype and dropping the MSN IM tool in favour of skype.

After sometime in March you will have to stop using MSN and go to skype.

Part of this is merging your contacts from both apps. As a point of note you have to be logged in to to skype with your MSN account & then merge in the skype contacts & NOT the other way around.

Anyway that aside and back to the point of this post.
I have always hated the Microsoft front end to Messenger with all its adverts.
The skype GUI is OK but is not what I call clean. In the past I have used trillian or latterly pidgin. These great apps allow you to connect to all your different IM suppliers including MSN, Google Talk, Yahoo, AIM through one app. However until recently pidgin could not connect to skype.

Now it can. You do need skype installed & running as well as pidgin & you will always need skype running - a slight pain I know. You then need to install a skype plugin for pidgin .

I have not tried trillian & I am not sure what will happen after the March deadline but in the meantime I have my skype contacts through pidgin.

One note though there was a few interaction issues after installed the plugin with skype complaining about pidgin trying to connect but after a few stop/starts of pidgin & skype it is all good.