Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Person to database

Posted on 2011-09-28
25
Medium Priority
?
264 Views
Last Modified: 2012-05-12
I have the follwoing code as you can see below

my table is this:

Table passenger
===============
paId, paFName, paLName, paType
---------------
paId             int(11) PK
paFName          varchar(15)
paLName          varchar(15)
paType           varchar(6)

My question is about the ID (paID)
In the GUI you should fill out an ID but it seems this id should just increase and not be filled out by hand. What do you think about this?
/**
	 * registerPerson - register a new person in database
	 * 
	 * @param id
	 *            - id of the person to register
	 * @param firstName
	 *            - first name
	 * @param lastName
	 *            - last name
	 * @param type
	 *            - type {"F" - female, "M" - male, "C" - child}
	 * @return true if person was saved successfully, else false
	 */
	public boolean registerPerson(int id, String firstName, String lastName,
			String type) {
		return true;
	}

Open in new window

0
Comment
Question by:Mickeys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 5
  • +1
25 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813014
while inserting id inserting into DB.do like this.your insert statement should be like this.

or create oracle SEQUENCE for ID so it wil automaticlaly incrmented.

(select max(id)+1 from  tablename,firstname,lastName,type)
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813018
I think you should not require the user to enter ID.

You can set up the table in such a way that when  you insert the row into the table it automatically inserts incrementing integer ID as one of the columns.

Another option is to use database object - sequence - which generates incremnting numbers, and before you
make insert you query the sequence for the next number and then use that number as ID
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813025

the syntax in Oracle is like that:


CREATE SEQUENCE seq_passenger_id;

SELECT seq_passenger_id.NEXTVAL FROM DUAL;


in mySQL I guess they don't have dual, let me check

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813029
check below on how to create sequence.

http://www.techonthenet.com/oracle/sequences.php
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813041
This is how you create table with auto_increment ciolumn in MySQL

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Open in new window

0
 

Author Comment

by:Mickeys
ID: 36813079
well the problem is that I aint allowed to change the tables. Otherwise I wouldnt have build it like that.

So my question is basiccly how I make it to run with this setup. I mean even if someon enter a digit ini the textfield......I could just igonre it if it increases it self. So what is your opinion on how to solve it?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813084
No you still don't let them enter digits - it makes no sense

0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813088
then your insert statment should be like this.

INSERT INTO TABLE NAME values
(select max(ID)+1 from  table name ,firstname,lastName,type)
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813092

you first chack if they already cretaed the table with auto-incementing id

because it is rather common parctivce - try tto insert one line and check if it inserted the id
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813108
if not, then perhaps the insert statemnt as suggested chaituu will work

or else you can first select max(id) from table

and then incerement it and  insert in the next statement
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36813197
If you can't alter the table then you need to get ready to catch a dup key exception and tell the user to choose something else if so- they're probably going to choose something that doesn't work sooner or later
0
 

Author Comment

by:Mickeys
ID: 36813646
INSERT INTO passenger values('Sven','Sven','M')
Error Code: 1136. Column count doesn't match value count at row 1


and if I do this

INSERT INTO passenger values(SELECT max(ID)+1 from  passenger, 'Sven','Sven','M')
SQL ERROR near SELECT
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813656


INSERT INTO passenger(paFName ,paLName,paType) values('Sven','Sven','M')
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813660
Try this way (see above) with naming the columns
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813690
INSERT INTO passenger(id,paFName ,paLName,paType) values(SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36813788
So you've decided to ignore the user-input id then?
0
 

Author Comment

by:Mickeys
ID: 36813827
INSERT INTO passenger(paFName ,paLName,paType) values('Sven','Sven','M')
Error Code: 1364. Field 'paId' doesn't have a default value


INSERT INTO passenger(paI,paFName ,paLName,paType) values(SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")' at line 1



Yes I have decided to igonre it. I cant really see why I should be able to input an id??? Since in the table that we got the passenger right now has the number 1-14. It is better it just increased by itself. Our atleast that is my point of view. Otherwivse I need to check if it is available as well.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813842


you can do it in two statements

select max(id) from passenger

and then retrieve and increment it in java code and

and insaert all four fields

0
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 1000 total points
ID: 36813850
check this.I


INSERT INTO passenger(paI,paFName ,paLName,paType)

values(SELECT 1 + coalesce((SELECT max(id) FROM passenger), 0), "aaa'',"dgdg","M")


http://stackoverflow.com/questions/1587562/problem-with-mysql-insert-max1
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1000 total points
ID: 36813860


ResultSet rs = stmt.executeQuery("select max(paId) from passenger");

  int maxId = -1;
  while(rs.next()) maxId = rs.getInt(1);

stmt.executeUpadte("INSERT INTO passenger(paId, paFName ,paLName,paType) values(" + (maxId+1) + ",'" + name + "','" + namelast + "','" + type + "')")
0
 

Author Comment

by:Mickeys
ID: 36813903
this worked
public boolean registerPerson(int id, String firstName, String lastName,
			String type) {
		int ie = 0;

		try {
			mStmt = mCon.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (mStmt.execute("select max(paId) from passenger")) {
				mRs = mStmt.getResultSet();

			} else {
				System.err.println("select failed");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			for (int i = 0; mRs.next(); i++) {
				ie = mRs.getInt(1);
				System.out.println(ie);
			}
		} catch (Exception e) {

		}

		int val = 0;
		ie++;

		try {
			val = mStmt
					.executeUpdate("INSERT INTO passenger(paId, paFName ,paLName,paType) values("
							+ ie
							+ ", '"
							+ firstName
							+ "','"
							+ lastName
							+ "','"
							+ type + "')");

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (val > 0)
			return true;

		else
			return false;

	}

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813908
Great!
0
 

Author Comment

by:Mickeys
ID: 36813915
but  I am still a little confused.

since paID is a primary key....isnt that bound to other tables then? which mean if I add a number it gets screwed up in other tables`?
0
 

Author Comment

by:Mickeys
ID: 36813918
hmmm and when I check the other tables I can confirm that this is the case.
That means I cant increase it. :-/
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813934


primary key is a key of this table - it may or may not be bound to other
tables. If there is already existing schema - you need to contact someone who
was maintaining it before - how otherwise would you know what other dependecy you need to honor?

In general if it allowed you to insert then if the schema was devised properly
 it probably meanse that you should be OK, but you cannot be sure of it of course
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question