From a JDBC Developer at Microsoft

MS SQL Server 2005 JDBC Driver and SSL

Great news! We just released JDBC v1.2 RTW with SSL support. Here are some tips to help you during development.

1) We have new connection properties for SSL: encrypt, trustServerCertificate, hostNameInCertificate, trustStore, trustStorePassword.

2) By default SSL encryption is OFF. In such scenarios driver does best effort to encrypt ONLY the login packet.

3) If you want to force encryption on the client side, set connection property "encrypt = true". You can also force encryption on the server side. Information on how to do this below.

http://technet.microsoft.com/en-us/library/ms189067.aspx

If server enforces encryption, driver will connect with full encryption regardless of your client encrypt setting (see #4 below for exceptions)

If client enforces encryption, driver will NOT connect to a server that does not support encryption. This is only possible if you are using SQL Server 2000.

4) Driver does not support SSL if your JVM's default JSSE provider does not support strong hash (i.e. hash size > 4K). This behavior is common among 1.4 VMs. If you are using one of these VMs, you will fail to connect to a server that forces encryption when you don't enforce encryption on the client side. One option is to switch to a newer VM version or a 1.4 VM that does support strong hash. Specifying "encrypt=true" on the connection string is also a solution. Be warned that in this case the connection will still fail if your certificate hash is strong.

5) By default, driver ALWAYS validates the Server certificate with SSL encryption. If you don't want the driver to validate Server certificate, you can set "trustServerCertificate=false".

6) Detailed information on MS SQL Server 2005 JDBC Driver v1.2 SSL support available on MSDN.

http://msdn2.microsoft.com/en-us/library/bb879935.aspx

Yesim

Microsoft SQL Server 2005 JDBC Driver v1.2 is ALIVE!

Long time, no see:-) Well, I'm back with great news! After a full year of hard-core development, JDBC v1.2 RTW is now publicly available. Here is why you should switch to v1.2 RTW (or start using it!)

1) It supports SSL encryption!

2) It's faster! It's robust when dealing with large data! All wonderful results of our new feature, responseBuffering=adaptive.

Give it a try. Let us know what you think. We love to hear from you!

http://msdn2.microsoft.com/en-us/data/aa937724.aspx

Yesim

Retrieving Date/Time/Timestamp objects using JDBC

   Finally, we can talk about how Microsoft SQL Server 2005 JDBC driver handles Calendar objects and temporal values. Data retrieval seems like a good enough point to start. You can use ResultSet and CallableStatement getDate/getTime/getTimestamp methods to retrieve java.sql.Date, java.sql.Time and java.sql.Timestamp values from your database. There are overloaded versions of these methods where you can specify a Calendar object that should be considered during retrieval. How our JDBC driver uses this Calendar object will be the main topic of this post.

Let's start with an example. It's almost the middle of August and another rainy day embellishes the notorious Seattle summer. I think I deserve to pick a more exotic place to base my examples on:-) Suppose I'm not awfully happy about Seattle weather and I'm looking for discount tickets to a sunnier place. America/Costa_Rica will do. So, online I find this amazing one-way flight from Seattle to Costa Rica. The flight company is based in Costa Rica and hence stores all flight times in its database in its local timezone. However, when I query their website for flight information they have to provide me with Seattle time for departure. They are storing their data in SQL Server 2005 and they use our JDBC driver to operate on their server. The departure time is stored in a datetime column. Here is how they would get the right information for me from their database using ResultSet.

  Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("America/Costa_Rica"));
  ResultSet rs = stmt.executeQuery("Select * from flights");
  rs.next();
  System.out.println("Departure time(String)    : " + rs.getString("departureTime"));
  System.out.println("Departure time(Timestamp) : " + rs.getTimestamp("departureTime", cal));

When we retrieve the value of departureTime column as a String we are receiving the data stored in the server formatted in our timezone. This data is not what we are looking for. For one, we know the data is stored in Costa Rican time. For another, with this approach we cannot even be sure if we are getting the departure time reported in Costa Rican timezone due to formatting in our local timezone. What we really need to do here is to provide a Calendar argument that specifies which timezone the data was stored in. Then, our driver will retrieve the data from the database and convert it to my local timezone, PST. There is currently an hour difference between Costa Rica and Seattle and the departure time for me should be an hour earlier than what is actually stored in the database. Here are the results.

  Departure time(String)    : 2008-08-10 21:28:08.963
  Departure time(Timestamp) : 2008-08-10 20:28:08.963  //departure time in my timezone, PST

