JDBC getting started with the 2005 JDBC driver for Sql Server 2000 and Sql server 2005
If you had told me a year ago that I would be working on a JDBC driver I would have honestly thought you were kidding. If you had suggested a ship date concurrent with Sql Server 2005 (within a reasonable window) I would have laughed out loud. This is turning out to be a tremendously interesting project in so many ways. I find that I am having a very hard time blogging about this project in general terms, so I am just going to start at the beginning.
Getting started with the new 2005 JDBC driver,
download the beta driver from:
https://www.microsoft.com/sql/downloads/2005/jdbc.mspx
sample code:
import java.sql.*;
public class test{
public static void main(String[] args) {
try{
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection connection1 = java.sql.DriverManager.getConnection("jdbc:sqlserver://server;user=myuser;password=mypassword");
System.out.println("Connected!");
Statement statement1 = connection1.createStatement();
ResultSet resultset1 = statement1.executeQuery("select * from foo");
while(resultset1.next() ){
System.out.println("Foo result = "+resultset1.getObject(1) );
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}
JDBC 3.0 is a well stablished standard, there are no surprises in the code itself. More interesting however is what is going on under the covers, and more importantly what _should be going on_. This is where we are looking for feedback, let us know what you think:
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
You may have a sense of deja vue looking at the name of the new driver, yes you are not mistaken we just switched the name of the old driver around. Confusing? Well maybe a little bit, but have you tried running names through legal? Let me just make it very clear that this driver is not in any way related to the 2000 driver.
Connection connection1 = java.sql.DriverManager.getConnection("jdbc:sqlserver:/server;user=myuser;password=mypassword");
This is actually a lot more interesting than it looks (especially considering that this is a non pooled connection). I guess a good starting point would be asking the following question: What is a connection string url? Well, it is url-looking thanks to that “:/” but what it is really for is to allow you to provide information to connect to your server. Lets go over some of the confusing concepts here:
Duplicate Keywords:
What happens when you enter more than one "user=" keyword value pairs? In ado.net and in oledb the last keyword wins, in odbc the first keyword can win (driver dependent). This has been historically done so that you can override an existing connection string without having to parse it, but it is confusing and bug prone. I would like to break with tradition and throw an exception on duplicates, what do you think?
Synonyms/Aliases:
Another source of confusion, is it database? databaseName? Both? In what order? Synonyms may be necessary for backwards compatibility but this is a 1.0 driver. The current beta has an interesting bug with synonyms and I see no reason to keep them, any thoughts?
Handling unknown keywords:
Generic drivers like Oledb or Odbc drivers need to be able to ignore any connection string keywords that they don’t understand. This is not true for a type four driver, I don’t see a good reason to allow these and many reasons to avoid them, we probably need to throw an exception here.
Case sensitivity:
Is database the same as Database? Well it turns out that String manipulation in java is not particularly performant, it is well worth enforcing case sensitivity on connection string keywords to avoid the performance penalty. As long as we start throwing on unknown keywords (see above) this will not be a major customer pain point.
Separators:
Currently we allow “;”, “/”, “:” and a few more separators between connection string keywords, I am not sure that this is a clean model. We should be able to stick to one separator (“;”) plus the (“:”) for port numbers, what do you think?
Escape characters:
This is a tough one, there is no doubt that we need them, what do we do with names with separators (see above) embedded in them? What about empty as in empty database or empty password? The simplest way to handle this would be to use something like “ ‘ “ as a separator and force you to escape any single quotes that you are going to use in your connection string, ex: user=’O’’neil’. This is not an ideal solution.
Statement statement1 = connection1.createStatement();
I will definitely have to have a separate blog about this, there is just too much going on under the covers for a quick overview. I am just going to point out that currently by default we are using a pseudo firehose cursor that may not scale well. I am personally not a great fan of server side cursors but a) JDBC requires them and b) the JDBC api seems to be geared towards using cursors. We may have to make some changes in this space.
ResultSet resultset1 = statement1.executeQuery("select * from foo");
Finally a straightforward code statement, you executeQuery and you get a resultset.
Rambling out, the information in this blog is provided "AS IS" and confers no rights.
Comments
- Anonymous
August 01, 2005
The comment has been removed - Anonymous
August 01, 2005
I took a quick look at the wikipedia post, you are a brave soul to put that up and I wish you all the best laugh.
As far as MDAC here is a great place to start:
"The end of the MDAC redistributable"
http://blogs.msdn.com/dataaccess/archive/2005/05/23/421039.aspx
To see a little bit of what is coming next:
http://blogs.msdn.com/dataaccess/archive/2005/07/29/445147.aspx
good luck - Anonymous
August 10, 2005
Thanks! I very much appreciate this. I gotta say, you MS database guys rock :-) Just finished reading the history of SQL Server in Inside Microsoft SQL Server 2000 and you all did most impressive work :-) - Anonymous
June 04, 2006
i like your website very much but please do get us more information about it