Getting a “more than one row returned” error when running a SQL.

Asked By Andreas 230 points N/A Posted on - 05/16/2011

 

I am using a EJB project to access my MySQL database.  I keep getting an error, "more than one row returned" when I run a select * from budget table.

 

• Status: Open
• Question Views: 1131
• Answer Count: 10
• Vote Up 23 Vote Down

• Answer Accepted: Yes
• Question Category: Java

Best Answer by Stella

Go To Solution

Answered By Stella 0 points N/A #98021

Getting a “more than one row returned” error when running a SQL.

The error message states that the data provider returned more than one row when it should not.  This normally will happen if the relationship defined in the entity beans does not correspond to the data that is stored in the underlying database. You may need to change the relationship or check your data.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Andreas 230 points N/A #98022

Getting a “more than one row returned” error when running a SQL.

I do not understand. Can you explain this to me ?

About Andreas

Questions
1

Answers
5

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Stella 0 points N/A #98023

Getting a “more than one row returned” error when running a SQL.

Assuming you have a "OneToOne" relationship between your Budget table and Budget Schedule table. (Example only).

The Budget Schedule table may be having more than one reference of the Budget in its data or vice versa.

OneToOne implies a row created in one table uniquely matches one row on the second table.

—————————————————————–

@Entity

class Budget {

@OneToOne

Private budgetSchedule = null

}

When the above code is deployed, a foreign key is created in the Budget Table. The relationship states that the Budget Schedule reference is unique and should not contain in more than one Budget row line.

In event 2 Budget rows are found holding the same reference to the Budget Schedule, you will get an error!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Andreas 230 points N/A #98024

Getting a “more than one row returned” error when running a SQL.

Thank you Stella!

I manually checked the rows of the table and yes, I can see that there are duplicate references in the data!

How could this happen if the relationship is defined as OneToOne in the first place ? Does not EJB apply referential integrity when rows are added ?

About Andreas

Questions
1

Answers
5

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Stella 0 points N/A #98025

Getting a “more than one row returned” error when running a SQL.

What I think that could have happened is that the programmer must have changed the relationship in the source code and had forgotten to clean up the data.

When you modify the relationship in the source code AFTER the table has got created, EJB will only apply the rules for NEW data and not for existing data.

That must be the error you are experiencing now when you try to retrieve data that was added when the constraint was missing or was previously defined as OneToMany.

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Andreas 230 points N/A #98026

Getting a “more than one row returned” error when running a SQL.

How can I make sure of what you say ? Is there a method to verify this behavior ?

About Andreas

Questions
1

Answers
5

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Best Answer

Best Answer

Answered By Stella 0 points N/A #98027

Getting a “more than one row returned” error when running a SQL.

What I could suggest is that you drop the tables and deploy your EJB project. Then verify by adding new data.

Or you can delete the old records from the last duplicated entry. 

Don't forget to backup your database before this!

You should not be getting this problem again!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Andreas 230 points N/A #98028

Getting a “more than one row returned” error when running a SQL.

Ouch! Dropping is a drastic action 🙂

I will try the delete option and let you know the outcome!

About Andreas

Questions
1

Answers
5

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Andreas 230 points N/A #98029

Getting a “more than one row returned” error when running a SQL.

Hi Stella, just to give you an update. I deleted the old data and tried a few rounds. It now works perfectly without any errors.

I checked the data and all rows are unique as well.

Thank you for helping me out !

About Andreas

Questions
1

Answers
5

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java

Answered By Stella 0 points N/A #98030

Getting a “more than one row returned” error when running a SQL.

Glad to be of Help! Have a nice day!

About Stella

Questions
0

Answers
48

Best Answers
10

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: Java