If I hurry, I can still make it to the airport on time. However, I would feel bad if I did not touch upon normalization issues with Date and Time. Quickly then. What do you think would be the results of calling getDate and getTime on this column with the same Calendar argument?

  System.out.println("Departure time(Date) : " + rs.getDate("departureTime", cal)); //prints 2008-08-09
  System.out.println("Departure time(Time) : " + rs.getTime("departureTime", cal)); //prints 19:28:08

According to getDate result, I already missed this plane! Surprising, isn't it? Well, not if you heard about normalization. Normalization is essentially setting the unused components of Date and Time objects to the epoch in UTC, 1/1/1970 00:00:00.0.  JDBC 3.0 spec holds the JDBC driver responsible for normalizing the values returned by getDate/getTime. In a recent QFE we changed our interpretation of when normalization should take place. Now, our driver performs normalization in the provided timezone as opposed to the application's local timezone. So, how exactly do we end up with a date that's a day earlier? Well, if you load up the milliseconds stored in the server in a Calendar with the given timezone, i.e. that of Costa Rica and then set the Hour, Minute, Second and Millisecond fields of Calendar to 0 as in the epoch, you will receive a date that is a day earlier. Hence, what we are converting to date here is no longer "2008-08-10 21:28:08.963" but it is "2008-08-10 00:00:00.000".  You can simulate this result with the following code.

  cal.clear();
  cal.setTimeInMillis(rs.getTimestamp("departureTime");
  cal.set(Calendar.HOUR_OF_DAY, 0);
  cal.set(Calendar.MINUTE, 0);
  cal.set(Calendar.SECOND, 0);
  cal.set(Calendar.MILLISECOND, 0);
  System.out.println("Converted date : " + new java.sql.Date(cal.getTimeInMillis()));

Similarly, for getTime the normalization is again implemented in the supplied Costa Rican timezone and you are actually converting "1970-01-01 21:28:08.963" to Time value. It just so happens that on this date the time difference between Seattle and Costa Rica is 2 hours instead of 1 because Seattle observes DST while Costa Rica doesn't. Not bad is it? Off I go now. If you like to daydream about sunnier places in the world, and want to know what time it is somewhere else, you can always visit the website below. It's a treasure if you are working on timezones.

http://www.timeanddate.com/

Comparing java.sql.Date values

   Before we move onto how Microsoft SQL Server 2005 JDBC driver handles Calendar arguments, let's take a look at Date, Time and Timestamp objects to make sure we are on the same page. Date, Time and Timestamp are the three temporal classes contained in java.sql package. All three extend java.util.Date base class and hence store time as milliseconds since the epoch in UTC. Of these three, Timestamp is the most similar to java.util.Date class as it contains all the functionality of java.util.Date while adding a nanoseconds field on top. Date and Time, on the other hand, use only related components of the java.util.Date class. Let's take Date as an example. Suppose you issue the following code snippet.

  java.sql.Date dateA = java.sql.Date.valueOf("2008-06-18");
  System.out.println("Date value : " + dateA); //prints out 2008-06-18
  System.out.println("Converted to Timestamp : " + new Timestamp(dateA.getTime()));

You will receive a Date value of "2008-06-18". The Timestamp value will contain the same date value with the time portions set to midnight, "2008-06-18 00:00:00.0". What happens if the conversion proceeds in the other direction? Take a look at the following.

  Timestamp ts = Timestamp.valueOf("2008-06-18 21:28:08.963");
  java.sql.Date dateB = new java.sql.Date(ts.getTime());
  System.out.println("Converted to Date : " + dateB);  //prints out 2008-06-18
  System.out.println("Equality with the first Date : " + dateA.equals(dateB));  //prints out false

Both dateA and dateB represent and print out the same dates. Why, then, do they compare as unequal? Well, the reason again lies in how time is stored in a Date object.  When you construct the Date object you are giving it a milliseconds since the epoch value. These milliseconds are internally stored without being normalized. Since we did not normalize the time components of this object either, i.e. subtract enough millis so that the time field is set to midnight, the resulting Date object ends up storing time information as well.  When it comes to testing for equality, the millis stored by both objects are compared and dateB is found to contain more millis than dateA since it also stores the time information.  You might wonder why Date constructor does not normalize the time components. I did. If this object was truly intended to carry only Date information, why would you let it store time information as well? If you also need the time information, why would you not operate on Timestamp instead? Well, this is a very simplistic approach to this issue and things get much more complicated when it comes to converting date values between timezones. Then, you realize you need the time information as well.

How would one compare two date values then? It depends on how you interpret the Date object. If you would like to ignore the time components as intended by Java, then you can use a Calendar object to retrieve the date components and compare them individually as in the following example.

     Calendar calA = Calendar.getInstance();
  calA.setTimeInMillis(dateA.getTime());
  Calendar calB = Calendar.getInstance();
  calB.setTimeInMillis(dateB.getTime());
  System.out.println("Compare day fields : " + (calA.get(Calendar.DAY_OF_MONTH) ==  calB.get(Calendar.DAY_OF_MONTH)));  //prints true

If you need the time components for your comparison you can still use the equals method and I would love to hear your reason:-)

