calling Stored Procedure in Java-CodePudding

I have a SP:

ALTER PROCEDURE [dbo].[auth_user]
    @username nchar(50),
    @password nchar(50)
AS BEGIN
    DECLARE @response AS bit = 0;

    UPDATE dbo.Users
    SET @response = 1
    WHERE Username = @username
        AND Password = @password

    RETURN @response
END

I try to get return value from SP in Java

    Connection connection = null;
    CallableStatement storedProcedure = null;
    ResultSet resultSet = null;

       ...

    connection = Shop.OpenConnection();
    
    storedProcedure = connection.prepareCall("{call auth_user(?, ?)}");
    storedProcedure.setString(1, UsernameField.getText());
    storedProcedure.setString(2, PasswordField.getText());
    
    resultSet = storedProcedure.executeQuery(); //Error
    if (resultSet.next()) {
        JOptionPane.showMessageDialog(null, "Correct username and password");
        MainFrame frame = new MainFrame();
        frame.setVisible(true);
    } else {
        JOptionPane.showMessageDialog(null, "Wrong username or password");
    }

I need to get return value(0 or 1), but I get error:

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

CodePudding user response：

I did it and I want to share with you this information.

connection = Shop.OpenConnection();

storedProcedure = connection.prepareCall("**{? = call auth_user(?, ?)}**");
storedProcedure.setString(2, UsernameField.getText());
storedProcedure.setString(3, PasswordField.getText());
        
storedProcedure.registerOutParameter(1, Types.INTEGER);
storedProcedure.execute();
        
System.out.println(storedProcedure.getInt(1));

I changed string of call SP -> {? = call auth_user(?, ?)} and declared each parameter with its number. That is, the return value is 1, the passed parameter is 2, and the passed parameter is 3

CodePudding user response：

In your SP, you declare @response as bit but don't give it a default value. Bits can be 0, 1, or NULL. @response is set to NULL.

In the update, you set it to 1 only when username and password match. If they don't match, @response is still NULL.

This should fix it.

DECLARE @response AS bit = 0;