SQL Server 2005 JDBC Driver Output Parameter/Result Set issue

I'm having an issue with the JDBC driver when I execute a stored procedure that both has a return value and also returns a result set. If I attempt to retrieve the return value (registered as an output parameter) after I execute the stored procedure, then any subsequent attempts to retrieve the result set always return null. Is this by design If I use the result set first and then later get the return value that works; however, in my situation I need to first check the return value before I work on the result set. Am I'm I doing something wrong

Code:

CallableStatement cs = connection.prepareCall("{ = call spGetCustomer( , ) }");
cs.registerOutputParameter(1, Types.INTEGER);
cs.setString(2,"blahblahblah");
cs.setBoolean(3,false);
cs.execute();

int retVal = cs.getInt(1);
ResultSet rs = cs.getResultSet(); // Always returns null, even though the SP actually returns a result set.

Hi Jim,

There is nothing wrong with the order of your calls. I think you may have found a bug. Could you tell me the return value from your execute() call I'd like to verify whether your stored procedure is actually returning a result set or update count first.

As a workaround, can you get the result set (cs.getResultSet()) first, and, if you have one, just hold onto it, but not iterate through it, until you've checked the return value

Thank you,

--David Olix

JDBC Development

Hi David,
I am having same issue in the case of executing the SP, in the SQL server 2000 JDBC Driver, am able to execute a SP through a PreparedStatement, which returns a result set from the #TEMP TABLE and able to move last and beforeFirst with TYPE_SCROLL_INSENSITIVE ResultSet Type and CONCUR_READ_ONLY.

Java Code:

pst = con.prepareStatement("EXEC testProcedure ", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pst.setString(1, "IN");
//
rs = pst.executeQuery();
rs.last();
System.out.println(" Row Number "+rs.getRow());
rs.beforeFirst();
while(rs.next())
{
System.out.println(" Procedure is "+rs.getString(1));
}

SP :

create proc testProcedure
@countrCode varchar(3)
as

select countryname INTO #TMPCOU from country where countryCode = @countrCode
SELECT COUNTRYNAME FROM #TMPCOU

Exception am getting is :

com.microsoft.sqlserver.jdbc.SQLServer Exception: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.

Right now we developed our product completely with SQL Server 2000, but in the production we are moving to SQL Server 2005, we have got just 3 more days for the implementation, we are using SP in many of the modules through session bean.

If this bug cannot be fixed in next 3 days, we need to advise our client to switch back to SQL Server 2000.
We need your urgent reply.

Hi,

You have discovered a different problem than the one above.

I believe what has happened here is that in the SQL Server 2005 driver, a server cursor is being used to execute a ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY statement where one is not needed or called for. Unfortunately, we cannot fix this bug in three days. However, I have added it to our bug tracking system so that it will be addressed for the next release of the driver.

Thank you for your feedback.

--David Olix

JDBC Development

Hi, David,

We are in the process of migrating SQL Server 2000 to SQL Server 2005. We are receiving the same error as mentioned above when a stored procedure is used to retrieve data from the database. (jdbc.SQLServerException: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.).

Is this bug fixed Some people suggested "the hotfix in KB917054" can be used to fix the problem. if so, where can we get the hot fix

Thanks.

Cathie

Hi Cathie,

What version of the SQL Server 2005 driver are you using Can you verify whether the problem happens with the v1.1 June CTP (available at http://www.microsoft.com/downloads/details.aspx FamilyID=f914793a-6fb4-475f-9537-b8fcb776befd&DisplayLang=en) If you are still getting the same exception in the June CTP, can you tell me the SQL you are trying to execute or, ideally, provide a snippet of code that shows the problem

Thanks,

--David Olix

JDBC Development

David,

Thanks, it works for our application.

But, according to the website, "This CTP release has been made available for general testing purposes only. Do not deploy the CTP software in production."

Do you know when will be the estimated release date for this version

Thanks.

Cathie

Cathie,

Currently the v1.1 driver is scheduled to ship this month (August). This is a quality driven release and slips do happen but it is likely that this should still hold.

Angel

Angel,

Thanks for the information.

Is there a way I can receive alert when the new release comes out

Thanks.

Cathie

Cathie,

I don't believe so, sorry. It will be posted to the Microsoft site and we will make sure to post here in the forums, in the newsgroups and in blogs.

I would expect it to ship in the next couple of weeks, maybe as soon as next week.

Angel

Thanks, Angel.

Hi David

I faced java batch execution return row no not accurate when there is an error in batch execution with SQL Server 2005 JDBC Driver v1.1 June but no problem with SQL Server 2000 JDBC Driver.

The reason to have the row no is to determine which row in the batch having the excution error and able to resume with the remaining row in the batch.

Your advice is highly appreciated.

Thanks & Regards,

Henry Neo

Hi Henry,

I think I understand what the problem might be here, but I'd like to get some more information to confirm. Are you seeing that the individual update counts returned in the BatchUpdateException are incorrect after the one that encountered the execution error, or that the total number of update counts is wrong Were you using PreparedStatement or CallableStatement for batch execution, or just Statement Would you be able to provide a code sample that demonstrates the problem

Thank you,

--David Olix

JDBC Development

Dear David,

Thanks for your prompt reply.

These are the coding. Your advice is highly appreciated.

Thanks & Regards,

Henry Neo

while(success)
{
try
{
Statement stmt = conn.createStatement();

for (iCount=iRowStart; iCount<vSQL.size(); iCount++)
{
// normal SQL insert / update / delete statement

String strSQL = (String) vSQL.elementAt(iCount);
tmt.addBatch( strSQL );
}

stmt.executeBatch();
}

catch (BatchUpdateException e)
{

// get the row that throw error and continue with next row.

int[] updateCounts = e.getUpdateCounts();
RowStart += updateCounts.length;

iRowStart ++;
Count = iRowStart;
}

if (iCount >= vSQL.size()-1)
break;
}

conn.commit();
stmt.close();
stmt = null;
conn.close();
conn = null;

Hi Henry,

The SQL Server 2005 JDBC driver always submits all statements in the batch for execution. Individual failures, if any, are recognized by a value of Statement.EXECUTE_FAILED in the corresponding slots of the array of update counts returned by BatchUpdateException.getUpdateCounts. So the number of update counts returned by BatchUpdateException.getUpdateCounts is always the number of statements in the batch, regardless of which statements (if any) failed to execute.

However, there is a bug in the current (v1.1) driver where a failure of one statement in the batch causes the update counts of all subsequent statements in the batch to be reported as EXECUTE_FAILED, even though execution of those statements may have succeeded. We are currently targeting a fix to that problem for the v1.2 driver.

Thank you,

--David Olix

JDBC Development