JDBC to SQL Server Express

Connecting JDBC-based tools such as SQL Developer or DbVisualizer to SQL Server Express required the following steps:

• Obtain JDBC Driver
• TCP/IP for SQL Server Express
• Authentication Method

Obtain JDBC Driver

Using SQL Developer, when you get the following exception …

Unable to find driver: net.sourceforge.jtds.jdbc.Driver

… download the jTDS JDBC driver and install it in your JRE's ext folder. The latest version of the driver is 1.2. Of course, there are other JDBC drivers for SQL Server you can use.

TCP/IP for SQL Server Express

By default, TCP/IP for SQL Server Express is disabled, so JDBC cannot connect to it and you may get the following exception …

Network error IOException: Connection refused: connect

Enable TCP/IP

To enable TCP/IP, start SQL Server Configuration Manager.

1. Expand SQL Server 2005 Network Configuration node.
2. In the right pane, select Protocols for SQLEXPRESS. The right pane should now show Protocols and Status columns.
3. Select Enable from the TCP/IP context menu.

Find or Configure TCP/IP Port

After enabling TCP/IP, you have to find out which port number to use. SQL Server Express allocates a port dynamically each time it is started, so to find or configure the port number, continue using SQL Server Configuration Manager …

1. Select Properties from the TCP/IP context menu. The TCP/IP Properties dialog should open.
2. Select the IP Addresses tab.
3. In the IPAll node …
   1. The TCP Dynamic Ports field shows the currently used port number. If you set that field to blank, then SQL Server Express should not automatically choose another port when it restarts.
   2. Set the desired port number in the TCP Port field.
   3. Press OK to apply your settings and close the dialog.

Test TCP/IP

If you change the TCP/IP port, you have to restart SQL Server Express before it can use the new port number. To test that your port number is used, start a cmd window and type: netstat -an. For instance, if you used port 1433, you should see this line in the list of ports used:

TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING

Authentication Method

By default, SQL Server Express uses Windows Authentication Mode to authenticate connections. If you get this exception …

Login failed for user '<User name>'. The user is not associated with a trusted SQL Server connection.

… then you may have to enable SQL Server Authentication Mode and create or enable a user.

1. Start Microsoft SQL Server Management Studio Express (SSMSE) and connect to your database server.
2. In Object Explorer pane, select Properties from your database's context menu. The Server Properties dialog should open.
3. Select Security page.
4. Select SQL Server and Windows Authentication Mode check box.
5. Press OK button to close the dialog.
6. In Object Explorer pane, expand Security / Logins node.
7. Select existing user sa. Note that there is a red downward arrow next to that user's image.
8. View sa's properties. The Login Properties dialog should open.
9. Select Status page.
10. Ensure that the Login: Enabled radio button is selected.
11. Select General page.
12. Enter a password for this user.
13. Press OK button to close the dialog.
14. If you refresh the Object Explorer pane, note that user sa no longer has a red downward arrow.

Finally …

After all these steps, you should be able to connect to your SQL Server Express database using JDBC.

SQLDeveloper Paste Multiple Rows

If you're using SQLDeveloper, you can copy and paste multiple rows into a table in one operation, which makes it easy to load small amounts of data into a database for testing.

1. Create your tabular data in Excel.
2. Copy your data into the clipboard.
3. In SQLDeveloper, open the Data tab of the required table.
4. Add as many rows as required by repeatedly pressing the Insert Row button.
5. Paste your data into SQLDeveloper.

If you look in the SQLDeveloper console page, you can see a sequence of INSERT statements followed by a COMMIT statement.

It seems that you can only insert a row if your database server supports transactions, so this trick works for Oracle but not for MySQL.

JUnit 4

Quick note. JUnit 4.1 is very different from JUnit 3.8. See …

• JUnit 4 Primer
• JUnit Reloaded.
• Get Acquainted with the New Advanced Features of JUnit 4

JUnit 4.1 is supported by Eclipse 3.2. Good stuff.

Windows Cmd Variables and For-loop Command

Strange Syntax for Variable Definition

My quickie script to export Oracle tables defined the for command variable using two percent symbols, %%i, instead of the expected %i% in Windows command shell. Even more strange is that only one % is required when the for command is written interactively in the command shell.

Cmd Variables

To demonstrate and understand how variables are defined in cmd, try the following script in a batch file (i.e. save the commands in a file and run it) and interactively (i.e. type in each command). Note that homedrive is an pre-defined environment variable while i is not defined.