Understanding Java Calendar Object

   This is the first of a series of posts I will dedicate to Calendar object in Java and how it interacts with JDBC API. Before moving onto JDBC API let's make sure we get the Calendar basics right. Java.util  package contains 4 temporal classes: Calendar, Date, Timezone and Gregorian Calendar. Originally, Date class contained methods that performed conversions between date values and year/month/day/hour/minute/second values. However, because Date is not timezone aware, converting Date values between timezones proved to be erroneous. Today these methods are deprecated and replaced by corresponding Calendar methods. Indeed, if you ever want to manipulate time in Java across timezones different than your local timezone, I suggest sticking to Calendar objects.

Let's start with a simple example to convey how calendar conversions are carried out. Suppose I live in Seattle (which I do:-) and suppose I have an aunt who lives in New York. My local time is "2008-06-18 21:28:08.963" and I want to know if it is too late to call my aunt. I will use the Calendar object to calculate what time it is in New York.

      SimpleDateFormat _formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
  java.util.Date date = _formatter.parse("2008-06-18 21:28:08.963");
  System.out.println("Seattle time : " + date);

Notice that I used a SimpleDateFormat object to create the java.util.Date value. SimpleDateFormat is a locale sensitive class that converts between date and text values. You can specify the locale while constructing this class. Since locale was omitted in this example, SimpleDateFormat assumes the default locale, PST for Seattle.

Now that I have a date that corresponds to my local time I can convert it to New York time. It should be as simple as the following.

  Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("America/New_York "));
  cal.setTimeInMillis(date.getTime());
  System.out.println("New York time : " + cal.getTime());

If you actually run this code snippet you will realize that the times printed are identical. Why would that be? The problem lies in how time is represented by a Date object. When you use a formatter to parse a textual date value (as we did above), you are actually retrieving the total number of milliseconds that passed since the epoch in UTC, midnight of January 1st 1970. Both getTime and setTimeInMillis operations are dealing with these milliseconds, also called fasttime. When you use toString() method of Date object you are essentially printing out the time in your local timezone when given number of milliseconds have passed since UTC. Hence, all cal.getTime() represents here is the total number of milliseconds that passed since UTC epoch (in New York) when X amount of millseconds passed since UTC epoch in Seattle. The answer, of course, would be X regardless of timezones.

Here is one simple solution to this conversion problem. Instead of dealing with milliseconds and timezone unaware Date object, retrieve the date/time values directly.

System.out.println("New York time : " + cal.get(Calendar.YEAR) + "-" + (1 + cal.get(Calendar.MONTH)) + "-" + cal.get(Calendar.DAY_OF_MONTH) + " " + cal.get(Calendar.HOUR_OF_DAY) + ":" + cal.get(Calendar.MINUTE) + ":" + cal.get(Calendar.SECOND) + "." + cal.get(Calendar.MILLISECOND));

You will receive "2008-6-19 0:28:8.893". 3 hours later than my local time as expected. Make sure to increment the month field by 1 since Calendar month is 0 based. 

A more devious solution would be to print out the time in New York using our local timezone. This solution could be useful only for printing/playing purposes. It alters the underlying milliseconds since we are now calculating a completely different time in our local timezone. Partial code snippet below.

  Calendar cal2 = Calendar.getInstance();
  cal2.set(Calendar.YEAR,     cal.get(Calendar.YEAR));
  cal2.set(Calendar.MONTH,    cal.get(Calendar.MONTH));
  cal2.set(Calendar.DAY_OF_MONTH,   cal.get(Calendar.DATE));
  ...
  System.out.println("New York time : " + cal2.getTime());

It is indeed too late to call an aunt. Please join us next time for how to compare java.sql.date and java.sql.time values.

Using Calendars with JDBC API

   For me timezone handling has been one of the harder concepts in developing a JDBC driver for SQL Server. The main problem here is that SQL Server, for better or worse, is timezone unaware. JDBC API, on the other hand, requires a driver to understand and respond correctly to timezones. What "correctly" means, now that's a difficult question. We recently released our second QFE on Calendar getters and setters and we hope to have come much closer to understanding how our customers want us to handle timezones. I will be posting explanations regarding this QFE and how Microsoft SQL Server 2005 JDBC driver handles calendar arguments. In the meantime you can request this QFE using the KB article number 921208. As always we would love to receive feedback from you.

For feedback on the driver: http://connect.microsoft.com/sqlserver

Microsoft SQL Server 2005 JDBC Driver 1.1 CTP II is live!

   Today we released JDBC Driver 1.1 CTP 2. The release contains bug fixes and two new features since v1.0, Database Mirroring and Integrated Authentication. I will be posting an example to demonstrate how to use Database Mirroring. In the meantime, the JDBC dev team here at Microsoft would love to hear feedback from you.

      Download CTP 2: http://msdn.microsoft.com/data/ref/jdbc/default.aspx

The bug fixes for this release are mainly focused around XA Transaction behavior, Timezone and DST handling, Boolean getters, PreparedStatement executions and Statement cancellation. Following is a shortened list of the bugs we fixed for this release. 

409319 Cancelling statement execution works from any thread without closing the connection.

423881 The driver properly returns update counts from triggers.

433170 Several aspects of XA transaction behavior have been corrected.

450355 getBoolean on a CHAR/VARCHAR/NVARCHAR column with a value of "1" returns true instead of false.

430837 Connections obtained from SQLServerXADataSource may be used in local transactions while they are outside the scope of an XA transaction.

425030 The driver correctly handles setting and retrieving dates, times and timestamps with time zones other than the system default.

426389 Calling Statement.execute no longer causes an SQLException with the message "sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure" for UPDATE WHERE queries when the selectMethod=cursor connection property is set.

436718 Calling PreparedStatement.execute no longer causes an SQLException with the message "sp_cursoropen/sp_cursorprepare: The statement parameter can only be a batch or a stored procedure with a single select, without FOR BROWSE, COMPUTE BY, or variable assignments" for INSERT INTO queries when the selectMethod=cursor connection property is set.

 

Breaking change in JVM 1.5 API for Big Decimal class

   If you are porting your JDBC application for SQL Server from 1.4 JVM to 1.5 JVM you might run into problems caused by the breaking change in the toString method of Big Decimal class. Namely, the following code prints out different values across different JVM versions.

String str = new BigDecimal("1E10").toString();

System.out.println("String is " + str);

//With 1.4 prints 'String is 10000000000'

//With 1.5 prints 'String is 1E+10

 

Neither SQL Server 2000 nor SQL Server 2005 converts string representations of numeric values that contain the exponential into numeric types. Accordingly, the following code would succeed when executed against 1.4 while failing against 1.5.

 

ResultSet rs = stmt.executeQuery("Select * from temp");

rs.next();

rs.updateString("col1", str);

rs.updateRow();

 

Exception thrown against 1.5 would be like the following:

 

com.microsoft.sqlserver.jdbc.SQLServerException:Error converting data type nvarchar to numeric.

The work around for this issue is simple. 1.5 API introduces a new BigDecimal method, toPlainString that is guaranteed to exhibit the same behavior as the toString method in 1.4. You can find more information about this change in 1.5 in Sun's bug database.

http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6298816

Initiation

   Welcome! I'm one of the few developers at Microsoft who indulge in Java code 95% of their time at work. For the past 7 months I've had the privilege of being part of a very special team at Microsoft, Microsoft SQL Server 2005 JDBC Driver development team. So far I've been highly enjoying this experience and I'm looking forward to releasing ever stronger versions of our JDBC driver.

I'm not a JDBC veteran and over the course of past 7 months I've discovered numerous pieces of useful information on JDBC and Java. I'm intending to post my discoveries and I hope they will be as helpful for you as they are for me. As a team we very much like to hear from JDBC users so please feel free to post positive feedback about my blog and our driver.