Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Not able to create other table

  Asked By: Madeline    Date: Sep 18    Category: Java    Views: 922
  

I am trying to develop
a relational database application. To do this I
will have to create multiple tables.At the
moment I am able to connect to a database called
COFFEEBREAK and create a table called COFFEES. I have
attempted to create a second table in the same database
called COFFEES2. The code compiles and runs but it only
creates the first table. Can anyone tell or show
me how to fix the code as without this I cannot
continue with my application.(Source code is given
below)Thanks everyoneimport java.sql.*;public
class connectiontest { public static void main(
String args[] ) { //Loads JDBC & ODBC
drivers and connects to given database
Connection connection; Statement
statement; String url = System.getProperty("user.dir") +
"\\COFFEEBREAK.mdb"; String conStr = "jdbc:odbc:Driver={Microsoft Access
Driver (*.mdb)};DBQ=" +
url; try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection =
DriverManager.getConnection(conStr);
connection.createStatement(); System.out.println("Connection
Established"); //If connection established
user should see this to inform them
//CREATE 2 TABLES, COFFEES & COFFEES2
try{ String createString; createString = "create
table COFFEES " + "(COF_NAME VARCHAR(32), " +
"SUP_ID INTEGER, " + "PRICE FLOAT, " + "SALES
INTEGER, " + "TOTAL INTEGER)"; String
createString2; createString2 = "create table COFFEES2"+
"(COFFEE_NAME2 VARCHAR (32), "+ "Make," +
"flavour)"; Statement sql =
connection.createStatement(); sql.executeUpdate(createString);
sql.executeUpdate(createString2); } catch(SQLException e){ //If
table exists
continue //insert some values to it String
insertQuery; } } catch (Exception
ex){ System.out.println("Connection Exception: " +
ex); System.out.println("Connection Exception: " + ex); }
}}

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Silvia Chapman     Answered On: Sep 18

At first glance I notice that there is no space
between "COFFEES2" and the following parenthesis. I am
not sure whether that would cause an error or not,
though.You should probably add this to your SQLException
catch block:e.printStackTrace();Try
that first and see if you get an exception  detailing
the problem

 
Answer #2    Answered By: Ty Thompson     Answered On: Sep 18

Thanks for that, there was a problem, it was
given as:<br><br>Connection Established<br>Member table
Created<br><br><br>java.sql.SQLException: [Microsoft][ODBC Microsoft Access
Driver] Syntax
error in field definition.<br>at
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)<br>at
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)<br>at
sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)<br>at
sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314)<br>at
sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:<br>264)<br\
>at Connection3.main(Connection3.java:62)<br>Press
any key to continue . . .<br><br>Does anyone know
what this means and how to fix  it.

 
Answer #3    Answered By: Grady Stewart     Answered On: Sep 18

Looking at your SQL statement:<br><br>create
table COFFEES2 (COFFEE_NAME2 VARCHAR (32), Make,
flavour)<br><br>The Make and flavour fields have no type definition.
See how COFFEE_NAME2 is of type VARCHAR? Each field
you define has to have a type definition similar to
this. I suggestion that you either get a book or do
some research on SQL statements in order to understand
how SQL works.

 
Answer #4    Answered By: Brendan Smith     Answered On: Sep 18

I've now changed that so both make & flavour is
of VARCHAR type. Once this had been done I compiled
and ran the codeand got the following error:
<br><br>Connection Established<br>java.sql.SQLException:
[Microsoft][ODBC Microsoft Access Driver] table  'COFFEES' already
exists.<br> at
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)<br> at
sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)<br> at
sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)<br> at
sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314)<br> at
sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:264)<br> at
connectiontest2.main(connectiontest2.java:49)<br><br>It says the table COFFEES
already exists so I
changed the name of the 2nd table to test but got the
exact same error message.

 
Answer #5    Answered By: Faiza Mian     Answered On: Sep 18

Once a table  is created in a database, any
attempt to create  the table again will fail. Some SQL
servers support clauses like:create table foo if
not exists()You will need to
check your database's documentation to find out if it
supports that syntax (or something
similar).

 
Answer #6    Answered By: Felix Gray     Answered On: Sep 18

It is very common to set up some scripts/programs
that drop all the database  objects you are
programming, so as to avoid this kind of
difficulty.<br><br>You run the "drop" script just prior to re-starting
your tests.

 
Answer #7    Answered By: Sultana Tabassum     Answered On: Sep 18

Naturally you should be VERY careful about dropping a whole table.
Can you say "backup"?

 
Answer #8    Answered By: Hollie Hughes     Answered On: Sep 18

Be careful to not drop that table  on your foot!
Back up! ;-) We are talking development, now.
This is not the production system. We are testing the
table create. Therefore, there should not be any
useable data and no need to do a backup. If we are
working on production data or even shared test databases,
then make the effort to backup the data. But
when you are the one creating the table, feel free to
blast it and start over

 
Didn't find what you were looking for? Find more on Not able to create other table Or get search suggestion and latest updates.




Tagged: