Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why can't I retrieve my data using Stored Procedure via Callable Statement

 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How can I retrieve my existing data using Stored Procedure via Callable Statement? The first parameter is for the SECTION_ID (Primary Key) and the second is for the SECTION_NAME. I don't know which where I go wrong, maybe writing a Stored Procedure. Here is my Stored Procedure correct me if I'm wrong.

Stored Procedure




Table


Code



When I try to run the project and insert a existing data. It shows nothing. It doesn't print the value that I desire the show. Any help or tips would appreciated!
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:When I try to run the project and insert a existing data. It shows nothing. It doesn't print the value that I desire the show. Any help or tips would appreciated!

There are a few issues with your code:
1/ your stored procedure has one IN parameter and an OUT parameter. In your Java code you have to supply a value for IN parameters and register the type for all OUT parameters. That's done in your code with these statementsBut this line of code makes no sense at all, since you should not set a value for an OUT parameter

2/ a stored procedure can return a result set, but in your case it does not. So you should not loop through the result set. If you want to retrieve the value of the OUT parameter you need to invoke the appropriate getXxx() method on the CallableStatement itself (and not on the result set). So using the following statement should return the section name

3/ a few remarks which are unrelated with your issue, but will definitely improve your code quality:
a) according to Java coding conventions, you only use underscores in names of constants, not local or instance variables
b) you don't need two try-with-resources statements to get a connection and create a callable statement, you can combine both statements in one try-with-resources statementc) always use curly braces, even if the block of statements has just one statement (like with the else branch)

Hope it helps!
Kind regards,
Roel

PS. I assume you are experimenting with callable statements, as this can be easily done with a database query as well...
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Update!


When I try to retrieve the value of OUT parameter. The textfield become enabled when I hit the search button. As you can see I just add so I can distinguish the what value is printing. While running my project and entered a existing record. It gives me a null it doesn't print anything. The changes only is the textfield become enabled. Thanks.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:While running my project and entered a existing record. It gives me a null it doesn't print anything. The changes only is the textfield become enabled.

And what happens if you execute the stored procedure on your database using the same id? Does it return the correct value?

Which database and JDBC driver are you using?
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
John Francis Ochotorina wrote:While running my project and entered a existing record. It gives me a null it doesn't print anything. The changes only is the textfield become enabled.

And what happens if you execute the stored procedure on your database using the same id? Does it return the correct value?

Which database and JDBC driver are you using?


Thanks for responding bro. It been two days since I can't get out of this error. I still didn't try running it on my Stored Procedure. I just add to distinguish the value and it gives me a "null". By the way bro I'm using mysqlconnector 5.1.38. Thank you.
Screenshot_4.png
[Thumbnail for Screenshot_4.png]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:I still didn't try running it on my Stored Procedure.

If you don't run the stored procedure directly on your database (using a database client) you don't know if the stored procedure is returning the correct value. So the Java code might be absolutely spot-on, but the issue is in the database code.

Just for fun, I created a similar stored procedure on my local MySQL instance. So the first step is to create the stored procedureThen the second step is to verify the stored procedure is working flawlessly when invoked on the database itself using some database client. These statementsproduces the following outputSo now I can savely say, the stored procedure is working just fine! And now it's time to create a (small) Java application which calls this stored procedure using JDBC and prints the result. Fasten your seatbelt, here we go! Here is the applicationWhen I execute this application, the program outputs "player with id[63] is: Roel De Nijs". So the Java application works flawlessly too. Yay!

Hope it helps!
Kind regards,
Roel
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I haven't used stored procedures in the past so correct me if I'm wrong, but if the query inside the procedure returns no rows then the value of the output parameter should be null, correct? And since the value of the output parameter is in fact null, then one might suspect that the query is returning no rows.

An alternative hypothesis is that the query is returning one row but the value of the SECTION_NAME column in that row is null. Like Roel suggested, assuming you know what the procedure is doing is not necessarily a helpful assumption.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:I haven't used stored procedures in the past so correct me if I'm wrong, but if the query inside the procedure returns no rows then the value of the output parameter should be null, correct? And since the value of the output parameter is in fact null, then one might suspect that the query is returning no rows.

That's incorrect! You could assign a value to an OUT parameter (using e.g. SELECT ... INTO statement) without returning any row. That's clearly illustrated in the code snippet I have posted. Although the stored procedure GetPlayerName doesn't return any rows, the value of the OUT parameter is populated with the appropriate name.
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:That's clearly illustrated in the code snippet I have posted. Although the stored procedure GetPlayerName doesn't return any rows, the value of the OUT parameter is populated with the appropriate name.


Sorry, no it isn't. At least not "clearly". Of course the stored procedure doesn't return any rows, we already know that. But the SELECT query inside the stored procedure might not return any rows -- or should I say it might not select any rows? -- and if that happens then I don't see how the OUT parameter is populated with anything at all. That's what I find unclear about your explanation.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Sorry, no it isn't. At least not "clearly". Of course the stored procedure doesn't return any rows, we already know that. But the SELECT query inside the stored procedure might not return any rows -- or should I say it might not select any rows? -- and if that happens then I don't see how the OUT parameter is populated with anything at all. That's what I find unclear about your explanation.

Fair enough. Here is a second attempt.

