Jump to content
yrstruly

Error With Connecting Do Database

Recommended Posts

HiFor this assignment i create a databse with Acces. when i run the following code i get an error message.Dropping indexes & tables …Could not drop primary key on UserStocks table: [Microsoft][ODBC Microsoft Access Driver] Cannot find table or constrant.Could not drop UserStocs table: [Microsoft][ODBC Microsoft Access Driver] Table ‘UserStocks’ does not exist.Creating tables ………………………..Creating Stocks table with primary key index …Creating Users table with primary key index …Creating UserStocks table …Exception creating UserStocks table: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.Creating UserStocks table primary key index …Exception creating UserStocks index: [Microsoft][ODBC Microsoft Access Driver] Cannot find table or constraint.Database created.An extract of an error further down after running MakeDB.java/* Chapter 11: The MakeDB Class Programmer: Michael Mick Date: December 16, 2004 Filename: MakeDB.java Purpose: To build an initial database for the StockTracker application*/import java.sql.*;import java.io.*;public class MakeDB{ public static void main(String[] args) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url = "jdbc:odbc:StockTracker"; Connection con = DriverManager.getConnection(url); Statement stmt = con.createStatement(); // The following code deletes each index and table, if they exist. // If they do not exist, a message is displayed and execution continues. System.out.println("Dropping indexes & tables ..."); try { stmt.executeUpdate("DROP INDEX PK_UserStocks ON UserStocks"); } catch (Exception e) { System.out.println("Could not drop primary key on UserStocks table: " + e.getMessage()); } try { stmt.executeUpdate("DROP TABLE UserStocks"); } catch (Exception e) { System.out.println("Could not drop UserStocks table: " + e.getMessage()); } try { stmt.executeUpdate("DROP TABLE Users"); } catch (Exception e) { System.out.println("Could not drop Users table: " + e.getMessage()); } try { stmt.executeUpdate("DROP TABLE Stocks"); } catch (Exception e) { System.out.println("Could not drop Stocks table: " + e.getMessage()); } /////////////////////////Create the database tables///////////////////////// System.out.println("\nCreating tables ......................"); // Create Stocks table with primary key index try { System.out.println("Creating Stocks table with primary key index ... "); stmt.executeUpdate("CREATE TABLE Stocks (" + "symbol TEXT(8) NOT NULL " + "CONSTRAINT PK_Stocks PRIMARY KEY, " + "name TEXT(50)" + ")"); } catch (Exception e) { System.out.println("Exception creating Stocks table: " + e.getMessage()); } // Create Users table with primary key index try { System.out.println("Creating Users table with primary key index ... "); stmt.executeUpdate("CREATE TABLE Users (" + "userID TEXT(20) NOT NULL " + "CONSTRAINT PK_Users PRIMARY KEY, " + "lastName TEXT(30) NOT NULL, " + "firstName TEXT(30) NOT NULL, " + "pswd LONGBINARY, " + "admin BIT" + ")"); } catch (Exception e) { System.out.println("Exception creating Users table: " + e.getMessage()); } // Create UserStocks table with foreign keys to Users and Stocks tables try { System.out.println("Creating UserStocks table ... "); stmt.executeUpdate("CREATE TABLE UserStocks (" + "userID TEXT(20) " + "CONTRAINT FK1_UserStocks REFERENCES Users (userID), " + "symbol TEXT(8), " + "CONSTRAINT FK2_UserStocks FOREIGN KEY (symbol) " + "REFERENCES Stocks (symbol))"); } catch (Exception e) { System.out.println("Exception creating UserStocks table: " + e.getMessage()); } // Create UserStocks table primary key index try { System.out.println("Creating UserStocks table primary key index ... "); stmt.executeUpdate("CREATE UNIQUE INDEX PK_UserStocks " + "ON UserStocks (userID, symbol) " + "WITH PRIMARY DISALLOW NULL"); } catch (Exception e) { System.out.println("Exception creating UserStocks index: " + e.getMessage()); } // Create one administrative user with password as initial data String userID = "admin01"; String firstName = "Default"; String lastName = "Admin"; String initialPswd = "admin01"; Password pswd = new Password(initialPswd); boolean admin = true; PreparedStatement pStmt = con.prepareStatement("INSERT INTO Users VALUES (?,?,?,?,?)"); try { pStmt.setString(1, userID); pStmt.setString(2, lastName); pStmt.setString(3, firstName); pStmt.setBytes(4, serializeObj(pswd)); pStmt.setBoolean(5,admin); pStmt.executeUpdate(); } catch (Exception e) { System.out.println("Exception inserting user: " + e.getMessage()); } pStmt.close(); // Read and display all User data in the database. ResultSet rs = stmt.executeQuery("SELECT * FROM Users"); System.out.println("Database created.\n"); System.out.println("Displaying data from databae ... \n"); System.out.println("Users table contains:"); Password pswdFromDB; byte[] buf = null; while(rs.next()) { System.out.println("Logon ID = " + rs.getString("userID")); System.out.println("First name = " + rs.getString("firstName")); System.out.println("Last name = "+ rs.getString("lastName")); System.out.println("Administratvie = "+ rs.getBoolean("admin")); System.out.println("Initial password = "+ initialPswd); // Do NOT use with JDK 1.2.2 using JCBC - ODBC bridge as // SQL NULL data value is not handled correctly buf = rs.getBytes("pswd"); if (buf != null) { System.out.println("Password Object = " + (pswdFromDB=(Password)deserializeObj(buf))); System.out.println(" AutoExpires = "+ pswdFromDB.getAutoExpires()); System.out.println(" Expiring now = "+ pswdFromDB.isExpiring()); System.out.println(" Remaining uses = " + pswdFromDB.getRemainingUses() + "\n"); } else System.out.println("Password Object = NULL!"); } rs = stmt.executeQuery("SELECT * FROM Stocks"); if(!rs.next()) System.out.println("Stocks table contains no records."); else System.out.println("Stocks table still contains records!"); rs = stmt.executeQuery("SELECT * FROM UserStocks"); if(!rs.next()) System.out.println("UserStocks table contains no records."); else System.out.println("UserStocks table still contains records!"); stmt.close(); // closing Statment also closes ResultSet } // end of main() // Method to write object to byte array and then insert into prepared statement public static byte[] serializeObj(Object obj) throws IOException { ByteArrayOutputStream baOStream = new ByteArrayOutputStream(); ObjectOutputStream objOStream = new ObjectOutputStream(baOStream); objOStream.writeObject(obj); // object must be Serializable objOStream.flush(); objOStream.close(); return baOStream.toByteArray(); // returns stream as byte array } // Method to read bytes from result set into a byte array and then // create an input stream and read the data into an object public static Object deserializeObj(byte[] buf) throws IOException, ClassNotFoundException { Object obj = null; if (buf != null) { ObjectInputStream objIStream = new ObjectInputStream(new ByteArrayInputStream(buf)); obj = objIStream.readObject(); // throws IOException, ClassNotFoundException } return obj; }} // end of class

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...