About Question enthuware.ocpjp.v11.2.1640 :

[ACV001]

Why there are CallableStatement questions when Selikoff says they have been removed from scope:

https://www.selikoff.net/ocp11-819/

Removed: Callable Statements Skip CallableStatements (Ch10 of Programmer II, Ch21 of CSG)

[admin]

We have confirmed reports from several candidates including our classroom trainees who took the exam that they got questions on this topic that is why we have added questions on it in the question bank.

[dalvir]

Hi

I'm suggesting some corrections in text on this page. Please review and reply with your guidance.

First, In the explanation of second option, the method signatures of setString method of CallableStatement and PreparedStatement interfaces are mentioned as below:

callableStatement.setString("NAME", "john",  java.sql.Types.VARCHAR); //valid
callableStatement.setString(1, "john",  java.sql.Types.VARCHAR); //valid

preparedStatement.setString("NAME", "john",  java.sql.Types.VARCHAR); //will NOT compile
preparedStatement.setString(1, "john",  java.sql.Types.VARCHAR); //valid

I believe content creator for this question here intended to explain about setObject method for these interfaces respectively because setObject method of these interfaces supports 3 argument overload version rather than setString which only supports 2 arguments version as below:

void setString(String parameterName, String x)
------------------------------------------------------------------------------------------------------------------------------------------
Secondly, in the explanation section on bottom of this page there is a following note:

"NOTE 4: You cannot mix the index and parameter markers in the same query. That is, you can either set parameters using the index or using the parameter markers but not both"

I'm using mysql-connector-j-8.3.0 and it is supporting this feature.

for example for this stored procedure:

Code: Select all

 create procedure sp_sum_numbers(IN a int, IN b int, OUT result int)
   begin
     set result= a+b;
   end

and the sample java code:

Code: Select all

  String sql = "{call sp_sum_numbers(?,?,?)}";
        try(var conn = DriverManager.getConnection(url,username,password);
            var call = conn.prepareCall(sql)){
        call.setInt(1,25);
        call.setInt("b",25);  // No exception; MySQL is supporting mixing index and named parameters in same query
        call.registerOutParameter(3, Types.INTEGER);
        call.execute();
        int sum = call.getInt(3);
        System.out.println("sum is = "+ sum);       // sum is 50
}

Please provide your guidance on it.

Thanks

[admin]

1. You are right. It should be setObject.
2. This is as per JDBC specification section 13.3.2:

It is not possible to combine setting parameters with ordinals and with names in the same statement. If ordinals and names are used for parameters in the same statement, an SQLException is thrown.

May be Mysql has not implemented this requirement strictly.