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.