• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle procedure works in direct SQL but not via Java

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've created an Oracle stored procedure called recalc_1_student_grades_v1. When I use sqlplus to connect to the database, I can run the procedure just fine:



However, when my Java program calls the database to run the exact same command, I get an SQLException:



The code that actually calls the database, tryUpdate(), is:



What am I doing wrong?
 
Ranch Hand
Posts: 344
Oracle Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Instead of executing the Stored Procedure via EXECUTE or CALL in the SQL String, try to execute it via a java.sql.CallableStatement instead.
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Grossman wrote:

The code that actually calls the database, tryUpdate(), is:

so
What am I doing wrong?



The error message says, that the SQL passed to the aStatement.execudeUpdate is wrong (that is, SQL from the 'doThis' string)..
Since you didn't show us what you are passing to this string, it's hard to guess what exactly is wrong.

 
Bob Grossman
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, Koen, your solution worked.

For anyone else reading this, the syntax of the call to the procedure is not obvious. Note the curly braces in the SQL. You use setInt(), setString(), etc. methods to set the values where the question marks go:


 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Bob Grossman wrote:Thanks, Koen, your solution worked.

For anyone else reading this, the syntax of the call to the procedure is not obvious. Note the curly braces in the SQL. You use setInt(), setString(), etc. methods to set the values where the question marks go:



Though really not obvious, it is actually documented in the CallableStatement javadoc.

Just for completeness, the curly braces are "translated" by the JDBC driver into proper syntax needed to call stored procedures in the database supported by the driver. As the exact syntax differs across databases, this allows to use database-agnostic code when calling stored procedures.

Nothing actually prevents you to use real target database syntax. In your case you'd replace the curly braces and the enclosed text with begin recalc_1_student_grades_v1(?, ?); end - rest of the code would remain the same. This does not bring you anything actually and just makes the code database dependent, but sometimes you can use this trick to use other database dependent feature that is not directly supported by JDBC, such as the returning clause in Oracle's DML statements, which can be quite useful in putting a sequence number into a new record and returning it to the application in one go (among other uses).
reply
    Bookmark Topic Watch Topic
  • New Topic