• 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

CallableStatement problem

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am trying to insert a record into my database using 2 stored procedures. These are pasted below. The code that I am using in the java program is as follows:



But This generates an error. Basically the GeneratePcId() stored procedure returns the value which is an integer and I want to use this as the input of InsertPrice.

I am getting an error in this line: int number = genid.getInt(1);

Can anybody advise what the problem is?

Thanks
Ricky

PROCEDURE `InsertPrice`(pc_id INT, pc_level INT, price INT, from_date DATE, to_date DATE)
BEGIN
INSERT INTO price VALUES (pc_id, pc_level, price, from_date, to_date);
END $$


PROCEDURE `GeneratePcId`()
BEGIN
select max(pc_id)+1 as newpcid from price;
END $$



[ April 02, 2007: Message edited by: Ricky James ]
[ April 02, 2007: Message edited by: Ricky James ]
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ricky, please post the error.

Your stored procedure GeneratePcId should return the value.

You did not mention your database brand, but you could do this as a return value, or as an out parameter.
Your jdbc call should fetch that new id. ("{? = call GeneratePcId()}")
You need to bind that out parameter to a java variable.

Oracle's tutors tell you how to do that:
Calling PL/SQL from Java.

Regards, Jan
 
Ricky James
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
Ricky, please post the error.

Your stored procedure GeneratePcId should return the value.

You did not mention your database brand, but you could do this as a return value, or as an out parameter.
Your jdbc call should fetch that new id. ("{? = call GeneratePcId()}")
You need to bind that out parameter to a java variable.

Oracle's tutors tell you how to do that:
Calling PL/SQL from Java.

Regards, Jan



Hi Jan,

Thanks for the reply.

I am using MySQL.

I get the following error when I try to run the program, although it compiles:

java.sql.SQLException: No output parameters registered.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.CallableStatement.getOutputParameters(CallableStatement.java:1426)
at com.mysql.jdbc.CallableStatement.getInt(CallableStatement.java:1247)
at CallableStatementExercise.query(CallableStatementExercise.java:33)
at CallableStatementExercise.main(CallableStatementExercise.java:12)
BUILD SUCCESSFUL (total time: 0 seconds)


And Jan, I didn't really understand where I could use the statement that you suggested. Can you please elaborate?

Thanks
Ricky
 
Ricky James
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I forgot to execute the statement. I have rectified that but I still get the same error:

The error that I get now is pasted below. Please let me know if it makes sense to you.

init:
deps-jar:
compile-single:
run-single:
Connecting to the database...
java.sql.SQLException: No output parameters registered.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
910)
at
com.mysql.jdbc.CallableStatement.getOutputParameters(CallableStatement.java:
1426)
at
com.mysql.jdbc.CallableStatement.getInt(CallableStatement.java:1247)
at
CallableStatementExercise.query(CallableStatementExercise.java:34)
at
CallableStatementExercise.main(CallableStatementExercise.java:12)
BUILD SUCCESSFUL (total time: 0 seconds)

And it points to "int number = genid.getInt(1);".

The code is here:
// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{call
GeneratePcId()}");
genid.execute();
int number = genid.getInt(1);
CallableStatement cstmt = conn.prepareCall("{call
InsertPrice (?, ?, ?, ?, ?)}");

// Bind values to the parameters
cstmt.setInt(1, number );
cstmt.setInt(2, 8);
cstmt.setInt(3, 600);
cstmt.setDate(4, Date.valueOf("2008-01-01"));
cstmt.setDate(5, Date.valueOf("2008-02-02"));

// Execute the query
cstmt.execute();

Thanks again.
ros
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The error exactly tells what you have to do:
java.sql.SQLException: No output parameters registered.

This link 18.4.5.1.3. Using CallableStatements to Execute Stored Procedures shows how you pass an out parameter from a MySQL procedure, and how to bind the parameter.

By reading this link, and the previous link I provided you to Oracle's documentation, you'll get it working.

Regards, Jan
[ April 02, 2007: Message edited by: Jan Cumps ]
 
Switching from electric heat to a rocket mass heater reduces your carbon footprint as much as parking 7 cars. Tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic