Several JDBC team members attended the 2009 PASS Summit Unite conference November 2-5, 2009 in Seattle. During the “Developing Java Applications Against SQL Server 2008” session, I showed a demo illustrating some of the features of our 2.0 release (driver auto-loading, integrated authentication, sending & retrieving xml data using SQLXML, and tracing). I’ve provided snippets of the demo code for the updating and retrieving of data using SQLXML and SAX (Simple API for XML) here on the blog for your reference.
The demo used Microsoft SQL Server JDBC Driver 2.0 with SQL Server 2008 and the AdventureWorks2008 database. The table used is called Person.Person and represented customer information for clients of AdventureWorks. One of the columns in this table is called Demographics and stores survey results in xml format.
The first part of the demonstration illustrated updating the Demographics column using SAXResult object with SQLXML and xml data stored in a file. You can assume that the helper method takeSurvey() returns the name of an xml file containing xml data representing survey results.
Connection conn = null;
PreparedStatement pstmt = null;
//Generate survey data for the customer
//and store it in an xml file
String surveyFileName = takeSurvey();
String firstName = "Sarah";
String lastName = "Marshall";
try
{
conn = DriverManager.getConnection(url);
//Create prepared statement that will update
//the xml data
pstmt = conn.prepareStatement(
"UPDATE [Person].[Person] " +
"SET [Demographics] = ? " +
"WHERE [FirstName] = ? AND [LastName] = ?");
//Create SQLXML object from the connection
SQLXML sqlxml = conn.createSQLXML();
//Set the SAX Result in the SQLXML
SAXResult sxResult =
sqlxml.setResult(SAXResult.class);
//Create a transformer to send the File contents
//to the SAX Result
SAXTransformerFactory stFactory =
(SAXTransformerFactory)
TransformerFactory.newInstance();
Transformer transformer =
stFactory.newTransformer();
//Send the File contents to the SAX Result
transformer.transform(new StreamSource(
new File(surveyFileName)), sxResult);
//Call the PreparedStatement setSQLXML API
//method to set the value
pstmt.setSQLXML(1, sqlxml);
pstmt.setString(2, firstName);
pstmt.setString(3, lastName);
//Execute prepared statement
pstmt.executeUpdate();
}
finally
{
//Clean up
if(pstmt != null)
{
try { pstmt.close(); }
catch(SQLException e) {}
}
if(conn != null)
{
try { conn.close(); }
catch(SQLException e) {}
}
}
The second part of the demo showed how to retrieve xml results from the server and parse those results using SAX. In order to use SAX to parse xml data, you need to implement a ContentHandler. Here is some sample code for a SurveyContentHandler used in this demo. It references a Survey object, which encapsulates the survey result xml data. I’ve included that code as well.
Survey class:
public class Survey
{
private String gender;
private Date birthDate;
private boolean isEmpty = false;
public Survey()
{
}
public String getGender()
{
return this.gender;
}
public Date getBirthDate()
{
return this.birthDate;
}
public void setGender(String s)
{
this.gender = s;
}
public void setBirthDate(Date d)
{
this.birthDate = d;
}
}
SurveyContentHandler class:
//By extending DefaultHandler we do not have to implement all the
//methods of ContentHandler interface,
//we can just implement the ones that surround our pertinent data
//(startElement, characters)
public class SurveyContentHandler extends DefaultHandler
{
//data members
private Survey survey = null;
//keep track of element while parsing
private String currentElement = null;
public SurveyContentHandler()
{
}
//Survey accessor method
public Survey getSurvey()
{
return this.survey;
}
//Called when SAX parser encounters an open element tag.
//Store the name of the element so
//when characters() is called, we know where in the XML
//we are and what data we need to store
@Override
public void startElement(String uri, String localName,
String qName, Attributes atts) throws SAXException
{
if((localName != null
&& localName.equals("IndividualSurvey"))
|| (qName != null
&& qName.equals("IndividualSurvey")))
{
this.survey = new Survey();
currentElement = "";
}
else if((localName != null
&& localName.equals("Gender"))
|| (qName != null
&& qName.equals("Gender")))
{
currentElement = "Gender";
}
else if((localName != null
&& localName.equals("BirthDate"))
|| (qName != null
&& qName.equals("BirthDate")))
{
currentElement = "BirthDate";
}
else if((localName != null
&& localName.equals("TotalPurchaseYTD"))
|| (qName != null
&& qName.equals("TotalPurchaseYTD")))
{
currentElement = "TotalPurchaseYTD";
}
else
{
currentElement = "";
}
if(!currentElement.equals(""))
System.out.println("About to process tag: " + currentElement);
}
//Called when SAX parser encounters an data between element
//tags. Store the data based on the element,
//set by startElement()
@Override
public void characters(char[] ch, int start, int length)
throws SAXException
{
String value = new String(ch, start, length);
if(currentElement != null
&& currentElement.equals("Gender"))
{
survey.setGender(value);
}
else if(currentElement != null
&& currentElement.equals("BirthDate"))
{
Date date = null;
try
{
//Use a SimpleDateFormat object to parse
//the value (formatter object
//not included in code snippets)
date = (Date)formatter.parse(
value.substring(0, length-1));
}
catch(ParseException pe)
{
System.err.println(
"Problem parsing BirthDate"
+ pe.getMessage());
}
survey.setBirthDate(date);
}
else if(currentElement != null
&& !currentElement.equals(""))
{
System.out.println("Processing " +
currentElement + ", data: " +
value);
}
}
}
Now that we have the utilities we need, here is the code sample for parsing the survey results for 50 customers in the Person.Person table.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
conn = DriverManager.getConnection(url);
//make execution faster for demo purposes
String selectQuery = "SELECT TOP(50) * FROM " +
"[Person].[Person] " +
"ORDER BY [LastName]";
//Create a statement
stmt = conn.createStatement();
//Generate a ResultSet by executing the query
rs = stmt.executeQuery(selectQuery);
//Store the survey objects returned for each customer
//in an ArrayList
ArrayList<Survey> results = new ArrayList<Survey>();
//Iterate through the rows returned in the result set
while(rs.next())
{
ResultSetMetaData rsmd = rs.getMetaData();
if(rsmd != null)
{
int numColumns = rsmd.getColumnCount();
for(int col = 1; col <= numColumns; col++)
{
String columnName =
rsmd.getColumnName(col);
String columnType =
rsmd.getColumnTypeName(col);
if(columnType.equals("xml") &&
columnName.equals(
"Demographics"))
{
//Get the xml data
//representing a survey
SQLXML custSurvey =
rs.getSQLXML(columnName);
//Create instance of
//custom content handler
SurveyContentHandler
scHandler =
new SurveyContentHandler();
//Get the SAXSource from
//SQLXML and then XMLReader
SAXSource sxSource =
custSurvey.getSource(
SAXSource.class);
XMLReader xmlReader =
sxSource.getXMLReader();
//Set the content handler
xmlReader.setContentHandler(
scHandler);
//Parse the XML – calls into
//the methods of the
//ContentHandler
xmlReader.parse(
sxSource.
getInputSource());
//Extract the survey details
//we collected while parsing
Survey survey =
scHandler.getSurvey();
//Add the customer to our list
//of customers
results.add(survey);
}
}
}
}
}
finally
{
//Clean up
if(rs!= null)
{
try { rs.close(); }
catch(SQLException se) {}
}
if(stmt != null)
{
try { stmt.close(); }
catch(SQLException se) {}
}
if(conn != null)
{
try { conn.close(); }
catch(SQLException se) {}
}
}
With an ArrayList of Survey objects, you could now process that list and generate some interesting statistics (left as an exercise for the reader).
--Erin Hardiman [SQL Server]
This post is provided “AS IS” and confers no express or implied warranties or rights.
Dear SQL Server developers and users:
It is this time of the year when we, in the SQL Connectivity product team, take a step back, review the priorities and goals for the long term and identify areas that will benefit from investments.
We view you and your organization as a key stakeholder in this process and would like to gather your inputs in this survey, which should take no more than 5-10 minutes and a few other surveys that we will conduct in the next few months. The feedback you provide is very valuable and be rest assured that each and every response will be read and will provide the background for some of the key decisions that we will make that will benefit our user community - developers, DBAs, and all those who use SQL Server or are looking for ways to expand the scope of the power of SQL Server within your organizations.
This survey will be open for your submissions until October 21, 2009 and can be found here.
Thanks
--Tres London [SQL Server]
For those interested in Spatial types, we'd like to know if Windows only support would work. It would be some extra work to ensure that they are work across all platforms, which is why we are asking. Also, do you use JPOX spatial types in your applications? Thanks.
--Tres London [SQL Server]
This post is provided 'as is' and confers no express or implied warranties or rights.
We are excited to announce the newest release of the Microsoft SQL Server JDBC Driver 2.0!
This version of the JDBC driver supports features introduced in the JDBC 4.0 API, including:
· national character set data types: NCHAR, NVARCHAR, LONGNVARCHAR, NCLOB
· SQLXML data type
· Wrapper interface to access SQL Server JDBC Driver specific methods
· client info properties
· new database metadata methods
· LOB creator methods
The 2.0 driver also adds:
· default adaptive response buffering behavior
· support for SQL Server 2008 collations
· enhanced tracing, including public method entry and exit traces
· performance improvements and bug fixes
Please feel free to download a copy and see for yourself!
If you've ever encountered this error with the Microsoft SQL Server JDBC Driver, you may be left scratching your head, wondering a) what it means, and b) why it is occurring. I'll tackle these in order... But if you're not interested in the details, skip the next two paragraphs.
Transactions, excluding XA transactions (which are different beasts entirely), are scoped to a connection. Once a connection is put in a transaction, either through a call to Connection.setAutoCommit(false) followed by some DDL or DML, or through execution of a BEGIN TRANSACTION statement, everything done on that connection should happen within that transaction until it is committed or rolled back. SQL Server forces drivers like the JDBC driver to honor that contract by passing a transaction ID back to the driver when the transaction is started and requiring the driver to pass that ID back to the server when executing subsequent statements. If the driver continues to use a transaction ID after the transaction has been committed or rolled back, that's when you get the "failed to resume the transaction" error.
So how does the driver end up using a transaction ID for a transaction that is no longer active? SQL Server sends "transaction started" and "transaction rolled back/committed" messages to the driver "in band" with a query's execution results (update counts, result sets, errors). The driver can't "see" the messages until the results that precede them have been processed. So once a transaction has been started, if a statement's execution causes a commit or rollback, the driver will think the transaction is still active until the statement's results have been processed. Now that you understand what’s going on and why, the next question is: who should be processing those results? You guessed it: the app.
How then to avoid the "failed to resume the transaction" error (and welcome back to those folks who didn't enjoy the detour above...) :
1) Always process ALL execution results before executing another statement on the same connection – especially on another thread. This is generally a good idea anyway, so that you don't miss other kinds of errors. If your statement does anything more than a simple INSERT, UPDATE or DELETE, use Statement.execute() to execute it and Statement.getMoreResults() to iterate through the results.
2) If you truly aren’t interested in the execution results, feel free to ignore them by reexecuting the statement or closing it. Just dropping the app’s reference to a Statement object doesn’t count as closing it. You need to call Statement.close().
3) Avoid mixing JDBC API transaction control (Connection methods: setAutoCommit, commit, rollback) with T-SQL transaction control statements. If possible, avoid T-SQL transaction control, and nested transaction statements in particular, altogether. If a stored procedure does an unexpected ROLLBACK inside a nested transaction, it can be difficult to locate.
4) Understand that SQL Server sometimes rolls back transactions for its own reasons. One of those reasons is type conversion errors. For example: INSERT INTO myTable VALUES ('bar') will roll back the transaction if the table column is an INTEGER, because SQL Server does not know how to convert the literal value 'bar' to an INTEGER. This happens often with DATETIME data, when the server is asked to convert from a textual value that it doesn't recognize as a timestamp.
--David Olix [SQL Server]
This post is provided 'as is' and confers no express or implied warranties or rights.
The SQL Server JDBC team is happy to announce the release of the January 2009 community technology preview. This beta release is feature complete, which includes, but not limited to, JDBC 4.0 SQLXML data type; National Character set; driver autoloading; new metadata methods; and enhanced tracing. You can download the beta release at http://msdn.microsoft.com/data/jdbc.
Jimmy Wu
SQL Server JDBC Team
We've heard from some customers that they are having difficulty getting Database Mirroring to work with our JDBC driver. Recently, Adam from our counterpart customer service team posted a great blog on this topic. Please refer to his blog regarding a couple of gotchas with the v1.2 driver.
http://blogs.msdn.com/psssql/archive/2008/12/31/sql-2005-jdbc-driver-and-database-mirroring.aspx
Jimmy Wu
SQL Server JDBC Team
Since the release of SQL Server 2008, we have heard from customers looking for a JDBC driver that supports SQL Server 2008. I would like to use this post to ask you, our customers, which features of SQL Server 2008 do you need supported through the JDBC driver. We want to make sure we are delivering features which adds the most value to our customers and your feedback will help us help you.
Here are some features to get things started:
- Date/Time/Datetime2/DatetimeOffset data type
- Sparse Columns
- Table-Value Parameters
- T-SQL MERGE command
- Filestream data type
- Spatial data type
- HierarchyID data type
Please add any you don't see on the list. If there are multiple features you need, please rank them in order of priority.
Thanks,
Jimmy Wu
SQL Server JDBC Team
In the previous post "Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver",I talked about how the current JDBC driver can interop with the new Date/Time/DateTime2/DatetimeOffset data types. This time around, I will describe how to interop with SQL Server 2008 Filestream.
Please refer to "SQL Server 2008 Books Online" for detailed information about Filestream.
To demostrate how the v1.2 driver can retrieve a resultset containing a filestream column, I used the following T-SQL query to create the test database and table:
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
FILENAME = 'c:\data\filestream1')
LOG ON ( NAME = Archlog1,
FILENAME = 'c:\data\archlog1.ldf')
GO
CREATE TABLE foo
(
id int NOT NULL PRIMARY KEY,
Photo varbinary(max) FILESTREAM NULL,
MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID()
)
GO
Let's assume that the table has already been populated with some data. Here is the code snippet that I used to retrieve the column values:
strCmd = "select * from foo";
rs = stmt.executeQuery(strCmd);
if (rs.next())
{
ResultSetMetaData rsmd = rs.getMetaData();
if (null != rsmd)
{
String x = rsmd.getColumnTypeName(2);
String name = rsmd.getColumnName(2);
System.out.println("Column " + name + " is data type: " + x);
int i = rsmd.getColumnType(2);
switch (i)
{
case (java.sql.Types.LONGVARBINARY):
System.out.println("Column is of JDBC type LONGVARBINARY.");
InputStream bStream = rs.getBinaryStream(2);
byte[] y = new byte[1024];
if (null != bStream)
{
// write the retrieved filestream data as a file.
System.out.println("Write the filestream data out as a file.");
FileOutputStream fs = new FileOutputStream("c:\\myFile");
int read = bStream.read(y);
while (read != -1)
{
fs.write(y);
fs.flush();
read = bStream.read(y);
}
fs.close();
}
break;
default:
throw new Exception("Unexpected data type found: " + String.valueOf(i) + ". Expected LONGVARBINARY.");
}
}
// now, update the data with a new file content.
System.out.print("Now, let's update the filestream data.");
FileInputStream iStream = new FileInputStream("c:\\testFile.xml");
rs.updateBinaryStream(2, iStream, -1);
rs.updateRow();
iStream.close();
}
rs.close();
When executed as a console application, you'll get the following output:
Column Photo is data type: varbinary
Column is of JDBC type LONGVARBINARY.
Write the filestream data out as a file.
Now, let's update the filestream data.
As you've probably already noticed, SQL Server 2008 Filestream is simply a LONGVARBINARY to the v1.2 JDBC driver. This means you can operate on a Filestream like any other LONGVARBINARY, though I recommend that you stick with binaryStream to minize the amount of buffering the driver has to do.
Jimmy Wu
SQL Server JDBC Team
Since SQL Server 2008 release over the summer, people have been asking, "So, does the existing JDBC driver work with SQL Server 2008?" or "When will there be a SQL Server 2008 JDBC driver?".
There are many things to consider with a general questions like these. What features of SQL Server 2008 are you truly looking forward to leveraging and can the features be used through existing drivers?
Today, I like to let people know that the existing v1.2 JDBC driver works with SQL Server 2008 as a downlevel client. This means that you will be able to connect to a SQL Server 2008 instance and execute queries/updates on majority of the SQL Server 2008 data types.
This is post, I will focus on how you can retrieve the new Date/Time/Datetime2/DatetimeOffset data types introduced in SQL Server 2008 with existing v1.2 driver.
The first question you may ask, "So what is the metadata type of these new data types?"
To ensure full data fidelity, SQL Server 2008 has chosen to return the values for these data type columns as "nvarchar". This means that all older SQL Server clients can operate on these new data types as String and it is up to the application layer to parser and understand these String values. The v1.2 driver is no different.
To demostrate how the v1.2 driver can retrieve a resultset containing these data types, I have created a table containing 4 columns (one for each type) and populated the table with some sample data.
Here is the T-SQL script that I used to create my table:
CREATE TABLE bar
(
[Date] date,
[Time] time,
[StartDate] datetime2,
[HireDate] datetimeoffset
)
go
insert bar values
(
'2008-01-01',
'13:59:00.1234567',
'2008-02-02T20:01:59.123456789',
'2008-04-01T10:05:02+08:00'
)
go
Here is the code snippet that I used to retrieve the column values:
String strCmd = "select * from bar";
ResultSet rs = stmt.executeQuery(strCmd);
if (rs.next())
{
ResultSetMetaData rsmd = rs.getMetaData();
if (null != rsmd)
{
int count = rsmd.getColumnCount();
for (int i = 1; i <= count; i++)
{
System.out.println("");
String nameType = rsmd.getColumnTypeName(i);
String name = rsmd.getColumnName(i);
System.out.println("Column " + name + " is data type: " + nameType);
String str = rs.getString(i);
System.out.println("Column " + name + ": " + str);
}
Date dt = rs.getDate(1);
System.out.println("Column 1 has value: " + dt.toString());
Time t = rs.getTime(2);
System.out.println("Column 2 has value: " + t.toString());
Timestamp ts = rs.getTimestamp(3);
System.out.println("Column 3 has value: " + ts.toString());
}
}
rs.close();
stmt.close();
The output looks like:
Column Date is data type: nvarchar
Column Date: 2008-01-01
Column Time is data type: nvarchar
Column Time: 13:59:00.1234567
Column StartDate is data type: nvarchar
Column StartDate: 2008-02-02 20:01:59.1234568
Column HireDate is data type: nvarchar
Column HireDate: 2008-04-01 10:05:02.0000000 +08:00
Column 1 has value: 2008-01-01
Column 2 has value: 13:59:00
Column 3 has value: 2008-02-02 20:01:59.1234568
Note: For the DatetimeOffset column, the v1.2 JDBC driver is unable to automatically create a Timestamp object from the String, through rs.getTimestamp() due to the Timestamp class not supporting time zone information. To leverage the Calendar or GregorianCalendar classes which do support time zone, the application will need to parse the string in order to create the Calendar object.
Jimmy Wu
SQL Server JDBC Team
What is SSL and why is my JDBC driver using it?
The v1.2 JDBC driver uses SSL (Secure Sockets Layer) to encrypt connections to SQL Server for improved security. Where it can, the v1.2 driver ALWAYS uses SSL to encrypt the login to SQL Server. For integrated auth connections, SSL provides an added layer of security. For SQL auth, where the user name and password would otherwise be sent in the clear, SSL provides an essential layer of security.
I trust that my network is secure without SSL... How do I turn off SSL encryption?
You can control whether a connection encrypts all data to and from the server after login using the ‘encrypt’ connection property. However, where it can, the v1.2 driver ALWAYS uses SSL to encrypt the login to SQL Server. You cannot disable SSL encryption of the SQL Server login.
Ok, but I upgraded to v1.2 and now I can’t connect! Why am I getting the error “The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: …”?
Odds are good that your Java SSL setup is messed up somehow.
SSL in Java is provided through the Java Secure Socket Extension (JSSE). Its reference guide (for J2SE 5) is here: http://java.sun.com/j2se/1.5.0/docs/guide/security/jsse/JSSERefGuide.html . A key aspect of JSSE is its pluggable provider model. Typically, JRE vendors supply JSSE provider implementations. There are at least two main JSSE providers available out there and they do not necessary work well together.
For SSL to work at all, it is absolutely necessary to have the JSSE providers configured correctly for the JRE you are using. There are two steps to this:
1) Look at the java.security file in your JRE installation (typically found in the jre\lib\security directory). The installed security providers are listed in that file as security.provider.x=… where ‘x’ is the priority used. For Sun JRE installations, the first priority provider should be Sun’s. E.g.: you should have the line “security.provider.1=sun.security.provider.Sun” in that file. For other JRE's, please refer to the JRE's documentation regarding their default provider name. We recommend when using the IBM JRE to specify the "com.ibm.jsse.IBMJSSEProvider" as the first security provider to use.
2) Next, make sure that the classpath points to the correct JAR files (in the jre\lib directory) for use with those providers. For Sun, the classpath should include jsse.jar. For IBM, should include ibmjsse.jar.
If either the classpath or the java.security file is not correct, SSL will not work. Not just with SQL Server, but with anything else either.
SSL works for other apps, just not with JDBC. And I’ve verified that the classpath is correct… Check certificates
If you are going to configure SQL Server to use a server certificate, then that certificate needs (or the certificate of one of its trusted issuing authorities) needs to be present in Java’s certificate store. If the certificate isn’t there, chances are that your JSSE provider will give you a nice descriptive, if sometimes cryptic, error. Configuring the client for use with SSL is covered in our docs here http://msdn2.microsoft.com/en-us/library/bb879943.aspx . Some certificates are quite large and trip up older JSSE providers. Of course there may be other VMs out there with similar problems, but we are not able to verify all of them. A smaller certificate may help in this case.
Not a configuration problem or a certificate problem. Now what.
When contacting Microsoft to help us be effective, we need to be armed with the following info:
1) The complete text of the error message. I.e. including the part after “Error: …” above.
2) A stack trace (if available).
3) A complete log (if available) containing FINEST TDS-level traces leading up to the error. In particular, the following loggers should be enabled in the logging properties:
com.microsoft.sqlserver.jdbc.TDSChannel.level = FINEST
com.microsoft.sqlserver.jdbc.TDSReader.level = FINEST
com.microsoft.sqlserver.jdbc.TDSWriter.level = FINEST
com.microsoft.sqlserver.jdbc.TDS.DATA.level = FINEST
Generating the log file with the XMLFormatter is preferred over SimpleFormatter, as it gives us more info.
4) The java.security file that was used.
5) Vendor and version of the JRE used.
6) SQL Server version
7) The connection string/connection properties used.
David Olix & Jimmy Wu
SQL Server JDBC Team
If you are using executeUpdate() to execute a query or stored procedure containing multiple SQL statements, the problem may just be that the error you're expecting wasn't the first result returned by the query. Consider a case where you use executeUpdate() to call a stored procedure containing multiple SQL statements, and the first result is an update count (from INSERT, for example), followed later by some error. Since executeUpdate() expects only a single update count, it returns the one for the INSERT statement as soon as it encounters it, leaving the error that follows it waiting to be retrieved. If you want your application to “see” that error (thrown as exception by the driver) and any subsequent results, you need to call the stored procedure using execute() and process all of the results using getMoreResults(). See this post for details.
--David Olix [SQL Server]
This post is provided 'as is' and confers no express or implied warranties or rights.
Have you ever encountered this exception from executeQuery()? If so, chances are good that your query actually did return a result set (we’ll be addressing the error message text...), but it wasn’t the first result. Look at your query or stored procedure. Does it contain multiple SQL statements? Maybe it includes DDL and DML to create and populate a temp table before the SELECT statement. If that is the case, then you really need to use execute(), not executeQuery(), to execute it. The problem is that executeQuery(), per JDBC spec, should only be used with queries whose only result is a single ResultSet. Quoting from Sun’s documentation of this method:
Throws:
SQLException - if a database access error occurs, this method is called on a closed Statement or the given SQL statement produces anything other than a single ResultSet object
Some JDBC drivers apparently just ignore and discard DDL and DML results in executeQuery() rather than throwing an exception.
For an explanation on how to use execute() and its partner, getMoreResults(), to execute complex queries, look here.
--David Olix [SQL Server]
This post is provided 'as is' and confers no express or implied warranties or rights.
Per JDBC spec, the Statement.executeUpdate() and Statement.executeQuery() methods are to be used only with queries that produce a single update count or result set, respectively. If you need to execute multiple SQL statements in a single query, perhaps through a stored procedure call, then you should use Statement.execute() to execute the query and Statement.getMoreResults() to process all of the results. However, in my not so humble opinion, the execute() and getMoreResults() methods aren’t exactly simple to use properly. For example, execute() and getMoreResults() return false when the result is an update count. But they also return false when there are no more results. Ok, so how do you tell whether you have an update count or no more results? You need to check the return value from a subsequent call to Statement.getUpdateCount() or Statement.getResultSet(). One way to process all results (including errors) from a complex query would be to use code like this:
CallableStatement cs = con.prepareCall("{call myStoredProc()}");
int resultNum = 0;
while (true)
{
boolean queryResult;
int rowsAffected;
if (1 == ++resultNum)
{
try
{
queryResult = cs.execute();
}
catch (SQLException e)
{
// Process the error
System.out.println("Result " + resultNum + " is an error: " + e.getMessage());
// When execute() throws an exception, it may just be that the first statement produced an error.
// Statements after the first one may have succeeded. Continue processing results until there
// are no more.
continue;
}
}
else
{
try
{
queryResult = cs.getMoreResults();
}
catch (SQLException e)
{
// Process the error
System.out.println("Result " + resultNum + " is an error: " + e.getMessage());
// When getMoreResults() throws an exception, it may just be that the current statement produced an error.
// Statements after that one may have succeeded. Continue processing results until there
// are no more.
continue;
}
}
if (queryResult)
{
ResultSet rs = cs.getResultSet();
// Process the ResultSet
System.out.println("Result " + resultNum + " is a ResultSet: " + rs);
rs.close();
}
else
{
rowsAffected = cs.getUpdateCount();
// No more results
if (-1 == rowsAffected)
{
--resultNum;
break;
}
// Process the update count
System.out.println("Result " + resultNum + " is an update count: " + rowsAffected);
}
}
System.out.println("Done processing " + resultNum + " results");
Remember: to execute a complex query with a mix of DDL, DML and result sets, and possible errors, always use execute() to execute the query and use getUpdateCount(), getResultSet(), and getMoreResults() to process the results.
--David Olix [SQL Server]
This post is provided 'as is' and confers no express or implied warranties or rights.
If you have a specific feedback about the Microsoft SQL Server JDBC Driver, please visit the Microsoft Connect. This web site allows you to search for driver issues submitted by customers, and it allows you to submit your own.
Selcin Turkarslan, SQL Server