If you replace line7 from the main method (in the previous code snippet) with this statementfalse will be printed. According to the API documentation of the PreparedStatement interface, false is returned if the first result is an update count or there is no result. So there are definitely no rows returned by the stored procedure, otherwise the return value would be true and you could iterate through a ResultSet object.

Now let's create a second stored procedure, which is very similar to the first one but doesn't define an OUT parameterNow the last statement of this stored procedure is a SELECT statement, so that will result in one row being returned (if the id of course exists in the table). If you would remove the WHERE clause in that query, the stored procedure would return all rows from the player table. Here is the Java code to execute this stored procedure and loop through the resultsThe output is exactly the same as the previous code snippet and thus "player with id[63] is: Roel De Nijs" will be printed.

Hope it helps!
Kind regards,
Roel
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:[Now the last statement of this stored procedure is a SELECT statement, so that will result in one row being returned (if the id of course exists in the table).


And if the ID doesn't exist in the table?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Roel De Nijs wrote:[Now the last statement of this stored procedure is a SELECT statement, so that will result in one row being returned (if the id of course exists in the table).


And if the ID doesn't exist in the table?

An empty result set will (obviously) be returned. So if you would invoke the GetPlayerName2 stored procedure in the previous code snippet with the non-existing player id -63, the same code will still execute and finish normally (no exceptions will be thrown at runtime) but nothing will be printed.
 
Paul Clapham
Sheriff
Posts: 21576
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:An empty result set will (obviously) be returned.


Not obviously at all. The original question was one which didn't return a result set. You've changed it to one which does, and then yes, obviously that result set is going to be empty. I see that my question, which I thought was fairly straightforward, isn't being understood. So let me ask a different one. Consider the stored procedure in the original post, the one which doesn't return a result set but instead has an output parameter. And assume that the table it's looking at is empty. Now there's no result set being returned, so obviously an empty result set is not returned. And no value is assigned by the procedure to the output parameter either.

So when the Java code asks for the value of the output parameter, what happens? (1) Null is returned (2) An exception is thrown (3) Something else.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Roel De Nijs wrote:An empty result set will (obviously) be returned.


Not obviously at all. The original question was one which didn't return a result set. You've changed it to one which does, and then yes, obviously that result set is going to be empty. I see that my question, which I thought was fairly straightforward, isn't being understood. So let me ask a different one. Consider the stored procedure in the original post, the one which doesn't return a result set but instead has an output parameter. And assume that the table it's looking at is empty. Now there's no result set being returned, so obviously an empty result set is not returned. And no value is assigned by the procedure to the output parameter either.

So when the Java code asks for the value of the output parameter, what happens? (1) Null is returned (2) An exception is thrown (3) Something else.



Changes:


I tried to follow the Roel De Nijs code but still printing me a null. Also I follow the second code that he gave still printing the null.



Stored Procedure


So I bring back my code to the last part. Where I still accepting a null value.


Stored Procedure


I changed my OUT parameter to myName instead of NAME. I notice that NAME I think is a reserved keyword because the color the syntax is blue. So I replace with myName. And successfully! I can retrieve my values using Stored Procedure via Call syntax. Sample code and screenshot below.



What I'm trying to do I search a existing record in the textfield. It will print out the values to the Section Name: __________ textfields. If the value's is existing. If not the Message Dialog will print out to the user if the name is not existing. Thank you responding.


Screenshot_6.png
[Thumbnail for Screenshot_6.png]
Screenshot_7.png
[Thumbnail for Screenshot_7.png]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:So when the Java code asks for the value of the output parameter, what happens? (1) Null is returned (2) An exception is thrown (3) Something else.

It all depends on the value of the output parameter before this SELECT was executed. If no row matches with the WHERE clause, no value will be assigned to the output parameter(s). So in this case, the output parameter is declared as a variable (with NULL as default value) and because no row exist in the database with the given id no value will be assigned to the output parameter. So if the Java code asks for the value of the output parameter, null will be returned.

Also note that a SELECT ... INTO statement will only work if at most one row is returned. If more than one row is returned, you'll get an error.

From the MySQL reference documentation about the SELECT ... INTO statement:
The selected values are assigned to the variables. The number of variables must match the number of columns. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row.


Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:I changed my OUT parameter to myName instead of NAME. I notice that NAME I think is a reserved keyword because the color the syntax is blue. So I replace with myName. And successfully! I can retrieve my values using Stored Procedure via Call syntax. Sample code and screenshot below.

Awesome! :thumbup: This means your stored procedure is definitely working and is returning the appropriate section name when you provide an id.

John Francis Ochotorina wrote:What I'm trying to do I search a existing record in the textfield. It will print out the values to the Section Name: __________ textfields. If the value's is existing. If not the Message Dialog will print out to the user if the name is not existing. Thank you responding.

That's not reflected in your code snippets you have posted. It seems you are only printing the results to the consoleDoes it print the appropriate value?

And I am also a little bit confused with the code snippets and the screenshots you have posted:
1/ in the code snippets you are using a variable sectionID to set the id in your stored procedure, but this variable is not declared or initialized in your code snippets...
2/ in your screenshot it seems you are searching for "Apple" and then you expect the result (if existing) to be printed in the field labeled with "Section Name". But that's impossible with the stored procedure getSECTION_NAME as it requires an int as a parameter and not a string...

Hope it helps!
Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic