aspose file tools*
The moose likes JDBC and the fly likes SQL Exception when creating table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Exception when creating table" Watch "SQL Exception when creating table" New topic
Author

SQL Exception when creating table

tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
I have a script file with all my commands ready to execute. I am using a small java program to read and execute each line.
I get an sql exception error which you can see below.
I have also used the below link to see if i can find the problem but have had no luck.
Eliacom- MySQL Errno 150, Errno 121, and other Foreign Key Errors. Complete List of Causes and Solutions

script file


error message


Line 25 in DatabaseGenerator is just where the sql command is executed.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30294
    
150

Tom,
You have an ordering problem. The Students table has a foreign key on the Registered table. But the Registered table hasn't been created yet. And you can't move Registered up because it has a foreign key on the students table. (You probably don't need the relationship in both directions.)

THe way to solve this is to create the table without the foreign key first. Then after the Registered table is defined, use an alter statement to add the foreign key.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
Thank you Jeanne, i will have another look at how i can order the creation better and which tables will need to be altered after creation
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1649
    
  14

In general, I'd recommend you build your SQL file and test executing it via your SQL interpreter first. This means you can trap and fix any errors in your SQL, before you add the extra layer of complexity by processing the SQL command file via Java.


No more Blub for me, thank you, Vicar.
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
Ok, so i thought i would try to execute my script file in mySQL workbench
The code executes up to this line

Where it gives the error message


I assume, seeing as the code runs via workbench, the problem is with the file i use to execute each statement?
tom davies
Ranch Hand

Joined: Apr 27, 2012
Posts: 168
All sql code now executes.
I will now need to check it in my java program and see if it executes from within there.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Exception when creating table