db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "William B." <willte...@yahoo.com>
Subject Columns of type 'REAL' cannot hold values of type 'CHAR'
Date Wed, 13 Dec 2006 21:59:20 GMT
I keep receiving the error message above when I try to add a salary. Please help
   
  import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
   
  public class Workers extends JFrame {
   private Connection connection;
   private Statement statement;
   private ResultSet resultSet;
   private ResultSetMetaData rsMetaData;
   private Container container;
   private JTable table;
   private JTextField input;
   private JButton addSalariedWorker, addCommissionWorker,
      addBasePlusCommissionWorker, addHourlyWorker;
     // constructor Workers
   public Workers()
   {
      super( "Add Staff Members" );
        // The URL specifying the workers database to which this program 
      // connects to using JDBC
    
      String url = "jdbc:derby:workers";  
        // Load the driver to allow connection to the database
      try {
         Class.forName( "org.apache.derby.jdbc.EmbeddedDriver" );
           connection = DriverManager.getConnection( url );
      } 
      catch ( ClassNotFoundException cnfex ) {
         System.err.println( "Failed to load JDBC driver." );
         cnfex.printStackTrace();
         System.exit( 1 );  // terminate program
      }
      catch ( SQLException sqlex ) {
         System.err.println( "Unable to connect" );
         sqlex.printStackTrace();
         System.exit( 1 );  // terminate program
      }
        // if connected to database, set up GUI      
      JPanel topPanel = new JPanel();
      topPanel.setLayout( new FlowLayout() );
      topPanel.add( new JLabel( "Enter query to insert workers:" ) );
        input = new JTextField( 50 );
      topPanel.add( input );
      input.addActionListener(
           new ActionListener() {
 
            public void actionPerformed( ActionEvent e )
            {
               addWorker( input.getText() );
            }
         }
      );
        // create four buttons that allow user to add specific employee
      JPanel centerPanel = new JPanel();
      centerPanel.setLayout( new FlowLayout() );
      
      addSalariedWorker = new JButton( "Add Salaried Worker" );
      addSalariedWorker.addActionListener( new ButtonHandler() );
        addCommissionWorker = new JButton( "Add Commission Worker" );
      addCommissionWorker.addActionListener( new ButtonHandler() );
        addBasePlusCommissionWorker =
         new JButton( "Add Base Plus Commission Worker" );
      addBasePlusCommissionWorker.addActionListener( 
         new ButtonHandler() );
        addHourlyWorker = new JButton( "Add Hourly Worker" );
      addHourlyWorker.addActionListener( new ButtonHandler() );
        // add four buttons to centerPanel
      centerPanel.add( addSalariedWorker );
      centerPanel.add( addCommissionWorker );
      centerPanel.add( addBasePlusCommissionWorker );
      centerPanel.add( addHourlyWorker );
        JPanel inputPanel = new JPanel();
      inputPanel.setLayout( new BorderLayout() );
      inputPanel.add( topPanel, BorderLayout.NORTH );
      inputPanel.add( centerPanel, BorderLayout.CENTER );
        table = new JTable( 4, 4 );
        container = getContentPane();
      container.setLayout( new BorderLayout() );
      container.add( inputPanel, BorderLayout.NORTH );
      container.add( table, BorderLayout.CENTER );
        getTable();
        setSize( 800, 300 );
      setVisible( true );
     } // end constructor Workers
     private void getTable()
   {        
      try {
         statement = connection.createStatement();
         resultSet = statement.executeQuery( "SELECT * FROM workers" );         
         displayResultSet( resultSet );
      } 
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   } 
     private void addWorker( String query )
   {        
      try {
         statement = connection.createStatement();
         statement.executeUpdate( query ); 
         getTable();
      } 
      catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
   } 
     private void displayResultSet( ResultSet rs ) throws SQLException
   {
      // position to first record
      boolean moreRecords = rs.next();  
        // if there are no records, display a message
      if ( !moreRecords ) {
         JOptionPane.showMessageDialog( this, 
            "ResultSet contained no records" );
         return;
      }
        Vector<Object> columnHeads = new Vector<Object>();
      Vector<Object> rows = new Vector<Object>();
        try {
         // get column heads
         ResultSetMetaData rsmd = rs.getMetaData();
      
         for ( int i = 1; i <= rsmd.getColumnCount(); ++i ) 
            columnHeads.addElement( rsmd.getColumnName( i ) );
           // get row data
         do {
            rows.addElement( getNextRow( rs, rsmd ) ); 
         } while ( rs.next() );
           // display table with ResultSet contents
         table = new JTable( rows, columnHeads );
         JScrollPane scroller = new JScrollPane( table );
         container.remove( 1 );
         container.add( scroller, BorderLayout.CENTER );
         container.validate();
        } // end try
        catch ( SQLException sqlex ) {
         sqlex.printStackTrace();
      }
     } // end method displayResultSet
     private Vector getNextRow( ResultSet rs, 
      ResultSetMetaData rsmd ) throws SQLException
   {
      Vector<Object> currentRow = new Vector<Object>();
      
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
         switch( rsmd.getColumnType( i ) ) {
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
               currentRow.addElement( rs.getString( i ) );
               break;
            case Types.INTEGER:
               currentRow.addElement( new Long( rs.getLong( i ) ) );
               break;
            case Types.REAL:
               currentRow.addElement( new Float( rs.getDouble( i ) ) );
               break;
            case Types.DATE:
               currentRow.addElement( rs.getDate( i ) );
               break;
            default: 
               System.out.println( "Type was: " + 
                  rsmd.getColumnTypeName( i ) );
         }
      
      return currentRow;
     } // end method getNextRow
     public void shutDown()
   {
      try {
         connection.close();
      }
      catch ( SQLException sqlex ) {
         System.err.println( "Unable to disconnect" );
         sqlex.printStackTrace();
      }
   }
     public static void main(String[] args) 
   {
      final Workers application = new Workers();
      application.addWindowListener( 
           new WindowAdapter() {
              public void windowClosing( WindowEvent e ) 
            {  
               application.shutDown();
               System.exit( 0 );
            }
         }
      );
   }
 
   // inner class ButtonHandler handles button event
   private class ButtonHandler implements ActionListener {
        public void actionPerformed( ActionEvent event )
      {
         String socialSecurityNumber = JOptionPane.showInputDialog(
            "Employee Social Security Number" );
         String insertQuery = "", displayQuery = "";
           // add salaried employee to table salariedWorkers
         if ( event.getSource() == addSalariedWorker ) {
            double weeklySalary = Double.parseDouble( 
               JOptionPane.showInputDialog( "Weekly Salary:" ) );
            insertQuery = "INSERT INTO salariedWorkers VALUES ( '" +
               socialSecurityNumber + "', '" + weeklySalary + "', '0' )";
            displayQuery = "SELECT workers.socialSecurityNumber, " + 
               "workers.firstName, workers.lastName, " + 
               "workers.employeeType, salariedWorkers.weeklySalary" +
               " FROM workers, salariedWorkers WHERE " + 
               "workers.socialSecurityNumber = " + 
               "salariedWorkers.socialSecurityNumber";
         }
           // add commission employee to table commissionWorker
         else if ( event.getSource() == addCommissionWorker ) {
            int grossSales = Integer.parseInt(
               JOptionPane.showInputDialog( "Gross Sales:" ) );
            double commissionRate = Double.parseDouble(
               JOptionPane.showInputDialog( "Commission Rate:" ) );
            insertQuery = "INSERT INTO commissionWorkers VALUES ( '" +
               socialSecurityNumber + "', '" + grossSales + "', '" +
               commissionRate + "', '0' )";
            displayQuery = "SELECT workers.socialSecurityNumber, " + 
               "workers.firstName, workers.lastName, " + 
               "workers.employeeType, commissionWorkers.grossSales," +
               " commissionWorkers.commissionRate FROM workers, " + 
               "commissionWorkers WHERE workers.socialSecurityNumber=" 
               + "commissionWorkers.socialSecurityNumber";
         }
           // add base plus commission employee to table 
         // basePlusCommissionEmployee
         else if ( event.getSource() == addBasePlusCommissionWorker ) {
            int grossSales = Integer.parseInt(
               JOptionPane.showInputDialog( "Gross Sales:" ) );
            double commissionRate = Double.parseDouble(
               JOptionPane.showInputDialog( "Commission Rate:" ) );
            double baseSalary = Double.parseDouble(
               JOptionPane.showInputDialog( "Base Salary:" ) );
            insertQuery = "INSERT INTO basePlusCommissionWorkers " + 
               "VALUES ( '" + socialSecurityNumber + "', '" + grossSales + 
               "', '" + commissionRate + "', '" + baseSalary + "', '0' )";
            displayQuery = "SELECT workers.socialSecurityNumber, " + 
               "workers.firstName, workers.lastName, workers." + 
               "employeeType, basePlusCommissionWorkers.baseSalary, " +
               "basePlusCommissionWorkers.grossSales, basePlus" + 
               "CommissionWorkers.commissionRate FROM workers, " +
               "basePlusCommissionWorkers WHERE " + 
               "workers.socialSecurityNumber = " + 
               "basePlusCommissionWorkers.socialSecurityNumber";
         }
           // add hourly employee to table hourlyEmployee
         else {
            int hours = Integer.parseInt(
               JOptionPane.showInputDialog( "Hours:" ) );
            double wage = Double.parseDouble(
               JOptionPane.showInputDialog( "Wage:" ) );
            insertQuery = "INSERT INTO hourlyWorkers VALUES ( '" +
               socialSecurityNumber + "', '" + hours + "', '" + wage + 
               "', '0' )";
            displayQuery = "SELECT workers.socialSecurityNumber, " + 
               "workers.firstName, workers.lastName, " + 
               "workers.employeeType, hourlyWorkers.hours, " +
               "hourlyWorkers.wage FROM workers, hourlyWorkers " +
               "WHERE workers.socialSecurityNumber = " + 
               "hourlyWorkers.socialSecurityNumber";
         }
           // execute insert query and display employee info
         try {
            statement = connection.createStatement();
            statement.executeUpdate( insertQuery );   
   
            // display the employee info
            statement = connection.createStatement();
            resultSet = statement.executeQuery( displayQuery );
            displayResultSet( resultSet );
         }
         catch ( SQLException exception ) {
            exception.printStackTrace();
   }
         
      } // end method actionPerformed
     } // end inner class ButtonHandler
  } // end class Workers
   
   
  SQL file
  connect 'jdbc:derby:workers;create=true'
;
  drop table workers
;
drop table salariedWorkers
;
drop table commissionWorkers
;
drop table basePlusCommissionWorkers
;
drop table hourlyWorkers
;
create table workers (
 socialSecurityNumber varchar (30) NOT NULL,
 firstName varchar (30) NOT NULL,
 lastName varchar (30) NOT NULL,
        birthday date NOT NULL,
 employeeType varchar (30) NOT NULL,
 departmentName varchar (30) NOT NULL,
 constraint pk_workers primary key (socialSecurityNumber)
) 
;
create table salariedWorkers (
 socialsecurityNumber varchar (30) NOT NULL,
 weeklySalary real NOT NULL,
 bonus real,
 constraint fk_salariedWorkers foreign key (socialSecurityNumber)
  references workers (socialSecurityNumber)
) 
;
create table commissionWorkers (
 socialSecurityNumber varchar (30) NOT NULL,
 grossSales int NOT NULL,
 commissionRate real NOT NULL,
 bonus real,
 constraint fk_commissionWorkers foreign key (socialSecurityNumber)
  references workers (socialSecurityNumber)
) 
;
create table basePlusCommissionWorkers (
 socialSecurityNumber varchar (30) NOT NULL,
 grossSales int NOT NULL,
 commissionRate real NOT NULL,
 baseSalary real NOT NULL,
 bonus real,
 constraint fk_basePlusCommissionWorkers foreign key (socialSecurityNumber)
  references workers (socialSecurityNumber)
) 
;
create table hourlyWorkers (
 socialSecurityNumber varchar (30) NOT NULL,
 hours int NOT NULL,
 wage real NOT NULL,
 bonus real,
 constraint fk_hourlyWorkers foreign key (socialSecurityNumber)
  references workers (socialSecurityNumber)
) 
;
  
 
  
 

 
---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.
Mime
View raw message