asked on

Invalid datatype wehn doing alter tables in jdbc?

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 :(

msgolez

ASKER

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...

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mayank S

Try running it as a batch using statement.executeBatch ()

msgolez

ASKER

thank you for the reply guys, ill try a couple of things tonite and let you guys how it went.

msgolez

ASKER

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!

CEHJ

:-)

CEHJ

>>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
>>

msgolez

ASKER

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?

CEHJ

>>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

msgolez

ASKER

huh.. didnt even notice that :D

thanks again!