• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Bear Bibeault
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Jj Roberts
  • Carey Brown
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

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

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 40354
819
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends on which database you are using.

cs.setInt(1, 8);  is the in parameter in this example.
 
Mihail Kuceruk
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


Or, maybe just ?= is the optional part, thus correct equivalents would be:
 
Jeanne Boyarsky
author & internet detective
Posts: 40354
819
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
call magic_number(?) - the ? means an input parameter here.
 
Mihail Kuceruk
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



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
author & internet detective
Posts: 40354
819
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



And here is how we can call it with jdbc:



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.
 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
double_number is an INOUT.



Should be since ?= is optional?

 
Mihail Kuceruk
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

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
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 40354
819
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Greenhorn
    Posts: 11
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Okay Jeanne.
    Thank you for your time and suggestions.
     
    You don't know me, but I've been looking all over the world for. Thanks to the help from this tiny ad:
    Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    reply
      Bookmark Topic Watch Topic
    • New Topic