Calling an Oracle SQL function

Question

I'm new to the connectivity between Java and Oracle and I'm designing a login page.

I'm attempting to call an Oracle function from JAVA. Assume the Oracle function is called FUNCT_PERSON with 2 IN parameters: username and password and return the user id which is a NUMBER.

Inside the FUNCT_PERSON function we use a SELECT statement and return user id number if found, otherwise it will return 0.

Here is my code:

String sql = "{ ? = call FUNCT_PERSON(?,?) }";
CallableStatement statement = connection.prepareCall(sql);
statement.setString(2,username);
statement.setString(3,password);
statement.registerOutParameter(1, java.sql.Types.INTEGER);  

Boolean check = statement.execute();   

if (!check) {
 //proceed to another page
} else {
 //Go back to the login page
}

I don't know if what I'm doing is right or not, especially the Boolean check = statement.execute(); line, because I don't really know what the check is for..

If the function return a user ID, that means it exists in the database and I'll proceed to another page. If not, then it will redirect to the login page.

Answer 1

You just need to recover the result of the operation and use it to validate the action to make:

String sql = "{ ? = call FUNCT_PERSON(?,?) }";
CallableStatement statement = connection.prepareCall(sql);
statement.setString(2,username);
statement.setString(3,password);
statement.registerOutParameter(1, java.sql.Types.INTEGER);  

statement.execute();   
//this is the main line
long id = statement.getLong(1);
if (id > 0) {
    //proceed to another page
} else {
    //Go back to the login page
}

Based on this tutorial

Answer 2

From the JAVA Docs:

The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).

Returns:true if the first result is a ResultSet object; false if the first result is an update count or there is no result

So Your check is wrong. execute doesn't say if the operation was successful or not.