File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "stored procedures" Watch "stored procedures" New topic
Author

stored procedures

Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
Hello all,

I am new to JDBC and am currently following The Java Tutorial for this. I am working on a program where I am supposed to update a database using stored procedures.

The database has a primary key column and I am first to query the database to know the maximum value of this key and then add 1 to this max value. This value I have to use as a new value while inserting the record into the database. I insert the rest of the field manually.

I have come across simple queries for stored procedures and am fairly comfortable with them but this is confusing me a bit.

The SQL scripts are pretty straightforward but I would be really thankful if somebody could help me with writing stored procedures for these.

Thanks

Knight
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

"templar_knight"

Welcome to JavaRanch!

Can you please change your display name to comply with our Naming Policy. You can do this here.

Thanks!


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
Have changed it. Sorry about that.
Thanks
Ricky James
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by templar_knight:
Hello all,

I am new to JDBC and am currently following The Java Tutorial for this. I am working on a program where I am supposed to update a database using stored procedures.

The database has a primary key column and I am first to query the database to know the maximum value of this key and then add 1 to this max value. This value I have to use as a new value while inserting the record into the database. I insert the rest of the field manually.

I have come across simple queries for stored procedures and am fairly comfortable with them but this is confusing me a bit.

The SQL scripts are pretty straightforward but I would be really thankful if somebody could help me with writing stored procedures for these.

Thanks

Knight


Thanks for changing your name.

What have you written so far? Which database are you using?
[ March 26, 2007: Message edited by: Paul Sturrock ]
Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
What have you written so far? Which database are you using?
=================================================================

Thanks for the reply. Much appreciated. I am using MySQL and have the following procedure for inserting the price:

PROCEDURE `insert_price`(pc_id int, pc_level INT, price INT, from_date DATETIME, to_date DATETIME)
BEGIN
INSERT INTO price VALUES (pc_id, pc_level, price, from_date, to_date);
END


I also know that the sql for calculating max of pc_id + 1 is
select max(pc_id) + 1 from price;

But I don't know how to write it into a procedure so that this new value goes into the insert_price procedure's first argument.

Thanks in advance.
Ricky
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Sorry, more questions

If you are using MySQL, why are you using the max id route to getting a new primary key value? Could you not just use MySQLs auto increment capability? And why must you use a StoredProcedure? The logic you suggest is pretty basic, I can't see wat you gain by wrapping it as a StoredProcedure?
Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
Sorry, more questions

If you are using MySQL, why are you using the max id route to getting a new primary key value? Could you not just use MySQLs auto increment capability? And why must you use a StoredProcedure? The logic you suggest is pretty basic, I can't see wat you gain by wrapping it as a StoredProcedure?




Well, I am trying to learn JDBC on my own and this is a basic exercise I am working on. I know that I can use this stuff you mention but am trying to learn through simple exercises. For example now I know what SQL script I have to use, if I can convert it to a Stored Procedure I'll be better able to understand this part of JDBC.

I tried a couple of books for sample code on JDBC, but they are too tough for me at this stage. This is a small step that can help me.

Cheers
Ricky

Thanks
Ricky
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

OK. Well I don't know MySQL that well so I can't really help, but the documentation for Stored Procedures seems fairly comprehensive. JDBC documentation is not going to cover MySQL stored procedures.

I'd suggest if you are not familiar with stored procedures you should try to learn them independent of JDBC for now.
Hap Olsen
Greenhorn

Joined: Mar 26, 2007
Posts: 3
One word of caution. Use a sequence for your primary key not just max + 1. This will avoid many potential problems you might have.

ie something like

INSERT into tablename (colname) values (sequence.NEXTVAL)

Hap
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: stored procedures