SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.

Oct 26, 2006

I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve

the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?

Any workaround?

Here is the pseudo code:

create Procedure foo(@output varchar(max))

{

set @foo = 'string that has more than 4000 characters...';

return;

}

Java code:

CallableStatement cs = connection.prepareCall("{call foo ?}");

cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.

cs.execute();

String result = cs.getString(1); // The result is truncated to 4000 char.

-- Also tried

CLOB clob = cs.getClob(1);

long len = clob.length(); // The result is 4000.

Thanks,

Eric Wang

View 3 Replies

SQL Server 2005 JDBC Driver Output Parameter/Result Set Issue

Mar 21, 2006

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.

View 36 Replies View Related

Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement

Feb 13, 2007

My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database.

I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver?

I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns.

First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement?

Second problem: What do I set the value of the parameter to?

The code looks like:

CallableStatement cstmt = myConnection.prepareCall(sQuery);

cstmt.registerOutParameter(1, Types.OTHER); // What is the right type?

cstmt.setNull(1, Types.OTHER); // What is the right type?

if (cstmt.execute()) {

ResultSet rs = cstmt.getResultSet();

}

Execution results in a NullPointerException from the driver.

What am I doing wrong?

Thanks for your assistance.

Jon Weaver

View 3 Replies View Related

Stored Procedures SQL Server 2005 And JDBC Driver Problems

Aug 22, 2007

Hi,

I am using SQL server 2005 stored procedures being called from my java application using the CallableStatement. As long as my stored procedure is a simple and direct Select statement things are moving nicely.
But my stored procedures are a little bit more complicated and this causes problems for me to parse the data in a ResultSet.
a sample stored procedure:
Create procedure sp_Get

@cat int,
@itemId bigint
as
declare @results table (tableId bigint, label varchar(200), typeId int)

if @cat = 1
begin

insert @results (tableId, label, typeId)
select

tableId = personId,
label = fname + lname,
typeId = 1
from Person where catId = @itemId

insert @results (tableId, label, typeId)
select
tableId = prospectId,
label = prospect,
typeId = 2
from Prospects where catId = @itemId
end
else if @cat = 2
begin

insert @results (tableId, label, typeId)
select

tableId = companyId,
label = Company,
typeId = 1
from Company where regionId = @itemId
end
/* result set*/
select

tableId,
label,
typeId
from @results
GO

my java code:
Connection conn = this.getConnection() //opens connection to db
CallableStatement cmd = conn.prepareCall(" { call sp_Get (?,?) }");
cmd.setInteger("cat", 1);
cmd.setLong("itemId", 2);

//this returns false?
boolean hasResults = cmd.execute();

//this also returns false?
boolean moreResults = cmd.getMoreResults();

The strange part of this is that when you execute this query in the SQL Server Managent Studio it returns 1 result set.
Why is my java code not able to see this result?

Thanks for any help,

- Rogier