Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12

Thread: SQL exception

  1. #1
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts

    SQL exception

    Hi,

    First time programming JSP as I have to do it for an uni assignment. Basically I have created a register HTML and JSP page:

    Code:
    <!-- Register.html -->
    <HTML>
    <HEAD>
       <TITLE>Register Form</TITLE>
    </HEAD>
    
    <BODY>
       <H1>Hotel Account</H1>
       <FORM>
    	
    
          ACTION="Register"  METHOD="POST">
    
          <PRE>
          Customer Name:             <INPUT TYPE=text NAME=CustomerName>
          Customer No:               <INPUT TYPE=text NAME=CustomerNo>
          Customer Type:             <INPUT TYPE=text NAME=CustomerType>
          Customer Comp:             <INPUT TYPE=text NAME=CustomerComp>
          Customer Address:          <INPUT TYPE=text NAME=CustomerAddress>
          Nationality:  		 <INPUT TYPE=text NAME=Nationality>
          Passport No:   		 <INPUT TYPE=text NAME=PassportNo>
          Travel Agency:         	 <INPUT TYPE=text NAME=TravelAgency>
          Password:                  <INPUT TYPE=text NAME=Password>
          </PRE>
         
          <INPUT TYPE=SUBMIT Value="Submit">
       </FORM>
    </BODY>
    </HTML>
    From there what I am trying to do is insert all the data gathered from the inputs into an DB.

    Now I have decided to do 4 Java classes and 1 servlet inorder to achieve this.

    I have created the account class to use the getter and setter methods:

    Code:
    /*
     * Account.java
     *
     * Created on October 14, 2008, 9:45 PM
     *
     * To change this template, choose Tools | Template Manager
     * and open the template in the editor.
     */
    
    /**
     *
     * 
     */
    public abstract class Account
    {
      String CustomerName, CustomerType, CustomerComp, CustomerAddress, Nationality, PassportNo, TravelAgency, Password;
      int CustomerNo;
      
           
           /**
       * default constructor
       */
      public Account()
      {
        CustomerName = "";
        CustomerNo = 0;
        CustomerType = "";
        CustomerComp = "";
        CustomerAddress = "";
        Nationality = "";
        PassportNo = "";
        TravelAgency = "";
        Password = "";
        
      }
      /**
       * Constructor with parameters
       * @param AccNo int
       * @param cName String
       * @param cAddress String
       * @param bal String
       * @param sDate Date
       */
       
      public Account(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
                     
      {
        CustomerName = cName;
        CustomerNo = cNo;
        CustomerType = cType;
        CustomerComp = cComp;
        CustomerAddress = cAdd;
        Nationality = n;
        PassportNo = pNo;
        TravelAgency = tAgency;
        Password = pass;
       }
      /**
       * Set Account Number
       * @param AccNo int
       */
      public void setCustomerName(String cName)
      {
        CustomerName = cName;
      }
      /**
       * Set Customer Name
       * @param cName String
       */
      public void setCustomerNo(int cNo)
      {
        CustomerNo = cNo;
      }
      /**
       * Set Customer Address
       * @param cAddress String
       */
      public void setCustomerType(String cType)
      {
        CustomerType = cType;
      }
      /**
       * Set Balance
       * @param bal String
       */
      public void setCustomerComp(String cComp)
      {
        CustomerComp = cComp;
      }
      /**
       * Set Start Date
       * @param sDate Date
       */
      public void setCustomerAddress(String cAdd)
      {
        CustomerAddress = cAdd;
      }
    
      public void setNationality(String n)
      {
        Nationality = n;
      }
      
      public void setPassportNo(String pNo)
      {
        PassportNo = pNo;
      }
      
       public void setTravelAgency(String tAgency)
      {
        TravelAgency = tAgency;
      }
       
       public void setPassword(String pass)
      {
        Password = pass;
      }
      
      public void setAllAccountData(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
      {
        CustomerName = cName;
        CustomerNo = cNo;
        CustomerType = cType;
        CustomerComp = cComp;
        CustomerAddress = cAdd;
        Nationality = n;
        PassportNo = pNo;
        TravelAgency = tAgency;
        Password = pass;
      }
      /**
       * Get Account Number
       * @return int
       */
      public String getCustomerName()
      {
        return CustomerName;
      }
      /**
       * Get Customer Name
       * @return String
       */
      public int getCustomerNo()
      {
        return CustomerNo;
      }
      /**
       * Get Customer Address
       * @return String
       */
      public String getCustomerType()
      {
        return CustomerType;
      }
      /**
       * Get Balance
       * @return double
       */
      public String getCustomerComp()
      {
        return CustomerComp;
      }
      /**
       * Get Start Date
       * @return Date
       */
      public String getCustomerAddress()
      {
        return CustomerAddress;
      }
    
      public String getNationality()
      {
        return Nationality;
      }
    
      public String getPassportNo()
      {
        return PassportNo;
      }
    
      public String getTravelAgency()
      {
        return TravelAgency;
      }
    
      public String getPassword()
      {
        return Password;
      }
      
      /**
       * get all account data
       * @return String
       */
      public String getAllAccountData()
      {
       return "CustomerName = " + CustomerName +
            "; CustomerNo = " + CustomerNo +
            "; CustomerType = " + CustomerType +
            "; CustomerComp = " + CustomerComp +
            "; CustomerAddress = " + CustomerAddress +
            "; Nationality = " + Nationality +
            "; PassportNo = " + PassportNo +
            "; TravelAgency = " + TravelAgency +
            "; Password = " + Password;
      }
    I have made an Account DB class to hold customer info:

    Code:
    /*
     * AccountDB.java
     *
     * Created on October 14, 2008, 10:06 PM
     *
     * To change this template, choose Tools | Template Manager
     * and open the template in the editor.
     */
    
    /**
     *
     *
     */
    import java.sql.*;
    /**
     * <p>Title: workshop07</p>
     * <p>Description: Database class</p>
     * <p>Copyright: Copyright (c) 2005</p>
     * <p>Company:ECU </p>
     * @author Leisa Armstrong
     * @version 1.0
     */
    
    
    /**
     * Class to create connection to a database and
     * and demonstrate basic SQL queries
     */
    public class AccountDB
    {
       /**
        * the connection object
        */
       static Connection connection;
       // select odbc driver
       static final String dbDriver =
          "sun.jdbc.odbc.JdbcOdbcDriver";
       static final String dbUrl = "jdbc:odbc:HotelDatabase1";
       // use the BankAccount database
       static final String dbName = "HotelDatabase1";
       // set dbUser to any user on your Windows OS
       static final String dbUser = " ";
       // set dbPassword to Windows password for dbUser
       static final String dbPassword = " ";
    
       /**
        * method to open the connection
        */
       public Connection getConnection()
       {
         System.out.println("Getting Database driver" );
         try
         {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver" );
    
         }
         catch( ClassNotFoundException e )
         {
           System.err.println(
           "Cannot load database driver: for DB2, "
           + "your classpath must include "
           + "SQLLIB\\JAVA12\\DB2JAVA.ZIP." );
          }
    
    
    
          if ( connection != null )
             return connection;
          try {
             connection = DriverManager.getConnection(
                dbUrl, dbUser, dbPassword);
          } catch( SQLException e ) {
             System.err.println(
                "Cannot connect to database: "
                   + "check that Access is running and  "
                   + "the Hotel Database database exists." );
          }
          return connection;
       }
    public ResultSet getAllCustomerInfo() {
                    ResultSet rs = null;
                    try {
                            Statement statement =
                                    getConnection().createStatement();
                            String sql =
                                    "SELECT * FROM Customer";
                        System.out.println( sql );
                            statement.executeQuery( sql );
                            rs = statement.getResultSet();
                    } catch( SQLException e ) {
                            System.out.println(
                                    "SQLException " + e.getMessage() );
                    } finally {
                            return rs;
                    }
            }
     public void addCust( CustomerAccount account )
                    throws SQLException, DBopException
            {
                    int nrows = 0;
                    Statement statement = getConnection().createStatement();
                    String sqlString ="INSERT INTO Customer VALUES ("
                                    + account.getCustomerName()
                                    + ",'"
                                    + account.getCustomerNo()
                                    + "','"
                                    + account.getCustomerType()
                                    + "',"
                                    + account.getCustomerComp()
                                    + ",'"
                                    + account.getCustomerAddress()
                                    + "','"
                                    + account.getNationality()
                                    + "','"
                                    + account.getPassportNo()
                                    + "','"
                                    + account.getPassword()
                                    + ")";
                           
    
                    nrows = statement.executeUpdate( sqlString );
                    if ( nrows != 1 )
                    {
                            throw new DBopException(
                                    "Instead of 1 row, "
                                            + nrows
                                            + " were inserted." );
                    }
                    return;
            }
      public void updateCust( CustomerAccount account )
                    throws SQLException, DBopException
            {
                    int nrows = 0;
                    Statement statement =
                            getConnection().createStatement();
                    String sql =
                            "UPDATE Customer SET "
                            + "CustomerName = "
                            + account.getCustomerName()
                            + ", "
                            //+ "CustomerNo = '"
                            //+ account.getCustomerNo()
                            //+ "', "
                            + "CustomerType = '"
                            + account.getCustomerType()
                            + "', "
                            + "CustomerComp = "
                            + account.getCustomerComp()
                            + ", "
                            + "CustomerAddress = '"
                            + account.getCustomerAddress()
                            + "', "
                            + "Nationality = '"
                            + account.getNationality()
                            + "', "
                            + "PassportNo = '"
                            + account.getPassportNo()
                            + "', "
                            + "Password = '"
                            + account.getPassword()
                            + " WHERE CustomerNo="+account.getCustomerNo();
    
    
                    System.out.println( sql );
                    nrows = statement.executeUpdate( sql );
                    if ( nrows != 1 )
                    {
                            throw new DBopException(
                                    "Instead of 1 row, "
                                            + nrows
                                            + " were updated." );
                    }
                    return;
            }
      }
    A customer account java class for the customer account itself:

    Code:
    /*
     * CustomerAccount.java
     *
     * Created on October 14, 2008, 10:11 PM
     *
     * To change this template, choose Tools | Template Manager
     * and open the template in the editor.
     */
    
    /**
     *
     * 
     */
    public class CustomerAccount extends Account
    {
       public CustomerAccount(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
      {
        super(cName, cNo, cType, cComp, cAdd, n, pNo, tAgency, pass);
        
      }
      
    
    }
    
    
    A DBopexception java class for the exception:
    
    /*
     * DBopException.java
     *
     * Created on October 14, 2008, 10:23 PM
     *
     * To change this template, choose Tools | Template Manager
     * and open the template in the editor.
     */
    
    /**
     *
     * 
     */
    
    public class DBopException extends Exception {
    
       public DBopException() {
          this("Could not complete AccountDB database operation");
       }
    
       public DBopException(String s) {
          super(s);
       }
    }
    And finally a register servlet:

    Code:
    /*
     * Register.java
     *
     * Created on October 14, 2008, 8:51 PM
     */
    import java.io.*;
    import java.text.*;
    import java.sql.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import javax.servlet.jsp.*;
    
    /**
     * 
     * @version 1.0
     */
    
    public class Register extends HttpServlet
    {
         private AccountDB accountDB;
         private Connection connection;
         private Statement statement;
         private CustomerAccount customerAccount;
         private String query;
    
      // set up database connection and create SQL statement
         public void init( ServletConfig config ) throws ServletException
         {
         // attempt database connection
    
              accountDB = new AccountDB();
              accountDB.getConnection();
         }  // end of init method
    
      // process survey response
         protected void doPost( HttpServletRequest request,
                           HttpServletResponse response )
         throws ServletException, IOException
         {
         // set up response to client
            response.setContentType( "text/html" );
            PrintWriter out = response.getWriter();
    
    
         // start XHTML document
            out.println( "<?xml version = \"1.0\"?>" );
    
            out.println( "<!DOCTYPE html PUBLIC \"-//W3C//DTD " +
                       "XHTML 1.0 Strict//EN\" \"http://www.w3.org" +
                       "/TR/xhtml1/DTD/xhtml1-strict.dtd\">" );
    
            out.println(
                       "<html xmlns = \"http://www.w3.org/1999/xhtml\">" );
    
    
    
         // head section of document
            out.println( "<head>" );
    
         // read response by customer
            String CustomerName, CustomerType, CustomerComp, CustomerAddress, Nationality, PassportNo, TravelAgency, Password;
           
           
             CustomerName = request.getParameter( "CustomerName" );
             int CustomerNo = Integer.parseInt(request.getParameter("CustomerNo"));
             CustomerType = request.getParameter("CustomerType");
             CustomerComp = request.getParameter("CustomerComp");
             CustomerAddress = request.getParameter ("CustomerAddress");
             Nationality = request.getParameter ("Nationality");
             TravelAgency = request.getParameter ("TravelAgency");
             PassportNo = request.getParameter("PassportNo");
             Password = request.getParameter("Password");
    
                
              // attempt to process the account information
             try
             {
                if (CustomerName.equals( "" ) ||
                CustomerType.equals( "" ) ||
                Password.equals("")) 
                {
                    out.println( "<H3> Please click the back " +
                              "button and fill in all " +
                              "fields.</H3>" );
                    out.close();
                    return;
                }    
                else
                    {
                    customerAccount = new CustomerAccount(CustomerName,
                                        CustomerNo,
                                        CustomerType,
                                        CustomerComp,
                                        CustomerAddress,
                                        Nationality,
                                        PassportNo,
                                        TravelAgency,
                                        Password);
                    accountDB.addCust(customerAccount);
                 }
                out.println( "<title>Thank you!</title>" );
                out.println( "</head>" );
    
                 out.println( "<body>" );
                out.println( "<p>Thank you account created." );
    
                 // end XHTML document
                out.println( "</p></body></html>" );
                out.close();
    
            }
           
          // if database exception occurs, return error page
               catch ( SQLException sqlException )
               {
                  sqlException.printStackTrace();
                  out.println( "<title>Error</title>" );
                  out.println( "</head>" );
                  out.println( "<body><p>SQLException: Database error occurred. " );
                  out.println( "Try again later.</p></body></html>" );
                  out.close();
               }   
             catch(DBopException dbException)
               {
                  dbException.printStackTrace();
                  out.println( "<title>Error</title>" );
                  out.println( "</head>" );
                  out.println( "<body><p>DBopException: Database error occurred. " );
                  out.println( "Try again later.</p></body></html>" );
                  out.close();
               }
           
         }  // end of doPost method
    
      // close SQL statements and database when servlet terminates
         public void destroy()
         {
         // attempt to close statements and database connection
            try {
               statement.close();
               connection.close();
            }
    
            // handle database exceptions by returning error to client
               catch( SQLException sqlException ) {
                  sqlException.printStackTrace();
               }
         }
    
      } // end class RegisterServlet
    Now the problem is when the project runs and I have inserted all the data once I submit it says SQLException..I have tried other ways but cant find a solution for it..could it be that I have too many classes? IS there a better way to achieve what I want?

    Any help would be appreciated.

    PS. Ignore the comments in the code as they indicate the wrong things.
    Last edited by An Enigman; 10-14-2008 at 05:27 PM.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Hi An Enigman,

    Please warp your code by [code][/code] tags while posting here, so that it'll be easy for one who read it. You may edit your post too.

    PS: Welcome to CF.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for the welcome and sorry about the code.

    I will keep it in mind in the future.

  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Quote Originally Posted by An Enigman View Post
    I will keep it in mind in the future.
    You may edit it now, and which may help you to get some quick helps.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Already have.

  • #6
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    Quote Originally Posted by An Enigman View Post
    Code:
    public class AccountDB
    {
    ... 
            public void addCust( CustomerAccount account )
                    throws SQLException, DBopException
            {
                    int nrows = 0;
                    Statement statement = getConnection().createStatement();
                    String sqlString ="INSERT INTO Customer VALUES ("
                                    + account.getCustomerName()
                                    + ",'"
    
                                    + account.getCustomerNo()
                                    + "','"
                                    + account.getCustomerType()
                                    + "',"
                                    + account.getCustomerComp()
                                    + ",'"
                                    + account.getCustomerAddress()
                                    + "','"
                                    + account.getNationality()
                                    + "','"
                                    + account.getPassportNo()
                                    + "','"
                                    + account.getPassword()
                                    + ")";
                           
    
                    nrows = statement.executeUpdate( sqlString );
                    if ( nrows != 1 )
                    {
                            throw new DBopException(
                                    "Instead of 1 row, "
                                            + nrows
                                            + " were inserted." );
                    }
                    return;
            }
     ...
      }
    Now the problem is when the project runs and I have inserted all the data once I submit it says SQLException..I have tried other ways but cant find a solution for it..could it be that I have too many classes? IS there a better way to achieve what I want?
    if you had taken a look at the sysout log you would have figured out the problem. your insert query is wrong...
    • i'm sure customerName is a string so, it has to be within quotes
    • you've not closed the quote that you've opened for the password column
    • not sure about the customerComp...but, is it numeric?


    yes. there are better ways to do this, you can start by validating user input and then by using prepared statements...(later you can worry about the unclosed statements/resultsets and leaking connections...)
    Last edited by shyam; 10-15-2008 at 12:11 PM. Reason: added witty comment :D
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • Users who have thanked shyam for this post:

    An Enigman (10-15-2008)

  • #7
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for that, CustomerComp is a string.

  • #8
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Also could you please explain the deal with these quotes?

    I dont understand what you mean by:

    # i'm sure customerName is a string so, it has to be within quotes
    # you've not closed the quote that you've opened for the password column

    Could you give me an example?
    Last edited by An Enigman; 10-15-2008 at 02:35 PM.

  • #9
    Senior Coder shyam's Avatar
    Join Date
    Jul 2005
    Posts
    1,563
    Thanks
    2
    Thanked 163 Times in 160 Posts
    you could try printing the sqlString and then running that against your database....that should help you clear up the quoting issues
    You never have to change anything you got up in the middle of the night to write. -- Saul Bellow

  • #10
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    No luck with that either. Still shows up the same error.

  • #11
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I made up a test sql insert statement like this:

    String sql =
    "INSERT INTO Customer VALUES ('Bob', 1, 'Ellen 38', 'No', 'Yes', 'Savings', 'one', 'yo', 'pass')";

    and it created an account..but it doesnt with the get methods..that is very wierd.

  • #12
    New Coder
    Join Date
    Oct 2008
    Posts
    75
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Managed to solve it.

    Thanks for all the help with it.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •