db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Columns of type 'REAL' cannot hold values of type 'CHAR'
Date Wed, 13 Dec 2006 22:54:15 GMT
William B. wrote:
> 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. 
> <http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index> 

Hi William -
So I guess this would be the query associated with the error (see the 
derby.log file to see for sure):

"INSERT INTO salariedWorkers VALUES ( '" +
               socialSecurityNumber + "', '" + weeklySalary + "', '0' )";

and weeklySalary and bonus are both datatype: real so should not be 
quoted in your VALUES clause.






Mime
View raw message