Logo 
Search:

Java Forum

 Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Problem with JDBC, SQL Server 7.0 and Stored Procedures

  Asked By: Lambodar    Date: Feb 22    Category: Java    Views: 1757
  

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: 

 

3 Answers Found

 
Answer #1    Answered By: Robin Bailey     Answered On: Feb 22

your 'Stored procedure' has nothing to retuen to your
java application.I think in your stored  procedure you
should use a cursor to be able to return  a resultset
to your java application.please consult SQL Server
documentation on how to define a cursor and return
values using it.

 
Answer #2    Answered By: Oscar Evans     Answered On: Feb 22

I tried your problem  but the jdbc  works as well as on the stored  Procedures which returns  a result set in T-SQL (SQL Server sql  language)

I create  a stored procedure  like this in my database


CREATE PROCEDURE spSelectTexts  AS

SELECT * FROM ST_TXT
WHERE ID_OR = 2
GO

and It  then

a code like this


package com.ararat.dataaccess;

import java.sql.*;
/**
 * <p>Title: ARARAT Retailing System</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2003</p>
 * <p>Company: </p>
 * @author Soheil Dowlatshahi
 * @version 1.0
 */

public class  TestDatabase {

  public  static void main(String[] args) {

    try  {
      Class driverClass = Class.forName("com.inet.tds.TdsDriver");
      Connection conn = DriverManager.getConnection("jdbc:inetdae:127.0.0.1:1433?database=arts","sa","");
      CallableStatement stmt = conn.prepareCall("{call spSelectTexts ()}");


      ResultSet rs = stmt.executeQuery();

      for (int j =0; rs.next() && j < 10;j ++ ) {
        for(int i= 1; i < 4; i++) {
          System.out.print(rs.getObject(i) + " ");
        }
        System.out.println("");
      }

      rs.close();
      stmt.close();
      conn.close();


    } catch(Exception ex) {

      ex.printStackTrace();
    }

  }
}


I think that you have problem is related to two possible cases:

1. you use a invalid Driver ( I uses Inet Driver TDS http://www.inetsoftware.de/English/produkte/JDBC_Overview/ms.htm)
2. you have some bugs in your proxy  class or you EResultset

Note: As an answer to Hamid Reza Sahlolbey I should say that the in T-SQL we can return  a result set as the return value of a store procedure if we don't specified an explicit Return in our code. It's different from Oracle PL/SQL which requires that all result set return values must be Cursors or something like this

 
Answer #3    Answered By: Michael Evans     Answered On: Feb 22

At least I do get the
information printed out when I tried your class  separately. As you
say, there might be a bug in one of the other classes, but I am going
to try  it out also. THe only problem  I had was that I have a remote
server, and it could not find it. My final code was therefore:


import java.sql.*;

public class TestDB {

public static  void main(String[] args) {

try {

Connection conn = null;
Class driverClass = Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver");
String URL = "jdbc:odbc:driver={SQL Server};
server=SERVERNAME;uid=USERID;pwd=PASSWORD;database=DBNAME";
conn =DriverManager.getConnection(URL);

CallableStatement stmt = conn.prepareCall("{call
spSelectTexts}");


ResultSet rs = stmt.executeQuery();

for (int j =0; rs.next() && j < 10;j ++ ) {
for(int i= 1; i < 4; i++) {
System.out.print(rs.getObject(i) + " ");
}
System.out.println("");
}

rs.close();
stmt.close();
conn.close();


} catch(Exception ex) {

ex.printStackTrace();
}

}
}

 
Didn't find what you were looking for? Find more on Problem with JDBC, SQL Server 7.0 and Stored Procedures Or get search suggestion and latest updates.




Tagged:            

 
 
 
 

Related Post