echo %homedrive
echo %i
echo %homedrive%
echo %i%
echo %%homedrive%
echo %%i%
echo %%homedrive%%
echo %%i%%

Here's the results of using a batch script:

> echo homedrive
homedrive
> echo i
i
> echo C:
C:
> echo
ECHO is on.
> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
%homedrive%
> echo %i%
%i%

Here's what happens when you enter these commands one at a time:

> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
C:
> echo %i%
%i%
> echo %%homedrive%
%C:
> echo %%i%
%%i%
> echo %%homedrive%%
%C:%
> echo %%i%%
%%i%%

When cmd reads a script in batch mode, it always consumes the leading % for each string as it looks for variables or has to escape a % (see %%i). If a variable is found, that string is always replaced, even if it is not defined (see echo %i%).

On the other hand, when cmd processes a command interactively, it only consumes the %'s in a string when that string is delimited by % (compare echo %homedrive and echo %homedrive%) and if that string maps to a variable name (compare echo %homedrive% and echo %i%). Also, % by itself is treated as a literal "%".

For Command

Back to the for command from the start of this article. In a batch script, a variable, %i, for the for command has to be entered as %%i so that cmd will replace %% with just %. In interactive mode, you only need %i because cmd does not regard this string as a variable. So here's the batch script version of a for command …

for %%i in (a, b, c) do echo %%i

… while here's the interactive version of a for command …

for %i in (a, b, c) do echo %i

The for command defines a variable only if a string starts with %. If you use %i%, then cmd replaces it with a value if i is defined (as expected) and you may encounter some other unexpected error depending on the value of i. However, if i is not defined, for command exits with this message:

%i% was unexpected at this time.

Another unexpected limitation of the for command is that a variable can be only one character long. If you try a variable name that is two or more characters long, you will get this …

for %xy in (a, b, c) do echo %xy
%xy was unexpected at this time.

Windows Cmd Oracle Export

A quickie: I had to dump all the data from an Oracle database and e-mail them to a colleague. Not being familiar with backup and restore for Oracle, I decided to just run exp for each of the tables. To get a list of tables in a schema, I executed this query in DbVisualizer, SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '<schema>', and saved the output to a file, table.txt. Then in Windows cmd, I wrote this script into a file and ran it:

for /f %%i in (table.txt) do exp <connection string> TABLES=%%i FILE=%%i

Pasting MS-Word Heading Numbers

When I cut a heading and its heading number from a Microsoft Word document and paste them into an Outlook 2003 HTML message or Wordpad, the heading numbers are always reset. For example, 4.1.1.1 My Heading is pasted as 1.1.1.1 My Heading. When you copy text into the clipboard, it can be copied using multiple formats (text, RTF, metafile, etc.). In this case, the plain text version has the correct heading number but the RTF version does not. Note that you can check the contents of the clipboard using clipbrd.exe.

Following on, I found that Outlook 2003 only allows the user to choose the paste format (Edit / Paste Special menu item) when the message is RTF, not HTML or plain text.

Asus Touchpad Advanced Settings

When I try to set the touchpad's advanced properties on my work-provided Asus notebook, I get the following error:

An exception occurred while trying to run "C:\WINDOWS\system32\shell32.dll,Control_RunDLL "C:\WINDOWS\system32\main.cpl",Mouse"

This problem still happened after I had installed TouchPad_XP_060220.zip from Asus, which installed the 8.2.0.0 driver. Deep breath … Downloaded and installed the latest Synaptics driver, XP_2K.8.3.4 and now I can configure the touchpad's advanced properties.

It's a bit of a puzzle why the Asus drivers stopped working. I suspect that some of the Windows updates conflict with the Asus drivers. It's a bit hard to narrow down which update, since I don't always fiddle with the touchpad's advanced settings. Microsoft, Synaptics and Asus don't seem to have any information about why this problem occurs.

Windows Hardlinks with Fsutil

Microsoft Internet Explorer 7 (MSIE7) doesn't open .xhtml files; instead it uses Firefox (probably because Firefox is my default browser). MSIE7 behaves that way regardless of whether I use the Open With context menu item in Explorer, open the file using its file browser or drag-and-drop that file into its window. On the other hand, Opera browser will open an .xhtml file. MSIE7's behaviour is a bit annoying because I go out of my way to test my web pages on different browsers.

I worked around this MSIE7 annoyance using NTFS hardlinks (similar to Unix hardlinks). When you create a file, a hardlink (or name) referring to this file is also created. You can create other hardlinks (or names) that refer to the same file using fsutil utility. A file is only deleted when the last hardlink is deleted.

To create a new hardlink called Name2 for an existing file called Name1 using fsutil, open a command shell and enter: fsutil hardlinks create Name2 Name1.

Windows XP Speech Recognition

I had a play with the Windows XP Speech Recognition service over Easter. Enabling and using this service was less obvious than I expected, so I wrote a short article explaining what I did.

Windows MAC Addresses

There's at least three ways to get MAC addresses for Windows computers:

• Open Network Connections applet from the Control Panel, Device Manager or run ncpa.cpl, then look at the properties of each network adaptor. This method is rather hit-and-miss and depends on the information provided by the drivers. My Intel PRO/Wireless driver shows the MAC address in the Advanced property sheet but the Realtek NIC driver doesn't show the MAC address at all.
• In the command shell, enter ipconfig /all. ipconfig provides a lot of networking information and you have to look for the Physical Address field.
• In the command shell, enter getmac /v. getmac provides just information about network adaptors.

The last method, getmac -v, provides just the required information. If you use the /fo csv option, you can even get the output formatted for a spreadsheet or database.

Why is it useful to know MAC addresses? If you're setting up a wireless router, you can restrict the computers that can use the router by specifying a list of allowed MAC addresses. If your company uses a software license manager such as FlexLM, you may need to provide the MAC address of your users' computers to your software vendor to obtain node-locked licences.

Drag-and-drop and Alt-Tab

Unexpected but nice feature in Windows. You can grab and start dragging an object with your pointer, then use Alt-Tab to bring the required application window to the foreground where you can drop the object. Previously, I thought both the originating and destination windows had to be visible at the same time on the desktop for a drag-and-drop action. Useful if you usually maximize your work windows on your desktop. For example, if you have your Excel window maximized, then realise you have to insert a TXT data file, you can either locate the file using the file browser dialog or drag-and-drop from Explorer into Excel.

OpenOffice 2.2 Database Connection

OpenOffice.org has released version 2.2 of their office suite. Seems to be just a bug-fix release. Having installed the latest version, I thought I'd connect the Base product to my test MySQL database. It should have been painless but I kept getting a JDBC driver could not be loaded error. After some head-scratching, it turns out that the MySQL JDBC driver had to be copied into the Java Runtime (JRE) ext folder. Here's how I set up a connection between OpenOffice and MySQL.

1. Download and extract mysql-connector-java-5.0.4-bin.jar.
2. Copy mysql-connector-java-5.0.4-bin.jar to C:\Program Files\Java\jre1.5.0_11\lib\ext\ folder.
3. Start OpenOffice and select Tools / Options menu item, then select Java from the tree pane.
4. In Java Options pane, select the appropriate JRE and select the OK button.
5. In OpenOffice, select File / New / Database menu item.
6. In Select Database step, select Connect to an existing database radio button, select MySQL from the drop down list, then press the Next button.
7. In Set up MySQL connection step, select Connect using JDBC (Java Database Connectivity) radio button, then press the Next button.
8. In Set up JDBC connection step, press Test class button. If your connector is copied in the JRE's ext, then the JDBC Driver Test dialog should report The JDBC driver was loaded successfully. If the test fails, it will report The JDBC driver could not be loaded. Close the test dialog and continue configuring your database connection.
9. Enter the Name of the database (e.g. test), Server URL (e.g. localhost) then press the Next button.
10. In Set up user authentication step, enter User name (e.g. root) then press Test Connection button. If the connection was unsuccessful, the Connection Test dialog should report Access denied for user 'X' to database 'Y'. Close the test dialog and press the Next button.
11. In Save and proceed step, use the default options then press the Finish button. OpenOffice will prompt you to save the database connection as an .odb file.

Notes: OpenOffice doesn't find the MySQL driver class after setting the CLASSPATH in the Options dialog. Another bug is that I can't remove a wrong JAR entry from the Options dialog.

Virtual CDs with MagicDisc

Tired of inserting a key disk whenever you want to play a game? Worried that your kids might destroy your CD drive? Or just annoyed with Windows' Autoplay spinning up your CD drive each time you insert a new CD*. One solution: install a virtual CD drive. There's plenty available; I chose MagicDisc because the instructions were straightforward.

*Of course, you can also disable Autoplay. Here's one way:

1. Run gpedit.msc.
2. In Group Policy dialog, select Computer Configuration / Administrative Templates / System / Turn off Autoplay.
3. In Turn off Autoplay dialog, select Enabled radio button.