?
Solved

Invalid datatype wehn doing alter tables in jdbc?

Posted on 2006-05-25
10
Medium Priority
?
242 Views
Last Modified: 2010-03-31
hello experts...

i have the following code:

            
            statem.executeUpdate( "create table population(cid varchar2(5), pop number, pyear date)" );
            System.out.println("Created populations table...");
            
            statem.executeUpdate( "create table encompassed(cid char(5), con varchar2(40), perc number)" );
            System.out.println("Created encompassed table...");
            
            statem.executeUpdate( "create table ethngroups(cid char(5), grp varchar2(30), perc number)" );
            System.out.println("Created ethngroups table...");
            
            statem.executeUpdate( "create table religions(cid char(5), rel varchar2(30), perc number)" );
            System.out.println("Created religions table...");
            
            statem.executeUpdate( "create table languages(cid char(5), lan varchar2(30), perc number)" );
            System.out.println("Created languages table...");
            
            statem.executeUpdate( "create table border(cid char(5), cou varchar2(40), len number, jus varchar2(40))" );
            System.out.println("Created border table...");
            
            statem.executeUpdate( "create table locatedat(cid varchar2(50), type char(5))" );
            System.out.println("Created locatedat table...");
            
            statem.executeUpdate( "create table provloc(cid char(50), pname varchar2(40))" );
            System.out.println("Created provloc table...");
            
            statem.executeUpdate( "create table cityloc(cid char(50), pid varchar2(50), cname varchar2(40))" );
            System.out.println("Created cityloc table...");
       
                  
                        
            //Apply integrity constraints
            //INSERT MASSIVE AMOUNTS OF XML DATA HERE

            System.out.println("Applying integrity constraints...");
            
            statem.executeUpdate( "alter table population add (constraint pop_fk" +
                                    "foreign key (cid) references cities(id))");
            System.out.println("Altered population table...");
                  
            statem.executeUpdate( "alter table encompassed add (constraint enc_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered encompassed table...");
                  
            statem.executeUpdate( "alter table ethngroups add (constraint ethn_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered ethngroups table...");
                  
            statem.executeUpdate( "alter table religions add (constraint rel_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered religions table...");
                  
            statem.executeUpdate( "alter table languages add (constraint lan_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered languages table...");
                  
            statem.executeUpdate( "alter table border add (constraint bor_fk" +
                                    "foreign key (cid) references countries(ccode))" );
            System.out.println("Altered border table...");
                  
            statem.executeUpdate( "alter table locatedat add (constraint locat_fk" +
                                    "foreign key (cid) references cities(id))" );
            System.out.println("Altered locatedat table...");
                  
            statem.executeUpdate( "alter table provloc add (constraint provloc_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered provloc table...");
                 
            statem.executeUpdate( "alter table cityloc add (constraint cityloc_fk1" +
                                    "foreign key (cid) references countries(ccode)," +
                                    "constraint cityloc_fk2 foreign key (pid)" +
                                    "references provinces(id))");    
            System.out.println("Altered cityloc table...");

well i get to create the tables but it wont let me alter it. i get an invalid datatype from oracle.
help please :(
0
Comment
Question by:msgolez
  • 5
  • 4
10 Comments
 

Author Comment

by:msgolez
ID: 16766411
oh there are more tables but i only posted the relevant ones. when i run the creates/alter as a script on the sqlplus itself, everyhing works...
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 16767464
Some of those brackets aren't necessary and you have spacing problems. e.g.

>>
statem.executeUpdate( "alter table languages add (constraint lan_fk" +
                              "foreign key (cid) references countries(ccode))");
>>

should be

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16767469
Try running it as a batch using statement.executeBatch ()
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:msgolez
ID: 16771062
thank you for the reply guys, ill try a couple of things tonite and let you guys how it went.
0
 

Author Comment

by:msgolez
ID: 16776553
should be

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");

actually, it was the space between the foreign key keyword and (cid) that was messing it up. thanks!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16776613
:-)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16776616
>>actually, it was the space between the foreign key keyword and (cid) that was messing it up.

No, it would have been the *lack* of space before 'foreign key' that was messing it up

>>
statem.executeUpdate( "alter table encompassed add (constraint enc_fk" +
                              "foreign key
>>
0
 

Author Comment

by:msgolez
ID: 16776638
im sorry but you kinda threw me off...

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");

isnt there a space after 'lan_fk'? so when the string is built it should be ok right?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16776652
>>isnt there a space after 'lan_fk'? so when the string is built it should be ok right?

There wasn't in your original, no
0
 

Author Comment

by:msgolez
ID: 16776668
huh.. didnt even notice that :D

thanks again!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

807 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