• 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
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

stored procedures

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"templar_knight"

Welcome to JavaRanch!

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

Thanks!
 
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
Have changed it. Sorry about that.
Thanks
Ricky James
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
For my next trick, I'll need the help of a tiny ad ...
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic