Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Problem with JDBC, SQL Server 7.0 and Stored Procedures - RS return

  Asked By: Ronnie    Date: Aug 09    Category: Java    Views: 1189
  

I am working with a servletbased Javaapplication on a JRunserver. I
am using
SQL Server 7.0 and have been making ordinary SQL-calls (i.e. SELECT,
UPDATE,
DELETE) without problems. Now I have created a stored procedure which
works
fine from the Query Analyzer:

CREATE PROCEDURE spFullName
@userID nvarchar (20)
AS
SELECT p.firstname, p.surname
FROM dbo.personorg p
WHERE userid = @userID
GO

It returns the full name of the employee, based on their userId (i.e.
"mah"). I have an interfaceservlet, a GetFromDB class which creates
the
SQL-calls and the Proxt class which connects to the database and
executes
the queries.My problem is that when I execute the stored procedure
from the
Javainterface it returns empty. Not null, just empty.

From my interfaceservlet I send the following call to my class
GetFromDB:

GetFromDB.getSPstring(out, "spFullName", "lgb"));

The method getSPstring looks like this:
public static String getSPstring(PrintWriter out, String spName,
String
userId)
{
String rsName;

try
{
rs = proxy.Exec(spName, userId);
}
catch (Exception e)
{
out.print("SQLException " + spName + " " + userID + "\n" +
e.getMessage());
return null;
}
rsName = rs.getString(1).trim() + " ";
rsName += rs.getString(2).trim();
return rsName;
}

This above method in turn calls the proxy-class and the method Exec
which
looks like this:


public synchronized EResultSet Exec(String spName, String var)
throws
SQLException
{
CallableStatement TheStatement;
ResultSet TheResultSet;
EResultSet EResult = new EResultSet();
String command = "{call " + spName + "(?)}";

try {
TheStatement = MyConnection.prepareCall(command);
TheStatement.setString(1, var);
} catch (Exception e)
{
DBReconnect();
TheStatement = MyConnection.prepareCall(command);
TheStatement.setString(1, var);
}


try {
TheResultSet = TheStatement.executeQuery();
} catch (Exception e)
{
DBReconnect();
TheStatement = MyConnection.prepareCall(command);
TheStatement.setString(1, var);
TheResultSet = TheStatement.executeQuery();
}

EResult.LoadData(TheResultSet);

TheResultSet.close();
TheStatement.close();

return EResult;
}

The EResult class and the LoadData method is my own, but I want to
point out
that it works when I send ordinary sql-calls.

Share: 

 

No Answers Found. Be the First, To Post Answer.

 




Tagged: