Sybex 816(Kindle) Appendix: Answers to Review Questions, Chapter 21, question 15 - errata

[Mihail Kuceruk]

Hello.
I have a question regarding question nr 15, from the chapter 21 (JDBC).
Here is the question itself:

Suppose learn() is a stored procedure that takes one IN parameter and one OUT parameter. What is wrong with the following code? (Choose all that apply.)

18: var sql = "{?= call learn(?)}";
19: try (var cs = conn.prepareCall(sql)) {
20:    cs.setInt(1, 8);
21:    cs.execute();
22:    System.out.println(cs.getInt(1));
23: }

A. Line 18 does not call the stored procedure properly.
B. The parameter value is not set for input.
C. The parameter is not registered for output.
D. The code does not compile.
E. Something else is wrong with the code.
F. None of the above. This code is correct.

Boyarsky, Jeanne; Selikoff, Scott. OCP Oracle Certified Professional Java SE 11 Developer Complete Study Guide (p. 1064). Wiley. Kindle Edition.

And here is the correct answer, mentioned by the author in Appendix:

C. Since an OUT parameter is used, the code should call registerOutParameter(). Since this is missing, option C is correct.

Boyarsky, Jeanne; Selikoff, Scott. OCP Oracle Certified Professional Java SE 11 Developer Complete Study Guide (p. 1174). Wiley. Kindle Edition.

I'm wondering why A is not considered as well a correct answer?
Because, the exercise is clearly told that we have 2 parameters: one IN and one OUT.
In the sql String, we have just one parameter indicated in "(?)", instead of 2.
This is a mistake, from my point of view, and will lead to SQLException in the runtime.
Parameters, regardless of their type (IN, OUT, INOUT), need to be declared in parentheses "()" of the called stored procedure.
In the same time, OUT parameters for readability, could be optionally as well declared before call using "?="
, but it's not mandatory.
Please, confirm me, if I'm right, or give a comment otherwise.

Thank you very much.

[Jeanne Boyarsky]

It depends on which database you are using.

cs.setInt(1, 8);  is the in parameter in this example.

[Mihail Kuceruk]

Good afternoon.
Thank you for your response.
Still not enough information, to understand it clearly, and to be able to pick a correct response on the exam.
Let me say, what information I have, and what remains still confusing for me.
First we have this information in the book:

Returning an OUT Parameter

40: var sql = "{?= call magic_number(?) }";
41: try (var cs = conn.prepareCall(sql)) {
42:    cs.registerOutParameter(1, Types.INTEGER);
43:    cs.execute();
44:    System.out.println(cs.getInt("num"));
45: }

On line 40, we included two special characters ( ?=) to specify that the stored procedure has an output value. This is optional since we have the OUT parameter, but it does aid in readability.


Boyarsky, Jeanne; Selikoff, Scott. OCP Oracle Certified Professional Java SE 11 Developer Complete Study Guide (p. 1053). Wiley. Kindle Edition.

My question here:
Does it mean that if we ?= before call, then ? in the parameter list of the called stored procedure is optional already?
Thus both of this statements would be correct:

var sql = "{?= call magic_number(?)}";
var sql = "{?= call magic_number()}";

Or, maybe just ?= is the optional part, thus correct equivalents would be:

var sql = "{?= call magic_number(?)}";
var sql = "{call magic_number(?)}";

[Jeanne Boyarsky]

call magic_number(?) - the ? means an input parameter here.

[Mihail Kuceruk]

Thank you Jeanne, for your response.

So, could you confirm me please, if I've got it right:
Calling a stored procedure with just one OUT parameter, could be done in either one of the next:

var sql = "{?= call magic_number(?)}";
var sql = "{?= call magic_number()}";

By the way Jeanne, can you tell me please, a SQL database that could work with ?=, in the context of the above question?
Thank you.

[Jeanne Boyarsky]

Conceptually yes. You don't need to know the requirements for specific databases for the exam. (And I don't know their syntax by heart)

[Mihail Kuceruk]

Okay. Jeanny being honest I didn't know this sytnax with ?= could be used.
I think maybe is because of the fact that I didn't work with such databases that accept this syntax.
But because I'm still curious of an real example, where we could use such syntax, I've done a little research.
And here is what I've found.

From official Microsoft documentation, regarding their SQL Server:
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-a-return-status?view=sql-server-ver15

Here is an example of the stored procedure:

CREATE PROCEDURE CheckContactCity     (@cityName CHAR(50))   AS   BEGIN     IF ((SELECT COUNT(*)     FROM Person.Address     WHERE City = @cityName) > 1)     RETURN 1   ELSE     RETURN 0   END

And here is how we can call it with jdbc:

public static void executeStoredProcedure(Connection con) {
    try(CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");) {
        cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
        cstmt.setString(2, "Atlanta");
        cstmt.execute();
        System.out.println("RETURN STATUS: " + cstmt.getInt(1));
    }
    // Handle any errors that may have occurred.
    catch (SQLException e) {
        e.printStackTrace();
    }
}

In this article, that is an official Microsoft documentation by the way, is said:
"In the case of the return status parameter, its ordinal position will always be 1 because it is always the first parameter in the call to the stored procedure."

I'm showing this code here because, we still have an incomplete answer in the answers for: Chapter 21, question 15.
Let me argue.
In the answers is said that that only "C" is correct answer:

C. Since an OUT parameter is used, the code should call registerOutParameter(). Since this is missing, option C is correct.

I think there is still an error in our exercise.
On line 20, we have:

cs.setInt(1, 8);

I think that could be a mistake. We should have instead:

cs.setInt(2, 8);

Because in this case, first parameter specified in sql is our OUT parameter.

So I would say, that maybe correct answers could be in this case:

B. The parameter value is not set for input.
C. The parameter is not registered for output.

Tell me please your thoughts about it.
Thank you.

[Nicholas Barbosa]

double_number is an INOUT.

var sql = "{call double_number(?)"
try(var cs = conn.prepareCall(sql)){
cs.setInt(1,8);
cs.registerOutParam(1,Types.INTEGER);
cs.execute();
}

Should be

cs.setInt(2,8); cs.registerOutParam(1,Types.INTEGER);

since ?= is optional?

[Mihail Kuceruk]

Hi Nicholas.
Your question refers to INOUT type of parameter.
This is not related to the question of this post.
Correct me if I am wrong

In any case, I would like to say that, in the case of INOUT - we don't have 2 parameters - it's just one.
So everything we need to do, is to set both IN and OUT configurations to the same parameter - what has the position 1.

var sql = "{call double_number(?)}";

Remember that an INOUT parameter acts as both an IN parameter and an OUT parameter, so it has all the requirements of both.

Boyarsky, Jeanne; Selikoff, Scott. OCP Oracle Certified Professional Java SE 11 Developer Complete Study Guide (p. 1054). Wiley. Kindle Edition. 

[Nicholas Barbosa]

Hi Mihail,iWhen I did question 15, I thought it was an INOUT so the letter C was true for me.

Since the same param refers ta IN and OUT param,should be cs.setInt (1, 8);

[Jeanne Boyarsky]

From the point of view of the exam, you just need to know:

There are two ?

There is not a second set() method called

[Mihail Kuceruk]

Okay Jeanne.
Thank you for your time and suggestions.